Exploring The Excel Interface

Exploring the Excel Interface When you first open Microsoft Excel, the window displays a new, blank worksheet, in which you can enter, format, and analyze data using the various commands available in the menu bar. Toolbars provide easy access to common commands, as do shortcut menus, which you access by right-clicking the cells in the worksheet.

You enter formulas into the formula bar, which is located below the toolbars. The formula bar also provides access to all the functions available in Excel; these functions let you perform a variety of simple and complex calculations on the data contained in your worksheets. The horizontal and vertical scroll bars at the right and bottom of the Microsoft Excel window let you navigate to parts of the worksheet outside the visible area. Menu Options

The Microsoft Excel menu bar contains all the commands required to create, work with, print, and save workbooks. To access these commands, click the name of the menu to open it, and then click the command you want to access.

Each menu groups together related commands, sometimes using submenus to further group commands: • •

• •

•

•

•

•

•

File - Create, open, save, print, and close workbooks. Edit - Perform editing functions on your workbook, including copying, cutting, and pasting data and objects, moving to various locations in a workbook, and undoing and redoing actions. View - Change the workbook's display size and style. Insert - Insert new worksheets, or additional space, such as new cells, columns, or rows; also insert charts, comments, functions, hyperlinks and other objects into your workbooks. Format - Apply formatting to worksheets and their contents. Tools - Use Excel's tools, such as the spell and grammar checker, macro recorder, and protection and collaboration features, and set your Microsoft Excel preferences. Data - Sort, filter, and otherwise arrange the data on your worksheets. Window - Switch between open workbook windows , or split the window of the current workbook. Help - Access Microsoft Excel's online help.

Note: Additional menus may appear when you install a program that includes a Microsoft Excel plug-in. To access a menu command, click the main menu to open it, and then select (click) the command. Excel hides those commands you don't use frequently, so if you don't see a command, click the double arrows at the bottom of the menu to expand it. Excel then displays all the available commands. Once you select a command, Excel includes it with the others you commonly use. Toolbars The Microsoft Excel toolbars group together shortcuts to common commands; these shortcuts take the form of buttons. You can click a toolbar button to quickly apply formatting, save or print a workbook, copy or paste data, or accomplish another of a variety of tasks.

To show or hide a toolbar, open the View menu, select Toolbars, and then select the toolbar you want to show or hide. You can also access the toolbar menu by rightclicking anywhere on a visible toolbar.

The most frequently used toolbars are the Standard toolbar and the Formatting toolbar. The Standard toolbar contains buttons for opening, saving, printing, and editing workbooks.

The Formatting toolbar contains drop-down menus and buttons for applying formatting to worksheets.

By default, these two toolbars appear next to each other, just below the menu bar. Other toolbars available in Excel are: •

Chart - Create and work with charts.

•

•

• •

• • •

•

•

• •

Clipboard - View and select the contents of the clipboard. Control Toolbox - Insert ActiveX controls into a form. Drawing - Insert and format drawing objects. External Data - Work with data imported from external sources. Forms - Insert form objects. Picture - Insert and format pictures. Pivot Table - Create and work with pivot tables, interactive tables for large amounts of data. Reviewing - Insert, edit, and delete comments for or by reviewers. Visual Basic - Record and run macros, and work with Microsoft Word Visual Basic code. Web - Navigate a Web document. WordArt - Insert and format WordArt.

You can customize any of these toolbars by adding and removing buttons, or you can create your own toolbars to group your favorite commands: Just right-click one of the visible toolbars, select Customize from the menu that appears, and, in the Customize dialog, select the Commands tab to add or remove commands. Each of Excel's toolbars can be moved simply by clicking and dragging the title bar (if the toolbar is floating) or the move handle (if the toolbar is docked). The move handle is located at the left edge of the toolbar. When you move the mouse over it, the pointer changes to

horizontal and vertical arrows, indicating you can drag the toolbar.

1. Drag the toolbar off the row. The toolbar changes from docked to floating.

2. Click the title bar and drag the toolbar back into place. If you drag a toolbar to a full row, the surrounding toolbars will shrink to make room for it. To access a button that's no longer visible, click the chevrons at the right edge of the toolbar.

Tip: To quickly remove buttons you don't use, or add new buttons, select Add or Remove Buttons. In the menu, uncheck the buttons you don't need, or select new buttons to add. Select Customize to choose from all the available commands. Formula Bar The formula bar displays the contents of the active, or selected, cell. If the cell contains a formula, the formula

will appear in the formula bar, while the value resulting from the formula is displayed in the cell itself. You also use the formula to enter formulas. Clicking the Edit Formula button next to the formula bar opens the Formula Palette, which helps you select and construct functions. Functions are formulas that are built into Microsoft Excel, and allow you to perform a variety of calculations. The name box to the left of the formula bar displays the current location of your cursor. If a cell is selected, the cell is displayed here. If you're in the process of selecting a range of cells, the name box displays the range (for example, A1 X C9), and changes as the selection changes. Status Bar The status bar at the bottom of the Microsoft Excel window displays the current status of operations being performed on the open workbook, such as the progress as the workbook is saved. It also includes the AutoCalculate feature, which displays a selected calculation for any range (or set) of cells you select, automatically. By default, it displays the sum of the values found in the cells.

