Using Excel In Any Condition

Microsoft Excel 2007 is a powerful tool that can be used to help analyse vast amounts of complex data. And if you need to present complex data in a format that's easy for anyone to interpret at a glance, Excel's Conditional Formatting function can help with sorting the bears from the bulls.

A typical spreadsheet can comprise of many rows of data. Just looking at this raw data will not give an idea of trends. However, with Conditional Formatting, you are able select one or more of the cells from your worksheet, and then highlight interesting or unusual cell values, and visualise the data using formatting such as data bars. Depending on the value of a cell, you can change the formatting of that cell. Formatting in cells can also be conditional based on the values in another cell or the results of a calculation. With the help of Conditional Formatting, you are able to change the appearance of a cell based on certain conditions, or no conditions, that you specify - ie Conditional Formatting.

To put it simply, then, Conditional Formatting is a Microsoft Excel 2007 feature that sets a cell's format according to conditions that you specify. Conditional Formatting allows you to present numeric data in different colours for example. You can shade data depending on whether the value is greater, equal to, or less than a specified number or range of numbers. You can also present data against a background pattern of alternating shades.

These effects help the viewer to evaluate information in cells and makes it easier to locate data, or find exceptions and even spot trends quickly.

To format a call, first select the Range, then on the Home tab, Styles Group locate Conditional Formatting. Now you can click on Highlight Cells Rules and select Greater Than. Type in the range of data you want to highlight and select the formatting you want to use.

While Conditional Formatting in practical cases is usually used to change the colour of the text, it can also be used to change the font style. The formatting can be used to make the font bold, add borders, or patterns to the cell.

You can hide values if you prefer to show data in an optical display in a column and then reshow if required. Obviously the same technique can be applied in Colour Scales, which uses different scales of colour to depict different values in a cell. A good example of this type of shadings is, for example, when highlighting different temperatures over a specific time scale. Colour coding in this case, would give a definite, easy to understand pattern when shades of colours would relate to higher or lower temperatures over an entire day. A low temperature could be shown as a cool blue, with hotter highs in sunshine yellow through to fiery reds.

One of the most exciting aspects of Conditional Formatting is Icon Sets. Icon Sets give you an opportunity to create visual effects in your data, allowing a representation of the value of a cell compared with other cells. Like data bars and colour scales, you can set the value for each of the different categories in an Icon Set using numbers, percent and formulas. Icon Sets can be used almost like a traffic light system is used to signify warnings (red), alerts (amber) and constants (green). Although you cannot add your own icons, there is a large number of icons to choose from in Microsoft Excel 2007.

If this sounds too complex to take in, don't worry, the Manage Rules dialog box in the worksheet helps you keep track of the different rules. By clicking on a rule, the range associated with rule is selected in a broken box format. This allows you the option of editing or deleting a rule, or adding new rules.

Office Excel 2007 is for anyone who needs to organise information and then analyse it to make more informed decisions. With the Office Fluent user interface that displays the tools you need when you need them, you don't have to be an Excel expert to format your spreadsheet, work with formulas, and obtain great results in a short time, but it helps to consult a qualified trainer to learn more about Conditional Formatting.

---

Author is a freelance copywriter. For more information on an advanced excel class, please visit http://www.microsofttraining.net

---