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. 

Contact Us

Categorized in:

similar articles

Learn to think and approach problems like our financial consultants.

Financial Reporting & Accounting

Key Considerations for Selecting ASC 842 Lease Accounting Software

FASB’s “new” lease accounting standard, ASC 842, has been in the works for several years, though the final effective date for non-public companies has been delayed several times. (The most recent news is that ASC 842 Lease Accounting will be effective for fiscal years beginning after December 15, 2021, and interim periods within fiscal years… View Article

September 24, 2020Mark Christian

Financial Reporting & Accounting

Softrax Revenue Manager: 10 Tips for Managing a Successful Implementation

Throughout my career, I have led a variety of ERP system implementations and launched software solutions for domestic and global public and private companies with $2M to $6B in annual revenue. However, I recently finished my first project leading an implementation of Softrax Revenue Manager. Spoiler alert: though we had some minor challenges due to… View Article

September 22, 2020Dan Lauten

Financial Reporting & Accounting

What You Should Know About Project Accounting

During a prior engagement, I spent several years as the CFO of a construction company during a rapid growth phase. One of the most valuable lessons from that opportunity was learning how to implement and utilize project accounting, a specific type of accounting used to track the expenses and revenue of a standalone project. Project-based… View Article

September 17, 2020Gregory Gorman

See All