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 Planning & Analysis

Office Leasing Strategy in the New Normal

With companies scrambling to determine Return to Office (RTO) strategies amid ongoing uncertainties, increased employee desire/demand for flexibility and even fully remote opportunities, management should no longer assume the next office lease will be anything like the last negotiation. Before thinking through typical considerations associated with office leasing strategy (e.g., location, lease term, layout and… View Article

January 27, 2022Jason Yu

Financial Planning & Analysis

Lessons Learned from Building Dashboards in Tableau

As data becomes more readily available than ever before, we’re met with the challenge of drawing easily digestible and meaningful insights from a sea of numbers. Fortunately, wherever demand exists, the market scrambles to find a solution: data visualization tools. These platforms can be your new best friends, and with them, you can create the… View Article

January 18, 2022Zara Hovasapyan

Entertainment

Building By-Title Ultimates: Best Practices from the Field

An ultimate is an entertainment industry-specific forecasting model that is an all-encompassing P&L from a title perspective. In other words, for a specific entertainment asset (e.g., film, TV show): What are all its potential revenue streams? What are all its associated costs? How profitable do we expect it to perform over its lifecycle? Ultimate forecasting… View Article

January 3, 2022Jaime Raisch

See All

Back to Insights