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 Reporting & Accounting

How to Improve Accounting Processes Using ESOAR (Part 1)

Organizations pursue process improvements to achieve a combination of higher efficiency, higher quality or better accuracy within their operations. If you’re struggling with how to improve accounting processes, you might take advantage of ESOAR, a process improvement methodology that can help you drive long-term value. When approaching processes using this methodology, one should: Eliminate wasteful… View Article

July 22, 2021Justin Vu

Financial Planning & Analysis

Accounting & Finance as a Strategic Business Partner or: 3 Ways to Shift from Bean Counter to Bean Grower

The bean counter is dead. Long live the bean grower. Finance and Accounting professionals’ role within the corporate ecosystem over the decades has evolved from the proverbial bean counter to emerge as a bean grower. No longer is the finance team viewed as the pocket-protector-sporting, 10-key-toting, necessary annoyance relegated to the back rooms of the… View Article

July 14, 2021Jo Ann Eilers

Project Management

About Project Scope Management in Finance and Accounting Projects

A project’s scope includes everything needed to get from the objectives to the results – all the work required to complete the project’s deliverables. “Project Scope Management” is the discipline composed of the processes required to ensure that a given project is on track to complete successfully. It involves defining and controlling what is included… View Article

June 29, 2021Olga Christodoulides

See All

Back to Insights