| Pivot Table Tip - Group by Number Ranges
Do you have pivot table data that you would like to group by age, price range, or any other numerical data? It is 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 a little clearer. Do you have an Excel question? Email me and it just might make it to a blog post! | 