How to Filter a Table to Get the 5% of a List in Excel

Sometimes, you may want to filter a table in Excel to get the top or bottom 5% of a list of values. For example, you may want to see the highest or lowest 5% of sales, profits, scores, ratings, etc. This can help you identify the best or worst performers, outliers, or trends in your data.

There are two main ways to filter a table to get the 5% of a list in Excel:

• Using conditional formatting and filtering by color
• Using a formula and filtering by value

In this article, we will explain both methods and show you how to apply them to a sample table.

Method 1: Using Conditional Formatting and Filtering by Color

One way to filter a table to get the 5% of a list in Excel is to use conditional formatting and filtering by color. Conditional formatting allows you to apply different formats, such as colors, icons, or data bars, to cells that meet certain criteria. Filtering by color allows you to filter a table based on the cell colors or icons.

To use this method, follow these steps:

1. Select the column that contains the values you want to filter by. For example, if you want to filter by profit, select the profit column.
2. Go to the Home tab, click on Conditional Formatting, and choose Top/Bottom Rules > Top 10%.
3. In the dialog box that appears, change the percentage to 5% and choose a color for the cells that meet the criteria. Click OK.
4. You will see that the cells in the top 5% of the list are highlighted with the color you chose.
5. To filter the table by the color, go to the Data tab, click on Filter, and click on the filter arrow in the header of the column you selected.
6. In the drop-down menu that appears, go to Filter by Color and choose the color you used for conditional formatting. Click OK.
7. You will see that the table is filtered to show only the rows that have the color you chose in the column you selected.

Method 2: Using a Formula and Filtering by Value

Another way to filter a table to get the 5% of a list in Excel is to use a formula and filtering by value. A formula allows you to calculate a value based on a condition or a function. Filtering by value allows you to filter a table based on the values in a column.

To use this method, follow these steps:

1. Insert a new column next to the column that contains the values you want to filter by. For example, if you want to filter by profit, insert a new column next to the profit column.
2. In the first cell of the new column, enter the following formula: =CELL>=PERCENTILE(CELL RANGE,0.95), where CELL is the cell reference of the value you want to filter by, and CELL RANGE is the range of cells that contains the values you want to filter by. For example, if you want to filter by profit in column B, and the profit values are in B2:B100, enter the formula: =B2>=PERCENTILE(B2:B100,0.95).
3. Copy the formula down to the rest of the cells in the new column. You will see that the formula returns TRUE for the cells that are in the top 5% of the list, and FALSE for the rest.
4. To filter the table by the formula, go to the Data tab, click on Filter, and click on the filter arrow in the header of the new column you inserted.
5. In the drop-down menu that appears, check the box for TRUE and uncheck the box for FALSE. Click OK.
6. You will see that the table is filtered to show only the rows that have TRUE in the new column you inserted.

Example

To illustrate the two methods, let’s use a sample table that shows the sales and profit of 10 products:

Suppose we want to filter the table to get the top 5% of the products by profit. Here is how we can do it using the two methods:

Method 1: Using Conditional Formatting and Filtering by Color

1. Select the profit column (B2:B11).
2. Go to the Home tab, click on Conditional Formatting, and choose Top/Bottom Rules > Top 10%.
3. In the dialog box that appears, change the percentage to 5% and choose a color for the cells that meet the criteria. Click OK.
4. You will see that the cells in the top 5% of the profit list are highlighted with the color you chose. In this case, only product E has a profit in the top 5%, so only cell B6 is colored.
5. To filter the table by the color, go to the Data tab, click on Filter, and click on the filter arrow in the header of the profit column (B1).
6. In the drop-down menu that appears, go to Filter by Color and choose the color you used for conditional formatting. Click OK.
7. You will see that the table is filtered to show only the row that has the color you chose in the profit column. In this case, only product E is shown.

Method 2: Using a Formula and Filtering by Value

1. Insert a new column next to the profit column ©.
2. In the first cell of the new column (C2), enter the following formula: =B2>=PERCENTILE(B2:B11,0.95).
3. Copy the formula down to the rest of the cells in the new column (C3:C11). You will see that the formula returns TRUE for the cell that is in the top 5% of the profit list, and FALSE for the rest. In this case, only product E has a profit in the top 5%, so only cell C6 is TRUE.
4. To filter the table by the formula, go to the Data tab, click on Filter, and click on the filter arrow in the header of the new column you inserted (C1).
5. In the drop-down menu that appears, check the box for TRUE and uncheck the box for FALSE. Click OK.
6. You will see that the table is filtered to show only the row that has TRUE in the new column you inserted. In this case, only product E is shown.

In this article, we have learned how to filter a table to get the 5% of a list in Excel using two methods: conditional formatting and filtering by color, and formula and filtering by value. Both methods are easy to apply and can help you analyze your data more effectively. You can also use the same methods to filter by other percentages, such as 10%, 25%, or 50%, by changing the percentage value in the conditional formatting or formula.