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 Systems

Improve Your BI Implementation Strategy with These Steps

Good data is a critical component for any business looking to grow and become more successful in the long term. One of the most common drains on an FP&A team’s time is wrestling with various data to extract the main drivers and actionable insights to improve decision making. Having the right systems in place will… View Article

September 14, 2021Ebrahim Allana

Financial Planning & Analysis

Crisis Management in Finance: The Importance of Flexibility

Some of the largest challenges that many of us have faced in our careers occurred during the COVID-19 pandemic. At the start of the pandemic, I was leading finance and accounting for a middle-market luxury home furnishings company, which experienced a quick drop in sales in late March and April of 2020. This instance of… View Article

September 1, 2021Bob Clark

Financial Planning & Analysis

Creating a Rolling Forecast Planning Environment: Changing Mindsets

Change is hard. When it comes to a business’s financial planning initiatives, that is never more evident. Annually, many companies, regardless of industry or size, still usurp three to six months of their internal resources’ time through the traditional compilation of an annual operating and capital expenditure budget. Often, by the time the final iteration… View Article

August 25, 2021Jo Ann Eilers

See All

Back to Insights