You can select a different type of calculation by rightclicking the AutoCalculate feature in the status bar and selecting a different type of calculation from the menu.

Section Review In this section, you learned:

•

The Excel interface Menu options Toolbars The formula bar

•

The status bar

• • •

Splitting the Window You can view different parts of the same worksheet at the same time by splitting the window. Splitting the window can be useful when you want to compare data in different locations, or copy date from one place to another. You can split the window both horizontally and vertically:

1.

To split the window horizontally, move the mouse over the split handle , the box at the top of the vertical scroll bar.

To split the window vertically, move the mouse over the split handle at the right of the horizontal scroll bar 2. When the mouse pointer changes to a double horizontal line with arrows, click and drag the split handle to the middle of the screen.

You can also split the window by selecting Split from the Window menu. This splits the window into four quadrants. To adjust the size of each of the panes, click and drag the border (split handle) separating them.

To return to a single window, double-click each of the split handles, or select Remove Split from the Window menu. Freezing Portions of the Window When you're comparing a lot of data, it's often helpful to freeze one portion of the worksheet, while you scroll through another portion. You can freeze a horizontal pane, a vertical pane, or both:

1. Select the row below, or the column to the right, of the place where you want the split to appear.

2.

From the Window menu, select Freeze Panes.

A thin line appears along the row or column, representing the split.

In the illustration above, the left pane is frozen. The horizontal scroll bar can be used to scroll through the right pane.

The contents visible on the left stay the same, while the contents visible on the right change as you scroll. Tip: You can freeze both a horizontal and a vertical pane by selecting the cell below and to the right of where you want the split.

This is useful if you have both row and column labels. You can freeze the labels while you scroll through the data.

To unfreeze the pane(s), select Unfreeze Panes from the Window menu. Hiding Rows and Columns

Instead of freezing portions of the window, you can hide rows or columns: 1. Select the row or column.

2.

From the Format menu, select Row or Column, and then select Hide.

The selection disappears.

Tip:

You can also hide entire worksheets by selecting Worksheet from the Format menu instead. To show the selection again, select Unhide from the same menu. Changing the Display Size To zoom into or out of areas of your worksheet, use the Zoom menu, located on the Standard toolbar.

To select a custom display size, select Zoom from the View menu and, in the Zoom dialog, enter a value into the Custom field.

Section Review In this section, you learned:

•

How to split the Excel window Freezing portions of the window Hiding rows and columns

•

Changing the display size (zoom)

• •

Understanding Workbooks and Worksheets A workbook is the Microsoft Excel file that stores your data. A workbook can contain many worksheets, which you use to enter and work with your data. You can enter and display data in multiple worksheets in a workbook, and perform calculations using data found across all the different worksheets. Worksheets are composed of cells, active areas in which you input data or formulas for calculation. Cells are aligned along columns, labeled with the letters A through IV, and rows, labeled with the numbers 1 through 65536.

When a cell is selected, its location appears in the name box, located to the left of the formula bar.

In Excel, this location is called a reference, and is used to point to values you want to use in a formula. References let you calculate data contained in different parts of your workbook, or in different workbooks altogether. References are indicated using the cell's column and row headings. Cell B2, shown in the name box above, refers to the cell in column B, row 2. Entering Text and Numbers To enter data into a cell, you must first select, or activate, the cell. To do this, you click the cell with your mouse, or move the cursor into the cell using the arrow keys on your keyboard. Once a cell is selected, type your data and press Enter (or Tab, if you want to move to the adjacent cell). Note: Among Excel's options is the ability to turn on and off editing in cells. When this option is turned on, you can double-click a cell and type directly into it. You can also double-click a cell and then select all or part of the data contained in the cell-if you want to format it, for example. When editing in cells is turned off, you must use the formula bar to enter and edit data: Select the cell,

type the data in the formula bar (or highlight the data in the formula bar, to edit it), and then press Enter. If you're having trouble editing directly in a cell, or wish to turn this option on or off: 1.

Select Options from the Tools menu.

The Options dialog opens. 2.

3.

Select the Edit tab.

Under Settings, check or uncheck the box labeled Edit directly in cell, as appropriate.

Working with Ranges

In Excel, you can work with multiple cells at once (called a range). To select a range, click the first cell and, holding down the mouse button, drag the mouse to the last cell. All the cells in the range you defined are selected.

Alternatively, you can select the first cell, hold down the Shift key, and then select the last cell in the range; this selects adjacent cells, as shown in the illustration above. If, instead, you want to select several cells that aren't adjacent, hold down the Ctrl key on your keyboard while you click each cell you want to select.

