Getting to know Excel 2007

The Excel 2007 interface has been completely revamped and redesigned compared to the older versions of Excel we are all used to. In place of the old pull-down menus, so prominent in all earlier versions of the program, Excel 2007 now relies primarily on the Ribbon, a block of commands displayed at the top of the screen and divided into distinct blocks called tabs. All that is left of the old pull-down menus is the pull-down menu opened with the Office Button, which replicates most of the File commands. Also, in place of the many toolbars of previous Excel versions, Excel 2007 offers a single toolbar called the Quick Access toolbar.

Excel's Ribbon User Interface

When you first launch Excel 2007, the program opens up the first of three new worksheets (named Sheet1) in a new workbook file (named Book1) inside a program window like the one shown in below and Color Plate 1.

The Excel 2007 display screen as it appears when you first launch the program.

The Excel program window containing this worksheet of the workbook is made up of the following components:

Office Button that when clicked opens the Office pull-down menu containing all the file related commands including Save, Open, Print, and Exit as well as the Excel Options button that enables you to change Excel's default settings.

Quick Access toolbar that contains buttons you can click to perform common tasks such as saving your work and undoing and redoing edits and which you can customize by adding command buttons.

Ribbon that contains the bulk of the Excel commands arranged into a series of tabs ranging from Home through View.

Formula bar that displays the address of the current cell along with the contents of that cell.

Worksheet area that contains all the cells of the current worksheet identified by column headings using letters along the top and row headings using numbers along the left edge with tabs for selecting new worksheets and a horizontal scroll bar to move left and right through the sheet on the bottom and a vertical scroll bar to move up and down through the sheet on the right edge.

Status bar that keeps you informed of the program's current mode, any special keys you engage, and enables you to select a new worksheet view and to zoom in and out on the worksheet.

Layout selector that enables you to select between three layouts for the Worksheet area: Normal, the default view that shows only the worksheet cells with the column and row headings; Page Layout View that adds rulers, page margins, and shows page breaks for the worksheet; and Page Break Preview that enables you to adjust the paging of a report.

Zoom slider that enables you to zoom in and out on the cells in the Worksheet area by dragging the slider to the right or left, respectively.

Selecting Commands on the Office Menu

Clicking the Office Button opens the only pull-down menu in Excel 2007. Almost all the commands on this pull-down menu are related to actions that affect the entire file, such as saving and printing. If you prefer, you can open this menu by pressing Alt+F (F for File) instead of clicking the Office Button.

Opening the Office Menu and Selecting Its Commands

In this tutorial, you get familiar with the commands on the Office menu as you practice opening the Office menu and selecting some of its commands. Make sure that Excel 2007 is running and an empty Sheet1 worksheet is active on your computer monitor.

1. Highlight the Office Button (by hovering the mouse pointer over it without clicking the mouse button) and wait until its ScreenTip appears. The ScreenTip shows you an image of the menu and gives you a brief description of its function.
   
   
2. Click the Office Button to open its pull-down menu.
   Note the commands New through Close in the left menu pane. Also note the Excel Options and Exit Excel command buttons at the very bottom of the menu.
   
   
