Cover | Table of Contents

Table of Contents

Chapter 1: Understanding Excel

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

The first chapter of this book is designed to give those readers with relatively little experience using Excel 2007 enough information to dive right in and start creating rich Excel workbooks immediately, and to fill in some of the details for more experienced Excel users who haven't had enough time to examine the program and its new user interface in depth.

This chapter covers:

• What's new in Excel 2007
• The Excel interface
• Workbook, template, and workspace files
• The anatomy of an Excel file
• Formatting
• Shortcut Menus and the Mini Toolbar (a.k.a. the "floatie")

After the significant leap forward from Excel 95 to Excel 97, Excel versions 2000, 2002, and 2003 were incremental improvements on the same basic application design. By contrast, Excel 2007 is a substantial departure from Excel 2003. Excel 2007 comes with a new user interface (detailed later in this chapter), a much larger worksheet, and new formatting capabilities, among many other changes. summarizes the most important changes in Excel 2007.

Table : Excel 2007 greatly expands the size of the worksheet and extends the possible number of sorting levels, characters in a cell, and colors in a workbook.

Capability	Old limit	New limit
Worksheet size	65,536 rows by 256 columns	1,048,576 rows by 16,384 columns
Total characters in a cell	1,024 characters	32,767 characters
Total characters printed in a cell	1,024 characters	32,767 characters
Colors in a workbook	56	16 million colors
Undo levels	16	100
Sort levels	3	64
Conditional format conditions	3	Limited by available memory
Maximum length of a formula	1,024 characters	8,192 characters
Maximum number of arguments to a function	30	255
Number of nested levels allowed in a formula	7	64

The new limits only apply when you save a workbook using the new Office 2007 file format. If you save a work-book using the old Excel 97–2003 file format, the old limits apply.

Excel's interface stayed more or less constant from Excel 97 to Excel 2003. During that time, the program's developers added some new features and moved items around in the menu and toolbar system, but the basic structure remained the same. All that changed in Office 2007.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

What's New in Excel 2007

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

After the significant leap forward from Excel 95 to Excel 97, Excel versions 2000, 2002, and 2003 were incremental improvements on the same basic application design. By contrast, Excel 2007 is a substantial departure from Excel 2003. Excel 2007 comes with a new user interface (detailed later in this chapter), a much larger worksheet, and new formatting capabilities, among many other changes. summarizes the most important changes in Excel 2007.

Table : Excel 2007 greatly expands the size of the worksheet and extends the possible number of sorting levels, characters in a cell, and colors in a workbook.

Capability	Old limit	New limit
Worksheet size	65,536 rows by 256 columns	1,048,576 rows by 16,384 columns
Total characters in a cell	1,024 characters	32,767 characters
Total characters printed in a cell	1,024 characters	32,767 characters
Colors in a workbook	56	16 million colors
Undo levels	16	100
Sort levels	3	64
Conditional format conditions	3	Limited by available memory
Maximum length of a formula	1,024 characters	8,192 characters
Maximum number of arguments to a function	30	255
Number of nested levels allowed in a formula	7	64

The new limits only apply when you save a workbook using the new Office 2007 file format. If you save a work-book using the old Excel 97–2003 file format, the old limits apply.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

The Excel 2007 Interface

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Excel's interface stayed more or less constant from Excel 97 to Excel 2003. During that time, the program's developers added some new features and moved items around in the menu and toolbar system, but the basic structure remained the same. All that changed in Office 2007.

Excel 2007, Word 2007, and PowerPoint 2007 all use the new Microsoft Office Fluent interface design, commonly referred to as the Ribbon ().

Figure : The Home tab of the new Excel 2007 user interface Ribbon.

shows one tab from the new Ribbon user interface, which replaces the menu bars and toolbars (collectively known as command bars) found in Excel 2003. The Ribbon contains most, but not all, of the commands available in Excel 2007. The Microsoft Office user experience team created the new Ribbon interface in an effort to make it easier to discover the full range of built-in capabilities in Excel 2007. The Office product teams constantly receive requests for features that were already built into the programs, so they designed the new interface and applied it to the three most popular Office programs.

The Ribbon resizes itself and its controls to reflect your monitor's resolution and the window size, so you might see a different set of controls (and differently appearing controls) than is shown in .

Rather than force users to poke through a maze of menus, toolbars, and dialog boxes to find the commands they're looking for, the Ribbon brings most of Excel's functionality to the top level of the user interface. The Home tab, shown in , contains the most common operations: cutting and pasting, font and cell formatting, finding and selecting cells, and so on.

The Page Layout tab of the Ribbon () hosts buttons that enable you to change the page's appearance and control printing. For example, you can change a worksheet's margins, turn gridlines on and off, select the parts of the worksheet you want to print, and add a background image.

