You can group rows or columns manually using the "Group" feature. That will tie a range of cells together so they can be collapsed or expanded. First you need to prepare your data, remove blanks, sort the data and insert subtotals manually.
Inserting subtotals manually and grouping rows are time consuming tasks but they can be done automatically if you use "Subtotal" feature instead. Here is how.
- Select A1:C17
- Right click on your selection
- Click "Sort"
- Click "Custom Sort..."
- Sort by "Salesperson"
- Add a Level
- Then by "Region"
- Click OK
Remember that you can´t insert subtotals if you are working with an excel table. You must convert the table to a normal range.
This picture shows subtotals, a grand total and symbols so you can quickly collapse/expand rows.
Subtotals on multiple levels
You can add more subtotals to your data list. We inserted subtotals before when a change occurred in column Salesperson, let's add a new subtotal for each change in column Region.
- Select cell range A1:C20
- Click "Subtotal" button on tab "Data"
- Click the drop down menu below the "At each change in:"
- Select Region
- Clear checkbox "Replace current subtotals"
- Click OK
Add data bars
- Create a new column and type =C2 in cell D2.
- Copy cell D2 to the cells below
- Remove cells that link to subtotals and grand total
- Select cell range D2:D24
- Go to tab "Home" on the ribbon
- Click "Conditional formatting" button
- Click "Data Bars"
- Click "More Rules..."
- Enable checkbox "Show Bar Only"
- I chose a "Solid Border" from the "Border" drop down menu.
- Click OK
Remove all subtotals
You can easily remove all subtotals, select the cell range you want to remove subtotals from. Go to tab "Data", click "Subtotal" button.
Click "Remove All" button.
Build a pivottable/pivotchart
A pivot table allows you to summarize a data table in many different ways, using different criteria. It is easy and you will be analyzing your data in no time. Pivottables are subtotals on steroids.
I highly recommend reading this blog post: Analyze trends using pivot tables, there is also a quick introduction to pivot tables.
You can also compare data, year to year, month to month and whatever time frame you like.