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.

Financial Planning & Analysis

Risks in a Rising Rate Environment

In today’s economic environment, macroeconomic rates – inflation, Fed Funds, interest rates, Prime and LIBOR, for example – are increasing across the board, pushing production costs higher and straining the wallets of consumers. For companies, one of the most obvious consequences is the higher interest that they will need to pay to service their debt…. View Article

July 6, 2022Danny Lee

M&A Due Diligence & Transactions

M&A Sell Side Transaction Support: Keys to Success

I was recently deployed into the sell side of an M&A transaction involving a high-growth company in the entertainment sector. This role, like other transaction support roles I have performed, involved a lot of granular-level data analysis and modeling. But the function of an M&A sell-side transaction support professional is always consistent: Collaborate with various… View Article

June 22, 2022Chris Routhe

Financial Planning & Analysis

Five Signs You Need to Update Your Headcount Management

For most companies, headcount is one of the most important assets, and more often than not, it is also the single largest expense. Proper management of headcount is essential to achieving a company’s growth objectives or cost savings targets. Given the vital importance of headcount to the success of any business, one would expect that the… View Article

June 8, 2022Chris Kirk

See All

Back to Insights