"Formatting a Worksheet"
Beginning Excel Agenda November 28, 2006 Welcome and Agenda Getting to know the screen o Parts of the Excel window o Navigating a Worksheet Entering Data o Entering Text o Entering Dates o Entering Values o Entering Formulas *Order of Precedence Working with Ranges o Selecting Ranges *Adjacent Ranges *Nonadjacent Ranges o Other Selection Techniques o Moving/Copying a Selection of Cells Formatting Worksheet Data o Changing the Size of a Column or Row o Inserting a Row or Colum o Deleting a Row or Column o Using the Formatting Toolbar *Comma Style *Currency Style *Increase/Decrease number of decimal places *Percent Style *Centering column titles o Copying Formats *Format Painter o Using the Format Cells Dialog Box o Changing Font Type, Size, Style and Color Fill Command Calculating with AutoSum Print w/Print Preview Tips and Tricks *AutoFormat *Hide Cells *Protect Cells More Functions *Average *Max *Min *Count & CountA *If Then Statement *Introduce HLookup Charting Getting to Know the Screen Parts of the Excel window name box Select all column headings button active cell row headings sheet tabs The excel window is referred to as the workbook window or worksheet window. The worksheet window provides a grid of columns and rows, and the intersection of a column and row is called a cell. Each cell is identified by a cell reference, which is its column and row location. The column letter is always the first in the cell reference. The cell in which you are working is called the active cell. Excel identified the active c ell by outlining it with a dark border. The cell reference for the active cell appears in the name box. Navigating a Worksheet Excel provides several ways of moving around within a worksheet. You can use your mouse to click a cell to make it the active cell, or you can use the vertical and horizontal scroll bars to display the area of the worksheet containing the cell you want to make active. You can also navigate a worksheet by using your keyboard. Below is a list of keyboard shortcuts that Excel provides so you can move from cell to cell within the worksheet quickly and easily. Keystroke Action Ctrl + Home Moves the active cell to cell A1. Ctrl + End Moves to the last cell in the worksheet that contains data Enter Moves the active cell down one cell F5 Opens the Go To dialog box, in which you can specify the cell you want to move to Home Moves that active cell to column A of the current row Page Up, Page Down Moves the active cell up or down one full screen Tab, Shift + Tab Moves the active cell to the right or left one cell Entering Data Entering Text To insert text into a worksheet cell, you first make the cell active and then you type the text you want the cell to contain. Excel automatically aligns the text with the left edge of the cell. Note: To enter multiple lines of text within a cell, press the Alt + Enter. Entering Dates In Excel, dates are treated as numeric values, not text. This allows you to perform calculations with dates, such as determining the number of days between two dates. Entering Values Values are numbers that represent a quantity of some type. Values can be numbers or negative numbers. Values can also be expressed as currency or as percentages. Not all numbers are treated at values. For example, Excel treats a telephone number (1-800-555-1212) or a social security number (555-33-2222) as a text entry. As you type information into a cell, Excel determines whether the entry can be treated as a value, and if so, automatically right-aligns the value within the cell. Entering Formulas The single most important reason for using a spreadsheet is to perform calculations on data. To accomplish that goal, you need to enter formulas. A formula is a mathematical expression that calculates a value. Excel formulas always begin with an equal sign (=) followed by an expression that describes the calculation to be done. A formula can contain one or more arithmetic operators, such as +, -, *, or /. Operation Operator Example Description Addition + =10+A5 Adds 10 to the value in cell A5 =B1+B2+B3 Adds the values of cells B1, B2, and B3 Subtraction - =C9-B2 Subtracts the value in cell B2 from the value in cell C9 =1-D2 Subtracts the value in cell D2 from 1 Multiplication * =C9*B9 Multiplies the value in cell C9 by the value in cell B9 =E5*0.06 Multiplies the value in cell E5 by 0.06 Division / =C9/B9 Divides the value in cell C9 by the value in cell B9 =D15/12 Divides the value in cell D12 by 12 Exponentiation ^ =B5^3 Raises the value in cell B5 to the third power =3^B5 Raises 3 to the power specified in cell B5 If an expression contains more than one arithmetic operator, Excel performs the calculations in the order of precedence. The order of precedence is a set of predefined rules that Excel follows to calculate a formula by determining which operator is applied first, second, and so forth. First is exponentiation (^), second is multiplication (*) or division (/), and third is addition (+) or subtraction (-). When building a formula, you must add parentheses to change the order of operations. Excel will calculate any expression contained within the parentheses before any other part of the formula. Example: The formula =3+4*5 equals 23 The formula =(3+4)*5 equals 35 Working with Ranges A group of worksheet cells is called a cell range, or just range. Ranges can be either adjacent or nonadjacent. An adjacent range is a single rectangular block. A nonadjacent range consists of two or more separate adjacent ranges. Selecting Ranges Selecting Adjacent or Nonadjacent Ranges of Cells To select an adjacent range of cells: Click a cell in the upper-left corner of the rectangle that comprises the adjacent range. Press and hold the left mouse button, and then drag the pointer through the cells you want selected. Release the mouse button. To select a nonadjacent range of cells: Select an adjacent range of cells. Press and hold the Ctrl key, and then select another adjacent cell range. With the Ctrl key still pressed, continue to select other cell ranges until all of the ranges are selected. Release the mouse button and Ctrl key. Other Selection Techniques You can also select large cell ranges that extend beyond the borders of the work book window. Selecting a large cell range using the mouse drag technique can be slow and frustrating. For this reason, Excel provides keyboard shortcuts that enable you to quickly select large blocks of data without having to drag through the worksheet to select the necessary cells. To Select… Action A large range of cells Click the first cell in the range, press and hold down the Shift key, and then click the last cell in the range. All of the cells between the first and last cell are selected. All Cells on a worksheet Click the Select All button, the gray rectangle in the upper-left corner of the worksheet where the row and column headings meet. All cells in an entire row or column Click the row or column heading. A range of cells containing data Click the cell where you want to begin the selection of the range, press and hold down the Shift key, and then double-click the side of the cell in the direction that you want to extend the selection. Excel selects all adjacent cells that contain data, extending the selection of the range to the first empty cell. Moving/Copying a Selection of Cells You can use the “drag and drop” technique to move/copy a cell range. First select the range of cells to move/copy. To move the cells, position the pointer over the selection border, and drag the selection to a new location. To copy the data, press the Ctrl key while you drag the selection to the new location. A copy of the original data will then appear at the location of the pointer when you release the mouse button. Formatting the Worksheet Changing the Size of a Column or Row You can change the width of one column or multiple columns or the height of one row or multiple rows. Excel provides several methods for changing the width of a column or the height of a row. Changing the Column Width or Row Height Click the column or row heading whose width or height you want to change Click Format on the menu bar, point to Column or Row, and then click Width or Height (or click AutoFit or AutoFit Selection to make the column or row as large as the longest entry of the cells). In the Column Width or Row Height dialog box, enter the new column width or row height, and the click the OK button. or Drag the column or row heading dividing line to the right or up to increase the column width or row height, or drag the dividing line to the left or down to decrease the column width or row height. or Double-click the column or row heading dividing line to make the column or row as large as the longest entry of the cells in the column or row. Inserting a Row or Column You can insert rows and columns in a worksheet, or you can insert individual cells within a row or column. When you insert rows, Excel shifts the existing rows down. When you insert columns, Excel shifts the existing columns to the right. If you insert cells within a row, Excel shifts the existing cells down; if you insert cells within a column, Excel shifts the existing cells to the right. Inserting a Row or Column into a Worksheet Select a cell where you want to insert the new row or column. Click Insert on the menu bar, and then click Rows or Columns or Right-click a cell where you want to insert a new row or column, and then click Insert on the shortcut menu. In the Insert dialog box, click the Entire row or Entire column option button, and then click the OK button. To insert multiple rows or columns, you select a cell range that contains multiple rows or columns before applying the Insert command. For example, to insert two new blank rows, select two rows or any portion of two rows. Sometimes you might need to insert individual cells, rather than an entire row or column Using the Formatting Toolbar The Formatting toolbar is one of the fastest ways to format a worksheet. By clicking a single button on the Formatting toolbar, you can increase or decrease the number of decimal places displayed in a selected range of cells, and display a value as currency with a dollar sign or a percentage with a percent sign. You can also use the Formatting toolbar to change the font type, style, color or size. Comma Style The comma style button formats the number with a comma every third digit to the left, and adds 2 decimal places by default. Currency Style The currency style button formats the active cell as a dollar amount. Increase/Decrease number of decimal places These buttons increase or decrease the number of decimal places shown in the active cell. Percent Style The percent style button formats the active cell as a percentage. Centering column titles This button is used to center the data in the active cell. The adjacent buttons can also be used to left/right-align the data. Using the Format Cells Dialog Box The Format Cells Dialog box is found under the “Format” menu. Locate and click on “Cells…” to bring up the box shown below. Use the tabs to browse different formatting categories. Changing Font Type, Size, Style and Color Here you can customize the text in the active cell.