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

Improve your Excel Productivity with these Shortcuts and formulas

I have given below 45 tips and tricks to improve your productivity while working in Excel. Useful Keyboard Shortcuts 1.  To format any selected object , press ctrl+1 2.  To insert current date , press ctrl+; 3.  To insert current time , press ctrl+shift+; 4.  To repeat last action , press F4 5.  To edit a cell comment , press shift + F2 6.  To autosum selected cells , press alt + = 7.  To see the suggest drop-down in a cell , press alt + down arrow 8.  To enter multiple lines in a cell , press alt+enter 9.  To insert a new sheet , press shift + F11 10.  To edit active cell , press F2 (places cursor in the end) 11.  To hide current row , press ctrl+9 12.  To hide current column , press ctrl+0 13.  To unhide rows in selected range , press ctrl+shift+9 14.  To unhide columns in selected range , press ctrl+shift+0 15.  To recalculate formulas , press F9 16.  To select data in current region , press ctrl+shift+8 ...
Post a Comment
Read more

Data Analytics

Introduction to Data Analytics What is Data Analytics? Data Analytics is the process of exploring and analyzing large data sets to help data driven decision making.                         Analyze Data        Decision Making Definition Data when suitably filtered and analysed along with other related Data Sources and a suitable Analytics applied can provide valuable information to various organizations, industries, business, etc. in the form of prediction, recommendation, decision and the like. Applications of Data Analytics Finance & Accounting, Business analytics, Fraud , Healthcare, Information Technology, Insurance, Taxation , Internal Audit, Digital forensic, Transportation, Food, Delivery, FMCG, Planning of cities, Expenditure, Risk management, Risk detection, Security, Travelling, Managing Energy, Internet searching, Digital advertisement , etc. Real life examples of Data Analytics 1. ...
Post a Comment
Read more

Introduction to Power BI and Power BI Desktop

  Introduction to Power BI and Power BI Desktop 1. What is Power BI 2. Power BI Desktop Installation 3. Power BI Desktop User Interface Power BI Desktop Installation Link https://www.microsoft.com/en-us/download/details.aspx?id=58494 Please Subscribe the Channel for Updates on new Tutorial videos https://www.youtube.com/channel/UCW_euuHC79CPXuwUDoqT5Rg Books https://www.amazon.in/Punit-Prabhu/e/ ... Business/Consulting/Corporate /Individual Training solutionsformso@gmail.com
Post a Comment
Read more