Excel 2010 offers some nice enhancements for formatting your data, including sparklines, slicers, and icon sets. Katherine Murray explains how to put the new tools to work.
Numbers are getting friendlier in Excel 2010, even if use worksheets only once in a while. You can add sparklines to show data trends and patterns on your worksheet. Slicers make it easy to change the way data is displayed in your PivotTables and PivotCharts. And conditional formatting features give you a whole range of options for showcasing your data. You can use color, icons, and more to give your worksheet a polished look and call attention to the values you most want your readers to see.
Note: This article is also available as a PDF download.
1: The big news: SparklinesSometimes, it's helpful to get a snapshot of trends in your data as you go, rather than waiting to produce a big report when all the data is in or to construct a chart to give readers an overall picture of trending data. Sparklines are a new feature in Excel 2010 that enable you to create a small graphic of data in a particular series. The sparkline — which can be formatted as a line, column, or win/loss sparkline — displays in a cell a representation of the data you selected. Figure A shows a line sparkline.
Sparklines enable you to show a snapshot of data in a series.
2: Spruce up your sparklines
When you first create sparklines on your worksheet, they come without any bells and whistles. If you want to add data markers at key points — like the highest value and lowest value — you need to click the check boxes of items you want to display in the Show group of the Sparkline Tools Design tab. You can further customize the sparkline by clicking Sparkline Color or Marker Color in the Style group. You can also change the style of a sparkline you've added by clicking a different type in the Type group or choose a different look for the sparkline by clicking the More button in the bottom-right corner of the Style gallery.
3: Add data bars to showcase your data
If you're comparing data values in your worksheet, you may want to use a formatting feature like data bars to help readers see how the values relate to each other. Excel 2010 enables you to create filled data bars or gradient bars in the cells you specify. If you love gradients (and who doesn't, really?), you can create gradient shading in individual cells to show readers at a glance how your values relate along a common theme. For example, if you're comparing sales in different regions, small gradient formats can show people which region is burning up the sales as well as which ones are lagging behind.
To add a data bar, select the cells you want to change and click Conditional Formatting in the Styles group of the Home tab. Then, click Data Bars and choose the color and style of data bar you want to add to the worksheet cells.
4: Add a color scale
Conditional formatting is easier than ever and offers a wider range of effects in Excel 2010. Applying a color scale to selected cells in your worksheet makes it easy to draw readers' eyes to what you feel is most important on your worksheet. Add a color scale by clicking the Conditional Formatting tool and clicking Color Scales. You can then click the selection you want to apply or click More Rules to create a new rule related to the color scales you add.
5: Slicers make pivot tables fun
Pivot tables and pivot charts may seem daunting to you unless you work with numbers as a steady part of your daily work. A pivot table or pivot chart enables you to easily change up the data you're showing to see the effects that result when you change the way the data is analyzed. Slicers make it super easy for you to customize the data display in tables and charts, in effect "slicing" the data according to your specifications.
Start by creating your pivot table or pivot chart and then click Slicer in the Filter group of the Insert tab. In the Insert Slicers dialog box, select the fields you want to use to display slices of your worksheet data and then click OK. Now you can click the items in each slicer to show worksheet data in different ways.
6: Freeze columns or row labels
If you are working with a large worksheet, the column or row labels will scroll off the page when you page through your data. You can keep your bearings by freezing the column or row labels in the display. Click the View tab and, in the Window group, click Freeze Panes. Now click Freeze Top Row to keep the column labels intact (assuming of course they are in your top row), and click Freeze First Column to freeze the row labels in place. If you decide later that you don't need to freeze these parts of your worksheet, click the View tab again, click Freeze Panes, and click Unfreeze Panes.
7: Highlight high and low values
Excel makes it easy for you to format data values so the highest and lowest values stand out clearly on your worksheet. Start by selecting the cells you want to include in the formatting and then click the Conditional Formatting tool in the Styles group of the Home tab. Click Top/Bottom Rules. In the list that appears, click the item that reflects the data values you want to highlight. You can choose to display the top or bottom 10 items, the top or bottom 10% of the values, or above or below average values. After you click your selection, a dialog box appears so that you can choose the color you want to use to highlight the resulting range.
8: Apply icon sets to data valuesNew icons have been added to Excel 2010 that enable you to illustrate the data values in your worksheet. Begin by selecting the data you want to format and then click Conditional Formatting in the Styles group. Point to Icon Sets and click the icon style you want to add to the worksheet selection (Figure B).
Choose an icon set to display on the worksheet.
9: Create rules for conditional formatting
You can create your own conditional formatting rules to fit the specific data needs of your worksheet. Begin the process by clicking Conditional Formatting in the Styles group of the Home tab. Click New Rule. Then, in the New Formatting Rule dialog box, select a rule type. In the lower portion of the dialog box, click the rule settings you want to apply to the formatted data. Finally, click OK to save your settings and format the selected data in the worksheet.
10: Remove conditional formatting
It is possible — and easy — to go overboard when you are assigning conditional formatting characteristics to your data. You might have a gradient in one column, showing the sales results for various regions. You might include icon sets to show the top regions over a certain dollar amount. When readers look at your worksheet, do the conditional formatting features help them understand your data easily? If not, you'll want to remove one or more of the formatting features you've used. In the Home tab, click the Conditional Formatting arrow in the Styles group and point to Clear Rules. Click Clear Rules From Selected Cells to remove the formatting for cells you've already selected or click Clear Rules From Entire Sheet to remove all conditional formatting from the worksheet.