Figure : The controls on the Page Layout tab let you determine how your page looks when it's displayed and printed.

To hide the Ribbon, press Ctrl-F1. Pressing Ctrl-F1 again brings the Ribbon back. When the Ribbon is hidden, clicking on the menu temporarily brings the Ribbon back.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

The Anatomy of an Excel File

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Think of an Excel file as a collection of data about a given topic, such as sales revenue or time spent on projects. For example, if you were keeping track of your billable hours on a series of projects and had to submit your hours each week, you could create a workbook representing a year and use each worksheet to record your hours per week. You would need to add worksheets for each week (a workbook's default is three), but the grouping would make sense. If your company maintained a quarterly billing cycle, you could also keep your hours in workbooks of only 13 worksheets, each representing a week in a fiscal quarter.

While an Excel workbook appears to be a single entity, there are actually three different layers: the data layer (the ground floor of your document that holds the worksheet data, formulas, and so on); a back drawing layer (the basement, where you can put background images, such as wallpaper or different colors that make the data easier to read); and a top drawing layer (the second floor, where you can add graphics to highlight important data, or display comments in a text box). illustrates how the three layers interact within a workbook.

Figure : This worksheet has elements in the back drawing layer, data layer, and top drawing layer.

The lines in the background of the sheet are repetitions of a texture graphic, while the image of a tiger is a clip art file. The contents on a higher drawing layer obscure the contents of a lower layer. In , the picture of the tiger obscures data contained in the worksheet's cells; in turn, the cell data obscures the background pattern.

In Excel, worksheets are where the rubber meets the road; worksheets hold your data, formulas, images, and formatting. Worksheets are tables made up of columns and rows—you can identify a cell in an Excel worksheet based on its column (which are lettered) and its row (which are numbered, e.g., A5).

If you would rather refer to a cell's address only by numbers, use "R1C1" notation, where the row is preceded by the letter "R", and the column is preceded by the letter "C". R1C1 refers to cell A1, R2C2 to cell B2, and so on.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Formatting

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

The data and formulas that fill a workbook are, ultimately, the most important part of your work with Excel, but how you present your data can make the difference between an easily understood summary and a difficult slog through a twisty maze of numbers. Each workbook element has formatting characteristics that you can change to maximize the effectiveness of your information.

Compared to the other elements of a workbook, there are very few formatting options you can apply to a worksheet as a whole. You can change the margins (Page Layout → Margins), or add a header and footer to a worksheet. As shown in , a header is a block of space reserved at the top of a page, while a footer is a block of space reserved at the bottom of a page. The text in a header and footer—the date, your company name, etc.—repeats on every printed page of the worksheet they're attached to. To display the "Header and Footer" dialog box, click Insert → Header and Footer, add custom text, or include AutoText entries such as the time, date, page number, and total number of pages for the worksheet. You can also include images in a header or footer.

Figure : A header and footer repeat information, such as page numbers and dates, on each printed page.

In a similar vein, you can repeat column and row titles on subsequent pages. Click the Page Layout tab, click the Page Setup group's dialog expander, and then click Sheet to display the Sheet tab of the Page Setup dialog box. Using the "Rows to Repeat at Top" and "Columns to Repeat at Left" controls, you can select the titles you want to appear on each page. If you're printing a worksheet that requires two or more pages, strongly consider repeating the row and column headers on each page.

Just remember that setting rows and columns to repeat on each printed page doesn't help as you scroll through a worksheet. Scroll far enough and those headings will disappear. You can freeze rows and columns so they remain visible as you scroll through a worksheet by clicking the cell below and to the right of the rows and columns you want to remain constant, clicking View → Freeze Panes, and selecting whether to freeze rows and columns based on the current selection, freeze just the first row, or freeze just the first column. You can return your worksheet to normal by clicking View → Freeze Panes → Unfreeze Panes.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Shortcut Menus and the Mini Toolbar

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

One handy Windows tool is the right-click menu. Right-clicking an object in a program (such as a cell, row or column header, or sheet tab) displays a short menu of commonly used commands. Microsoft calls these shortcut menus context menus. shows the shortcut menu that appears when you right-click a cell.

Here is a quick summary of Excel's right-click talents:

• Right-clicking a cell displays a shortcut menu with a formatting toolbar you can use to format the cell quickly; editing commands that let you paste, cut, copy, or paste a special item (such as a picture or another workbook) into the cell; commands that let you delete, insert, or clear the contents of the cell; attach a comment; change the cell's format using the Format Cells dialog box; input a value that already exists in the column; filter or sort worksheet values based on other values in the cell's column; define the cell as part of a named range; and create a hyperlink.
  

  Figure : shortcut menu contains buttons and menu items
• Right-clicking a row or column header displays a short-cut menu that contains commands to format the object you right-clicked; cut, copy, paste, and paste special; insert, delete, or clear the contents of the row or column; display the Format Cells dialog box; change the row's height or the column's width; as well as options to hide or unhide the row or column.
• Right-clicking a sheet tab lets you insert a new worksheet; delete, rename, hide, move, or copy the sheet you right-clicked; select all sheets; change the worksheet's tab color; or view the code of any macros attached to the sheet.
• Right-clicking a drawing object lets you format the object quickly using controls on a formatting toolbar; cut, copy, or paste the object; edit the object's text; change the object's appearance, size, grouping, and ordering; assign a hyperlink or macro to the object; and assign the shape as the default shape for that workbook.
• Right-clicking a Ribbon button lets you add the button to the Quick Access Toolbar. Similarly, right-clicking a Ribbon group's name bar (e.g., the Page Setup tab's Themes group) lets you add the group to the Quick Access Toolbar.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

