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

How to create a Waterfall Chart

Download Example Waterfall chart file from below link https://drive.google.com/file/d/17OKYxHKzT8NxWM0FuPqEb26ntzQqa29_/view?usp=sharing How to Create a Waterfall Chart in Excel If you want to build a waterfall chart of your own, we’ve got the step-by-step instructions for you. Although Excel 2016 includes a waterfall chart type within the chart options, if you’re working with any version older than that, you will need to construct the waterfall chart from scratch.  Step 1: Create a data table Let’s start with a simple table like annual sales numbers for the current year. You will see in the table below that the sales amounts vary for each month. Some months will have positive sales growth, while others will be negative.     Insert three additional columns to your Excel table to represent the movement of the columns on the waterfall chart. The base column will represent the starting point for the fall and rise of the chart. You will input all the negative numbers fro...
Post a Comment
Read more

Send Bulk Email from Excel for Outlook

  Download the File from below Link https://drive.google.com/file/d/1tcb4lzNFgEfDKsvQqCW05sgoiGFEhqcK/view?usp=sharing Instructions are given in the image below. Save the File as Excel Macro - Enabled workbook (.xlsm) Use this file to send bulk emails at a time i personally have sent more than 2000 bulk emails at a time. Error may occur if email id typed contains space etc. Only one email id one cell.
Post a Comment
Read more

Sort Left To Right

So you are stuck rearranging the columns every day. You can reorder the columns with a left-to-right sort. Add a new row above the data. Type numbers to represent the correct sequence for the columns. Select Data, Sort. In the Sort dialog, click the Options... button and choose Sort Left to Right. Click OK. Specify Row 1 in the Sort By dropdown. Click OK. The problem: The column widths do not travel with the columns. But it is easy to select the data and Press  Alt+O ,  C ,  A  or select Home, Format, Column, AutoFit
Post a Comment
Read more