3. Highlight the Save As command on the Office menu (but don't click it).
   Note all the Save a Copy sub-options that now appear in the right menu pane.
   
   
4. Now, click the Save As command on the Office menu to select it. Excel opens the Save As dialog box where you can modify the name, location, and type of Excel workbook file before saving a copy of it.
   
   
5. Press the Esc (Escape) key on your keyboard to close the Save As dialog box.
   
   
6. Press Alt+F to open the Office menu again, this time from the keyboard. This time, small letters appear on each command as well as on the sub-option buttons attached to the Save As and Print commands. These are the access keys that you can type to select an option rather than clicking its name or button.
   
   
7. Type W to display the Print command sub-options, and then type V to select the Print Preview sub-option.
   Excel displays an alert dialog box indicating that there is no data in the Sheet1 worksheet to preview. Note the appearance of the dashes in the Excel worksheet display showing where the pages would be divided.
   
   
8. Click OK in the alert box and then press Alt+FI to open the Excel Options dialog box. The Excel Options dialog box contains all the options for changing the Excel program and worksheet options. These options are divided into categories Popular through Resources.
   
   
9. Click the Advanced button in the left pane to display all the Advanced options in the right pane. Next, scroll down to the Display Options for This Worksheet section and click the Show Page Breaks check box to remove its check mark before you click the OK command button to close the dialog box.
   Note that deselecting the Show Page Breaks option in the Excel Options dialog box removes all the dashed page break lines from the Sheet1 worksheet.

Getting to Know Ribbon

The Ribbon (shown in Figure below) radically changes the way you work in Excel 2007. Instead of having to memorize (or guess) on which pull-down menu or toolbar Microsoft put the particular command you want to use, their designers and engineers came up with the Ribbon that always shows you all the most commonly used options needed to perform a particular Excel task.

Excel's Ribbon consists of a series of tabs containing command buttons arranged into different groups.

The Ribbon is made up of the following components:

• Tabs for each of Excel's main tasks that bring together and display all the commands commonly needed to perform that core task.
  
  
• Groups that organize related command buttons into subtasks normally performed as part of the tab's larger core task.
  
  
• Command buttons within each group that you select to perform a particular action or to open a gallery from which you can click a particular thumbnail - note that many command buttons on certain tabs of the Excel Ribbon are organized into mini-toolbars with related settings.
  
  
• Dialog Box launcher in the lower-right corner of certain groups that opens a dialog box containing a bunch of additional options you can select.

To get more of the Worksheet area displayed in the program window, you can minimize the Ribbon so that only its tabs are displayed - simply click Minimize the Ribbon on the menu opened by clicking the Custom Quick Access Toolbar button, double-click any one of the Ribbon's tabs or press Ctrl+F1. To redisplay the entire Ribbon, and keep all the command buttons on its tab displayed in the program window, click Minimize the Ribbon item on the Custom Quick Access Toolbar's drop-down menu, double-click one of the tabs or press Ctrl+F1 a second time.

When you work in Excel with the Ribbon minimized, the Ribbon expands each time you click one of its tabs to show its command buttons but that tab stays open only until you select one of the command buttons. The moment you select a command button, Excel immediately minimizes the Ribbon again to just the display of its tabs.

Keeping tabs on the Excel Ribbon

The very first time you launch Excel 2007, its Ribbon contains the following seven tabs, going from left to right:

Home tab with the command buttons normally used when creating, formatting, and editing a spreadsheet arranged into the Clipboard, Font, Alignment, Number, Styles, Cells, and Editing groups (see Color Plate 1).

Insert tab with the command buttons normally used when adding particular elements (including graphics, PivotTables, charts, hyperlinks, and headers and footers) to a spreadsheet arranged into the Shapes, Tables, Illustrations, Charts, Links, and Text groups (see Color Plate 2).

Page Layout tab with the command buttons normally used when preparing a spreadsheet for printing or re-ordering graphics on the sheet arranged into the Themes, Page Setup, Scale to Fit, Sheet Options, and Arrange groups (see Color Plate 3).

Formulas tab with the command buttons normally used when adding formulas and functions to a spreadsheet or checking a worksheet for formula errors arranged into the Function Library, Defined Names, Formula Auditing, and Calculation groups (see Color Plate 4). Note that this tab also contains a Solutions group when you activate certain add-in programs such as Conditional Sum and Euro Currency Tools.

Data tab with the command buttons normally used when importing, querying, outlining, and sub totaling the data placed into a worksheet's data list arranged into the Get External Data, Manage Connections, Sort & Filter, Data Tools, and Outline groups (see Color Plate 5). Note that this tab also contains an Analysis group if you activate add-ins such as the Analysis Toolpak and Solver Add-In.

Review tab with the command buttons normally used when proofing, protecting, and marking up a spreadsheet for review by others arranged into the Proofing, Comments, and Changes, groups (see Color Plate 6). Note that this tab also contains an Ink group with a sole Start Inking button if you are running Office 2007 on a Tablet PC.

View tab with the command buttons normally used when changing the display of the Worksheet area and the data it contains arranged into the Workbook Views, Show/Hide, Zoom, Window, and Macros groups (see Color Plate 7).

Selecting Commands from the Ribbon

Make sure that Excel 2007 is running and an empty Sheet1 worksheet is active on your computer monitor.

1. Press the Alt key.
   Note the access-key letters that now appear on the Office button, Quick Access toolbar options and the Ribbon Tabs.

   If you prefer selecting Excel commands from the keyboard, you will probably want to memorize the following access keys for selecting the seven tabs:

   Home tab: Alt+H
   Insert tab: Alt+N
   Page Layout tab: Alt+P
   Formulas tab: Alt+M
   Data tab: Alt+A
   Review tab: Alt+R
   View tab: Alt+W
   
2. Type W to display the contents of the View tab and then type VG to remove the check mark from the Gridlines check box in the Show/Hide Group.
   
   
3. Click the Gridlines check box to select it again and redisplay the gridlines in the worksheet.
   As you may have noticed, the Ribbon takes up quite of bit of screen space that is otherwise used to display worksheet data. You can take care of this by setting Excel to minimize the Ribbon each time you select one of its commands to display only the tab names.
   
   
4. Click the Customize Quick Access Toolbar button (the button with the line and downward-pointing triangle at the end of the Quick Access toolbar) and then click the Minimize the Ribbon option at the very end of this menu.
   Excel immediately minimizes the toolbar to display only the seven tab names. As long as this option is selected on the Customize Quick Access toolbar menu (indicated by a check mark in front of its menu option), Excel continues to reduce the Ribbon to its tab names any time after you select one its commands.
   
   Keep in mind that you can expand the Ribbon to display all the command buttons on the currently selected tab any time that the Ribbon is minimized simply by double-clicking the tab or pressing Ctrl+F1.
   
   
5. Click Data on the minimized Ribbon. Excel expands the Ribbon to display all of the Data tab command buttons.
   
   
6. Click anywhere in the worksheet area to minimize the Ribbon once again.
   The only problem with this minimized Ribbon arrangement is that the temporarily expanded Ribbon covers the first three rows of the worksheet. This makes it very difficult to work with data at the top of the worksheet. For that reason, as well as to help you get comfortable with unfamiliar Ribbon commands, you will work with the Ribbon expanded at all times in all remaining exercises in this workbook.
   
   
7. Click the Customize Quick Access Toolbar button and then click the Minimize the Ribbon option to deselect this option by removing its check mark. Then, click the Home tab to displays its command buttons.
   The Ribbon now remains fully displayed at all times as you select any of its tabs and command buttons without ever obscuring any part of the worksheet display.

Adapting the Quick Access toolbar

When you first start using Excel 2007, the Quick Access toolbar contains only the following few buttons:

• Save to save any changes made to the current workbook using the same filename, file format, and location.
  
  
• Undo to undo the last editing, formatting, or layout change you made.
  
  
• Redo to reapply the previous editing, formatting, or layout change that you just removed with the Undo button.

The Quick Access toolbar is very customizable as Excel makes it really easy to add any Ribbon command to it. Moreover, you are not restricted to adding buttons for just the commands on the Ribbon: you can add any Excel command you want to the toolbar, even the obscure ones that don't rate an appearance on any of its tabs.

By default, the Quick Access toolbar appears above the Ribbon tabs immediately to the right of the Office Button. To display the toolbar beneath the Ribbon immediately above the Formula bar, click the Customize Quick Access Toolbar button (the drop-down button to the right of the toolbar with a horizontal bar above a down-pointing triangle) and then click Show Below the Ribbon on its drop-down menu. You will definitely want to make this change if you start adding more buttons to the toolbar so that the growing Quick Access toolbar does not start crowding out the name of the current workbook that appears to the toolbar's right.

Adding command buttons on the Customize Quick Access Toolbar's drop-down menu

When you click the Customize Quick Access Toolbar button, a drop-down menu appears containing the following commands:

• New
  to open a new workbook
• Open
  to display the Open dialog box for opening an existing workbook
• Save
  to save changes to your current workbook
• E-mail
  to open your mail
• Quick Print
  to send the current worksheet to your default printer
• Print Preview
  to open the current worksheet in the Print Preview window
• Spelling
  to check the current worksheet for spelling errors
• Undo
  to undo your latest worksheet edit
• Redo
  to reapply the last edit that you removed with Undo
• Sort Ascending
  to sort the current cell selection or column in A to Z alphabetical, lowest to highest numerical, or oldest to newest date order
• Sort Descending
  to sort the current cell selection or column Z to A alphabetical, highest to lowest numerical, or newest to oldest date order

When you first open this menu, only the Save, Undo, and Redo options are selected (indicated by the check marks in front of their names) and therefore theirs are the only buttons to appear on the Quick Access toolbar. To add any of the other commands on this menu to the toolbar, you simply click the option on the drop-down menu. Excel then adds a button for that command to the end of the Quick Access toolbar (and a check mark to its option on the drop-down menu).

To remove a command button that you add to the Quick Access toolbar in this manner, click the option a second time on the Customize Quick Access Toolbar button's drop-down menu. Excel removes its command button from the toolbar and the check mark from its option on the drop-down menu.

Adding command buttons on the Ribbon

To add any Ribbon command to the Quick Access toolbar, simply right-click its command button on the Ribbon and then click Add to Quick Access Toolbar on its shortcut menu. Excel then immediately adds the command button to the very end of the Quick Access toolbar, immediately in front of the Customize Quick Access Toolbar button.

If you want to move the command button to a new location on the Quick Access toolbar or group with other buttons on the toolbar, you need to click the Customize Quick Access Toolbar button and then click the More Commands option near the bottom of its drop-down menu.

Excel then opens the Excel Options dialog box with the Customize tab selected (similar to the one shown below). Here, Excel shows all the buttons currently added to the Quick Access toolbar with the order in which they appear from left to right on the toolbar corresponding to their top-down order in the list box on the right-hand side of the dialog box.

To reposition a particular button on the bar, click it in the list box on the right and then click either the Move Up button (the one with the black triangle pointing upward) or the Move Down button (the one with the black triangle pointing downward) until the button is promoted or demoted to the desired position on the toolbar.

You can add separators to the toolbar to group related buttons. To do this, click the <Separator> selection in the list box on the left and then click the Add button twice to add two. Then, click the Move Up or Move Down buttons to position one of the two separators at the beginning of the group and the other at the end.

To remove a button added from the Ribbon, right-click it on the Quick Access toolbar and then click the Remove from Quick Access Toolbar option on its shortcut menu.

Adding non-Ribbon commands to the Quick Access toolbar

You can also use the options on the Customize tab of the Excel Options dialog box to add a button for any Excel command even if it is is not one of those displayed on the tabs of the Ribbon:

• Click the type of command you want to add to the Quick Access toolbar in the Choose Commands From drop-down list box.
  The types of commands include the File pull-down menu (the default) as well as each of the tabs that appear on the Ribbon. To display only the commands that are not displayed on the Ribbon, click Commands Not in the Ribbon near the bottom of the drop-down list. To display a complete list of all the Excel commands, click All Commands at the very bottom of the drop-down list.
  
  
• Click the command whose button you want to add to the Quick Access toolbar in the list box on the left.
  
  
• Click the Add button to add the command button to the bottom of the list box on the right.
  
  
• (Optional) To reposition the newly added command button so that it is not the last one on the toolbar, click the Move Up button until it's in the desired position.
  
  
• Click the OK button to close Excel Options dialog box.

Getting Help

The Excel 2007 Help system shows itself in a number of ways that you may or may not expect from working with other programs. Most of the time, you access Help by clicking the Help button in the upper-right corner of your Ribbon. (The Help button contains the image of a question mark with a circle around it.) This action brings up the default Help page, as shown in Figure below.

On this page, Excel provides a series of links to the topics that Microsoft believes users will need to access most often. To access any of the topics, you click the blue linked text. Follow these steps to use the Help system to find out what is new in Excel 2007:

1. Open Excel. Click the Help button. Notice the online indicator in the lower-right area of the Help page.
   
   
2. Click the What is New link. The Help page displays the list of links for the new information topics.
   
   
3. Click the What's New in Microsoft Excel 2007 link. The first part of the page for this Help topic appears. As you read through the page, you can change the font size, and you change the size of the page by clicking and dragging its edges.
   
   
4. Use the Back arrow button or the Home button to return to the main Help page.
   You navigate Help pages the same way you navigate in your browser. You can use the Forward, Back, and Home buttons to move between the Help topics you have seen.
   
   
5. Click the Getting Help link. This Help page displays a list of Help subcategories as well as a list of topics for getting help.
   
   
6. Read through the list of available Help categories. Click and read any that you think are useful.
   In addition to navigating the Help topics by using the established links, you can find help about topics by typing a phrase into the Search box and searching for help.
   
   
7. Find the Search box at the top of the Excel Help window.Type the word pivot into the search box and either click Search or press Enter. A page that flashes on-screen tells you that Excel is searching the Help system for your topics. Then it lists the 20 results it found. Each item listed is a link to a Help item that references the word pivot. To see all topics, use the scroll bar to the right to scroll through the topics.