How Excel Tries to Help

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Excel has a lot going on (you can decide whether that means Excel is "feature rich" or "bloated"), and it should make your life easier. And it does, with several automated features. One useful task Excel performs in the background is saving AutoRecover information—worksheet data, macros, formulas, etc.—so if the program crashes, you lose as little work as possible. There are three other things Excel does in the background (AutoCorrect, AutoComplete, and Smart Tags), but you can turn these—and AutoRecover—off if you wish.

One thing Excel doesn't do automatically is highlight suspected spelling and grammar errors in your worksheet. To check spelling, click Review → Spelling.

People make the same spelling mistakes again and again, usually those little transpositions ("adn" for "and") that drive you crazy. Don't fret. Excel has this and other common misspellings in its list terms to correct automatically.

If Excel makes a change you don't want, you can undo the change by pressing Ctrl-Z or clicking the Undo button on the Quick Access Toolbar.

You can add, remove, or redefine AutoCorrect entries by clicking Office Button → Excel Options, clicking the Proofing category head, and then clicking the AutoCorrect Options button. In the dialog box, you can select the types of changes Excel can make or, by unchecking the "Replace text as you type" box, prevent Excel from making any corrections automatically. To add an entry, type the text in the Replace field and the text to replace it in the With field.

You can define an AutoCorrect entry that inserts your boilerplate text whenever you type several letters of the phrase (such as "vty" for "very truly yours").

Excel also recognizes web addresses and network paths, and automatically formats them as hyperlinks. Excel 2007 also determines whether your entry should be added to an existing Excel table. You can control these options on the "Auto-Format as You Type" tab.

Most Excel users often enter the same data in a worksheet. For example, if you keep your business' customer data in a worksheet, many of your customers will probably be from the same state. Excel recognizes repeated values in a column and offers to fill in the expected value. You can turn Auto-Complete on by clicking Office Button → Excel Options, clicking the Advanced category head, and then, in the Editing Options section at the top of the dialog box, checking the "Enable AutoComplete for Cell Values" box.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Chapter 2: Excel Tasks

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

This chapter gives you quick answers about how to perform common and essential tasks in Excel. You'll find information on tasks that are new to you, and you'll save time on tasks you already know. The tasks are divided into the following categories:

• Working with files
• Printing
• Moving around in a workbook or worksheet
• Manipulating workbooks and worksheets
• Manipulating rows, columns, and cells
• Entering and editing data
• Formatting cells
• Working with hyperlinks
• Working with headers and footers
• Summarizing data
• Using named ranges
• Defining alternative data sets
• Controlling how data is displayed
• Protecting all or part of a workbook
• Spelling and other tools
• Customizing Excel
• Collaborating
• Working with the Web
• Summarizing data with charts
• Analyzing data with PivotTables and PivotCharts

Use the following answers to help you create, find, save, preview, and set the properties of Excel files.

How do I…

Create a new workbook without starting Excel?

Right-click any blank space in a folder or desktop and select New → Microsoft Excel Worksheet.

Create a new workbook within Excel?

Press Ctrl-N or click Office Button → New, then double-click Blank Workbook under the "Blank and Recent" section.

Create a workbook from a template?

Click Office Button → New; in the Templates pane, click the category of template you want to display, and then double-click the template on which you want to base your workbook.

Open a workbook?

Click Office Button → Open or press Ctrl-O; navigate to the file you want to open. Optionally, you can click the down arrow on the Open dialog box's Open button to open the workbook in different ways (read-only, in a web browser, etc.).

In Excel 2002 and 2003, you could search for a file from within Excel. That capability doesn't exist in Excel 2007, so you must use the Windows operating system's Search feature. To search for a file in Windows, click the Start Button → Search, and use the controls in the Search dialog box to find the file you want.

Save a workbook?

