If you are working with a spreadsheet that has massive amounts of data, you may find that you need to filter or highlight pieces of this data from time to time. There are many ways to do this and conditional formatting is one of those ways.
From the Home menu, when you click Conditional Formatting, there are several options. Let’s just look at the first two: Highlight Cell Rules and Top/Bottom Rules.
Highlight Cell Rules allow you to highlight specific data based on criteria that you enter. If you have a spreadsheet with a lot of numberical data in it, open it now. As an example, highlight a column of data you would like to test. Let’s select the Highlight Cell Rules and then select Greater Than… Enter a number then select the highlighting format you want. I will leave it at the default, “Light Red Fill with Dark Red Text”. Hit OK. Now any numbers that are greater than the number you entered will be highlighted.
Next, let’s highlight another column of numbers or amounts we want to test and select Conditional Formatting. Select Top/Bottom Rules. Select Top 10%. Here, you can change the percentage, but I will leave both defaults: 10% and “Light Red Fill with Dark Red Text. Hit OK. Now the top 10% of the numbers you selected will be highlighted.
Note: Watch your formatting on these rules. Some of the formulas only work for numerical data, not text or date fields.
We love feedback from our customers and implement suggestions often. What shortcut do you use the most? If you have a question or idea for a Microsoft Windows or Excel tip that you would like to see, let me know and I might write about it!
Training Specialist