Subscribe for access to this FREE e-learning mini-course!


Your First Name:


Your Surname:


Your Email Address:



Subscribe
Close


Small logo
Hamburger menu
Menu
bookings:   0333 6000 111
service:       0333 6000 555
    or           
Free Video Tutorials : Excel Navigation
Free Video Tutorials : Excel Navigation
Home Quick Course Finder Microsoft Software Microsoft Excel Excel Resources
How to Group Numbers in an Excel Pivot Table
Search

EXCEL NAVIGATION: GROUP NUMBERS IN PIVOT TABLE

Learn how you can group numbers in an Excel pivot table

Background information and resources for Excel courses that we hope you find useful

We all know Pivot Tables are a great feature in Excel, so if you are already familiar with the basics you may get to a position where you want to group data in pivots.

You can group by text or numbers. For example, you can group all your sales by sales region or division, or group your data by numbers such as Division 1 to 8.

You could group data by a specified range or percentage as well. For example, you can group them by age range - e.g. anyone from 1 to 10, 11 to 20, 21 to 30, etc.

EXAMPLE ON HOW TO GROUP NUMBERS IN AN EXCEL PIVOT TABLE

In the example below, there is a list of participants, all aged between 20 and 60. Their age has been calculated from the DOB of the person and the current date. This was done using DATEDIF function.

Example on how to group numbers in an excel pivot table

We now want to group them by age range so it gives us the frequency of particular age ranges from the lowest to the highest.

First of all, insert a Pivot table as normal against this data and add the 'Age' field to the Row section. This will give you the full age range here, for example from 20 to 60 years.

Excel Navigation: Group numbers in pivot table

Click on a single year entry and use the menu to select the 'Group Field' or 'Group Selection' option on the PivotTable Options/Analyse tab (depending on your version of Excel).

Here we see the grouping options below with an interval range of 10. Click OK.

Excel Navigation: Group numbers in pivot table

Now you can add the age field again, to the value section, plus any other relevant fields. The thing to remember is that you are adding the Age field twice to the pivot table. Once to the Rows field and one to the Values.

Excel Navigation: Group numbers in pivot table

CALCULATED ITEMS

Dates are easy to group in a pivot table, as Excel offers different options, e.g. to group by month. If you are looking to group a pivot table text field into categories which don't currently exist within your data, you may need to look at creating 'Calculated Items' which you can learn on our Excel Professional course.

WE HOPE YOU FOUND THIS USEFUL

Want to see how we can really help you?

LINKS TO MORE ...

Main Excel Resources Page

Excel Resources

Video tutorials & articles, and templates to help you work better in Excel

      Click to view

 

Video Tutorials - Navigating Excel

Video Tutorials - Navigating Excel

Navigate and control the layout of your spreadsheets to make working with Excel much easier

Click to view

Excel Navigation: Why selecting multiple cells?

Excel Navigation: Why selecting multiple cells?

See how to fix accidental selection of multiple cells and how to use multiple selection in your work with Excel

      Click to read more

 

Excel Navigation: How to hide sheets

Excel Navigation: How to hide sheets

Learn how to hide worksheets when working with Microsoft Excel

      Click to read more

Excel Navigation: How to remove blank rows

Excel Navigation: How to remove blank rows

Useful tricks to help you remove blank rows in Microsoft Excel

      Click to read more

 

Excel Navigation: How to lock cells

Excel Navigation: How to lock cells

Learn how to lock cells in Microsoft Excel

      Click to read more

GET IN TOUCH

Call us on:
0333 6000 111

   You can Email us direct (You'll usually get a response within 2-3 working hours)
   Or fill out the details below and we'll be in touch very soon:
SEND MESSAGE

HOW TO GROUP NUMBERS IN AN EXCEL PIVOT TABLE

We can enhance your skills with courses for Microsoft Excel spreadsheets at any of our twelve training centres UK wide, or we can come to your offices: if you have groups of three or more staff who need training, this is a very convenient, flexible and cost effective option. Moreover, most of our excel courses are 1 day, which keeps "out of office" periods for attendees to a minimum.

We have been delivering Microsoft Excel course all over the UK since 1995 and have continually developed our philosophy of high quality, flexible and results-driven provision of courses for Excel to ensure constantly improving standards of quality.   So you can be confident that we have the expertise, and the experience, to give you a great return on your investment in Free Video Tutorials : Excel Navigation.

Why not contact us today to see how we can help with providing your MS Excel classes ?


Stay in touch

Stay In Touch!

Google+
LinkedIn
Twitter
FaceBook
YouTube

Contact Details

Activia Training

Locations Address: Head Office, Regus House,
268 Bath Road
Slough, Berkshire SL1 4DX

Phone Sales:           0333 6000 111

Service:       0333 6000 555

Accounts:   0333 6000 777



Back to top