Introduction to Excel 2007-Whats New?

• What is the Ribbon?

• What is the Quick Access Toolbar?

• What is the Mini toolbar?

• How can I easily find useful keystroke combinations?

• Can I create larger worksheets in Excel 2007 than in Excel 2003?

• What is Formula AutoComplete?

• What is the Microsoft Office Button?

• What are themes?

• What is SmartArt?

• How do I change the zoom level for a worksheet?

• How do I display multiple copies of a workbook at the same time?

• Are there new ways to look at my spreadsheet before I print it?

• My friend does not have Excel 2007. How can I send her files she can use?

• What else has changed?

Microsoft Office Excel 2007 is truly new and improved. At first the new interface and new features may confuse experienced users, but when you are familiar with the new interface, you will never want to use an earlier version of Excel! If you are a novice user of Excel, the new interface will make it much easier for you to locate the more complex and useful features of Excel 2007. In this introductory chapter we will briefly describe the changes in Excel 2007. Our book’s main focus is on using Excel for business modeling and data analysis, so we will not spend much time on visually oriented features such as SmartArt and Themes. For more information about these features, we refer the reader to Microsoft Office Excel 2007 Inside Out by Mark Dodge and Craig Stinson.

• What is the Ribbon?

  When the Excel team asked Excel users what they typically had trouble doing in Excel, they found that over 90 percent of the features people wanted but couldn’t find were already available in Excel 2003. The problem was that many great features such as Data Tables, Data Consolidate, and Solver were hard to find. To make it easier for users to find and learn about all the wonderful features of Excel and other Microsoft Office programs, the Office team created the Ribbon. (See Figure 0-1 on the next page.)

  
  Figure 0-1: The Ribbon: Home tab

  The options available from the Ribbon depend on which tab is selected:

  • Home.   This tab contains most worksheet editing and formatting commands (such as font type and cell alignment) as well as clipboard commands (such as Paste and Paste Special).

  • Page Layout.   This tab contains commands that control the printing of your worksheets as well as worksheet appearance (for example, do I want gridlines to show?)

  • Insert.   Use commands on this tab when you want to insert things such as clip art, charts, and PivotTables into a worksheet.

  • Data.   Commands on this tab pertain to data analysis features such as sorting and filtering.

  • Formulas.   Use commands on this tab when you want to name a range of cells, gain access to the wonderful Excel functions, control calculation options, or audit the structure of a worksheet.

  • Review.   Use commands on this tab to manage worksheet comments, protect worksheets, check spelling, track worksheet changes, or perform related tasks.

  • View.   Use commands on this tab to control how your worksheet is viewed. You can freeze panes, tile and arrange windows, and control the page layout (more on this later in this section).

  • Developer.   Commands on this tab are used primarily to develop Excel macros. You can also insert user forms and controls (discussed in Chapter 25, “Spin Buttons, Scroll Bars, Option Buttons, Check Boxes, Combo Boxes, and Group List Boxes”). If this tab is not visible, click the Microsoft Office Button (see Figure 0-2), click Excel Options, and then on the Popular page, check the Show Developer Tab In The Ribbon box.

  • Add-Ins.   Excel Add-Ins such as the Solver or Analysis Toolpak are available from this tab. The tab is visible only when at least one add-in is installed.

  
  Figure 0-2: Microsoft Office Button

  The tabs and Ribbon make it much easier to see what Excel has to offer. If you think the Ribbon takes up too much space, you can hide it (or redisplay it) by pressing Ctrl+F1, double-clicking any tab, or right-clicking the Ribbon and then clicking Minimize The Ribbon.

