Look Who it Was Random video

Excel Grouping Percentages into Buckets for a Pivot Table - Method 2 - Episode 913

2,327 views · Published 23 December 2008 · 3:30 · Indexed 20 October 2026

Channel: MrExcel.com · 2008 · Education

Watch on YouTube

Microsoft Excel Tutorial: Grouping Percentages into 5% buckets that are formatted in a pivot table.
Welcome back to the MrExcel netcast! In this episode, we're going to tackle a problem that many of you have encountered - grouping percentages in a Pivot Table. In our previous video, we discussed one solution to this problem. However, in today's video, we're going to explore another method that may be more efficient and effective.

So, let's dive right in. As a quick recap, Jason's issue was that he wanted to show groups of percentages in a Pivot Table, but there was no way to format it correctly. Our first solution involved creating a separate table and using the TEXT function to format the percentages as desired. While this worked, it was a bit of a workaround and not a true solution within the Pivot Table itself.

Now, let's take a look at Method 2. We're going to use the VLOOKUP function, but with a twist. Instead of using the usual ,FALSE at the end, we're going to leave it blank, essentially defaulting to a TRUE. This will give us a range of values, which we can then use to group our percentages. We'll also be using the TEXT function again to format the percentages in the desired way.

Once we have our new "Group" column, we can create our Pivot Table and use the Group field in the row labels. This will give us a count of customers in each individual group. And just like that, we have successfully grouped our percentages in a Pivot Table! While this may seem like a bit of a workaround, it's a clever way to overcome the limitations of Pivot Tables and achieve the desired result.

I hope you found this video helpful and learned something new. Don't forget to hit that like button and subscribe to our channel for more Excel tips and tricks. Thanks for watching and we'll see you in the next 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-on-youtube/

 

Yesterday, I talked about Jason's request to format a grouped pivot field in the row area of a pivot table. There is a solution, but it requires a bit of work in the original data set. Episode 913 will show you about the TEXT function and the other type of VLOOKUP.

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

Table of Contents
(0:00) Theme Music 
(0:15) Problem Statement: Formatting Percentages in Pivot Table Rows Area
(0:28) Build a lookup table with percentages
(0:43) TEXT function to convert percents to formatted text
(1:41) Using VLOOKUP approximate to add groups back into source Excel data
(2:04) Pivot Table with Category in Rows Area
(2:42) Wrap-up and closing theme

#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:
Counting customers in a Pivot Table
Creating a table with groups in Excel
Formatting percentages using the TEXT function
How to use the TEXT function in Excel
Overcoming limitations of Pivot Tables in Excel
Sorting values in Excel using custom formatting
Tips and tricks for data formatting in Excel
Using the Fill handle in Excel
Using TRUE in a VLOOKUP function in Excel
Using VLOOKUP in a Pivot Table
Using VLOOKUP with a range in Excel
YouTube tutorial on formatting percentages in a Pivot Table


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

More from this channel