Click the Save button on the Quick Access Toolbar, press Ctrl-S, or Office Button → Save. Click Office Button → Save As to save a workbook under a different name, in a different location, or as a different file type. Enter the name of the new file, navigate to the directory folder where you want to save the file, and click Save.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Working with Files

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Use the following answers to help you create, find, save, preview, and set the properties of Excel files.

How do I…

Create a new workbook without starting Excel?

Right-click any blank space in a folder or desktop and select New → Microsoft Excel Worksheet.

Create a new workbook within Excel?

Press Ctrl-N or click Office Button → New, then double-click Blank Workbook under the "Blank and Recent" section.

Create a workbook from a template?

Click Office Button → New; in the Templates pane, click the category of template you want to display, and then double-click the template on which you want to base your workbook.

Open a workbook?

Click Office Button → Open or press Ctrl-O; navigate to the file you want to open. Optionally, you can click the down arrow on the Open dialog box's Open button to open the workbook in different ways (read-only, in a web browser, etc.).

In Excel 2002 and 2003, you could search for a file from within Excel. That capability doesn't exist in Excel 2007, so you must use the Windows operating system's Search feature. To search for a file in Windows, click the Start Button → Search, and use the controls in the Search dialog box to find the file you want.

Save a workbook?

Click the Save button on the Quick Access Toolbar, press Ctrl-S, or Office Button → Save. Click Office Button → Save As to save a workbook under a different name, in a different location, or as a different file type. Enter the name of the new file, navigate to the directory folder where you want to save the file, and click Save.

Click Office Button → Save As to display the Save As dialog box. Type a name for the web page in the File name box, click the "Save as type" down arrow, and then click Web Page (*.htm; *.html). Click the Entire Workbook or Selection option button to indicate which part(s) of the workbook you want to save, and then click the Save button.

Save all open workbooks as a workspace?

Click View → Save Workspace. If you open the workspace file, Excel will open all the workbooks you had open when you created the workspace.

Change the summary information of a workbook?

Click Office Button → Prepare → Properties, and enter new information in the appropriate fields.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Printing

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Use the following answers to help you print entire workbooks, individual worksheets, parts of worksheets, and more.

How do I…

Print a single copy of the active worksheet without using the Print dialog box?

Click Office Button → Print → Quick Print or Ctrl-P.

Set the print area of a worksheet?

Select the cells you want to print and click Page Layout → Print Area → Set Print Area.

Excel 2007 checks for blank cells at the bottom and right edge of a print area and eliminates them automatically so you don't print any pages with empty cells.

Set the print area of multiple worksheets?

Select the sheet tabs of the worksheets for which you want to set the print area. Then, on the active worksheet, select the cells you want to print and click Page Layout → Print Area → Set Print Area.

Print an entire worksheet even if it has a defined print area?

Click Office Button → Print, and in the "Print what" section, check the "Ignore print areas" box.

Set a print area made up of discontiguous cells?

Select the first group of cells you want to print, then hold down Ctrl and select the next groups of cells, and click Page Layout → Print Area → Set Print Area.

Remove a print area?

Click Page Layout → Print Area → Clear Print Area.

View how my worksheet will appear when it's printed?

Click View → Page Layout.

Preview what I am about to print?

Click Office Button → Print → Print Preview.

Preview where the page breaks occur in what I am about to print?

Click View → Page Break Preview. You can drag the page breaks to new locations in the Page Break Preview window ().

Figure : Set page breaks for a worksheet in the Page Break Preview window.

Print a range of pages of a worksheet?

Click Office Button → Print, and in the Print range section, enter the first and last page you want to print in the From and To boxes.

Print every worksheet in a workbook?

Click Office Button → Print and select Entire Workbook in the "Print what" area.

Print selected worksheets in a workbook?

Hold down Ctrl when you click the tabs of the worksheets you want to print; click Office Button → Print. ("Active Sheets" will be selected in the Print what area.)

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Moving Around in a Workbook or Worksheet

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Use the following answers to help you navigate in workbooks and worksheets.

How do I…

Move from one worksheet to another?

Click the sheet tab at the bottom of the screen representing the worksheet you want, or Press Ctrl-PgDn to select the next sheet, and Ctrl-PgUp to select the previous sheet.

Scroll around in a worksheet?

Click the arrows in the vertical scroll bar to move up or down through a worksheet. Click the arrows in the horizontal scroll bar to move left or right. You can also drag the scroll handle on either scroll bar to move quickly through your worksheet. Changing the view of your worksheet using the scroll bars does not change the active cell.

Hide the scroll bars?

Click Office Button → Excel Options, click the Advanced category header, and scroll to the "Display options for this workbook" section. Uncheck the "Show horizontal scroll bar" and "Show vertical scroll bar" boxes.