• What is the Quick Access Toolbar?

  There are probably many commands that you use more often than others. Having to switch between tabs to find the command you want could really slow you down. Excel now provides you with the Quick Access Toolbar which allows you to collect your favorite commands in one place. The default location of the Quick Access Toolbar is above the Ribbon in the upper-left portion of the screen. See Figure 0-3.

  
  Figure 0-3: Quick Access Toolbar

  You can add a command to the Quick Access Toolbar by simply right-clicking the command and choosing Add To Quick Access Toolbar. You can also add commands by clicking the Microsoft Office Button, clicking Excel Options, and then displaying the Customize page (shown in Figure 0-4). After choosing a command you want to add just select Add and click OK. You may remove any command from the Quick Access Toolbar by right-clicking the command and then clicking Remove From Quick Access Toolbar. You may move the Quick Access Toolbar below the Ribbon by right clicking on the toolbar and selecting Show below the Ribbon.

  
  Figure 0-4: You can add, remove, and arrange commands on the Quick Access Toolbar.

• What is the Mini toolbar?

  When you select the contents of a cell or right-click a cell or a cell range the Mini toolbar appears. See Figure 0-5. The Mini-toolbar gives you quick access to the most commonly used formatting tools.

  
  Figure 0-5: The Mini toolbar

  If you want to prevent the Mini toolbar from appearing, click the Microsoft Office Button followed by Excel Options. Then on the Popular page, clear the Show Mini Toolbar On Selection check box.

• How can I easily find useful keystroke combinations?

  Press the Alt key once to display the available tab-level keyboard shortcuts (and again to hide them). Pressing the key that represents the tab on which the command you want is located displays that tab and all its command-level shortcuts. For example, Alt+M displays the Formulas tab. Alt+H+H displays the Fill Color gallery, and so on.

• Can I create larger worksheets in Excel 2007 than in Excel 2003?

  Excel 2003 has a worksheet size limit of 64,000 rows and 256 columns. Excel 2007 allows up to 1,048,576 rows and 65,536 columns. To test this, click F5 (which allows you to go to any cell), type in CAT1000000 for example, and click OK. Excel sends you to cell CAT1000000! Excel 2007 was also designed to perform calculations on large worksheets much more quickly. In fact, if your computer has n processors, then certain operations can be performed close to n times as quickly as Excel 2003 because Excel 2007 is programmed to take advantage of multiple processors.

  Many other limits have been improved in Excel 2007, such as these:

  • 4.3 billion available colors

  • Unlimited levels of conditional formatting

  • Sorting on up to 64 columns

  • 100 levels of Undo allowed

  • 8,000 characters allowed in a formula

  • 32,000 characters allowed in a cell

• What is Formula AutoComplete?

  Suppose you start typing in a formula to average a range of cells. You would begin by typing in =AV. Then the new Formula AutoComplete feature appears. See Figure 0-6. Instead of typing average all you need do is press Tab or double-click AVERAGE. Excel then enters =Average( into your formula.

  
  Figure 0-6: Formula AutoComplete feature

  If you use range names (see Chapter 1, “Range Names”) or the new Table feature (see Chapter 24, “Tables”) you will really see the benefits of Formula AutoComplete.

• What is the Microsoft Office Button?

  We have already briefly discussed the Microsoft Office Button. From the menu that appears when you click the Microsoft Office Button, you can:

  • Perform key file-level tasks such as Save, Close, Open, and Print.

  • Customize various aspects of Excel (by clicking Excel Options).

  • Install Excel add-ins (by clicking Excel Options and then displaying the Add-Ins page).

• What are themes?

  Themes let you control the colors, fonts, and special effects used in your worksheets. Themes apply to your entire workbook. To select a theme, simply click the Page Layout tab and then in the Themes group, click Themes. You will be presented with many theme choices, some of which are shown in Figure 0-7. When you point to a theme, the Office Live Preview feature shows you how things will look if you select that theme. Alternatively, by making selections from the Colors, Fonts, and Effects lists, you can create your own customized theme and save it for later use. Any customized themes you create will appear in the Custom category.

  
  Figure 0-7: Theme choices

• What is SmartArt?

  SmartArt gives you many neat shapes and effects that go beyond the usual circles, rectangles, and arrows. To see how SmartArt works, open a new workbook and on the Insert tab, in the Illustrations group, click SmartArt. You will see the options shown in Figure 0-8.

  
  Figure 0-8: SmartArt choices

  We chose the first option and entered in the text for each block the name of a starting member of the Dallas Mavericks NBA team. We obtained the figure shown in Figure 0-9.

  
  Figure 0-9: SmartArt example

• How do I change the Zoom level for a worksheet?

  Excel 2007 makes it a snap to zoom in and out of a worksheet by moving the Zoom slider located in the lower-right corner of your screen. See Figure 0-10.

  
  Figure 0-10: Zoom slider

• Are there new ways to look at my spreadsheet before I print it?

  The Workbook Views group on the View tab displays the possible views shown in Figure 0-11.

  
  Figure 0-11: Workbook view options

  Normal view is your usual worksheet view. Page Layout view shows individual pages and lets you add headers and footers, adjust margins, and so on. Page Break Preview shows and allows you to adjust page breaks.

• How do I display multiple copies of a workbook at the same time?

  Suppose your workbook contains a worksheet for each month of the year. You might want to perform computations that involve different months, so it would be helpful to see different worksheets of the workbook on the screen at the same time. To see multiple views of your workbook, click the View tab and then repeatedly click New Window to bring up enough views of the spreadsheet (for example, if you want three views, click New Window twice). Then click Arrange All and choose how you want the copies of your workbook displayed. Displaying a different worksheet in each window makes it easier to develop formulas involving more than one worksheet.

• My friend does not have Excel 2007. How can I send her files she can use?

  If your friend has Office 97, Office 2000, Office XP or Office 2003, save the file in the Excel 97-2003 Workbook file format. If your friend has Office 95, save the file in the Microsoft Excel 5.0/95 Workbook file format. The default file format for Excel 2007 files is Excel Workbook, which creates a file with the .xlsx extension.

  Note 

  If you use certain new features of Excel 2007, your workbook may not be entirely compatible with earlier versions of Excel.

  To determine whether your workbook is compatible with earlier Excel versions you can run the Excel Compatibility checker. Click the Microsoft Office Button, point to Prepare, and then click Run Compatibility Checker. For the workbook containing the SmartArt shown in Figure 0-9 on the next page, the Compatibility Checker would inform us that our SmartArt is not compatible with earlier versions of Excel.

  
  Figure 0-12: Compatibility Checker

• What else has changed?

  The new features of Excel discussed in this chapter do not pertain to business modeling and data analysis, so we gave only a brief explanation of these features. The following new features of Excel 2007 are very important in data analysis and business modeling so they will be covered in great detail.

  • The method for creating range names has been greatly improved. See Chapter 1 for details.

  • Conditional formatting options have been greatly expanded. You will be amazed by the new visually oriented conditional formatting choices that can be used to help you better understand your data (data bars, icons and color scales). See Chapter 22, “Conditional Formatting,” for a complete discussion of conditional formatting.

  • Sorting and filtering have been vastly improved. You can even sort on cell or font color! Excel 2007 also makes it easy to remove duplicates. See Chapter 23, “Sorting in Excel,” and Chapter 40, “Filtering Data and Removing Duplicates,” for more details.

  • The new Table feature will revolutionize spreadsheet modeling. Proper use of the Table feature will cause your formulas, formatting, and charts to automatically update as you add new data to your workbook. See Chapter 24 for a complete discussion of the Table feature.

  • Pivot tables have been revamped and improved. See Chapter 38, “Using Pivot-Tables to Describe Data,” for a detailed discussion of this important data analysis feature.

  • The appearance of Excel charts has been improved. This will become clear as you glance at the Excel charts included in the book.

  • The new IFERROR function (see Chapter 11, “IF Statements”) will make it easier to modify the dreaded #REF and #N/A error messages that often make spreadsheet calculations a nightmare!

  • The new COUNTIFS (Chapter 18, “The COUNTIF, COUNTIFS, COUNT, COUNTA, and COUNTBLANK Functions”) and SUMIFS and AVERAGEIFS (Chapter 19, “The SUMIF, AVERAGEIF, SUMIFS, and AVERAGEIFS Function”) will make it much easier to summarize data sets.