Microsoft Excel Tutorial - Adding a Calculated Field to Excel pivot tables.
Todd wants to calculate (12/52)*Sales on each subtotal row in a pivot table.
Bill shows how to add a calculated field to a pivot table and then uses some formatting to show the field only on the subtotal rows.
To download the data: [ Ссылка ]
In this episode, we're tackling a common issue that many Excel users face - adding a calculation to each pivot table subtotal row. Our viewer, Todd, has sent us a pivot table with two items in the row fields - Person and Project. He wants to calculate 12 divided by 52, times sales at each subtotal row. However, the current solution of having a formula outside of the pivot table is not ideal as it will cause issues when new projects are added. So, I'm here to show you a better solution.
First, select a cell inside the pivot table, preferably on the Sum of Sales heading. Then, go to the pivot table Analyze tab and click on Fields, Items & Sets. From there, we will create a new Calculated field. Now, the big question is - is this a bonus or an allocation? Let's go with allocation for now. In the formula, we will enter 12 divided by 52, times sales, which will calculate the desired value for each row in the underlying data. Click okay and let's do a quick test to make sure it's working correctly.
Now, here's the tricky part. We only want this calculation to appear on certain rows, not all of them. After considering different options, I've found that the easiest solution is to select all the cells where we want the calculation to appear and change the font to white. This will make the numbers invisible, but they will still be included in the subtotal. Trust me, it looks great and Todd's manager will never know the difference.
But, what happens when we add a new project? We'll have to go back and change the font on that extra cell to white. I know, it's not the most elegant solution, but I've explored other options like editing the MDX, creating a data model pivot table, and using DAX, but the white font method seems to be the most practical. If you have a better solution, please let me know in the comments below.
I want to thank Todd for sending in this question and I hope this solution helps him and others facing a similar issue. If you enjoy these videos, please like, subscribe, and ring the bell to be notified of new episodes. And don't forget to leave your questions and comments down below. Thank you for watching and I'll see you next time for another net cast from MrExcel.
Buy Bill Jelen's latest Excel book: [ Ссылка ]
You can help my channel by clicking Like or commenting below: [ Ссылка ]
Table of Contents
(0:00) Formula outside of pivot table
(0:28) Calculated Field in Pivot Table
(1:11) Verify the calculation
(1:30) White Font trick
(1:55) When pivot table changes
(2:35) Clicking Like really helps the algorithm
#excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial
This video answers these common search terms:
how to calculate in pivot table excel
how to do calculations in a pivot table in excel
how to add calculated field in pivot table excel
how to make calculations in excel pivot table
how to create calculated field in pivot table excel
how to get excel pivot tables to calculate the difference
how to calculate percentage in excel pivot
how to insert calculated field excel pivot table
how to create a calculated field excel in pivot table
how to modify a calculated value in excel pivot table
how to add custom calculated field pivot table excel
how do i create a calculated field in an excel pivot table
how to view formula from calculated field excel pivot table
how to use pivot table for excel calculations
Join the MrExcel Message Board discussion about this video at [ Ссылка ]
