Alif Bin Hussain
While working with a large amount of data, one might need to filter only a fraction of the whole data for various purposes. Excel can be a very useful tool for drilling down a specific part of data. In this article, we will show you how to create a drill down in Excel with easy steps.
Download Practice Workbook
Download this practice workbook for practice while you are reading this article.
3 Easy Steps to Create Drill Down in Excel
In this article, we will demonstrate 3 easy steps to drill down in Excel. We will use the following sample dataset for this purpose.
Step 1: Create a Dataset with Proper Parameters
In the first step, you need to create your dataset in Excel. Here, we created a column of Sales Representatives names with two other columns of Product and Revenue Earned. As a result, our dataset looks like this.
Read More: How to Drill Down in Excel Without Pivot Table (With Easy Steps)
Step 2: Insert a Pivot Table
In this step, we need to insert a pivot table from the created dataset.
- First of all, select the whole table. Hence, from your Insert tab, go to,
Insert → Pivot Table → From Table/Range
- Once you select From Table/Range, a window will pop up.
- If you want your pivot table in the same worksheet, select Existing Worksheet.
- Then click on the arrow to choose Location.
- Now select the cell where you want your pivot table and click on the arrow.
- After that, press Enter and you will find Pivot Table Fields on the right side of your worksheet.
- From the Pivot Table Fields, drag Sales Rep and drop it down to the Filters area.
- After that, you will find Sales Representatives in the Filters area.
- Similarly, drag Product under the Rows area and Revenue Earned in the Values area.
- Finally, if you look at your worksheet, you will find your pivot table.
Read More: How to Create a Calculator Using Macros in Excel (with Easy Steps)
Similar Readings
- How to Insert Clipart in Excel (4 Easy Ways)
- Create Double Entry Bookkeeping in Excel
- How to Write X Bar in Excel (3 Easy Ways)
- Calculate Bootstrapping Spot Rates in Excel (2 Examples)
- How to Edit Document Properties in Excel (with Detailed Steps)
Step 3: Create Drill Down in Excel
Now it is time to drill down data.
- Double-click on any of the Rev Earned data.
- It will open a new worksheet with the drilled down data.
- In this example, we double clicked on the Rev Earned of Printer and all data with the Printer column opened up in another worksheet. Look at the below GIF to understand the procedures clearly.
- Alternatively, you can move your cursor on the data and click the right button of your mouse and then select Show Details.
- It will create a new worksheet and you will have your drilled-down data in there.
- Pivot tables arrange data alphabetically by default. If you want to rearrange the data, you can use the Sort option.
- If you want to create your pivot table in a new worksheet, select New Worksheet from Pivot Table from table or range pop-up box.
Read More: How to Fix Formula in Excel (9 Easy Methods)
Conclusion
Thanks for making it this far. I hope you find this article useful. Now you know how to drill down in Excel. Please let us know if you have any further queries and feel free to give us any recommendations in the comment section below.
Related Articles
- How to Show Menu Bar in Excel (2 Common Cases)
- Use of Task Pane in Excel (Detailed Analysis)
- How to Change 1000 Separator to 100 Separator in Excel
- Show Full Cell Contents on Hover in Excel (5 Quick Ways)
- How to Check If Value Is Between 10 and 20 in Excel
- Mark Workbook as Final in Excel (with Easy Steps)
- How to Move Data from Row to Column in Excel (4 Easy Ways)
