UrbanPro
true

Learn Microsoft Excel Training from the Best Tutors

  • Affordable fees
  • 1-1 or Group class
  • Flexible Timings
  • Verified Tutors

Search in

Hidden Gems of MS Excel - Compare Year-on-Year Performance Using Pivot Table

Ankur Sharma
10/12/2019 0 0

Did 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

0 Dislike
Follow 2

Please Enter a comment

Submit

Other Lessons for You

Excel Tip: Conditional Formatting To Highlight First Value Among Duplicates.
In Column “C”, I have product list and some of them are duplicates. My requirement is to format with background color of green for 1st found value among the duplicates. To solve this, I used...

Rand() Function
Purpose: Get a random number between 0 and 1. Return Value: Any decimal number between 0 and 1. Syntax: =RAND() Usage: RAND() function will returns a number between 0 and 1. For example, = RAND()...

Shortcut of Excel for Selection
Select the whole column: CTRL + SPACE Select the whole row: SHIFT + SPACE Select table: SHIFT + CTRL + SPACE bar Select visible cells only: ALT + ; Select entire region: CTRL + A Select range from...


Excel-Select All Data/Columns/Rows with One Click
You might know how to select all by using the Ctrl + A shortcut, but few know that with only one click of the corner button, all data/Columns/Rows will be selected in seconds.
X

Looking for Microsoft Excel Training Classes?

The best tutors for Microsoft Excel Training Classes are on UrbanPro

  • Select the best Tutor
  • Book & Attend a Free Demo
  • Pay and start Learning

Learn Microsoft Excel Training with the Best Tutors

The best Tutors for Microsoft Excel Training Classes are on UrbanPro

This website uses cookies

We use cookies to improve user experience. Choose what cookies you allow us to use. You can read more about our Cookie Policy in our Privacy Policy

Accept All
Decline All

UrbanPro.com is India's largest network of most trusted tutors and institutes. Over 55 lakh students rely on UrbanPro.com, to fulfill their learning requirements across 1,000+ categories. Using UrbanPro.com, parents, and students can compare multiple Tutors and Institutes and choose the one that best suits their requirements. More than 7.5 lakh verified Tutors and Institutes are helping millions of students every day and growing their tutoring business on UrbanPro.com. Whether you are looking for a tutor to learn mathematics, a German language trainer to brush up your German language skills or an institute to upgrade your IT skills, we have got the best selection of Tutors and Training Institutes for you. Read more