Move around a worksheet using the keyboard?

The Up, Down, Left, and Right arrows will move the active cell in the respective direction. Tab moves the active cell one column to the right. Shift-Tab moves the active cell one column to the left. Pressing Enter moves the active cell down one row; Shift-Enter moves it one row up.

For a complete list of movement keys, see .

Change the default movement of the Enter key?

Click Office Button → Excel Options, click the Advanced category header. In the "Editing options" section, check the "After pressing Enter, move selection" box, then open the Direction pull-down menu and select the direction you want.

Go to a specific place in a workbook?

Click Home → Find & Select → Go To, or Ctrl-G or F5. Select or type in the cell or range you want to move to (such as a named range) and click OK.

Control which items are displayed in the Go To dialog box?

Click Home → Find & Select → Go To Special, and select the items to show.

Find text in a worksheet?

Click Home → Find & Select → Find or Ctrl-F. Enter the text to find in the dialog box. Click the Find Next button. Click the Options button to display more ways to search for text in your worksheet. With the extra options displayed, open the Within pull-down menu on the Find tab and select "Workbook" to search all the sheets in your workbook. Click Find All to list all occurrences at the bottom of the "Find and Replace" dialog box. Click an instance in this list to highlight the cell containing the text.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Manipulating Workbooks and Worksheets

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Use the following answers to add, delete, or rearrange the worksheets in your workbooks.

How do I…

Add a worksheet?

Click Home → Insert → Insert Sheet. The worksheet appears in front of the active worksheet. You can also right-click a sheet tab, click Insert, and double-click Worksheet.

Delete a worksheet?

Right-click the sheet tab and click Delete, or use Home → Delete → Delete Sheet.

Move a worksheet?

Click the tab and drag. Or right-click the sheet tab and click Move or Copy. Open the To Book menu to move the sheet to a different workbook (or a new workbook).

To move the sheet within the same workbook, select its place in the "Before sheet" list.

Copy a worksheet?

Ctrl-click the sheet tab, drag it to the new place, release the mouse button, then release Ctrl. Or right-click the sheet tab and click Move or Copy. Check the Create a copy box, open the To Book menu, and select the workbook (or a new workbook).

If you're setting up a workbook where all the worksheets will have the same structure, format one worksheet the way you want it, then create copies of it.

Rename a worksheet?

Right-click the sheet tab, click Rename, and type in a new name. Or select Home → Format → Rename Sheet.

Reorder worksheets?

Drag the sheet tab into a new position on the tab bar. As you drag the tab, a black triangle marks where the worksheet would be placed if you released the mouse button.

Change the default number of worksheets in a workbook?

Click Office Button → Excel Options, click the Popular category header (if necessary), and change the number in the "Include this many sheets" field. The maximum is 255 sheets.

Change the color of a sheet tab?

Right-click the sheet tab, click Tab Color, and pick the color in the palette that appears; or click Home → Format → Tab Color and pick the desired color.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Manipulating Rows, Columns, and Cells

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Use these answers to change the layout of an individual worksheet by adding, deleting, and moving cells, rows, and columns.

How do I…

Insert cells into a worksheet?

Click the cell where you want to insert a new cell and click Home → Insert → Insert Cells. Pick which way the surrounding cells should shift and click OK.

Insert a block of cells into a worksheet?

Select a group of cells in the shape of the desired insertion and click Home → Insert → Insert Cells; select the option button indicating the direction you want the surrounding cells to shift.

Insert a column?

Click any cell in the column to the right of where you want the new column to appear; click Home → Insert → Insert Sheet Columns.

Insert a row?

Click any cell in the row below where you want the new row to appear; click Home → Insert → Insert Sheet Rows.

Use the Insert Options button?

Click the Insert Options button and select the option button representing the formatting you want the inserted material to take on.

The Insert Options button only appears when you insert cells, rows, or columns that contain formatting.

Hide the Insert Options button?

To make it disappear for this operation only, press Esc. To prevent it from appearing after you insert cells, rows, or columns, Office Button → Excel Options, click the Advanced category header, scroll down to the "Cut, copy, and paste" section, and uncheck the "Show Insert Options buttons" box.

Move a column?

Select the column, right-click, and click Cut. Click any cell in the column to the right of where you want the moved column to appear; right-click and click Home → Insert → Insert Cut Cells. You can also Shift-drag the border of the column to the new place.

Move a row?

Select the row you want to move and click Cut. Click any cell in the row below where you want the moved row to appear; click Home → Insert → Insert Cut Cells. You can also Shift-drag the border of the row to the new place.

Delete cells from a worksheet?

Select the cells and click Home → Delete → Delete Cells; select the direction the surrounding cells should shift.

Delete a column?

