Do you have pivot table data that you would like to group by age, price range, or any other numerical data?
It’s possible and very simple, using pivot table’s group by number feature.
As an example, we have a pivot table that shows product sales and we want to find out the number of sales by price range.
Step 1: Clean Up Your Data
The first step is to make sure your raw dataset is clean. In order for this to work, all the values in the column you want to group on should be numbers. For additional tips on cleaning up your raw data, click here.
Step 2: Create the Pivot Table
Next, create a pivot table, with the field you want to group on as a row label. In our example, we are going to use the price as the row label, and the number (count) of transactions in the value area.
As you can see from the picture below, our resulting pivot table has individual prices. This is not helpful.
Step 3: Group
Next, right-click on your grouping field (row label) and select group.
The Grouping dialog box pops up, with the lowest and highest numbers in your range already selected. Next to *By*, define the range. In our case, I will use 5. What is not shown in this graphic is that I also rounded the starting (10) and ending (35) numbers to make my groupings cleaner.
Here is the original result.
I then changed the headings and formatted the numbers to make the results more visually appealing.
Do you have an Excel question?
Comment below and it just might make it to a blog post!
About the Author
Jackie Kiadii and her team of subject matter expert (SME) trainers provide Microsoft software training that delivers measurable results – increasing productivity, reducing inefficiency, and improving the bottom line. Topics include: Project, Access, Excel, Excel VBA, PowerPoint. Upcoming Excel courses are on our calendar, click here.
Need onsite group training? Call 770.498.7333 or email us. Our courses can be customized to your needs. Jackie has provided software training for employees of The Coca-Cola Company, Emory University, Novelis, and more. This training is available for groups of 4 or more students.
Click to Grab Your Excel Ebook – Free!
