Excel Tips

Excel Tips: How to Consolidate and Convert Dates to a Standard List or Pivot Table

Many times, we deal with datasets that are more granular than what we desire. Take “sales by day” as an example. Most companies prefer to look at the sales data by month or quarter. Follow these quick steps to make the conversion:

  1. In order to summarize the sales information by year and quarter, first highlight the data and create a pivot table. Then select the Date field.
  1. Next, go to Pivot Table Option> Group> Group By Field.
  1. Group the dates as Months, Quarters and Years.
  1. When the grouping is done, the line items, in this case, “sales per day,” will be grouped under months, quarters and years.
  1. Next, remove the dates to leave the quarters and the year on the pivot table.
  1. Now you can rearrange the fields and bring in the Customer & Sales data — and see your data in years and quarters.

I hope you find this tip useful! Interested in more Excel solutions? Read about a powerful and flexible BI toolset — that you may own and not realize — in the blog, The Best New Business Toolset is Built into Microsoft Excel.

Keep in mind, if you would like to use more advanced capabilities in Excel to make an immediate impact on your organization, 8020 Consulting is here to help. Just click on the button below to connect with us. 

Categorized in:

similar articles

Learn to think and approach problems like our financial consultants.

M&A Due Diligence and Transaction Support

Exploring Ongoing Changes in Licensing and Acquisition Management

The nature of licensing and acquisition deals has been changing. The increased pressure for liquidity has driven many companies to consider the sale or licensure of assets. This article focuses on elements of deal structure, due diligence and integration management that an acquiring company should consider when approached by an interested seller. Shifts in Deal… View Article

February 18, 2021Danelle Larsen

Financial Planning and Analysis

Notes from the Field: Cash Flow Crisis Management

One of the things I love about my job – and indeed one of the reasons I pursued a career in business – is that business is as much an art as a science. As a consultant, I am often brought in to put out fires, whether it is helping a company through a major… View Article

February 10, 2021Jorge Lopez

Financial Planning and Analysis

Why is Strategic Planning Important?

Have you ever wondered, “Why is strategic planning important?” It might help to imagine taking a vacation without knowing where you are going or how you are going to get there. With some luck, that vacation could be fun and maybe even productive, but generally speaking, it would be a generally risky proposition. Strategic planning… View Article

February 3, 2021Kendra Shipley

See All