When you’re working in a spreadsheet, it’s often useful to visually identify the top percents of your data. This is an easy way to see data that meets certain conditions. Fortunately, this is easy to do, thanks to Excel’s Conditional Formatting features. Let’s learn how to show the top percents of your data in Microsoft Excel.
How to Show the Top Percents of Your Data in Excel
Imagine a list containing a dozen numbers. This dataset spans a series of values, all between 0 and 100. Let’s say that you want to see the top 10%, 25%, or other percentage of this dataset. You could perform the analysis manually, but thanks to Conditional Formatting in Excel, you don’t have to.
To get started, click and drag your cursor to select the dataset. In this case, the data lies in the range A1:A12. Then, go to the Home tab. You’ll find that at the top of your screen, on Excel’s ribbon.
The option you’ll need lies in the Conditional Formatting dropdown menu, which is just right of center on the ribbon. Go ahead and click on it, and you’ll see an array of choices displayed. These are grouped by category, and the one you want will be in the Top/Bottom Rules subsection.
Hover over Top/Bottom Rules to see your options. Since you want to see the topmost percentages in your data, go ahead and click on Top 10%.
On the New Formatting Rule menu, the percentage defaults to 10%, but you can easily change this. In this example, type in 25%. Finally, click OK.
Excel will highlight the three values – 98, 98, and 100 – that represent the top 25% of your dataset.
As you can see, it’s easy to show the top percents of your data in Microsoft Excel. This is an excellent way to quickly visualize the upper elements in your data.