Skip to main content

Year Over Year calculation using Pivot Table



Instead of creating a formula outside of the pivot table, you can do this inside the pivot table.

Start from the image with column D empty. Drag Revenue a second time to the Values area.

Look in the Columns section of the Pivot Table Fields panel. You will see a tile called Values that appears below Date. Drag that tile so it is below the Date field. Your pivot table should look like this:

This pivot table has customers down the left side. Across the top are four columns: Sum of Revenue for 2021, 2022. Then Sum of Revenue2 for 2021 and 2022.

Double-click the Sum of Revenue2 heading in D4 to display the Value Field Settings dialog. Click on the tab for Show Values As.

Change the drop-down menu to % Difference From. Change the Base Field to Date. Change the Base Item to (Previous Item). Type a better name than Sum of Revenue2 - perhaps % Change. Click OK.

In the Value Field Settings dialog, choose the second tab, called Show Values As. In the top drop-down menu, choose % Difference From. The Base Field should be Date. The Base Item should be (previous).

You will have a mostly blank column D (because the pivot table can't calculate a percentage change for the first year. Right-click the D and choose Hide.

Labels:

Comments

Post a Comment

Popular posts from this blog

Pivot table for Each item in Report Filter - Hidden Feature

The pivot table below shows products across the top and customers down the side. The pivot table is sorted so the largest customers are at the top. The Sales Rep field is in the report filter. If you open the Rep dropdown, you can filter the data to any one sales rep. This is a great way to create a report for each sales rep. Each report summarizes the revenue from a particular salesperson‘s customers, with the biggest customers at the top. And you get to see the split between the various products. The Excel team has hidden a feature called Show Report Filter Pages. Select any pivot table that has a field in the report filter. Go to the Analyze tab (or the Options tab in Excel 2007/2010). On the far left side is the large Options button. Next to the large Options button is a tiny dropdown arrow. Click this dropdown and choose Show Report Filter Pages.... Excel asks which field you want to use. Select the one you want (in this case the only one available) and click OK. Over the next few...
Post a Comment
Read more

Power Query - Excel's most powerful tool

Power Query cleans and transforms data You add your data sources (Excel tables, CSV files, database tables, webpages, etc.) Press buttons in the Power Query Editor window to transform your data. Output that data to your worksheet or data model (PowerPivot) that is ready for pivot tables or reporting. Power Query is like a machine because once you have your query setup, the process can be repeated with the click of a button (refresh) every time your data changes. If you have used macros to transform your data, you can think of this as  a much easier alternative to VBA that does NOT require coding . Common Data Tasks Made Easy Do you work with data that has been exported from a system of record?  This could be a general ledger, accounting, ERP, CRM, Salesforce.com, or any reporting system that contains data. If so, you probably spend a lot of time transforming or re-shaping your data to create additional reports, pivot tables, or charts. These data transformations could include ...
Post a Comment
Read more

Data Model an easy substitute for Vlookup

  you have a data set with product, date, customer, and sales information. The IT department forgot to put sector in there. Here is a lookup table that maps customer to sector. Time for a VLOOKUP, right? There is no need to do VLOOKUPs to join these data sets if you have Excel 2013 or newer. These versions of Excel have incorporated the Power Pivot engine into the core Excel. (You could also do this by using the Power Pivot add-in for Excel 2010, but there are a few extra steps.) In both the original data set and the lookup table, use Home, Format as Table. On the Table Tools tab, rename the table from Table1 to something meaningful. I’ve used Data and Sectors. Select one cell in the data table. Choose Insert, Pivot Table. Starting in Excel 2013, there is an extra box, Add This Data to the Data Model, that you should select before clicking OK. The Pivot Table Fields list appears, with the fields from the Data table. Choose Revenue. Because you are using the Data Model, a new line a...
Post a Comment
Read more