0Did You know You can Compare Year-on-Year& Performance Using Pivot Table?
Yes. Just drag-&-drop and Compare Year-on-Year& Performance Quickly.
& Year-on-Year simply means Periodic. As per the information in the Dataset, You could do Weekly &/OR Monthly &/OR Quarterly &/OR Any Other Frequency
Please review the following screenshot of a PivotTable:
As evident in the above screenshot, the Pivot Table includes Sales Achieved by 4 Salespersons - Manish Pandey, M.S. Dhoni, Shreyas Iyer, Virat Kohli - for 3 Years - 2016, 2017, 2018.
To analyse/compare Performance, identify Trend/Pattern, what if You also want to Quickly determine Year-on-Year Performance for the 4 Salespersons?
Here, Quickly is the Key Word.
Consider the following screenshot. Please note last column - Sum of SALES2. This column Compares performance with Previous Year.
i.e. Sales Achieved in 2017 is Compared with Sales Achieved in 2016 and Sales Achieved in 2018 is Compared with Sales Achieved in 2017
This was calculated in PivotTable itself. Isn't this really helpful for better analysis!
$in the last column, Custom Formatting was used to change color of negative % to red
Q) How to compare periodic performance?
A) PivotTable before Year-on-Year Performance is compared.
PivotTable Fields are organized as follows:
Step 1: Click on any cell in the column - Sum of SALES2.
Step 2: Right-click
Step 3: Click Value Field Settings
Value Field Settings dialogue box will open
Step 4: As evident in the above screenshot, in Show values as select % Difference From
In Base Field: select Order Date
In Base Item: select previous
Step 5: Click OK
Result:
To change the color of negative % to red:
Step 6: Select column - Sum of SALES2
Step 7: Press Ctrl + 1 to open Format Cells dialogue box
Step 8: In Number > in Custom, type: 0.00%;[Red]-0.00%
Acknowledgement → MyExcelOnline
Source + to learn more, click → Microsoft Official Website
