ALL ABOUT OFFICE

EXCEL

HOME

ACCESS

EXCEL

WORD

OUTLOOK

POWERPOINT

Sub-Totals

If your data is arranged in a suitable way then Excel can add sub-totals or other summaries like averages automatically.  Firstly, you need to sort your data by the column you are going to group on, e.g. Year for the rainfall data shown below.

Next, click your cursor anywhere in your list of data and then click on the Data drop-down menu and choose Subtotals.  Your whole list should be highlighted and a window will appear as shown below where you can set the item to be totalled and the grouping settings.  You can also change the function to be used for the summary, you could choose functions such as Average, Count, Max or find the Standard Deviation of the group.

If the Summary below data box is ticked then a grand total or overall average, etc will be placed at the bottom of your data.  The example below shows our subtotalled data, the year 2003 has been expanded to see all the data. 

You can expand and collapse the data for each year by clicking on the + (to expand) and - (to collapse) signs in the outlining bar to the left of the worksheet.  If you want to copy only the subtotal figures when you have all the data collapsed then you will need to use Excel's special copying features as the normal copy facility will copy all the hidden rows as well when the outline is collapsed.

To remove your subtotals, make sure your cursor is in your list of data and then click on the Data drop-down menu and choose Subtotals.  Click the Remove All button in the window shown below and your data will be shown without any outlining or sub-totals.

Click here to return to the Excel index.

webmaster@allaboutoffice.co.uk