Select the column(s); right-click in the selected area and click Delete.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Entering and Editing Data

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Use the following answers to help you enter, edit, and validate data.

How do I…

Change how the cursor moves after pressing Enter?

Click Office Button → Excel Options, click the Advanced category header. In the "Editing options" section, check the "After pressing Enter, move selection" box, then open the Direction pull-down menu and select the direction you want.

Use AutoComplete?

Click Office Button → Excel Options, click the Advance category header, and then, in the "Editing options" group, check the "Enable AutoComplete for cell values" box.

Turn off AutoComplete?

Uncheck the "Enable AutoComplete for cell values" box.

Pick a value from a list of values already appearing in the column?

Right-click the cell into which you want to enter the value, and select "Pick from Drop-down List". In the list that appears, select the item you want to enter into the cell.

Repeat the same value across a range of cells?

Select the cells into which you want to enter the value, type the value in the active cell, and press Ctrl-Enter.

Press Alt-Enter.

Use Fill Series?

Type the first value of a series in a cell, type the second value of the series in a cell next to the original cell, select the cells, and drag the Fill Handle at the bottom-right corner of the active cell to the last cell you want included in the series.

The difference between the value in the first cell and the value in the second cell is the increment Excel uses to fill in the rest of the series. For example, the values 1 and 2 would result in the series of 1, 2, 3, 4…, while the values 1 and 3 would result in the series of 1, 3, 5, 7.…

Change how dragging the Fill Handle extends a series?

Hold down the Ctrl key as you drag the Fill Handle. If dragging the Fill Handle would normally extend a series, Ctrl-dragging will repeat the selected values; if dragging the Fill Handle would normally repeat values, it will extend the series.

Use Fill Series with dates?

Type the first date in a cell, type the second date in a cell next to the original cell, and drag the Fill Handle at the bottom right of the active cell to the last cell you want included in the series.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Formatting

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

How do I…

Set the default font and font size?

Click Office Button → Excel Options, click the Popular heading; using the controls in the When Creating New Workbooks section, choose the default font and size. You must close and restart Excel 2007 for the change to take effect.

Apply basic font formatting?

Select the cells you want to format, click Home, and use the buttons in the Font group.

Click the cell and, in the Formula bar, select the part of the contents you want to format. You can also double-click the cell and select the part you want to format directly. Use the buttons on the Home tab's Font group to format the cell contents.

Change the text color?

Click the Font Color button to impose the current color. To change the color, click the down arrow on the button and select the color from the palette. The last color you picked appears as an underline on the button.

Change the orientation of the cell's contents?

Click Home → Orientation. Either click a preset orientation or click Format Cell Alignment to display the Alignment tab of the Format Cells dialog box. In the Format Cells dialog box, type the number of degrees the text should be rotated, or drag the red pointer in the Orientation pane to the desired angle.

Align the contents of a cell?

To set the vertical alignment, display the Home tab of the Ribbon and then, in the Alignment group, click the Top Align, Middle Align, or Bottom Align button. To set the horizontal alignment, click the Alignment group's Align Text Left, Center, or Align Text Right button.

Applying some cell styles and formats can change a cell's horizontal alignment

Indent the contents of a cell?

Display the Home → Increase Indent or Home → Decrease Indent. To set a custom indent, click the Home tab and then click the Alignment group's dialog box expander to display the Format Cells dialog box's Alignment tab. In the Indent box, enter the number of characters you want to indent the entry.

Wrap text within a cell?

Display the Home → Wrap Text.

Shrink text to fit within the existing borders of the cell?

Click the Home tab of the Ribbon and then click the Alignment group's dialog box expander to display the Format Cells dialog box's Alignment tab. On the Alignment tab, click the "Shrink to fit" box.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Working with Hyperlinks

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

The following answers show you how to create, remove, edit, and follow hyperlinks. You will also learn how to set a URL as a default value so you can just enter the page name (e.g., index2.htm) of a web page.

How do I…

Create a hyperlink?

Click Insert → Hyperlink or Ctrl-K to display the Insert Hyperlink dialog box (). Indicate the type of hyperlink using the buttons to the left ("Existing File or Web Page", "Place in This Document", or "E-mail Address") and fill in the fields. Click the "Browse for File" button to navigate and pick the file or location you want to link to.

Figure : Select an existing Office Theme from the Themes gallery, or create one of your own.

Figure : Use the Insert Hyperlink dialog box to create and edit your hyperlinks.

Remove a hyperlink?

Right-click the cell and click Remove Hyperlink.

Edit a hyperlink?

Right-click the cell, click Edit Hyperlink, and use the controls in the Edit Hyperlink dialog box to edit the hyperlink.

Follow a hyperlink?

Click the cell that contains the hyperlink; Excel opens the address in a new browser window.

