Скачать или смотреть Excel - Mastering Pivot Tables: Calculate Percentage of Total for Grand Total - Episode 869

Microsoft Excel Tutorial: Mastering Pivot Tables: Calculate Percentage of Total for Grand Total.

Welcome back to the MrExcel netcast, where we tackle your toughest Excel questions. In this episode, we're diving into a question from Richard about Pivot Tables and how to calculate the percentage of total for the Grand Total row. This may seem like a simple task, but as we explore different solutions, you'll see that there are some geeky and tough methods involved.

Richard's Pivot Table has different reason codes listed on the rows and branches on the columns. As he switches between quarters, the number of reasons changes, causing the Grand Total row to shift. His goal is to have a formula that always shows the percentage of the total, but only for the Grand Total row. This presents a challenge because the cell reference for the Grand Total changes with each quarter.

One solution to this problem is to use a clever version of VLOOKUP. By searching for a large number within the range of the Grand Total column and using the TRUE argument, we can return the last numeric value in the column. This ensures that the formula will always divide by the correct number, regardless of the quarter. Another solution is to adjust the settings of the Pivot Table to show all reason codes, even if there is no data for them. This allows us to predictably know the location of the Grand Total row and solve the problem in a different way.

Thank you for tuning in to this episode of the MrExcel netcast. We hope you found these solutions helpful and will be able to apply them to your own Pivot Table challenges. Don't forget to subscribe to our channel for more Excel tips and tricks, and we'll see you next time for another netcast from MrExcel.

Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/

You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-...


Richard is building a formula outside of the pivot table that needs to divide by the grand total of the pivot table. In Episode 869, I show two solutions, one of which is an incredibly geeky use of the other type of VLOOKUP.

Table of Contents:
(00:00) Introduction by Bill Jelen
(00:24) Pivot Table setup
(00:34) Different reasons in each quarter
(00:44) Formula to show percentage of total
(01:01) Use of VLOOKUP
(01:11) Explanation of VLOOKUP
(01:32) Example of VLOOKUP
(02:04) Solution using VLOOKUP
(02:24) Copying formula for multiple cells
(02:39) Avoiding the problem entirely
(03:07) Another solution using max function
(03:17) Summary of solutions
(03:27) 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:
Division by correct number returned from Pivot Table
Formula for percentage of total
Grand Total
Large number search
Last numeric item in table
MrExcel
Pivot Table
Show items with no data
Solution for Richard's problem
VLOOKUP


Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads...