RTM’d today: Microsoft Excel 2010 Step by Step

We’re pleased to announce the release of one of our newest Office 2010 books – Microsoft Excel 2010 Step by Step! This book will be available for purchase in late June/early July. If you happen to have a subscription to Safari Books Online, you can start reading it as early as next week.

Contents at a Glance

1 Setting Up a Workbook

Creating Workbooks . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2
Modifying Workbooks . . . . . . . . . . . . . . . . . . . . . . . . . .. . . . . . . . . . . 7
Modifying Worksheets . . . . . . . . . . . . . . . . . . . . . . . . .  . . . . . . . . . . 11
Customizing the Excel 2010 Program Window . . . . . . . . . .. . . . . . . . . . 15
Zooming In on a Worksheet . . . . . . . . . . . . . . . . . . . . . .. . . . . . . . . . 16
Arranging Multiple Workbook Windows. . . . . . . . . . . . . . . . . . . . . . . . 17
Adding Buttons to the Quick Access Toolbar . . . . . . . . . . . . . . . . . . . . 18
Customizing the Ribbon . . . . . . . . . . . . . . . . . . . . . . . . .. . . . . . . . . . 20
Maximizing Usable Space in the Program Window. . . . . . . .. . . . . . . . . . 23
Key Points . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 27

2 Working with Data and Excel Tables

Entering and Revising Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 30
Moving Data Within a Workbook. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 34
Finding and Replacing Data. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 38
Correcting and Expanding Upon Worksheet Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . 43
Defi ning Excel Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 48
Key Points . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 53

3 Performing Calculations on Data

Naming Groups of Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 56
Creating Formulas to Calculate Values. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 60
Summarizing Data That Meets Specifi c Conditions. . . . . . . . . . . . . . . . . . . . . . . . . . . . 70
Finding and Correcting Errors in Calculations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 74
Key Points . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 81

4 Changing Workbook Appearance

Formatting Cells. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .84
Defi ning Styles . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .90
Applying Workbook Themes and Excel Table Styles . . . . . . . . . . . . . . . . . . . . . . . . . . . 94
Making Numbers Easier to Read. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 101
Changing the Appearance of Data Based on Its Value . . . . . . . . . . . . . . . . . . . . . . . .106
Adding Images to Worksheets . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .113
Key Points . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 119

5 Focusing on Specific Data by Using Filters

Limiting Data That Appears on Your Screen . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .122
Manipulating Worksheet Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .128
Selecting List Rows at Random . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .128
Summarizing Worksheets with Hidden and Filtered Rows . . . . . . . . . . . . . . . .129
Finding Unique Values Within a Data Set . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .132
Defi ning Valid Sets of Values for Ranges of Cells. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 135
Key Points . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 141

6 Reordering and Summarizing Data

Sorting Worksheet Data. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .144
Organizing Data into Levels. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 153
Looking Up Information in a Worksheet . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .160
Key Points . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 165

7 Combining Data from Multiple Sources

Using Workbooks as Templates for Other Workbooks. . . . . . . . . . . . . . . . . . . . . . . .168
Linking to Data in Other Worksheets and Workbooks . . . . . . . . . . . . . . . . . . . . . . . . 175
Consolidating Multiple Sets of Data into a Single Workbook . . . . . . . . . . . . . . . . . .180
Grouping Multiple Sets of Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .184
Key Points . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .187

8 Analyzing Alternative Data Sets

Defining an Alternative Data Set . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .190
Defining Multiple Alternative Data Sets . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .194
Varying Your Data to Get a Desired Result by Using Goal Seek . . . . . . . . . . . . . . . .198
Finding Optimal Solutions by Using Solver. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .201
Analyzing Data by Using Descriptive Statistics. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .207
Key Points . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .209

9 Creating Dynamic Worksheets by Using PivotTables

Analyzing Data Dynamically by Using PivotTables . . . . . . . . . . . . . . . . . . . . . . . . . . .212
Filtering, Showing, and Hiding PivotTable Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .222
Editing PivotTables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .236
Formatting PivotTables. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 242
Creating PivotTables from External Data. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .250
Key Points . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 257

10 Creating Charts and Graphics

Creating Charts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .260
Customizing the Appearance of Charts. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .267
Finding Trends in Your Data. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 274
Summarizing Your Data by Using Sparklines . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 276
Creating Dynamic Charts by Using PivotCharts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 281
Creating Diagrams by Using SmartArt. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .286
Creating Shapes and Mathematical Equations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .293
Key Points . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .301

11 Printing

Adding Headers and Footers to Printed Pages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .304
Preparing Worksheets for Printing . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .309
Previewing Worksheets Before Printing. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 312
Changing Page Breaks in a Worksheet . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 312
Changing the Page Printing Order for Worksheets . . . . . . . . . . . . . . . . . . . . . . 314
Printing Worksheets . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 318
Printing Parts of Worksheets . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .322
Printing Charts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 326
Key Points . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 327

12 Automating Repetitive Tasks by Using Macros

Enabling and Examining Macros. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .330
Macro Security in Excel 2010 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .330
Examining Macros . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 332
Creating and Modifying Macros . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .336
Running Macros When a Button Is Clicked . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 339
Running Macros When a Workbook Is Opened. . . . . . . . . . . . . . . . . . . . . . . . . . . . . .344
Key Points . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .347

13 Working with Other Microsoft Office Programs

Including Office Documents in Workbooks . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .350
Storing Workbooks as Parts of Other Office Documents . . . . . . . . . . . . . . . . . . . . . . 355
Creating Hyperlinks. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .358
Pasting Charts into Other Documents . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .364
Key Points . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .365

14 Collaborating with Colleagues

Sharing Workbooks. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .368
Sidebar: Saving a Workbook for Secure Electronic Distribution . . . . . . . . . . . 372
Managing Comments . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .372
Tracking and Managing Colleagues’ Changes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 375
Protecting Workbooks and Worksheets . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 379
Sidebar: Finalizing a Workbook. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .385
Authenticating Workbooks . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .386
Saving Workbooks for the Web . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .388

Introduction

For those of you who are upgrading to Microsoft Excel 2010 from an earlier version of the program, this introduction summarizes the new features in Excel 2010. One of the first things you’ll notice about Excel 2010 is that the program incorporates the ribbon, which was introduced in Excel 2007. If you used Excel 2003 or an earlier version of Excel, you’ll need to spend only a little bit of time working with the new user interface to bring yourself back up to your usual proficiency.

Managing Excel Files and Settings in the Backstage View

If you used Excel 2007, you’ll immediately notice one significant change: the Microsoft Office button, located at the top left corner of the program window in Excel 2007, has been replaced by the File tab. After releasing the 2007 Microsoft Office System, the Office User Experience team re-examined the programs’ user interfaces to determine how they could be improved. During this process, they discovered that it was possible to divide user tasks into two categories: “in” tasks, such as formatting and formula creation, which affect the contents of the workbook directly, and “out” tasks, such as saving and printing, which could be considered workbook management tasks. When the User Experience and Excel teams focused on the Excel 2007 user interface, they discovered that several workbook management tasks were sprinkled among the ribbon tabs that contained content-related tasks. The Excel team moved all of the workbook management tasks to the File tab, which users can click to display these commands in the new Backstage view.

Previewing Data by Using Paste Preview

One of the most common tasks undertaken by Excel users involves cutting or copying a worksheet’s contents, such as text or numbers, and pasting that data into either the same workbook or a separate Office document. Users have always been able to paste data from the Microsoft Office Clipboard and control which formatting elements were pasted into the destination; however, in versions prior to Excel 2010, you had to select a paste option, observe the results, and (often) undo the paste and try another option until you found the option that produced the desired result. In Excel 2010, you can take advantage of the new Paste Preview capability to see how your data will appear in the worksheet before you commit to the paste. By pointing to any of the icons in the Paste Options palette, you can switch between options to discover the one that makes your pasted data appear the way you want it to. Troubleshooting The appearance of buttons and groups on the ribbon changes depending on the width of the program window. For information about changing the appearance of the ribbon to match our screen images, see “Modifying the Display of the Ribbon” at the beginning of this book.

Customizing the Excel 2010 User Interface

When the Office User Experience team designed the ribbon interface for Excel 2007, they allowed users to modify the program window by adding and removing commands on the Quick Access Toolbar. In Excel 2010, you can still modify the Quick Access Toolbar, but you also have many more options for changing the ribbon interface. You can hide or display built-in ribbon tabs, change the order of built-in ribbon tabs, add custom groups to a ribbon tab, and create custom ribbon tabs which, in turn, can contain custom groups. These custom groups provide easy access to existing ribbon commands as well as custom commands that run macros stored in the workbook.

Summarizing Data by Using More Accurate Functions

In earlier versions of Excel, the program contained statistical, scientific, engineering, and financial functions that would return inaccurate results in some relatively rare circumstances. For Excel 2010, the Excel programming team identified the functions that returned inaccurate results and collaborated with academic and industry analysts to improve the functions’ accuracy. The Excel team also changed the naming conventions used to identify the program’s functions. This change is most noticeable with regard to the program’s statistical functions.

It is possible in Excel 2010 to create formulas by using the older functions. The Excel team assigned these functions to a new group called Compatibility Functions. These older functions appear at the bottom of the Formula AutoComplete list, but they are marked with a different icon than the newer functions. Additionally, the tooltip that appears when you point to the older function’s name indicates that the function is included for backward compatibility only.

When a user saves a workbook that contains functions that are new in Excel 2010 to an older format, the Compatibility Checker flags the functions and indicates that they will return a #NAME? error when the workbook is opened in Excel 2007 or earlier versions.

Summarizing Data by Using Sparklines

In his book Beautiful Evidence, Edward Tufte describes sparklines as “intense, simple, wordlike graphics.” In Excel 2010, sparklines take the form of small charts that summarize data in a single cell. These small but powerful additions to Excel 2010 enhance the program’s reporting and summary capabilities. Adding a sparkline to a summary worksheet provides context for a single value, such as an average or total, displayed in the worksheet. Excel 2010 includes three types of sparklines: line, column, and win/loss. A line sparkline is a line chart that displays a data trend over time. A column sparkline summarizes data by category, such as sales by product type or by month. Finally, a win/loss sparkline indicates whether the points in a data series are positive, zero, or negative.

Filtering PivotTable Data by Using Slicers

With PivotTables, users can summarize large data sets efficiently, such as by rearranging values dynamically to emphasize different aspects of the data. It’s often useful to be able to limit the data that appears in a PivotTable, so the Excel team included the functionality for users to filter PivotTables. The PivotTable indicates that a filter is present for a particular data column, but it doesn’t indicate which items are currently displayed or hidden by the filter. Slicers, which are new in Excel 2010, visually indicate which values appear in a PivotTable and which are hidden. They are particularly useful when presenting data to an audience that contains visual thinkers who might not be skilled at working with numerical values. For example, a corporate analyst could use a Slicer to indicate which months are displayed in a PivotTable that summarizes monthly package volumes.

Filtering PivotTable Data by Using Search Filters Excel 2007 introduced several new ways to filter PivotTables. Excel 2010 extends these filtering capabilities by introducing search filters. With a search filter, you begin typing a sequence of characters that occur in the term (or terms) by which you want to filter. As you type in these characters, the PivotTable field’s filter list displays only those terms that reflect the values entered into the search filter box.

Visualizing Data by Using Improved Conditional Formats

In Excel 2007, the Excel programming team greatly improved the user’s ability to change a cell’s format based on the cell’s contents. One new conditional format, data bars, indicated a cell’s relative value by the length of the bar within the cell that contained the value. The cell in the range that contained the smallest value displayed a zero-length bar, and the cell that contained the largest value displayed a bar that spanned the entire cell width. The default behavior of the Excel 2010 data bars has been changed so that bar length is calculated in comparison to a baseline value, such as zero. If you prefer, you can display values based on the Excel 2007 method or change the comparison value to something other than zero. Data bars in Excel 2010 also differ from those in Excel 2007 in that they display negative values in a different color than the positive values. In addition, data bars representing negative values extend to the left of the baseline, not to the right. In Excel 2007, the conditional formatting engine placed the zero-length data bar in the cell that contained the smallest value, regardless of whether that value was positive or negative. You have much more control over your data bars’ formatting in Excel 2010 than in Excel 2007. When you create a data bar in Excel 2010, it has a solid color fill, not a gradient fill like the bars in Excel 2007. The gradient fill meant that the color of the Excel 2007 data bars faded as the bar extended to the right, making the cells’ relative values harder to discern. In Excel 2010 you can select a solid or gradient fill style, apply borders to data bars, and change the fill and border colors for both positive and negative values. Another conditional format introduced in Excel 2007, icon sets, displayed an icon selected from a set of three, four, or five icons based on a cell’s value. In Excel 2007, users were limited to using the icons within each set and had no ability to create their own sets. In Excel 2010, you can create custom icon sets from the icons included in the program and, if you prefer, define conditions that, when met, display no icon in the cell.

Finally, with Excel 2010 you can create conditional formats that refer to values on worksheets other than the sheet that contains the cell you’re formatting. In previous versions of Excel, users had to create conditional formats that referred to values on the same worksheet.

Creating and Displaying Math Equations
Scientists and engineers who use Microsoft Excel to support their work often want to include equations in their workbooks to help explain how they arrived at their results. Excel 2010 includes an updated equation designer with which you can create any equation you require. The new editor has several common equations built in, such as the quadratic formula and the Pythagorean theorem, but it also contains numerous templates that you can use to create custom equations quickly.

Editing Pictures within Excel 2010

When you present data in an Excel workbook, you can insert images into your worksheets to illustrate aspects of your data. For example, a shipping company could display a scanned image of a tracking label or a properly prepared package. Rather than having to edit your images in a separate program and then insert them into your Excel 2010 workbook, you can insert the image and then modify it by using the editing tools built into Excel 2010. One very helpful capability that is new in Excel 2010 is the ability to remove the background elements of an image. Removing an image’s background enables you to create a composite image in which the foreground elements are placed in front of another background. For example, you could focus on a flower’s bloom and remove most of the leaves and stem from the photo. After you isolate the foreground image, you can place the bloom in front of another background.

Managing Large Worksheets by Using the 64-bit Version of Excel 2010 Some Excel 2010 users, such as business analysts and scientists, will need to manipulate extremely large data sets. In some cases, these data sets won’t fit into the more than one million rows available in a standard Excel 2010 worksheet. To meet the needs of these users, the Excel product team developed the 64-bit version of Excel 2010. The 64-bit version takes advantage of the greater amount of random access memory (RAM) available in newer computers. As a result of its ability to use more RAM than the standard 32-bit version of Excel 2010, users of the 64-bit version can store hundreds of millions of rows of data in a worksheet. In addition, the 64-bit version takes advantage of multicore processors to manage its larger data collections efficiently. All of the techniques described in Microsoft Excel 2010 Step by Step apply to both the 32-bit and 64-bit versions of the program.

Summarizing Large Data Sets by Using the PowerPivot (Project Gemini) Add-In

As businesses collect and maintain increasingly large data sets, the need to analyze that data efficiently grows in importance. More powerful computers offer some performance improvements, but even the fastest computer takes a long time to process huge data sets when using traditional data-handling procedures. A new add-in, PowerPivot for Excel 2010, uses enhanced data management techniques to store the data in a computer’s memory, rather than forcing the Excel program to read the data from a hard disk. Reading data from a computer’s memory instead of a hard disk speeds up the data analysis and display operations substantially. Tasks that might have taken minutes to complete in Excel 2010 without the PowerPivot add-in now take seconds. PowerPivot relies on the Microsoft SQL Server Analysis Services engine to produce its results, so discussion of it is outside the scope of this book. If you would like to learn more about PowerPivot, you can visit the team’s blog at blogs.msdn.com/powerpivot/.

Accessing Your Data from Almost Anywhere by Using the Excel Web App and Excel Mobile 2010

As the workforce becomes increasingly mobile, information workers need to access their Excel 2010 data as they move around the world. To enable these mobile use scenarios, the Excel product team developed the Excel Web App and Excel Mobile 2010. The Excel Web App provides a high-fidelity experience that is very similar to the experience of using the Excel 2010 desktop application. In addition, you can collaborate with other users in real time. The Excel Web App identifies which changes were made by which users and enables you to decide which changes to keep and which to reject. You can use the Excel Web App in Windows Internet Explorer 7 or 8, Safari 4, and Firefox 3.5. With Excel Mobile 2010, you can access and, in some cases, manipulate your data by using a Windows Phone or other mobile device. If you have a Windows Phone running Windows Mobile 6.5, you can use Excel Mobile 2010 to view and edit your Excel 2010 workbooks. If you have another mobile device that provides access to the Web, you can use your device’s built-in Web browser to view your files. A full discussion of the Excel Web App and Excel Mobile 2010 are beyond the scope of this book.