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:

excel tips.png

 

  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.
excel tip 2.png

 

  1. Next, go to Pivot Table Option> Group> Group By Field.
excel pivot table.png

 

  1. Group the dates as Months, Quarters and Years.
excel tip 3.png

 

  1. When the grouping is done, the line items, in this case, “sales per day,” will be grouped under months, quarters and years.
excel tip 4.png

 

  1. Next, remove the dates to leave the quarters and the year on the pivot table.
excel tip 5.png

 

  1. Now you can rearrange the fields and bring in the Customer & Sales data — and see your data in years and quarters.
excel tip 6.jpg

 

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.

8020 Consulting in the News

8020 Consulting Congratulates MarVista Entertainment on 15 Years

8020 Consulting celebrates MarVista Entertainment on 15 years of business success—as featured in the October 10, 2018, edition of The Hollywood Reporter. We would like to congratulate Fernando, Tony, Darrell, and the entire awesome team at MarVista, and we look forward to many more years of successful partnership.

October 15, 2018Kelly Patchett

Financial Reporting & Accounting

New Leasing Standard Implementation: Creating an ASC 842 Project Plan

The accounting community has been discussing the new leasing standard (ASC 842) since February of 2016. If your company hasn’t already developed an implementation plan, then now is the time. The new standard takes effect for public companies with annual periods beginning after Dec. 15, 2018 and for all other entities for annual periods beginning… View Article

October 11, 2018Susan Weikel

Financial Planning & Analysis

Why Building FP&A Infrastructure is Critical for Growing Organizations

A well-structured Financial Planning & Analysis (FP&A) team can facilitate the direction of a company, leveraging organizational strategy to guide departmental objectives. Think of FP&A as the financial nucleus of an organization: it keeps the movement and momentum of the organization synchronized—both financially and operationally.

October 2, 2018Kristin Jue

See All