Pivot Table: How to Group Data by Number Ranges

Untitled Document

Excel pivot table tip


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.

Pivot table field grouping by numbers

As you can see from the picture below, our resulting pivot table has individual prices. This is not helpful.

Pivot table initial results


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.

pivot table number grouping dialog box

Here is the original result.

pivot table number grouping results

I then changed the headings and formatted the numbers to make the results more visually appealing.

pivot table grouped by nubmers final result


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.

Atlanta Computer Training on Facebook Atlanta Computer Training - LinkedIn Software Tips on Twitter

Click to Grab Your Excel Ebook – Free!

Get the free Excel ebook

12 Tips to Empower Excel Users

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>