Enhanced presentation and grouping spreadsheet

The new sales director wants you to show more detail in the management accounts about income streams. However, the MD likes to keep things simple. Is there a way to satisfy both without using two spreadsheets?

Conflicting requests

The sales director has set up new performance targets for his sales team, split across different types of sale (UK, Europe, Rest of World, online advertising and mailshots). Therefore, in order to monitor performance against target, he needs the actual figures for each income stream in his monthly reports.

However, the MD is only interested in seeing a UK and overseas sales split. So do you now have to produce two reports to satisfy both users?

The Excel Group feature allows you to group large quantities of data. So if you have a lengthy spreadsheet with many tiers of data, you can easily consolidate that data without using another spreadsheet.

By grouping the desired data, you are able to hide clutters of numbers without removing them from the spreadsheet. Similarly, you are also able to unmask the data and view it again at the simple click of a button.

How’s it done?

Step 1. Organise the data to be outlined. Make sure the fields you want to group are adjacent to each other. Insert summary rows or columns beneath or beside the fields that you will be grouping. Summary rows or columns should contain formulas to average or sum up the data to be grouped.

Step 2. Select the range of data you wish to group. It must be a series of rows or columns, not just a group of cells. Highlight the first cell of each of the rows/columns you want to include in your group.

Step 3. Select the Data tab to find the Outline group (Group and Outline menu in Excel). To group data, click on Group and select either row or column depending on what you want to group. By making several groups of data, you have created an Outline.

+ or – buttons. You will notice that a grey section appears next to the rows or above the columns. It indicates that the rows/columns are grouped together. Clicking on the minus button will hide all the grouped rows/columns. To unhide the group, click the plus button.

Tip 1. Copy and paste your data into a test spreadsheet so you can work out all the kinks before modifying the original.

Tip 2. You can't undo a group or outline simply by pressing the "Undo" button. To undo a group or outline, select all the grouped data, and go back into the Outline group and click Ungroup.

Tip 3. If you don't want the user to be able to expand and collapse the rows, then go to the Review tab (or Tools/Protection in Excel) and Protect Sheet.

Warning. If you want to add subtotals automatically, do not group the table. Subtotals only work on raw data.

Grouping data in your Excel spreadsheet will organise the sheet and make it easier to view. Data can be collapsed so that only the summary is available for the MD or expanded for the sales director to view details.