Three must-see Excel 2011 tricks

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.

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.

Themes allow you to easily change the overall appearance of your worksheet

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.

Cell styles let you change styles at the cell level. For instance, use the Check Cell style to mark questionable data.

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.

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.

Sparklines show you trends in your data that you might otherwise miss.

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.

Conditional formatting can reveal trends, outliers, and specific values in your data. Excel 2011 makes it easier to use by adding pre-defined formats.

Using the sales spreadsheet data, for example, it’s easy to add a four-level icon set that shows performance against percentiles; 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.

Product mentioned in this article

Amazon Shop buttons are programmatically attached to all reviews, regardless of products' final review scores. Our parent company, IDG, receives advertisement revenue for shopping activity generated by the links. Because the buttons are attached programmatically, they should not be interpreted as editorial endorsements.