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

A Brief Primer on Financial Data Management and Reporting

During my recent engagement with a leading management consultancy firm, I discovered an issue that is quite common among complex organizations: over-customization in financial data management, data tagging and financial report creation. This over-customization often manifests itself in financial reporting views that ultimately fail to correspond to GL data hierarchies. The issue is exacerbated by… View Article

March 15, 2023Chris Routhe

Business Advisory

A Look at Team Assessment Projects

As 8020 Consultants, we focus on issues that produce provable results measured by an increase in our Clients’ bottom line. While financial project execution and interim financial management are our bread and butter, sometimes, operational improvement goals bring our attention to the softer side of a corporation – its people. In the cases where the… View Article

March 8, 2023Ellen Vayner

Financial Planning & Analysis

Finance and Operations: The Path to Strategic Alignment

When thinking of Finance and Operations, envision two sides of the same coin. Operations executes the company mission and strategic goals while consistently adapting to ever-changing global dynamics. Finance assures resources are allocated properly to these same strategic goals, while working as the gatekeepers of the organization’s financial health. These two departments are more interrelated… View Article

March 1, 2023Romesh Jayawardene

See All

Back to Insights