Data Groups & Formulas:

Title: Jan's Illustrated Computer Literacy 101
Did you want Working with Numbers: 2007,2010,2013,2016  or españolIcon: Change web

Creating groups within your data is generally done so you can do subtotals or other calculations about the records in just that group.

Excel provides help by letting you use its Sort features and then use its Subtotal command.

With these commands you can sort a plain list of sales and then automatically create subtotals. The subtotals can even be functions besides Sum, like Average, Max or Min.

You might want to see sales for each salesman, or in a geographic area, or for each product sold, or for a day, week, or month in which the sales took place. 

The example below was sorted by Salesman and Date, and then subtotaled on Amount of Sale.

Sample: Widgets sheet - original

Original data

Sample: Widgets sheet sorted by salemen

Sorted on Salesman, then Date
Subtotaled on Amount of Sale

Where you are:
JegsWorks > Lessons > Numbers

Before you start...

Project 1: Excel IntroTo subtopics

Project 2: Excel BasicsTo subtopics    

Project 3: Format & ArrangeTo subtopics

Project 4: Groups & Formulas
    SubtotalsTo subtopics
    FootprintFormat Groups
    Images & DrawingsTo subtopics
    Changes & FormulasTo subtopics
    ExercisesTo subtopics

Project 5: DesignTo subtopics


Sorting and subtotaling often mess up formulas that were already in place. You will need to learn how to repair the damage.

TipOnce you have created subtotals for one grouping, it is not easy to subtotal on a something different. Therefore it is a good idea to put the data that you want to subtotal on a new sheet and leave the original data in place. After all, you might get another spiffy idea later and need the original data.