Pivot Table Automation with Calculated Field and Calculated Item (When and How to Use Each)

Course Alert:
If you would like to learn in detail, how to calculate income statement variances and the impact they have on sales $, profit $ and profit margin % and ebitda %, and how to explain performance vs budget and prior periods, click on the link for a detailed video course (at a special price). You will also learn how to analyse and present the results of the variances to management and will be able to download solved variance calculation Excel templates. https://ebitda.thinkific.com/courses/...

What are Pivot Table Calculated Fields?
Calculated Fields are calculations within the Excel Pivot Tables based on the fields or columns already available in the data. You can create many calculations inside the pivot table so that when you slice and dice or update the pivot table, the calculations also get updated with it.

What are Calculated Items?
Calculated items represent Pivot table calculations of the data inside each field. So for example, if you have Calendar month, Sales and Standard margin as three fields available in the data set. Any calculation at the filed level such as std margin % calculated from Sales and Std margin field is done by clicking on "Calculated Fields" on the Fields, Items and Sets button of the Pivot Table Analyze menu. However, any calculations at the level of the data itself, for example calendar month Feb minus Jan is done by clicking on the Calculate Item selection of the same menu.

Why is Calculated Item grayed out or turned off sometimes?
It matters what field or cell you have selected on the Pivot Table as it impacts turning On or Off of the Calculated Item feature. If you have selected a Values cell, then Calculated Field will be available but Calculated Item will be greyed out. In order to make the Calculate Item option available you will need to select one of the Cells from the Rows section of the Pivot Table.

Can we use Formulas when Calculating Fields and Items?
Yes. this is exactly what the Calculated Fields and Calculated Items are for. However, the application of formulas is not entirely the same as the formulas in regular excel cells. Some formulas do not work at all and some formulas do not work the same way as they would work in a regular spreadsheet cell.

How to use IF statement in Calculated Fields and Items
You can use the IF Statement with the syntax as normal in the Calculated Fields and Items calculation. Look at the video where I shared how I used the IF function to calculate sales commission %, and then nested AND function with the IF function to calculate a conditional bonus calculation.

What does List Formula do in a Pivot Table Analyze Menu
The list Formula option creates a separate spreadsheet and shows all the Calculated Fields and Calculated items used in a pivot table. This helps you to see any time the calculated fields which are not originating from the raw data set. This is also very helpful when you are reviewing a pivot table file that was prepared by someone else who included calculated items and calculated fields in the file.

I think using advanced pivot table functions such as Calculated Fields and Calculated Items help the Excel users significantly in being productive and creating and refreshing reports quickly with automated calculations. This saves time and avoids chances of mistakes when formulas are maintained outside of the pivot table calculations.

Hope you find the information in the video helpful. If you like to watch more videos in accounting, financial analysis and controller ship, videos that help you directly in doing your job, subscribe to my channel. If you liked the video, I would love if you could LIKE it and leave a comment. If you have any questions or feedback, again leave a comment. Lets stay connected at #learnaccountingfinance #pivottable # calculatedfield # calculateditem