Excel Tip of the Day: Format Elegant Spreadsheets Without Formulas
Unless you were a math whiz in college, you probably have no great love for Excel. Manipulating Excel formulas can be challenging, but the payoff (like elegantly formatted tables that identify important stuff like key performance indicators) can be worth it. But did you know that you can get Excel to identify important values -- like the top ten numbers in a list, values that are greater than certain threshold, or numbers that are below the average -- automatically? It's a feature called Conditional Formatting.
Conditional formatting has been around in Excel a long time, but it seems like most people don't know about it or never remember to use it. In Excel 2010, Microsoft moved it front and center --right into the Home tab -- to encourage you to try it out. Here's what you need to do:
- Start by selecting a range of values, such as a column of numbers that represent the most important data on your spreadsheet.
- In the Home tab, click Conditional Formatting. Now just choose the item you want to format your data.
Highlight Cell Rules flags certain cells that match any criteria you like. If you want to see any cells that exceed 100, for example, choose Greater Than and enter 100. You can also use this to warn yourself if a field has a certain text string or is a duplicate of another cell.Top/Bottom Rules lets you rely on Excel to automatically call out the best or worst values in the set. Use this to mark the top ten values in blue or the lowest ten in red.
Data Bars, Color Scales, and Icon Sets are all different ways to format your data to call attention to outliers. Use these commands to color code best and worst values, or add an icon to indicate relatively better and worse numbers. The best part is that you don't have to specify how Excel does this; it figures it out relative to the best and worst numbers in the range you specify.
More on BNET:
