New! Features
Microsoft Excel 2010
Microsoft Excel 2010 is a spreadsheet program that you can use to track and analyze sales, create budgets, and organize finances—both business and personal. With enhancements to the user interface, and the addition of advanced data tools, enhanced formula writing, page layout, SmartArt graphics, Office themes and Quick Styles for text, shapes, tables, and pictures, you can accomplish a variety of business or personal tasks more easily in Excel 2010.
Only New Features
If you’re already familiar with Excel 2007, you can access and download all the tasks in this book with Microsoft Excel 2010 New Features to help make your transition to the new version simple and smooth. The Excel 2010 New Features as well as other 2007 to 2010 transition helpers are available on the Web at www.perspection.com.
What’s New
If you’re searching for what’s new in Excel 2010, just look for the icon: New!. The new icon appears in the table of contents and throughout this book so you can quickly and easily identify a new or improved feature in Excel 2010. The following is a brief description of each new feature and its location in this book.
Office 2010
64-bit Office programs (p. 2) The 64-bit version of an Office program is built specifically for 64-bit computers. For example, in the 64-bit version of Excel, you can break through the physical memory (RAM) limitation of 2 GB that exists with the 32-bit version, and crunch numbers with ease.
File tab (p. 4-5, 12) The File tab replaces the Office button from Office 2007 and provides access to Backstage view, which lets you access common file management tasks, such as opening, saving, and sharing files.
Customize Ribbon (p. 4, 6-7, 389) In the Options dialog box, you can customize the Ribbon by adding tabs, groups, and command buttons. You can also show and hide the Developer tab.
Recently Used Files (p. 12-13) For easy access, you can add recently used documents to the File tab. In addition to Recent Document, you can also access files on the Recent Places list.
Compatibility Checker (p. 26) The Compatibility Checker generates a report that provides a summary of the potential losses and the number of occurrences in the workbook. You can also specify what versions to show compatibility issues.
Accessibility Checker (p. 27) The Accessibility Checker identifies potential difficulties that people with disabilities might have reading or interactive with an Office document. In addition to the Accessibility Checker, you can also add alternative text (also known as alt text) to objects and other items to provide information for people with visual impairments who may be unable to easily or fully see it.
Auto Recovered (p. 30-31) You can select the Keep the last Auto Recovered file if I close without saving check box in Options as a safe guard to protect your unsaved work. You can use the Manage Versions button on the Info screen under the File tab to open any available recovered unsaved files.
Live Preview of Paste Options (p. 50, 52-53, 79, 237, 396, 398) When you point to an option on a Paste menu, a live preview of the paste contents appears in the document. When you point to a paste option, use the ScreenTip to determine the option.
Math AutoCorrect (p. 58) If you use math symbols in your work, you can use Math AutoCorrect to make it easier to insert them. It works just like AutoCorrect.
Actions (p. 60, 447) Actions, a replacement for smart tags, help you integrate actions typically performed in other programs directly in Excel. For example, you can insert a financial symbol to get a stock quote. To use an action, you right-click an item to view any custom actions associated with it.
More Themes and Styles (p. 157) Office comes with more themes and styles.
Print and Preview Screen (p. 183-184) Instead of a dialog box, you can preview a document and choose printing options together on the same Print screen on the File tab; it detects the type of printer that you choose—either color or black and white—and then prints the appropriate version.
Office.com (p. 188, 436) In the Clip Art task pane, you can select the Include Office.com content check box to access clip content on the Web at Office.com. In the Options dialog box, you can set an option to show or hide customer submitted Office.com content.
Screenshot (p. 196) With the Screenshot button, you can capture a screenshot of your computer and insert it into an Office document. As you capture screens, the Screenshot gallery stores them, so you can use them later.
Artistic Quick Style Gallery (p. 197) With the Artistic Quick Style gallery, you can change the look of a picture to a sketch, drawing, or painting.
Crop to Shape Gallery (p. 199) The Crop to Shape gallery makes it easy to choose the shape you want to use.
Compress a Picture (p. 204) Office allows you to compress pictures in order to minimize the file size of the image. In doing so, however, you may lose some visual quality, depending on the compression setting.
Correct a Picture (p. 205) The brightness and contrast controls change a picture by an overall lightening or darkening of the image pixels. In addition, you can sharpen and soften pictures by a specified percentage.
Recolor a Picture (p. 206) The Color Picture Quick Style gallery provides a variety of different recolor formatting combinations.
Crop a Picture (p. 208-209) Use the Crop button to crop an image by hand. In addition, you can crop a picture while maintaining a selected resize aspect ratio or crop a picture based on a fill or fit.
Remove Picture Background (p. 210) With the Remove Background command, you can specify the element you want in a picture, and then remove the background.
SmartArt Graphic Types (p. 216-217) Office provides more built-in SmartArt graphic types: picture and Office.com. With SmartArt graphic layouts, you can insert pictures in the SmartArt shapes.
Gradient Fills (p. 244-245) The Gradient Fills dialog box allows you to adjust the brightness of a gradient.
Record Macros with Chart Elements (p. 272, 455) When you use the macro recorder with charts, it now records formatting changes to charts and other objects.
Unlimited Points in a Data Series (p. 288-289) In previous Office versions, you were limited to 32,000 data points in a data series for 2-D charts. Now you can have as much as your memory to store.
Trust Center (p. 364, 366-367) The Trust Center provides new security and privacy settings for Trusted Documents, Protected view, and File Block Settings. In Trusted Documents, you can set options to open trusted documents without any security prompts for macros. Protected view provides a place to open potentially dangerous files, without any security prompts, in a restricted mode to help minimize harm to your computer. In File Block Settings, you can select the Open and Save check boxes to prevent each file type from opening, or just opening in Protected view, and from saving.
Language Preferences (p. 444) You can set language preferences for editing, display, ScreenTip, and Help languages. If you don’t have the keyboard layout or related software installed, you can click links to add or enable them.
Save to Web (p. 476-477) Office programs provide a Save As command on the Save & Send screen to save Office documents directly to a Windows Live SkyDrive folder using a Windows Live account.
SharePoint Workspaces (p. 496-497) An Office program that allows you to create and work with local versions of workspaces (SharePoint and Groove, or shared folders) and contacts.
Office Web Apps (p. 472-481, 485, 490-493) After you store Office documents on the Windows Live SkyDrive, or a Microsoft SharePoint server, you can view, edit, or share them in a browser using a Microsoft Office Web App.
SharePoint and Groove Workspaces (p. 494-497) The Ribbon replaces menus, toolbars, and most of the task panes in SharePoint Workspaces 2010. The Ribbon is comprised of tabs with buttons and options that are organized by task.
Co-authoring with SharePoint (p. 499) If you have a Microsoft SharePoint server, you can have multiple authors work on the same Office document at the same time. When multiple authors are working on the same document, you can see who is editing the document and where they are working in the Status bar or on the File tab Info screen.
Office Mobile (p. 500) You can use an Office Mobile 2010 program to view and edit Office documents stored on your phone, sent to you as e-mail attachments, or stored on a Microsoft SharePoint server through SharePoint Workspace Mobile 2010.
Excel 2010
Functions (p. 104, 345, 347) Functions—such as beta and chi-squared distributions—for the academic, engineering, and scientific community have been improved for more accuracy. Some statistical functions have been renamed for consistency with the real world.
AutoFilter (p. 308) The column headers in a table now remain visible in long lists for ease of use. In the AutoFilter menu, you can use the Search box to quickly find what you want in a long list.
Conditional Formatting (p. 136-140) For data bars, you can set the bar direction, and specify custom formatting for negative values. With an expanded set of icons, you can mix and match icons for a custom look as well as hide them based on your criteria. You can apply multiple formatting to the same data in order to achieve the results you want. In addition, you can refer to values in other worksheets.
Sparkline (p. 144-145) A sparkline is a tiny chart in the background of a cell that provides a visual representation of a data range. Sparklines are useful for showing trends in a series of data, which you can print in a worksheet.
PivotTable (p. 312, 314-315) In PivotTables, you can fill down and repeat labels to make viewing data easier, use the AutoFilter menu to find and filter data in fields and columns, and use the Calculations button to display data in different ways. When you sort and filter large amounts of data in a PivotTable, Excel uses multi-threading to improve performance. Excel also provides support for the Undo command in large PivotTables.
PivotChart (p. 318-319) In a PivotChart, you can use the AutoFilter menu to filter data directly in the PivotChart report, just like in a table or PivotTable. The additional Analyze tab provides options to collapse or expand fields, insert a slicer, refresh or clear data, and show or hide the Field List and different Field Buttons on a PivotChart.
Slicers (p. 316-317) Instead of using the AutoFilter menu each time you want to filter data in a PivotTable, you can create a slicer button that enables you to quickly filter data in a PivotTable with a single click. A slicer includes a header, filtering buttons, a Clear Filter button, a scroll bar, and move and resize controls. If you have multiple PivotTables, you can share a slicer between them as needed.
Solver (p. 332-333, 447) The Solver add-in has an improved interface, an evolutionary Solver, based on genetic algorithms, that handles models with any Excel function, global optimization options, better linear programming and nonlinear optimization methods, and linearity and feasibility reports. It’s also available in a 64-bit version.
Excel Options (p. 430, 433-435, 447) The Advanced category of the Excel Options dialog box includes some additional options. In the Editing options section, you can set cursor movement to logical (default and previous version method) or visual. In the Display section, you can set the default display direction, either right-to-left or left-to-right. If you’re using a graphics accelerator on your computer and don’t want to use it in Excel, you can disable it. If you prefer the sheet tabs on the right side of the workbook instead of the left, you can set an option to change it.
What Happened To . . .
Conditional Sum Wizard add-in This add-in is no longer included with Excel 2010. In earlier versions of Excel, you could use the Conditional Sum Wizard to help you write formulas that calculate the sums of values that met specified conditions. This functionality has been replaced by the function wizard and several newer functions, such as the SUMIF and SUMIFS functions. Formulas that were generated by this wizard will continue to work in Excel 2010. You can edit them by using other methods.
Lookup Wizard add-in This add-in is no longer included with Excel 2010. In earlier versions of Excel, you could use the Lookup Wizard to help you write formulas that would find a value at the intersection of a row and column by looking up values. This functionality has been replaced by the function wizard and the available Lookup and reference functions (reference). Formulas that were generated by this wizard will continue to work in Excel 2010. You can edit them by using other methods.
Smart tags Smart tags are not supported in Excel 2010. Smart tag indicators (such as the purple triangles in the corners of cells) are no longer displayed in the worksheet when you select a cell or move the pointer over the cell. Instead, you can right-click a cell to view any custom actions associated with its contents by clicking Additional actions on the shortcut menu. Additional actions are disabled by default, but you can enable or disable this functionality on the Actions tab of the AutoCorrect dialog box.
Click the File tab, click Options, click the Proofing category, click AutoCorrect Options button, and then click the Actions tab.
SmartArt Pattern Fills The pattern fills interface for shape objects, such as chart elements, was not provided with the new SmartArt technology in Excel 2007, causing display issues when pattern fills were used in earlier versions of Excel. In Excel 2010, SmartArt provides the pattern fills interface so that you can apply a pattern fill to shape objects. Shape objects that contain pattern fills in Excel 97-2003 workbooks will also be displayed correctly when those workbooks are opened in Excel 2010.
In Excel 2010, in addition to shape objects, the following controls and objects are converted to the new SmartArt technology: Form controls, Microsoft ActiveX objects, OLE objects, and Camera tool objects
It is important to note that shape objects drawn in earlier versions of Excel that are not upgraded to Excel 2010 SmartArt format cannot be grouped with shape objects that are created in or upgraded to Excel 2010 SmartArt format. Mixed shape objects will be layered, with the earlier versions of shape objects drawn on top of all later versions. This also means that Excel 2010 charts cannot be shown on dialog sheets that were created in an earlier version of Excel. You won’t be able to access the new shape objects by clicking Select Objects on the File & Selection button menu on the Home tab. To select the newer shape objects, you must use the Select Multiple Objects command, which you can add to a Ribbon by using the Custom Ribbon category in Excel Options.
Clip Art task pane The Search in box is no longer available, which means that you can no longer limit your search to specific collections of content. To narrow your search, you can use multiple search terms in the Search for box. The Organize clips link is no longer available. To open Microsoft Clip Organizer from Windows 7, Windows Vista, or Windows XP, click the Windows Start button, click All Programs, click Microsoft Office, click Microsoft Office 2010 Tools, and then click Microsoft Clip Organizer.
Clip Organizer Clip Organizer is a tool that arranges and catalogs clip art and other media files stored on your hard disk. As described earlier in this article, Clip Organizer is no longer accessible directly from the Clip Art task pane in Microsoft Office programs.
Automatic organization Clip Organizer is a tool that arranges and catalogs clip art and other media files stored on your hard disk. As described earlier in this article, Clip Organizer is no longer accessible directly the Clip Art task pane in Microsoft Office programs.
Send to e-mail recipient as attachment The command for sending a clip as an attachment in e-mail messages is no longer available.
Delete from collection Although you can delete clips from Clip Organizer, you can no longer delete a clip from a specific collection.
Find similar style The command that allowed you to find clips of a similar style is no longer available.
List and Details views The List and Details views are no longer available. Instead, all clips appear as thumbnails in the Clip Organizer window.