Set a hyperlink base that all hyperlinks in a workbook will use?

Click Office Button → Properties, click the Summary tab, and enter a web address in the Hyperlink base field. You can override this base by entering a full URL (e.g., http://www.oreilly.com/).

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Working with Headers and Footers

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Use these solutions to create headers and footers that will appear at the top and bottom of each printed page. You will learn how to add text, format it, add changing values (such as the current time or page number), and add images.

How do I…

View a workbook in Page Layout View?

Click View → Page Layout.

Create a header or footer with AutoText?

Click Insert → Header and Footer. In the Design contextual tab's Header & Footer group, click either the Header or Footer button and select the AutoText you want to fill the header or footer.

Add custom text to a header or footer?

Click Insert → Header and Footer. If necessary, in the Design contextual tab's Navigation group, click either the "Go to Header" or "Go to Footer" button to activate the header or footer. Click the left, center, or right section of the header or footer, and type text in the section.

Format text in a custom header or footer?

Select the text, click Home, and use the controls in the Font group to format the text.

Add the printed page number to a header or footer?

Click Insert → Header and Footer, and then click the header or footer section where you want the page number to appear. In the Design contextual tab's Header & Footer Elements group, click the Page Number button ().

Figure : Use the controls on the Header & Footer Tools Design contextual tab to define the contents of your header and footer.

Print the total number of pages in a worksheet as a header or footer?

Click Insert → Header and Footer, then click the header or footer section where you want the number of pages to appear. In the Design contextual tab's Header & Footer Elements group, click the "Number of Pages" button.

Include the current time in a header or footer?

Click Insert → Header and Footer, and then click the header or footer section where you want the time to appear. In the Design contextual tab's Header & Footer Elements group, click the Current Time button.

Include the current date in a header or footer?

Click Insert → Header and Footer, and then click the header or footer section where you want the date to appear. In the Design contextual tab's Header & Footer Elements group, click the Current Date button.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Summarizing Data

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

The following answers show you how to display running totals for selected data, add formulas to cells, edit formulas, identify formula precedents and dependents, check formulas for errors, and watch how the values in selected cells change as other worksheet data changes.

How do I…

Display a running total on the status bar?

Select the cells. The sum, average, and count of the selected cells appear at the bottom right of the screen in the status bar.

Change how selected cells are calculated on the status bar?

Right-click the status bar, and click the names of the operations you want to add to or clear from the status bar. Unlike in previous versions of Excel, you can display all six available summaries (sum, count, numerical count, average, minimum, and maximum) on the status bar at once.

How do I…

Type a formula into a cell?

Type = and then enter the rest of the formula.

Add a function to a formula using Formula AutoComplete?

Type = and then type the first letter or letters of the function. Click the desired function from the Formula Auto-Complete list and press Tab to add it to the formula.

Edit a formula in a cell?

Click the cell and edit the formula on the formula bar. You can also double-click the cell and edit directly.

Create a summation formula quickly?

Select a cell below the cells you want to sum and click Formulas → AutoSum to create the formula. Verify the range.

Create other kinds of formulas quickly?

Select a cell below the cells you want to sum and click the down arrow at the bottom of the AutoSum button. Select the function you want from the list, or click More Functions to select any available function using the Insert Function dialog box.

Press Esc to abort entering a formula into a cell.

Add a formula using the Insert Function dialog box?

Click Formulas → Insert Function.

Turn off formula help ScreenTips?

Click Office Button → Excel Options, click the Advanced header and then, in the Display section, uncheck the "Show function ScreenTips" box.

Show or hide the formula bar?

Click Office Button → Excel Options, click the Advanced tab and then, in the Display section, uncheck the "Show formula bar" box.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Using Named Ranges

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

The following answers show you how to streamline cell references by creating, renaming, editing, and deleting named ranges, as well as demonstrating how to use named ranges in formulas.

How do I…

Create a named range?

Select the cells you want in the range and click Formulas → Define Name. Type the name of the range in the Name field and click OK.

Your named range can't duplicate the name of a workbook cell. For example, DAY1 is a legal cell reference, so you can't use it as a range name. The range name DAY01 is acceptable, however. Named range names may also not start with numbers or contain special characters such as !, @, #, or $. Named range names may contain underscores, though.

Use a named range in a formula?

Type the name of the range in the formula where you would normally put the names of the cells, such as =SUM(Week1) instead of =SUM(A7:A13). The available named ranges appear in the Formula AutoComplete list as you type the formula.

Delete a named range?

Click Formulas → Name Manager. Select the range, click the Delete button, and click OK to verify the operation.

Rename a named range?

Click Formulas → Name Manager. Select the range and click Edit. Type a new name for the range and click OK.

Change the cells in a named range?

Click Formulas → Name Manager. Select the range. Select the text in the "Refers to" field, click the button on the right end of the field, select the new group of cells, click the button again, then click OK.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Defining Alternative Data Sets

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

The following answers show you how to extend the usability of a worksheet by defining alternative data sets, called scenarios. You will learn how to create, display, and edit scenarios; bring in scenarios from other workbooks; and summarize scenarios on a new worksheet.

How do I…

Create a scenario?

Click Data → What-If Analysis → Scenario Manager, click the Add button. Type a name in the "Scenario name" field. Click in the "Changing cells" field, click the button on the right end of the field, select the cells you want to change, click the button again, then click OK. In the Scenario Values dialog box, type the new values for the listed cells and click OK.

You can change up to 32 cells in a scenario.

Display a scenario?

Click Data → What-If Analysis → Scenario Manager.Click the scenario you want to display and click the Show button.

If you close a workbook with a scenario displayed, those values are saved as the new values for the changed cells. It is a good idea to create a "normal" scenario that contains the original values in your worksheet so you can restore them if you close your workbook while a scenario is displayed.

Edit a scenario?

Click Data → What-If Analysis → Scenario Manager. Select the scenario and click the Edit button. Type a new name in the "Scenario name" field. Click in the "Changing cells" field, select the cells you want to change, and click OK. In the Scenario Values dialog box, enter new values for the listed cells and click OK.

Merge scenarios from another worksheet?

Click Data → What-If Analysis → Scenario Manager, click the Merge button. In the Merge Scenarios dialog box, open the Book menu, select the desired workbook, and click OK.

Summarize existing scenarios?

Click Data → What-If Analysis → Scenario Manager, click the Summary button. Save the summary as a standard report or PivotTable, click in the Results cells field, select the cells containing the formulas that change as a result of the changed scenario data, and click OK.

Delete a scenario?

Click Data → What-If Analysis → Scenario Manager. Select the scenario and click the Delete button.

Find the value needed in one cell to produce a given result from a formula?

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Controlling How Data Is Displayed

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Use the following solutions to change the order of the data in your worksheets; limit what data is displayed; have Excel calculate subtotals for groups of data; and to define views that incorporate filter, sorting, and hidden row/column settings.

How do I…

Sort a column in ascending order?

Click a cell in the column, then click Home → Sort & Filter → Sort A to Z.

Sort a column in descending order?

Click a cell in the column, then click Home → Sort & Filter → Sort Z to A.

Make a sort case-sensitive?

Click Home → Sort & Filter → Custom Sort. Click the sorting rule you want to make case sensitive, and click Options. Check the "Case sensitive" box.

Sort a subset of a column's values?

Select the cells to sort, and select the desired sorting option. If the Sort Warning dialog appears, select "Continue with the current selection" and click Sort.

Sort multiple columns by the values in the left-most column?

Select the cells to sort and select the desired sorting option.

Sort multiple columns based on a user-defined order?

Select the cells to sort and click Home → Sort & Filter → Custom Sort. Define the primary sorting criteria in the Sort By section; you can add more criteria by clicking Add Level and filling in the Then By sections.

Type a list of values in a column, click Office Button → Excel Options, click the Popular category heading, and then click the Edit Custom Lists button. In the Custom Lists dialog box, verify that the cells appear in the "Import list from cells" field and click the Import button.

Sort using a custom data list?

Select the cells to sort, click Home → Sort & Filter → Custom Sort, click the Order down arrow, click Custom List, and then, in the Custom Lists dialog box, click the custom list by which you want to sort the data.

In previous versions of Excel, you could only use a custom data list as your primary sorting criteria. In Excel 2007, you can use one or more custom data lists as any sorting criteria.

Sort by cell color?

Select the cells to sort, click Home → Sort & Filter → Custom Sort, click the Sort On down arrow, and click Cell Color. Click the Order down arrow, click the color you want to appear at the top or bottom of the sorted list, and then select either On Top or On Bottom from the final list box in the criteria row.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Protecting All or Part of a Workbook

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

How do I…

Protect a worksheet?

Click Review → Protect Sheet. In the dialog box, type a password users can enter to remove the protection, and check the boxes for every action users are allowed to perform in this workbook.

Unprotect the worksheet if it's protected, select the range to be protected, and then Review → Allow Users to Edit Ranges. Click the New button, type a title for the range and a password to unlock it and confirm the password, and click OK. Now protect the worksheet using the steps in the previous item.

Protect a workbook?

Click Review → Protect Workbook. Type a password that must be entered to access the workbook.

Protect and share a workbook?

Click Review → Protect and Share Workbook. Check the "Sharing with track changes" box, and type a password that must be entered to access the workbook.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Spelling and Other Tools

Content preview·Buy PDF of this chapter|