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

Arrange and View Multiple Worksheets at Once in Excel
Arrange and View Multiple Worksheets at Once in Excel This feature allows you to view multiple worksheets at once from the same workbook at the same time. You need to follow beneath simple...

Computer Basic.
If you know the design of table. Then go to second steps: 1) If you type a long data in a single cell or some other data in next cell, problem arise that the first cell data is not showing clear. Then...

Presentation Perfect Reports
Presentation Perfect Reports. Why Presentable, neatly crafted Reports makes a job more perfect and easy for Business Analyst who is going make decisions out of the information structured in reports. Many...

Arrange and View Multiple Worksheets at Once in Excel
Arrange and View Multiple Worksheets at Once in Excel This feature allows you to view multiple worksheets at once from the same workbook at the same time. You need to follow beneath simple...

Macros/VBA Function - Format
Hi VBA/Macro Learners, Today we will see how to use FORMAT Function in VBA to get modify the format of Numbers,Dates,Times & String. Syntax: FORMAT(expression,format,,) 'Ex1: Modify the Format...
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