This article is also available as a TechRepublic download and as a TechRepublic gallery.
Formatting an Excel worksheet to look professional and at the same time convey the vital information you want to communicate with your audience has always been a bit of a challenge. With the release of Microsoft Office 2007, formatting styles in Excel 2007 has been simplified with the introduction of the Office Ribbon interface. Colorful and dynamic spreadsheet formatting at the worksheet and cell level are just a few mouse clicks away. This TechRepublic How do I… explores the formatting features of the Styles section of the Home tab in Excel 2007.
The Styles section of the Home tab in the Excel 2007 ribbon interface (Figure A) contains three icons:
- Conditional Formatting
- Format as Table
- Cell Styles
With conditional formatting, you can emphasize unusual values or otherwise highlight specific information. Information and data will stand out as important to your audience is a clear visual way. Clicking the Conditional Formatting icon (Figure B) reveals a set of menu options, including:
- Highlight Cells Rules
- Top/Bottom Rules
- Data Bars
- Color Scales
- Icon Sets
To see how this might work, take a look at Figure C. I have decided to highlight the expenses that are greater than $199 with a light-red fill and dark red text.
In Figure D, I used the Color Scales to emphasize the difference in income for each month.
In Figure E, I used the Icon Sets to indicate the level of acceptableness of each value of Net Income.
Of course our little example worksheet is getting a little busy, but just imagine you have a huge worksheet. The ability to draw the audience’s eye to specific values could be invaluable in conveying important information. Using the features found in the Conditional Formatting section means that the shading, colors and icons will change as the underlying data changes. So you really only have to set the formatting once and let the data determine how your worksheet gets formatted.
Format as Table
There are dozens of pre-built table style to choose from under the Format as Table icon in the Styles section of the Home tab on the Excel 2007 ribbon interface (Figure F). You can apply colors, margins, borders, and shading all at once by choosing from this list of possibilities.
Format as Table
Applying a table style changes our example worksheet into a table with drop down menus on the column headings so we can specify what information we would like to display. (Figure G)
If you cannot find a table style you like in the dozens offered you can also define your own style from the Format as Table menu.
The Cell Styles icon in the Styles section on the Home tab ribbon provides you with dozens more formatting options that can be applied at the cell-level (Figure H).
Using some of the styles found in the Cell Styles menu, I was able to add a little class to the Headings without going through the whole process of changing fonts, font colors, spacing, and borders. (Figure I)
The Microsoft Office 2007 Suite includes hundreds of pre-built styles for formatting your Excel worksheets. Whether you are making an informal weekly report to your immediate supervisor or a formal report to the Board of Directors, you always want to make the most professional presentation possible. Using the extra formatting features available in Excel 2007, you don’t have to have a boring spreadsheet report anymore.
Figure J is our example worksheet with some formatting from the Styles menu added for emphasis.