Create A Drill Down Experience In Excel - Online Excel Training

Home » How To Drill Down In Excel » Create A Drill Down Experience In Excel - Online Excel Training

Maybe your like

Most people quickly learn to hide rows and columns in Excel. However, this has a risk and a safer way to achieve the same thing is to rather group them. This also allows you to create nested groupings.

To follow along go to 1.2

YouTube

Ad Free Version

Available in the paid for courses.

Our recommended course for you is:

Online Intermediate Excel Course

Under the DATA tab as part of the Outline box you have the Group and Ungroup function. In order to mimic a hide rows, you follow the same steps i.e.

  • Highlight the rows
  • Click on DATA and then GROUP.
Group Ungroup Rows Columns

You will see that to the left of the row numbers you get a + or – icon. These icons are easy to spot and you are more likely to notice if rows or columns have been hidden.

These can be clicked on to open and close a selection of rows. So if you look below by the + you will see that rows 15 and 16 are hidden. Also note the 1 and 2 at the top left of the screen. You can click on these to open all or close all the groupings.

Group Ungroup Rows Columns

A major benefit with grouping is that you can nest the groupings. Below if we highlight rows 15 to 22 and group them, a 3rd level appears. You can now open a report at, for example, gross profit level and then drill down into Sales and Cost of Sales levels and further into the underlying products.

Group Ungroup Rows Columns

The one common complaint is that the icons appear below the groupings. You can change this by going to the Outline segment of the ribbon and clicking on the expand icon as shown below.

Group Ungroup Rows Columns

You can now change the setting to ‘Summary rows below details’. Note below that the – icon is now shown on row 14 instead of 23

Group Ungroup Rows Columns

Tag » How To Drill Down In Excel