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.

Interim Financial Management

Prepping for a Successful Series A Funding Round

If you are the CEO or CFO of a start-up company that has successfully taken a business concept and built it into an operating company: hats off to you! You may now be looking externally for significant assistance to take your company to the next level of growth, which usually entails an external round of… View Article

December 1, 2020Danelle Larsen

Business Stabilization

Be Prepared: Business Continuity Planning

Business continuity planning involves identifying essential & non-essential parts of your business to help you navigate business disruption.

November 19, 2020Kendra Shipley

Manufacturing Operations Finance

Inside Preparing a Manufacturing Company for a Transaction

Just like in other industries, preparing for a manufacturing transaction is challenging. Setting up a company for a transaction can involve steps like creating GAAP-compliant financial statements, ensuring proper allocation methods and building financial projections with hopefully increasing margins, tax and legal issues. (Not to mention leading a cross functional team of investment bankers, tax… View Article

November 17, 2020Prashant Bansal

See All