- When you have a random sequence of numbers in your spreadsheet, use conditional formatting to quickly locate cells with values below, above, or equal to a certain number or range.
- Select your data: click on its header to select an entire column, for example (more selection tips).
- Click on Conditional Formatting in the ribbon's Home tab, and choose "Highlight Cells Rules":
- You have three choices to compare values to a particular number: (ranges explained next!) • To show all cells above a certain number, pick "Greater Than".
• For all values smaller than a particular value, choose "Less Than".
• To highlight an exact value in the selection, go with "Equal To".
- When the "Format cells that are..." dialog opens, type a value above, below, or equal to which you want to highlight in your worksheet. Pick from the dropdown the formatting style you want to apply - notice that Excel shows you a real time preview in the background. Click OK to proceed.
Tip: to add your own colors, backgrounds, and borders, pick "Custom Format" in the dropdown.
- The cells matching your condition now visually pop out of your sheet - here's an example.
- To check which cells fall within a range of values, go to "Conditional Formatting > Highlight Cells Rules > Between..." - this time, Excel collects a starting and ending number. This is an "inclusive range", so your start and end numbers will themselves be included. Click OK.
- Excel also includes very handy shortcuts to show the extremes within a range: under Conditional Formatting, pick "Top/Bottom Rules". Then choose "Top 10 items" or "Bottom 10 items": you'll be able to change that number, but the highest or lowest values in your selection will be highlighted! You can do the same for top and bottom 10%, and above or below average.
Email AOL Mail Gmail Hotmail Yahoo Mail MS Office Excel 2010 OneNote 2010 Outlook 2010 Word 2010 OS & Devices Kindle Fire Windows 7 Web Dev C# and .NET JavaScript jQuery