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

Avoiding Common Pitfalls in Your Annual Planning Cycle

A proper financial planning and analysis process results in faster decision making, the mitigation of revenue and cost risks and performance optimization for the entire organization. Upon entering an annual planning cycle, it is critical not to take for granted the obvious questions that can provide key insights to financial assumptions in your model. And more… View Article

November 16, 2022Casandra del Carmen

Financial Systems

IBM Cognos TM1 vs. Planning Analytics: A Guide to the Transition & New Features

Planning Analytics is the new buzz word for organizations making the change to the latest version of Cognos TM1, but what is the buzz all about? The answer starts with the name: Planning Analytics, or “PA,” is the new name for TM1. In terms of the differences between IBM Cognos TM1 vs. Planning Analytics, much… View Article

November 9, 2022Kieran Chatulani

Business Stabilization

A Helpful Business Continuity Plan Checklist

For strategic business planners, COVID provided many lessons as the ultimate stress test, bringing issues seemingly from all angles. The scale and length of the disruptions likely stretched many a business continuity plan beyond their limits in a very short amount of time. It is imperative that companies redefine the breadth of their planning to… View Article

October 26, 2022Jim Hliboki

See All

Back to Insights