To quickly select an entire row or column, click the row or column heading, respectively. You can select multiple rows or columns by clicking on the first row or column heading in the range, holding down the

mouse button, and dragging to the last row or column heading in the range.

Tip: To select the entire contents of a worksheet, click the box in the upper left corner of the worksheet, where the first row and first column intersect:

Working with Ranges (Continued) Ranges in Excel are used in a variety of ways. You can select a range to apply the same formatting to all the cells (such as font, size, color, borders, and shading), or you can enter the same data into each of the cells in the range using the Ctrl + Enter shortcut: 1. Select the range.

2. Type the data.

3.

Press Ctrl + Enter.

Ranges are also used in performing calculations. When you enter a formula in the formula bar, you designate a range by typing the first cell in the range, followed by a colon (:), followed by the last cell in the range. For example: A1:F20 The range above includes cells A1 through A20, B1 through B20, C1 through C20, and so on through cell F20. Inserting Cells

Use the Insert command to insert a row, a column, or a blank cell into a worksheet. To insert a row or column: 1. Select the row below, or the column to the right, of the location where you want the new row or column inserted. 2. Select Rows or Columns from the Insert menu, as appropriate. Or, right-click the row or column and select Insert from the shortcut menu.

A new, blank row or column is inserted. Note that it assumes the size and formatting of the adjacent row or column.

To insert a blank cell: 1. Select the cell adjacent to the location where you want to insert the new cell. 2. Select Cells from the Insert menu. Or, rightclick the current cell and select Insert from the shortcut menu.

The Insert dialog opens.

3. Choose how you want to shift the existing cells to accommodate the new one. 4. Click OK. Deleting Cells To delete cells: 1. Select the cell, the range, or the row or column you want to delete. 2. From the Edit menu, select Delete. Or, rightclick the selection and select Delete from the shortcut menu.

Moving and Copying Data To move data from one cell to another: 1. Select the cell whose contents you want to move. 2. Point to the edge of the cell.

3. When the mouse pointer becomes an arrow, click and drag the cell to the new location.

4.

Release the mouse button.

You can drag and drop entire ranges of cells:

You can also use Excel's drag and drop functionality to copy cells; simply hold down the Ctrl key on your keyboard as you drag. Alternatively, you can use the Copy and Paste functions: 1. Select the cell whose contents you want to copy. 2. From the Edit menu, select Copy. Or, rightclick the cell and select Copy from the shortcut menu. (You can also use the Ctrl+c keyboard shortcut.)

A blinking dashed border appears around the cell.

3. Select the cell where you want the data to be copied. 4. From the Edit menu, select Paste. Or, rightclick the new cell and select Paste from the shortcut menu. (You can also use the Ctrl+v keyboard shortcut.)

5.

The data is pasted into the new cell.

You can continue to paste the same data into as many cells as you like. Tip: If you're only copying the data to one cell, you can press Enter as soon as you select the new cell. Excel will automatically paste the data. 6. To finish pasting and remove the dashed outline from the original cell, press Enter. Tip: To quickly copy a data to an adjacent cell: 1. Select the cell containing the data.

The fill handle is displayed as a small black square in the bottom right corner of the cell outline.

2. Click on the fill handle and, holding down your mouse button, drag to the adjacent cell. When that cell is highlighted, release the mouse button.

The data is copied to the adjacent cell.

This is useful when you need to fill many cells with the same data, since you can drag the fill handle across an entire range:

The data is copied to all the cells in the selected range.

In the illustration above, the cell being copied contained a formula that adds the values from the cells above. When the cell was copied, the formula was pasted across a range of cells-not the value displayed in the original cell. Excel adjusted the cell references in the formula for each new column, so that the values entered in the cells above were totaled. When you copy a formula, Excel adjusts the cell references by the number of rows and columns the formula was moved. So, for example, you can enter one formula that totals the values in a column, and copy the formula to many different columns without having to change the cell references for each column-Excel does it automatically. This is explained in more detail in Absolute and Relative Cell References. Note: Excel does not change the references when you move a formula.

Section Review In this section, you learned:

•

The differences between workbooks and worksheets Entering text and numbers Working with ranges of cells Inserting cells Deleting cells

•

Moving and copying data

•

• • •

