How to Drill Down in Excel Without Pivot Table (With Easy Steps)

In this article, we will learn how to drill down without Pivot Table in Excel. It is difficult to compute the sum or average of different groups in large datasets. For this reason, we use Pivot Table to show the summary of groups. Then, we can drill down to the primary dataset. But, we can also do the same task with the Subtotal feature. So, without delay, let’s explain the steps of drilling down without forming a Pivot Table in Excel.

What Is Drill Down in Excel?

Drilling down means looking at something in depth. Here, in Excel, Drill Down is used to instantly move from a summary view to a details view of the dataset. Usually, for large datasets, we use Pivot Table and Subtotal feature to summarize the dataset according to different groups. And we can drill down from the summary view to the details view at any time by using these 2 methods.

Step-by-Step Procedures to Drill Down Without Pivot Table in Excel

To explain the methods, we will use a dataset that contains information about the Sales Amount of some sellers. We will sort the dataset and use the Subtotal feature to know the total sales for each product and region. Then, we will drill down from the summary view using the Subtotal feature instead of Pivot Table.

STEP 1: Make Dataset Ready

Firstly, make your dataset ready and select any cell in the dataset.

STEP 2: Sort Your Data

In the second step, we will sort the dataset.
For this reason, select Filter from the Sort & Filter group of the Data tab.

Now, a Sort window has opened up.
We want to sort the data by Product in ascending order.
That’s why, select Product, Cell Values, and A to Z in the menu of the respective fields.

After that, select Add Level to add another level.
So, a new level has shown up.
Now, we want to sort the data by Region.
Therefore, we have selected Region in the ‘Then by’ field of the new level.
Also, select Cell Values and A to Z in the respective fields.
Now, click OK.

After following the previous steps, the dataset will look like this.
The dataset is now sorted with Product and Region.
After that, we will summarize the dataset with the help of the Subtotal feature.

STEP 3: Add Subtotal to Dataset

Now, go to the Data tab and select Subtotal from the Outline group.

So, the Subtotal window will pop up.
After that, select Product in the ‘At each change in’ field as we have groped the data by Product.
In the Use function, we have put Sum as we want to calculate the total Sales.
You can choose the function as per your use.
In the following step, select Sales Amount like the figure below to show the sales amount result for each group.
Then, we need to give a tick on Replace current subtotals and Summary below data.
Now, click OK.

So, we have successfully added the first Subtotal of the dataset.
Now, we can observe the Sales Amount based on Products.

Again, we need to select Subtotal in the Data tab to open the Subtotal dialog box.
After that, select Region in the ‘At each change in’ field.
This will add the second Subtotal in the same dataset.
Then, select Sum, and Sales Amount in the respecting places.
And most importantly, deselect the ‘Replace current subtotals’ section.
Click OK to proceed.

Finally, you can observe the result like this.
We have been able to add another layer of Subtotal in Excel.
Now, we can observe the Sales Amount based on Products and Region.

STEP 4: Drill Down Using Plus (+) Icon

Now, we want to show the summary only.
Therefore, click on the (–) icon shown in the following figure.
Alternatively, you can do the same if you click on 1 named tab.

As a result, we can see the Grand Total sales of the dataset.
Here, Grand Total Sales indicates the final summary of the dataset.
Now, we will drill down from the summary view to the details view of the dataset.

In the following step, click on button 2 and you can observe the 2nd layer summary.
Here, the summary of the Sales Amount based on the Product is shown with Grand Total.

Now, click on button 3 to drill down to the 3rd layer summary of the dataset.
Instantly, the summary of the Sales Amount based on the Product and Region is shown with Grand Total.

Finally, we want to drill down to the full details view of the dataset.
Here, we will return to the original dataset.
So, click on button 1 and full details of the dataset has opened up.
Now, we can observe the dataset, alongside with 2nd, 3rd and 4th layer summary.
In this way, we can drill down in Excel without Pivot Table.

Download Practice Workbook

To practice by yourself, download the following workbook.

Drill Down Without Pivot Table.xlsx

Conclusion

In this article, we have demonstrated step-by-step procedures to drill down in Excel without Pivot Table. There is a practice workbook at the beginning of the article. Go ahead and give it a try. Last but not least, please use the comment section below to post any questions or make any suggestions you might have.

<< Go Back to Pivot Table Analyze | Pivot Table in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!