Excel 2010 cheat sheet

Slice and dice your data with Slicers

The process of working with data in PivotTables was much improved in Excel 2007, and Excel 2010 goes a few steps farther. Filtering data has been considerably streamlined with the inclusion of Slicers, which are small windows that make it easy to click values to add or remove them from a PivotTable filter.

In the example below, Slicers for State and City have been attached to a PivotTable. Clicking a value on a Slicer includes or excludes it from the filter. (Blue shading indicates that a value is included; white means it's not.)

Slicers are easy to set up. Click anywhere in a PivotTable, and on the PivotTable Tools tab that appears in the Ribbon, choose Options --> Insert Slicer. Choose the field you want to filter from the list of fields in your PivotTable, then choose OK. Excel opens a Slicer window with a button for each existing value in the selected field.

If you want to limit the results to California, define a Slicer for the State field, then click on just California in the new State Slicer. If you Ctrl-click on Oregon, Excel will update the PivotTable to show results from just those two states.

There are some limitations to Slicers. Once you select a field to display in a Slicer window, you can't subdivide it or group values -- you can't create a Slicer by calendar quarter for a "month" field, for example. You can sort Slicer buttons from A to Z or from Z to A, but you can't specify your own order (such as North, South, East, West).

Slicers can, however, be moved about your worksheet and resized. They're a good choice for creating dashboards, and they're intuitive for even novice users to work with. You can also use the same Slicer in different PivotTables; we provide the steps for doing so later in this story.

More new features in Excel 2010

While Backstage, Sparklines and Slicers are the most important new features in Excel 2010, there are several others that are well worth exploring. Here are a few of our favorites.

Paste Preview

Here's a once-simple question that has gotten more complex with time: How do you want to handle content that you paste into Excel?

If you're copying a section of another spreadsheet, for example, do you want to copy the values or the formulas -- or both -- as in the original? How do you want to handle formatting -- keep the original formatting or that of the spreadsheet you're pasting into? Would you like to paste the values as a graphic instead? Do you want to create a link to the original content?

Excel's new Paste Preview feature solves the problem elegantly. When you paste anything into Excel, a small icon of a clipboard appears next to what you're pasting, with a down-pointing triangle next to the clipboard. If you click the triangle, you will see small thumbnails for all the paste options available to you for the specific type of content you're pasting -- whether to retain formatting of the data you're importing, or to paste the formula itself or the data created by the formula, or to retain the borders of the cell you're importing, and so on.

Depending on what you're pasting, those options may be very simple or very complex. Hover your mouse over any thumbnail to see a description of what the paste option will do.

Even though this feature is called Paste Preview, you don't actually get a preview of the way your content will look . In Excel, Paste Preview is more about the way data is imported, not how it's displayed.

PivotTable and PivotChart enhancements

Slicers aren't the only improvements to PivotTables in Excel 2010. It's now possible to show values in a completely new and useful way. The Show Values As feature adds several new automatic calculations, such as percent of parent row/column total, percent of running total, or rank (smallest to largest or vice versa). And for larger PivotTables, you may appreciate how you can now repeat labels in columns.

If you use the pop-up window to do simple filtering of data, you'll find that the search box starts displaying values as soon as you begin entering your search term, which can accelerate searching.

Also, table headers remain visible at the top of your table as you scroll up and down; and if you apply a filter to the table, those filter conditions are accessible by clicking on the filter icon in the table headings.

PivotTables aren't the only display element with easy filtering. PivotCharts now include buttons to help you control what is displayed. They repeat the controls you find in the Field List sidebar, and all can be turned off at once before you print the chart.

Proactive protection against problems

If you're in a hurry, you may exit Excel 2010 without saving your work. Excel will now protect you from yourself by letting you recover previous versions of a file -- even those you didn't save.

Heedful of another potential problem -- malware-laden files -- Excel 2010 introduces the Protected View feature. If you open a file you received as an email attachment or downloaded from the Internet, Excel opens it in Protected View and places a small warning message at the top of the file. At this stage you can view the file, but you can't edit or print it; it's essentially blocked from accessing your computer.

Similarly, if you open a workbook that contains "active content" such as macros, Excel by default disables the macros and displays a warning message.

In either case, if you know the file is safe, click the Enable button; Excel marks the file as a Trusted Document and you can now edit it. When you open the file again after saving it, you won't see the nag message and you can work with the file normally.

If Protected View annoys you, click File --> Options --> Trust Center --> Trust Center Settings. From there you can turn off Protected View altogether or customize it to a limited extent -- for example, you could turn it off for documents you receive in Outlook but leave it on for documents you download from the Web.

Image editing tools

Excel 2010 offers new tools for performing basic image editing on a graphic or photo you're using in a document. These tools certainly don't rival Photoshop or even midrange image-editing software, but for basic, quick-and-dirty editing, they're quite good.

Select an image in a workbook and you'll see the Picture Tools tab on the Ribbon. The tools are straightforward and self-explanatory. For changing the brightness or contrast, for example, click the appropriate button at the left end of the Ribbon and you'll see thumbnails that show you the results of changing the brightness and contrast in various pre-set ways. Simply select the one you want to apply, and it's done.

The Remove Background button does what it says -- it removes the background of a photo so that you can create a silhouette. The Color button gives you a wide variety of options, such as Recolor, which lets you perform tasks such as converting a color photo to grayscale or black-and-white.

If you want to reduce the amount of space your workbook takes up on your hard disk, or shrink a picture because you're posting the file with the picture onto the Web, click Compress Pictures and make your selection.

You can also add a variety of shadows and special effects by clicking the Picture Effects button. And pay attention to the Preset option when you choose Picture Effects, because you can choose from a variety of effects already selected for you, including 3D effects.

Other image-editing tools include adding "artistic effects" such as making a photograph look like an Impressionist painting. Again, it's all straightforward and self-explanatory, especially if you've used graphics-editing tools before.

Other tweaks to know about

Excel 2010 is available in a 64-bit version, removing the 2GB workbook limit of the 32-bit version. Other limits have been raised, such as the number of data points you can plot in a chart (though let's face it, more isn't always better -- and it often makes for much messier charts). You can also now double-click on a chart element to change its formatting.

Speed is a focus of Excel 2010. For instance, multi-threading makes PivotTables retrieve, sort, and filter data faster; saving large files is also faster. Microsoft has also addressed several graphics-related performance issues -- charts are moved and resized faster, and Excel is now more responsive to working with shapes. Large worksheets are also handled more quickly (especially filtering and sorting), and Excel 2010 supports asynchronous user-defined functions.

For statisticians and scientists, Microsoft says its calculations are more accurate, mentioning beta and chi-squared distributions in particular. A variety of new statistical, engineering, math and trigonometry functions have been added. (For a complete list of function changes, see Microsoft's Excel 2010 site.)

A few small changes from Excel 2007

Microsoft introduced a number of useful features in -- including new visualization tools, Styles and Themes, and an array of table tools -- that users of earlier Excel versions will likely want to learn about. These are still available in Excel 2010, and for the most part they work as they did in Excel 2007, with a few small changes and enhancements:

Conditional formatting options -- for, say, highlighting duplicates, unique values or values in the top or bottom 10% -- have been expanded: Conditions can now be set based on cells in other worksheets within the same workbook.

When you're using icon sets as visualization tools -- such as green, yellow and red traffic lights to illustrate high, medium and low values -- you can now turn one or more of the icons off (so just the highest values show a traffic light, for example). And the collection of icon sets has been expanded to include triangles, boxes and stars.

Excel 2007 introduced gradient vertical bars -- the higher the cell value, the (proportionally) longer the bar. Unfortunately, negative values threw the program for a loop. That's been corrected; negative values appear to the left of a new vertical axis, and positive values extend to the right.

Like Excel 2007, Excel 2010 defaults to saving files in the new .xlsx format. If you are sharing workbooks with users of Excel 2003 or earlier versions, you may want to automatically save files in the older .xls format instead. To save all future workbooks in the older format by default, click on the File tab to open the Backstage interface. Choose Options --> Save --> Save files in this format --> Excel 97-2003 Workbook. Click OK.

Some features have been retired or altered in Excel 2010. For example, the Conditional Sum Wizard (for assistance with writing a formula that includes only cells meeting specific conditions) has been replaced by SUMIF and SUMIFS functions. Old formulas using the Wizard will, fortunately, continue to work.

Also removed is the Lookup Wizard (for writing formulas using cells at the intersection of specified rows and columns); you'll have to use the Function Wizard instead.

Smart Tags (tiny triangles in the corners of cells) are still present, but they no longer display information when you simply mouse over them; you'll need to right-click on the cell instead.

If you're a power user and work with Excel's Solver feature (to find solutions using more sophisticated methods than the built-in Goal Seek feature), you'll have to explicitly enable the Solver feature in Excel 2010; it's no longer installed by default. To load Solver, click on the File tab and choose Options --> Add-ins --> Solver Add-in --> Go. (If Excel warns you that the add-in isn't installed, click Yes to install it.) Check the Solver Add-in box and click OK. The Solver icon now appears on the Data tab in the Analysis group.