Are you looking to find the top or bottom N values in your Excel spreadsheet, but aren’t sure how to do it? In this guide, we’ll be going over how you can use the AutoFilter feature in Microsoft Excel to easily find top or bottom N values from your data set. By following our step-by-step guide below, you can start using this powerful technique today!
There are many different ways to use AutoFilter, and this blog will show you some of the most useful tips and tricks.
To find the Top or Bottom N values
Suppose you have a dataset and need to create a report to display only the Top 5 or Bottom 5 data values.
Please follow the below steps to find the Top 5 or Bottom 5 values in your data
- Select the range of cells you want to search
- Go to the Home tab
- Click the Sort & Filter button
- Choose Largest to Smallest, you can choose either sort Smallest to Largest or sort Largest to Smallest
- Excel will automatically sort your data
- To find the top 5 values, look at rows 2 to 6 in the data range
- To find the bottom 5 values, look at rows 17 to 21 in the data range
To find the Top or Bottom N values without sorting the data
Please follow the below steps to find the Top 5 values in your data without sorting
- Select the range of cells you want to search
- Go to the Home tab
- Click the Sort & Filter button
- Choose Filter – you can see the drop-down icon appear in both A1 and B1 cells
- Click the dropdown icon in cell B1
- Select Number Filters
- Choose Top 10…
- Select either Top or Bottom
- Choose N value = 10 if you need Top or Bottom 10
- Press OK
Conclusion
AutoFilter feature in Microsoft Excel is an amazing tool that allows you to filter and sort data in your worksheets. If you work with data in Excel, then the AutoFilter feature is an absolute must-know. AutoFilter allows you to quickly and easily filter and sort data in your worksheets, making it much easier to find the information you need. AutoFilter is a versatile tool that can be used in a variety of different ways.