Formatting Cells Formatting Numbers Numbers in Excel can be formatted in a variety of ways: as whole numbers, as decimals, as percentages, and even as currency. By default, numbers use the General format-this displays numbers exactly as you enter them (although Excel rounds the numbers using decimals or scientific notation if the cells are too narrow to accommodate what you've entered). To apply a different number format to the contents of a cell:

1. Select the cell.

2.

From the Format menu, select Cells.

The Format Cells dialog opens. 3.

Select the Number tab.

4.

All the available number formats are listed under Category. Select a category, such as Currency.

5. Formatting options for that category appear on the right side of the dialog. Select the options you want, such as the number of decimal places to use. 6. Click OK. 7. The formatting is applied to the selected cell (or range of cells).

Note: It isn't necessary to apply formatting to basic date and time entries. When you separate numbers with a slash (/) or a hyphen (-), Excel interprets the number as a

date. You can also enter a time, such as 12:00, followed by an a or a p to indicate morning or evening (for example, 12:00 p). If you don't specify morning or evening, Excel assumes the default a (morning). Changing the Appearance of Cells Use the Formatting toolbar to apply basic formatting to cells, such as font face, size, color, bold or italics: 1. Select the cell you want to format. Alternatively, you can select the contents of the cell, or a part of the contents, by double-clicking inside the cell and highlighting the contents you want to format (or selecting the contents in the formula bar).

2.

Use the buttons and drop-down menus on the Formatting toolbar to apply formatting. For example: a. Select a new size from the Font Size dropdown menu.

The font size is applied to the contents of the cell.

b.

Next, click the Bold formatting.

button to apply bold

c. Use the color picker to apply a color to the contents of the cell.

For example, select the red swatch to make the text in the cell red.

Additional font options are available in the Format Cells dialog: 1. Select the cell whose contents you want to format. 2. From the Format menu, select Cells. You can also right-click the cell (or range of cells) and select Format Cells from the shortcut menu. The Format Cells dialog opens. 3.

Select the Font tab.

Here, you can see a sample of the options you select in the Preview pane. You can also use the Format Cells dialog to change the alignment and orientation of the contents of cells. For example, you can rotate the contents of cells containing your column headings: 1. Select the cell whose contents you want to rotate. 2.

From the Format menu, select Cells.

The Format Cells dialog opens. 3.

Select the Alignment tab.

4. To change the orientation of your cell's contents, enter a new orientation, in degrees, into the Degrees field. You can also click and drag the red point in the text pane to a new location indicating the orientation you want. The number in the Degrees field changes as you drag the pointer.

5.

Click OK to apply the changes.

Applying Borders and Shading

To apply a simple border to a cell (or range of cells), use the Borders button on the Formatting toolbar: 1. Select the cell(s) you want to apply a border to.

2.

3.

On the Formatting toolbar, click the arrow next to the Borders button to view available border styles.

Select a border style to apply it to your cell(s). For example, select Outside Borders to place a border around the outside of a range of cells.

The border appears only around the outside of the range; no border appears between the individual cells.

To apply borders to each of the cells, choose All Borders instead.

Notice that the borders are rotated to match the orientation that was applied to the cell contents. Additional line styles are available in the Format Cells dialog: 1.

With your cells selected, select Cells from the Format menu.

The Format Cells dialog opens. 2.

Select the Border tab.

3.

4.

Under Style, select a line style. You can also select a color for the border using the Color drop-down menu. Click OK to apply the border.

Similarly, you can apply a fill color to cells using the Fill Color button on the Formatting toolbar: 1. Select the cells. 2. On the Formatting toolbar, click the arrow next to the Fill Color button and select a color swatch.

The color is applied to the selected cells.

To apply a pattern to the cells, use the Format Cells dialog: 1. Select the cells. 2. Select Cells from the Format menu. The Format Cells dialog opens. 3.

Select the Patterns tab.

4.

5.

The swatches under Color let you choose a fill color for the cells, just as the Fill Color button on the Formatting toolbar does. If necessary, select a fill color. Select a pattern from the Pattern drop-down menu. The patterns are shown in swatches above the color swatches.

6. You can apply a color to the pattern by selecting one of the color swatches from the menu. If you don't select a color, the pattern will be black (for example, black dots). 7. Click OK to apply the pattern to the cells.

Using Format Painter

You can quickly copy formatting from one cell to another using the Format Painter: 1. Select a cell containing the format you want to copy.

2.

Click the Format Painter Standard toolbar.

button on the

A blinking dashed border appears around the cell whose format is being copied. 3. Select the cell you want to apply the formatting to.

The formatting of the original cell is copied to the new cell.

Section Review In this section, you learned:

•

How to format cells Formatting numbers Changing the appearance of cells How to apply borders and shading

•

Using the format painter

• • •

Workbooks Creating a New Workbook To create a new workbook: 1.

From the File menu, select New.

The New dialog opens.

2.

Make sure the General tab is selected, and then double-click Workbook.

A new blank workbook opens. Tip: To quickly create a new workbook from the default template, click the New button on the Standard toolbar. Excel also provides a number of custom templates you can use. These are available on the Spreadsheet Solutions tab in the New dialog. Saving a Workbook

To save your current (open) workbook, select Save from the File menu, or click the Save button on the Standard toolbar. If this is the first time you've save the workbook, the Save As dialog opens, prompting you to enter a file name and select a location for the file.

To save changes to your open workbook, click the Save button again. Excel saves the file in its current location. Tip:

To save a copy of your workbook under a new file name, use the Save As command again. In the Save As dialog, enter a new name for the workbook file. Selecting Worksheets Use the sheet tabs at the bottom of the Excel window to switch between sheets in a workbook. Simply click on the tab for the sheet you want to activate. Note: The active worksheet is the worksheet that's currently selected; its contents are visible in the Microsoft Excel window. You activate a worksheet in order to work in it.

The scroll buttons to the left of the sheet tabs let you display tabs that may be hidden.

Click the left and right scroll buttons to scroll through the tabs.

You can click the first and last scroll buttons to move to the first and last tabs, respectively.

To see a list of all the available sheets, right-click one of the scroll buttons. Then select a sheet from the shortcut menu to activate it.

Tabs might be hidden if the split bar is positioned far to the left. You can click and drag the split bar to view a larger number of tabs.

Once you've repositioned the split bar, use the scroll buttons to scroll back through the tabs. The tabs will appear in the new space you've created.

To select multiple sheets in a workbook, click a sheet tab and then hold down the Ctrl (Control) key while clicking each additional sheet tab you want to select.

If you're selecting adjacent sheet tabs, you can click the first tab and then hold down the Shift key while clicking the last tab in the range you want to select. To select all the sheets in a workbook, right-click one of the sheet tabs and select Select All Sheets from the shortcut menu. When you select multiple worksheets, the changes you make are applied to all the sheets in the selection. Inserting a Worksheet To insert a new worksheet into the open workbook, select Worksheet from the Insert menu, or rightclick a sheet tab and select Insert from the shortcut menu.

When the new sheet is inserted, you can name it and, if necessary, move it to the desired location in the workbook. Naming Worksheets

It's helpful to name the individual worksheets in a workbook, especially when the workbook contains a number of sheets. The easiest way to do this is to double-click the sheet tab for the worksheet you want to name, and then type the new name and press Enter.

You can also right-click the sheet tab and select Rename from the shortcut menu. This highlights the current name, allowing you to type over it with a new name. Press Enter when you've finished.

Moving and Copying Worksheets To move a worksheet to a new location in the current workbook, use your mouse to drag and drop the sheet tab: 1. Press and hold the mouse button over the sheet tab for the worksheet you want to move. 2. Holding down the mouse button, drag the mouse to the new location.

3. Release the mouse button. The sheet tab for the worksheet appears in the new location.

You can also copy the worksheet to a new location by holding down Ctrl key while you drag the sheet tab. To copy multiple sheets, press Ctrl while you select each sheet and then release the Ctrl key. Then press the Ctrl key again and, holding it down, drag the group of sheet tabs to the new location.

Finally, release the mouse button, and then release the Ctrl key. The sheets are copied to the new location.

To move or copy worksheets to a different workbook:

1. Open both workbooks-the one that currently contains the worksheets and the one that you want to move or copy the worksheets to. 2. Activate the workbook that currently contains the worksheets by clicking its button on the Windows taskbar. 3. Select the sheets you want to move or copy. 4. From the Edit menu, select Move or Copy Sheet, or right-click the appropriate sheet tab (or group of selected tabs) and select Move or Copy from the shortcut menu. The Move or Copy dialog opens.

5.

From the To book drop-down menu, select the workbook you want to move or copy the worksheet(s) to.

Tip: You can move or copy the sheets to a new workbook by selecting (new book) instead. 6.

7.

Under Before sheet, select the location in the workbook where you want to move or copy the sheets. The sheets will appear before the worksheet you select here. To copy the worksheets to the new location, check the Create a copy checkbox, and then click OK.

To move the worksheets, leave the box unchecked and simply click OK. Deleting Worksheets To delete sheets from a workbook, select the sheets and then select Delete Sheet from the Edit menu. You can also right-click the sheet tab (or group of tabs) and select Delete from the shortcut menu.

Keep in mind that when you delete a worksheet, you delete all the data and functions that the sheet contains. Click OK to confirm the deletion. Printing a Workbook To print your workbook, the current worksheet, or a selection within the active worksheet, select Print from the File menu, or click the Print button on the Standard toolbar. This opens the Print dialog.

Under Print what: •

Select Entire workbook to print all the sheets in the workbook.

•

Select Active sheet(s) to print only the active worksheet.

To print multiple worksheets (but not the entire workbook), select the worksheets by Ctrl + clicking the sheet tabs for the sheets you want to print, and then, in the Print dialog, select Active sheet(s). •

Select Selection to print only the selected cells. For example, to print cells A1 through G10 in the active worksheet, select the cells with your mouse, click the Print button, and then select Selection.

When you've defined the print settings, click OK to print. Defining a Print Area You can also define a specific print area within a worksheet so that only that area is printed. For example, you may want to print a chart without printing the values you used to create that chart. To define a print area: 1. Select the cells that comprise the area you want to print. 2. From the File menu, select Print Area, and then Set Print Area from the submenu.

3.

To print the contents of the print area, click the Print button and, under Print what, leave Active sheet(s) selected.

Excel prints only the area you've defined. To clear the print area, select Clear Print Area from the Print Area submenu. Even when you've defined a print area, you can still print other cells in the active worksheet. To do so, simply select the cells you want to print, click the

Print button, and, under Print what, select Selection. Closing a Workbook To close the current workbook, select Close from the File menu, or simply click the Close Window button in the upper right corner of the Excel workbook window. To close all Excel, along with all open workbooks, click the Close button in the upper right corner of the Excel window. If you have outstanding (unsaved) changes, Excel will ask you if want to save them before closing. Click Yes to save the changes, or No to close the window and discard the changes. Section Review In this section, you learned: • • • • • • •

Creating a new workbook Saving a workbook Selecting worksheets Inserting a worksheet Naming worksheets Moving and copying worksheets Deleting worksheets

•

Printing a workbook

•

Closing a workbook

Excel Worksheets Basic Formulas A formula is an equation that calculates a value for a cell or a range of cells. You enter a formula into the formula bar preceded by an equal (=) sign. This tells Excel to calculate a value for what you've entered, rather than treating your entry as plain text. Formulas can be very simple-such as addition or subtraction-or very complex, performing multiple calculations and referencing additional formulas in numerous cells. Formulas remain behind the scenes; that is, cells display the resulting values of the formulas you've entered, rather than the formulas themselves. Because formulas can refer to other cells in a worksheet, other worksheets, or even other workbooks, the value of a cell containing a formula will change as the contents of the referenced cells change. For example, if you want to calculate the total number of widgets you sell in the year 2004, and for each month of the year you have a cell containing the

total number of sales that month, you can enter a formula into a "Total Annual Sales" cell to add together the sales for each month. By the end of June, you may have only sold 715 widgets:

But by the end of the year, as new figures are added for the remaining months, your total widget sales may climb to over 1,300:

As you can see, Excel automatically updates this total as figures are entered into the cells for monthly sales totals. Note that in the example above the formula that calculates the value for the total annual sales actually refers to cells containing formulas of their own. Cell B6 adds together the contents of cells B2 through B4;

cell C6 adds together the contents of cells C2 through C4; and so on. Cell C9, which calculates the total annual sales, adds together the values of cells B6 through M6. Thus, Excel can calculate values that are then used to calculate new values for other cells. Constructing a Basic Formula To enter a formula into a cell: 1. Select the cell. 2. In the formula bar, enter an equal (=) sign, and then type the formula. 3. Press Enter. Similarly, you can view a formula contained in a cell by clicking the cell. When the cell is selected, the formula is displayed in the formula bar.

To edit the formula, you simply change it in the formula bar and press Enter.

The simplest formulas can use familiar operators, such as: • • • •

The plus sign (+) for addition The minus sign (-) for subtraction The asterisk (*) for multiplication The backslash (/) for division

Excel calculates formulas using standard mathematical rules; specifically, values are calculated from left to right, with any operations contained in parenthesis performed first. For example, if you want to calculate the average sales among all three widgets for the month of January, you might enter the following formula into a new cell below the monthly total: =(B2+B3+B4)/3 In this formula, "B2" refers to the value contained in cell B2; "B3" refers to the value contained in cell B3; and "B4" refers to the value contained in cell B4. If you later change the value of one of those cells-for example, you find 46 units of Widget 1 were sold, and not 45-Excel will update the total. The number "3" in the formula is a constant-a value that doesn't change, unlike the cell references, whose values can change if you edit the data in the cells.

When you press Enter, Excel calculates the value and displays it in the cell.

In the example above, it isn't necessary to include the parentheses in the formula, since Excel calculates the values from left to right. However, using parentheses to indicate a combined value (in this case, the total of three cells) is often helpful, particularly in more complex formulas. The formula can be further simplified by replacing the three referenced cells with a single reference to

the cell containing the formula that calculates January's total sales, cell B6: =B6/3 Excel Functions Excel provides many built-in formulas, called functions, which make it much simpler to perform calculations. In the last section, the following formula was used: =(B2+B3+B4)/3 "B2", "B3", and "B4" represented the number of widgets sold in the month of January. "B2" represented Widget 1 sales, "B3" Widget 2 sales, and "B4" Widget 3 sales, as shown in the sample spreadsheet below:

To calculate the total number of sales for the month of January, you could enter the formula =B2+B3+B4 into cell B6 (next to Total). However, it's easier to use the SUM function, particularly when you're adding together a large number of cells in a range:

=SUM(B2:B4) Functions begin with the name of the function (for example, "SUM") followed by parentheses enclosing the specific values being calculated (called arguments). These values can be constants, cells references, or even text you want to combine. To refer to a range of cells, type the first cell in the range, then a colon (:), and then the last cell in the range (for example, "B2:B4"). You enter a formula that uses a function the same way you'd enter any other formula: select the cell, type an equal sign (=) and then the formula, with its function, in the formula bar, and press Enter.

Excel calculates the value and displays it in the cell. Functions simplify formulas by telling Excel to use a predefined method of calculating values. Microsoft Excel includes functions for calculating averages, statistics, dates, and much more. In many cases, you

need only supply simple data, such as the range of cells to be used in the calculation. Using the Formula Palette Excel's Formula Palette can help you construct formulas that contain functions by displaying a list of the built-in functions and the arguments used for each. To access the Formula Palette: 1. Select the cell in which you want to enter the formula. 2. Click the Edit Formula button in the formula bar. 3. The Formula Palette opens.

4.

From the Functions drop-down menu, select a function that describes the type of calculation you want to perform.

Tip: Select More Functions to see additional functions. When you select a function, the Formula Palette displays fields in which you can enter the arguments (the values to be calculated), as well as a description of the function and the current result of the calculation.

In the example above, the AVERAGE function has been selected. The AVERAGE function adds together the values (called arguments) you supply and then divides this total by the number of values that were added, which Excel calculates for you. In this function, you only need supply the values whose average you want to calculate. 5.

Enter the values you want calculated into the Number 1 field.

By default, Excel has assumed you want to use the values of the range of cells above the cell containing the formula (B2:B6). This range can be deleted and another one can be entered, and the formula displayed in the formula bar will change accordingly. Alternatively, you can enter constants separated by commas, such as "3,5,7,9", in which case Excel will calculate the average of the numbers 3, 5, 7, and 9. Below, "B6" has been changed to "B4" so Excel will calculate the average of the values in cells B2 through B4.

Tip: If you like, you can hide the Formula Palette and use your mouse to select the range of cells whose values you want Excel to use in the formula: a.

Click the button to the right of the Number 1 field .

The Formula Palette disappears, leaving only the field.

b. If necessary, delete the existing contents of the field. Then select the range of cells you want to use in the formula.

As you select the cells, Excel enters the range into the field, updating it as you move your mouse. c.

Click the button to the right of the field display the Formula Palette again.

to

6.

When you've finished entering the values for your function, click OK to close the Formula Palette.

Excel calculates the value and displays it in the cell.

Absolute and Relative Cell References By default, Excel uses relative cell references. Relative cell references are those whose locations Excel calculates based on the location of the formula. This means that when you insert a new row or column into a range included in a formula, Excel automatically includes the new data in its calculations, and edits the formula accordingly. Similarly, when you copy a formula from one cell to another, Excel adjusts the cell references. For example, you can copy the formula in cell B6 below (which adds together the values in the range B2:B4) to cell C6, and Excel will adjust the formula to add together the values in the range C2:C4.

This is because the cell range used in the original formula is relative-it tells Excel to use the values in the cells 4, 3, and 2 rows above the formula. The copy of the formula, in cell C6, also looks for values 4, 3, and 2 rows above-in the range C2:C4. As another example, suppose the International Widget Company decided to include the sales of widget accessories in the January monthly total. A new row is inserted below row 4 (which contains the monthly values for Widget 3). This moves the row containing the formula down one row, to B7. In the new row, total sales are entered for the widget accessories. As soon as a value is entered into cell B5, Excel changes the formula in cell B7 (formerly B6) from =SUM(B2:B4) to =SUM(B2:B5), and updates the resulting value:

In contrast, absolute cell references are references that refer to a specific location. These are indicated by a dollar sign ($) preceding the part of the reference that should be absolute-that is, before the row reference, before the column reference, or before each, to make the entire cell reference absolute: • $B4 indicates that column B is absolute, but row 4 is relative. • B$4 indicates that column B is relative, but row 4 is absolute. • $B$4 indicates that the entire reference-to cell B4-is absolute. When you use an absolute reference, Excel will not adjust formulas when they're copied to new locations. Note:

If you include in your formula a range that uses absolute cell references (for example, =SUM($B$2:$B$4)) and then insert a row or column that produces a new cell located within that range (for example, you insert a new row before row 4), Excel will adjust the range in the formula, just as it would if you'd used relative references. This is because Excel assumes that you still want your calculation to include the first and last cells you initially specified. However, if you insert a new below row 4 (as we did when we added Widget Accessories), Excel will not adjust the range in the formula, and cell B5 (newly inserted) will not be included in the calculation. Absolute references should be used when you want to use the same calculated value in different locations in a workbook (for example, if you want to include total annual sales, calculated on the first sheet of a workbook, in subsequent worksheets). Tip: To quickly change a cell reference from relative to absolute (or vice versa), select the cell and press F4 on your keyboard. This toggles the references through the various combinations of absolute and relative columns and rows.

Protecting a worksheet When you protect a worksheet, you prevent users from viewing or making changes to the contents, depending on the protection options you use: •

•

If you want to prevent users from viewing certain rows or columns, hide them before you protect the worksheet using the Hide command in the Format Rows or Columns submenu. If you want to prevent users from viewing formulas in particular cells: 1. Select the cells whose formulas you want to hide. 2. From the Format menu, select Cells.

The Format Cells dialog opens. 3.

Select the Protection tab.

4. 5. •

Check the Hidden checkbox. Click OK.

If you want users to be able to enter or edit data into certain cells, but not others, you must unlock those cells you want them to be able to use before you protect the worksheet: 1. Select the cells you want users to be able to edit. 2. From the Format menu, select Cells.

The Format Cells dialog opens. 3.

Select the Protection tab.

4. 5.

Uncheck the Locked checkbox. Click OK.

Once you've set the properties you want for the individual rows, columns, and cells in the worksheet, you can protect the worksheet. To do so: 1.

From the Tools menu, select Protection, and then select Protect Sheet from the submenu.

The Protect Sheet dialog opens.

2.

3.

4.

5.

To protect the contents of the worksheet, with the exception of any cells you unlocked, make sure the Contents checkbox is checked. Selecting Objects protects graphics, charts, and comments in the worksheet. Selecting Scenarios prevents users from changing and deleting scenarios. To password-protect the worksheet, enter a password into the Password field.

You'll be prompted to re-enter the password. Do so to confirm the password and click OK. 6.

Click OK.

To remove protection from a worksheet, select Protection and then Unprotect Sheet from the Tools menu. If you entered a password when you protected the sheet, you'll be prompted to re-enter it now. Do so, and then click OK.

Section Review In this section, you learned:

•

How to work with worksheets Basic formulas Constructing a formula Excel functions Using the formula palette Absolute and relative cell references

•

Protecting a worksheet

• • • • •

Formatting Worksheets Using Styles Styles are collections of formatting choices that you can apply to the data in your cells, or to entire cells, ranges, or worksheets. Later, you can quickly change the formatting of all the cells that use a particular style, simply by changing the formatting of the style. Microsoft Excel includes built-in styles to format numbers as currency, percentages, and whole numbers with commas. You can modify these styles to suit the needs of your worksheets, or you can create your own custom styles.

Applying a Style The common number styles can be applied using the buttons on the Formatting toolbar: •

Click

to apply the currency style.

•

Click

to apply the percent style.

•

Click

to apply the comma style.

Additional options are available from the Style dialog: 1.

With the cell(s) selected, select Style from the Format menu.

The Style dialog opens.

2.

3.

Select a style from the Style name drop-down menu. The features of the style appear below, under Style includes. Click OK.

Modifying an Existing Style To modify an existing style: 1.

From the Format menu, select Style.

The Style dialog opens.

2.

3.

Select the style you want to modify from the Style name drop-down menu. Click the Modify button.

The Format Cells dialog opens.

4. Use the options on the various tabs to select the formatting you want the style to use. 5. Click OK to close the Format Cells dialog. 6. If necessary, check (select) or uncheck any formatting options under Style includes. 7. Click Add. 8. To apply the style to the selected cell(s), click OK. Otherwise, click Close to close the Style dialog. Creating a New Style To create a new style: 1.

From the Format menu, select Style.

The Style dialog opens. 2.

Enter a name for the new style into the Style name field.

3.

Click the Modify button.

The Format Cells dialog opens. 4.

5.

6. 7.

Use the options on the various tabs to select the formatting you want the style to use, and then click OK. Check (select) or uncheck any formatting options under Style includes.

Click Add. To apply the new style to the selected cell(s), click OK. Otherwise, click Close to close the Style dialog.

Copying Styles from Other Workbooks Excel also lets you copy styles contained in other workbooks:

1. Open both workbooks-the one containing the styles you want to copy and the one you want to copy the styles into. 2. In the workbook that will receive the styles, select Style from the Format menu. The Style dialog opens.

3.

Click the Merge button.

The Merge Styles dialog opens.

Select the workbook that contains the styles you want to copy and click OK. 5. If both workbooks contain styles with the same names (for example, the default number styles), confirm that you want to replace the existing styles in the current workbook. 4.

If you don't want to replace the existing styles, click No when prompted. Only new styles will be added to the current workbook.

Using AutoFormats The AutoFormat feature lets you quickly format a range of cells using one of a variety of built-in formatting combinations. When you add columns or rows to a range that's been AutoFormatted, the new cells automatically assume the AutoFormat's properties. To apply an AutoFormat: 1.

Select the cells you want to format.

2.

From the Format menu, select AutoFormat.

The AutoFormat dialog opens.

3. Scroll through the list to view the available formatting combinations. Select a format by clicking on it. 4.

You can choose to use only some of the properties of the selected format by clicking the Options button.

5. Uncheck any of the properties you don't want to use. 6. Click OK. The formatting is applied to the selected cells.

To remove an AutoFormat, simply select the cells, open the AutoFormat dialog, and select None, located at the end of the list. Section Review In this section, you learned:

•

Formatting worksheets Using styles Modifying styles Creating a new style Copying styles from other workbooks

•

Using autoformats

• • • •