Microsoft Excel 2011 is packed with new features, but three areas in particular are worth taking some time to learn.
1. Themes and Cell Styles spruce up your spreadsheets
Better-looking spreadsheets mean a better chance that people will linger over your data. Two closely-related features can spruce up your spreadsheets with a minimum of work.
Themes Themes let you change the overall appearance of your worksheet with only a couple of mouse clicks. Choose a new theme and colors, fonts, and effects all switch.
Related story – Print like a pro from Microsoft Excel
To change your worksheet’s theme, first make sure you’ve saved any changes (so you can easily revert to the saved version, if you wish). Next make sure the Ribbon is visible (View -> Ribbon), click the Home button in the Ribbon, then click on the Themes button to display the available themes.
To change your worksheet’s appearance, select a new theme. Note, too, that you can save your own theme by using the Save Theme option at the bottom of the theme chooser. This is ideal for those in corporate environments who may have required settings for fonts and colors in their worksheets. Create a custom theme using those fonts and colors, and you’re ready to go.
Cell Styles Cell Styles are closely related to Themes, but work at the cell level instead of at the worksheet level. As with Themes, you’ll find Cell Styles on the Home tab of the Ribbon. Highlight a cell, or range of cells, and click Styles to see a drop-down panel containing a number of pre-defined styles.
For instance, if you’ve got a range of data that you’re unsure of and want to check later, select the Check Cell style to turn those cells into double-outlined white-on-gray; use the Note style to give the selection the appearance of a yellow sticky note. As with Themes, you can define and save your own Cell Styles, too, making it simple to apply a personal style to any cell.
2. Sparklines help you track trends
Excel 2011’s Sparklines make it easy to see trends in your data by looking at a single cell. Consider a worksheet tracking sales by salesperson by product over time, month by month. With only a few products and salespeople, you’re looking at a huge array of numbers, making it difficult to see who are your strongest salespeople.
Related story – Harley Davidson dealer taps Excel 2010 to chop costs
Sparklines solve that problem by placing a graph of a data range into one cell, giving you a visual representation of that data. With a glance, it becomes obvious which of your salespeople are doing well, and which are not.
To create a Sparkline, select the cell where you’d like the graph to appear (typically close to the data being graphed), then select Insert -> Sparklines. Select the data range to be graphed and click OK. A Sparkline will appear in the cell you selected.
3. Conditional Formatting reveals key values
The greatly-improved Conditional Formatting feature in Excel 2011 is another good tool for revealing trends and key values in your data. In the past, Conditional Formatting could do this, but you really had to work hard to get the most out of the feature. Excel 2011 offers a lot of pre-defined conditional formats that can be used with a few mouse clicks.
To get started, select the data range you’re interested in analyzing, then choose Format -> Conditional Formatting. The Manage Rules window will appear; click the plus sign at the bottom to add a new rule for your selected range. This opens the New Formatting Rule window, which is where you define the rule.
Use the Style pop-up to choose the type of formatting you’d like to apply. The 2-Color Scale and 3-Color Scale options color cells based on how their values compare to the conditions you specify. Data Bar draws a small bar graph behind each cell’s values. Icon Sets insert icons into cells based on your conditions. Classic lets you pick from five additional formats, such as formatting only unique or duplicate values, top and bottom values, above or below average values, and formula-based formatting.
Related story – Five awesome Excel tips to make your data come alive
Using the sales spreadsheet data, for example, it’s easy to add a four-level icon set that shows performance against per centiles; the colored dots make it obvious which figures are good and which are bad.
If you’re a spreadsheet jockey, you’ll want to get to know these features, as they can really make your job easier–and make the information you provide to others much more usable. The combination of Themes, Cell Styles, Sparklines, and Conditional Formatting lets you create worksheets that are not only easier to read, but easier to interpret as well.
Macworld senior contributor Rob Griffiths is Master of Ceremonies at Many Tricks.
