Microsoft Excel 2003 Create a New Workbook Open a Saved Workbook Change Margins Zoom In/Out Save a Workbook Save to a New Location or with a Different Name Change to Landscape Orientation Moving from Cell to Cell Selecting Cells A single cell A range of cells A large range of cells An entire row An entire column Adjacent rows or columns Nonadjacent cells All cells of a worksheet Click the New icon on the standard toolbar Click the Open icon on the standard toolbar.. Click the document to be opened. Click Open. File, Page Setup. Change settings Click the Zoom button on the standard toolbar Click the Save icon in the standard toolbar. Name the document and select location to save. File, Save As. Name the document and Select location to save. File, Page Setup. Click the Page tab. Click on Landscape. Click in any cell to move to that cell. Arrow Keys -Next cell in direction of arrow Tab -Next cell to the right Enter -Next cell below Ctrl+Home -Cell A1 on worksheet Ctrl+End -Last Cell with data Home -First column of current row Click in the cell Click first cell and drag to last cell Click in first cell then Shift+click in last cell Click the row heading (the row number) Click the column heading (the column letter) Drag across the row or column headings. Or select the first row or column and then Shift+click the last row or column heading Select the first cell, then Ctrl+click in each of the other cells Click the blank rectangle at top left corner where the row headings and column headings intersect Formatting Changing Fonts Changing Font Size Changing Font Color Aligning Data Select cell(s). Click the Font drop down on the formatting toolbar. Choose a font. Select cell(s). Click the Font Size drop down on the formatting toolbar. Choose a size. Select cell(s). Click the Font Color drop down on the formatting toolbar. Choose a color. Select cell(s). Click appropriate Align icon in the formatting toolbar. Donna McGauley, Instructional Technology Specialist 1 Change Row Height (Manually) Change Row Height (To an Exact Height) Change Column Width (Manually) Change Column Width (To an Exact Width) Change Column Width Insert a Row Insert a Column Format Data as Currency Format Dates Click and drag the line under the row header. Drag it down to increase row height, up to decrease row height. Go to Format - Row - Height. Enter height in points (72 pts.=1 inch). Click OK Click and drag the line to the right of the column header. Drag right to increase the width, left to decrease. Go to Format - Column - Width. Enter width in points (12 pts.=1 inch). Click OK Select the entire column. Format – Column - AutoFit Selection (To AutoFit longest entry) Select the entire row below where you want the new row inserted. Insert - Rows Select the entire column to the right of where you want the new column inserted. Insert - Columns Select cells to be formatted. Format - Cells. Click Number tab. Click Currency and choose options. Select cells to be formatted. Format - Cells. Click Number tab. Click Date and choose options. Create a workbook Rename, insert, delete, and move worksheets Enter data To rename sheet tabs: Right-click a sheet tab at the bottom left of the window, and then click Rename. (Or on the Format menu, point to Sheet, and then click Rename.) Type the new name, and then press ENTER. To add color to sheet tabs: Right-click the sheet tab at the bottom left of the window, and then click Tab Color. (Or on the Format menu, point to Sheet, and then click Tab Color.) In the Format Tab Color dialog box, select the color you want, and then click OK. To insert a worksheet: On the Insert menu, click Worksheet. To delete a worksheet: Right-click the sheet tab of the sheet you want to delete. Click Delete. (Or on the Edit menu click Delete Sheet.) To move a worksheet: Right-click the sheet tab of the worksheet you want to move, and then click Move or Copy. In the Move or Copy dialog box, choose where you want the tab to go, and then click OK. To create a new workbook if you've already opened Excel, click New on the Standard toolbar. Or on the File menu, click New. In the New Workbook task pane, click Blank workbook. Press TAB to move the insertion point to the right one cell on the same row. Press ENTER to move the insertion point down one cell in the same column. Donna McGauley, Instructional Technology Specialist 2 Data-entry timesavers Edit data Insert and delete columns and rows To enter dates: Use a slash or a hyphen to separate the parts of a date. For example, 7/15/2005 or 16-July-2005. To enter today's date, press CTRL+; (semicolon). To enter a time: Type a space and then an "a" or a "p" after the time. For example, 9:00 p. Otherwise, Excel enters the time as AM. To enter the current time, press CTRL+SHIFT+; (semicolon). To enter numbers: Negative numbers entered with parentheses (100) will be displayed with a minus sign: -100. To enter fractions, leave a space between the whole number and the fraction. For example, 1 1/8. To enter a fraction only, enter a zero first. For example, 0 1/4. AutoFill: Use the replicator handle to enter the months of the year, days of the week, consecutive dates, or a series of numbers. You type one or more entries, and then use the handle to fill in the rest. AutoComplete: If the first few characters you type in a cell match an entry you've already made in the same column, Excel will fill in the remaining characters for you. Just press ENTER. This works for text or for entries with text and numbers. It does not work for numbers only, nor does it work for dates or times. Do one of the following: Double-click the cell that contains the data you want to edit. Or, Click the cell that contains the data you want to edit and click anywhere in the formula bar. To delete characters, press BACKSPACE, or highlight them and then press DELETE. To insert characters, click where you want to insert them, and then type. To overwrite characters, highlight them and then type the new characters. To enter your changes, press ENTER or TAB. To insert columns, click a cell immediately to the right of where you want to insert a new column. On the Insert menu, click Columns. To delete, click a cell in the column. On the Edit menu, click Delete. Choose Entire column and click OK. To insert or delete another column, press F4. To insert a row, click a cell in the row immediately below where you want the new row. Then on the Insert menu, click Rows. To delete a row, click a cell in the row. On the Edit menu, click Delete. Choose Entire row and click OK. Donna McGauley, Instructional Technology Specialist 3 To create a chart Create a chart Choose a chart type Decide what to chart Chart Wizard options Select the data you want to chart, and then do any of the following: Click the Chart Wizard button on the Standard toolbar. Click the Chart command on the Insert menu. Press F11. To make more choices about the chart, click Next and select options in the Chart Wizard. To create a quick chart, just click Finish. When the Chart Wizard opens, Excel selects the Column chart type. If you select another chart type on the Standard Types tab, you can click the Press and Hold to View Sample button to get an idea of what your data will look like in that chart type. If you select another chart type on the Custom Types tab, you will see an automatic preview. On the Data Range tab of the Chart Wizard, you can select either Rows or Columns in the Series in option. Your choice determines which data is charted. The worksheet values that are charted are called the data series. You can change the selection and see in the preview how the chart will look. The Chart Wizard has many options, all of which you can preview in the wizard. The options available depend on the chart type. Chart titles: Add descriptive titles to your chart so that people don't have to guess what the chart's about. For a column chart, this tab has boxes for three titles: one for the main chart title at the top, and one for each of the chart axes. After you enter the titles, they can be seen in the preview. Axes: Hide or display the information on the side or bottom of the chart. Gridlines: Hide or display the lines that extend across the chart. Legend: Place the chart legend in different locations on the chart, not just to the right. Data Labels: Label the names in the data series, label the amounts of charted values, and label the categories on the charted values. Data Table: Display the data used to create the chart in a grid below the chart. You might select this option when you place a chart on a separate sheet and want to have the worksheet data visible along with the chart. Chart Location: Place a chart As new sheet by itself or As object in on the worksheet with the data. When you create a chart the quick way by clicking Finish in the first step of the wizard, the chart is automatically placed As object in. Donna McGauley, Instructional Technology Specialist 4 Create professional-looking charts Select chart elements Make formatting changes Change chart colors Revise fonts Select the chart area by clicking the drop-down list in the Chart Objects box in the Chart toolbar and then selecting Chart Area. If the toolbar does not appear go to the View menu - Toolbars, and then click Chart. After you select a chart item, you can format it or revise it by right-clicking and selecting the command from the shortcut menu. The chart item you select determines what commands are available on the shortcut menu and the Format or Chart menus. Select the individual chart item to format. Then select the item's Format dialog box by: Clicking the Format menu. Right-clicking the shortcut menu. Double-clicking the chart item. Begin by selecting the chart item you want to change. For example, to change the plot color, select the plot area. To change the color of a data series, select a data series. On the Format menu or on the shortcut menu, click the command for the selected chart item. To add a gradient fill, click Fill Effects. Click One color under Colors. Click the arrow in the Color 1 box and select a color. You can experiment with the shade by moving the slider in the Colors box to make the shade darker or lighter. Make a selection under Shading styles. You can see the result in the preview. Click OK twice. Change all fonts at one time Click the border of the chart to select it or click in the white area of the chart. In the Font box on the Formatting toolbar, click the font you want. Change one font at a time Select a title. In the Font box on the Formatting toolbar, click the font you want. You might also make text larger or smaller by clicking the Font Size box on the Formatting toolbar. Print labels using Excel data in a Word mail merge In Excel, set up the data to use in the mail merge. Make sure the column labels clearly identify the type of data in the column; this helps you select the right data as you construct the mail merge. For example, labels such as First Name, Last Name, Address, and City are better than Column 1, Column 2, Column 3, and Column 4. Make sure you have a separate column for each element that you want to include in the mail merge. On the Tools menu, point to Letters and Mailings, and then click Mail Merge Wizard. Donna McGauley, Instructional Technology Specialist 5 In the first two steps of the Mail Merge Wizard, select the document type and start the document. If you're creating mailing labels, click Labels in Step 1, and then click Label options in Step 2 to select the size and type of labels to print. In the third step, under Select recipients, click Use an existing list, and then click Browse. In the Look in list, click the folder in which you saved the workbook with your data, click the workbook, and then click Open. In the Select Table dialog box, locate and click your list. Make sure the First row of data contains column headers check box is selected, and then click OK. In the Mail Merge Recipients dialog box, click any column labels in your data that correspond to the Word identifiers on the left. This step makes inserting your data in the form documents easier. For more information about matching fields, see Word Help. If you want to include only selected recipients in the mail merge, click Edit recipient list and select the recipients you want. Use the rest of the wizard steps to write, add recipient information, preview, personalize, save, and print or e-mail your documents. Working with big worksheets Freeze panes Split panes Column names: Select the first row below the names. Row names: Select the first column to the right of the names. Both column and row names: Click the cell that is both just below the column names and just to the right of the row names. You split panes by making a selection in the worksheet, and then clicking Split on the Window menu. You can split panes into: Two panes above and below each other: Select the row below where you want the split to appear. Two side-by-side panes: Select the column to the right of where you want the split to appear. Four panes: Click the cell below and to the right of where you want the split to appear. To remove the split, click Remove Split on the Window menu. Or double-click the split bar to remove the split. Keyboard shortcuts Arrow keys SHIFT+Arrow key CTRL+Arrow key F5 F2 Moves one cell up, down, left, or right Extends a selection cell by cell as you press the arrow key. Moves to the edge of your data. Opens the Go To dialog box. Edits the active cell and positions the insertion point at the end of the contents of the cell. Donna McGauley, Instructional Technology Specialist 6 CTRL+END CTRL+HOME PAGE UP PAGE DOWN CTRL+PAGE UP CTRL+PAGE DOWN ALT+PAGE UP ALT+PAGE DOWN CTRL+SHIFT+PAGE UP CTRL+SHIFT+PAGE DOWN Moves the insertion point to the last used cell on the worksheet. Moves the insertion point to the beginning of the worksheet. Moves one screen up. Moves one screen down. Moves to the previous sheet. Moves to the next sheet. Moves one screen to the left. Moves one screen to the right. Selects the current and previous sheet. Selects the current and next sheet. Entering formulas Add, divide, multiply, and subtract Use cell references in formulas Cell references A10 A10,A20 A10:A20 B15:E15 A10:E20 Add the values in a row or column Type an equal sign (=), use math operators, and then press ENTER. =10+5 to add =10-5 to subtract =10*5 to multiply =10/5 to divide Formulas are visible in the formula bar when you select a cell that contains a result. If the formula bar is not visible, on the Tools menu, click Options. Click the View tab, and select the Formula bar check box. Entering cell references lets Excel automatically update formula results if cell values are changed. For example: Type=C4+C7 in a cell. Or type the equal sign (=), click cell C4, then type the plus sign (+), and finally click cell C7. Refer to values in the cell in column A and row 10 cell A10 and cell A20 the range of cells in column A and rows 10 through 20 the range of cells in row 15 and columns B through E the range of cells in columns A through E and rows 10 through 20 Use the SUM function, which is a prewritten formula, to add all the values in a row or column: Click a cell below the column of values or to the right of the row of values. Click the AutoSum button on the Standard toolbar, and then press ENTER. To add some of the values in a column or row: Type an equal sign, type SUM, then type an opening Donna McGauley, Instructional Technology Specialist 7 parenthesis. Type or select the cell references you want to add. A comma (,) separates individual arguments that tell the function what to calculate. Type a closing parenthesis, and then press ENTER. For example: =SUM(B2:B4,B6) and =SUM(B2,B5,B7) Use the AVERAGE, MAX, or MIN functions. Click a cell below or to the right of values for which you want to find the average (arithmetic mean), the maximum, or the minimum. Click the arrow next to AutoSum on the Standard toolbar. Click Average, Max, or Min, and then press ENTER. To see more functions, click More Functions on the AutoSum list to open the Insert Function dialog box. Into an adjacent cell using the fill handle: Select the cell that contains the formula, then position the mouse pointer over the lower-right corner of the cell until the black cross (+) appears. Drag the fill handle over the cell or cells to which you want to copy the formula, then release the mouse button. Without using the fill handle: Select the cell that contains the formula, and on the Edit menu, click Copy. Select the cell or cells that you want to copy it to. To copy the formula and any formatting, on the Edit menu, click Paste. To copy the formula only, on the Edit menu, click Paste Special, and then click Formulas. ##### The column is not wide enough to display the content. Increase column width, shrink contents to fit the column, or apply a different number format. #REF! A cell reference is not valid. Cells may have been deleted or pasted over. #NAME? You may have misspelled a function name. Cells with errors such as #NAME? may display a color triangle. If you click the cell, an error button appears to give you some error correction options. How to use the button is not covered in this course. If a formula has more than one operator, Excel follows the rules of operator precedence instead of just calculating from left to right. Multiplication is done before addition: =11.97+3.99*2 is 19.95. Excel multiplies 3.99 by 2, and then adds the result to 11.97. Operations inside parentheses take place first: =(11.97+3.99)*2 is 31.92. Excel adds first and then multiplies the result by 2. Excel Find the average, maximum, or minimum Copy a formula Understand error values Use more than one math operator in a formula Donna McGauley, Instructional Technology Specialist 8 uses operators from left to right if they have the same level of precedence. Multiplication and division are on the same level. Lower than multiplication and division, addition and subtraction are on the same level. Lists How to create a list How to add or delete list rows and columns How to total, count, or average list data How to sort and filter data Create a custom filter Covert a list to a range Select the range of data that you want to make into a list, or just click inside the data if you want it all. On the Data menu, point to List, and then click Create List. If your data has column headings, make sure that the check box My list has headers is selected. Click OK. If your list does not have headers, Excel will create them for you. They'll say "Column1," "Column2," and so on. The list is identified by a dark blue border. When you select a cell, row, or column outside a list, the list becomes inactive and is surrounded by a light blue border. Use any of the following methods: Type data in one of the cells with the asterisk. This will automatically add a row to the end of the list, and move the asterisk to identify the next row as the insert row. Type in an empty row adjacent to the list. The list will automatically expand to include that row, unless the total row has been displayed, or the last row of the list is also empty. Type in an empty column adjacent to the list. The list will automatically expand to include the column. To sum data in the last column, on the List toolbar, click the Toggle Total Row button. To turn off the total, click the button again. If the last column contains data that cannot be summed, such as a column of names, Excel will count the number of items instead of giving a sum. You can do more calculations than sums. Click in the cell with the sum. An arrow will appear to the right. Click the arrow and make a selection from the menu. Click in a list to activate the AutoFilter arrows. Then click any arrow and make a selection to either sort or filter data in that column. Click the AutoFilter arrow in the column you want to filter. Click Custom to open the Custom AutoFilter dialog box. Then make your selections in the dialog box. Click in the list to activate the List toolbar. On the List toolbar, click List. Click Convert to Range. You'll see a message asking whether you want to convert the list to a normal range. Click Yes. The data no longer has any of the special list functionality. It's just normal data in Excel. Donna McGauley, Instructional Technology Specialist 9 Getting Around on Worksheets Zoom the view to select vast ranges of cells Find All You can change the view to select a huge range of cells that extends beyond the window borders. Click the Zoom box on the Standard toolbar to change the view from 100%. Then you can see the entire range that you want to select. You can also click Zoom on the View menu to do the same thing. To use Find All to find all instances of the same thing entered in cells throughout a worksheet: On the Edit menu, click Find. Type what you want to find in the Find what box. Click Find All. Copy and move worksheets All instances of what you are looking for will appear in a list in the Find and Replace dialog box. Click a specific occurrence in the list and the insertion point goes right to the specific cell in the worksheet. To copy a worksheet, do one of the following: Hold down CTRL while you drag the sheet along the row of sheet tabs. When you get to the location where you want to add the copied worksheet, release the mouse button and then the CTRL key. Or, Right-click a worksheet tab and then click Move or Copy on the shortcut menu. Click the sheet that you want to copy in the Before sheet list. Then select the Create a copy check box and click OK. Or, On the Edit menu, click Move or Copy Sheet. Click the sheet that you want to copy in the Before sheet list. Then select the Create a copy check box and click OK. To move a worksheet, do one of the following: Drag the worksheet tab of the sheet that you want to move to its new position. Or, Right-click the worksheet tab that you want to move, and then click Move or copy on the shortcut menu. Click the position that you want to move the sheet to in the Before sheet list, and then click OK. Or, Click the worksheet tab of the sheet that you want to move. On the Edit menu, click Move or Copy Sheet. Click the position that you want to move the sheet to in the Before sheet list, and then click OK. Create multiple worksheets with common data or Group the worksheets on which you want the common data or formatting. To group: Two or more adjacent sheets: Click the tab for the first sheet, Donna McGauley, Instructional Technology Specialist 10 formatting at the same time and then hold down SHIFT and click the tab for the last sheet. Two or more nonadjacent sheets: Click the tab for the first sheet, and then hold down CTRL and click the tabs for the other sheets. All sheets in a workbook: Right-click a sheet tab, and then click Select All Sheets on the shortcut menu. [Group] will appear in the title bar at the top of the worksheet. On the first worksheet, enter all the common data or common formatting that you want. Ungroup the worksheets by clicking any unselected worksheet tab. If no unselected worksheet tab is visible, right-click the tab of a selected sheet, and then click Ungroup Sheets on the shortcut menu. Filter and sort data [Group] will disappear from the title bar to indicate that the worksheets are no longer grouped. The information that you entered on the first worksheet will be on the subsequent worksheets. Data that is in rows and columns can be filtered and sorted. On the Data menu, point to Filter, and then click AutoFilter. An arrow appears at the top of the column in which you've selected data to filter. Click the arrow and select what you want to see. When you filter data, the other data is hidden from view. You can also sort data differently by clicking the AutoFilter arrow and then clicking either Sort Ascending or Sort Descending in the list Functions and arguments Search for a function Display a function tooltip Click the Insert Function button on the formula bar to open the Insert Function dialog box. Type what you want to do in the Search for a function box, and then click Go. (If you don't get a useful response, try other keywords. Or review the list of function types in the Or select a category box.) In the Select a function box, click a function to read its description near the bottom of the Insert Function dialog box. Click OK to insert the selected function in your worksheet and open the Function Arguments dialog box. You can display function tips for all built-in Excel functions. Select a cell, type an equal sign (=), type a function name, then type an opening parenthesis: =PMT( Once the opening parenthesis is typed, the function tip appears with the function arguments listed in order of entry. Entering a Donna McGauley, Instructional Technology Specialist 11 Find descriptions of function arguments value and then typing a comma causes the next listed argument to appear in heavy dark type. A comma separates each argument from the next; without commas, a function will not work. In the Function Arguments dialog box Click in the box beside an argument name. Read the argument's description near the bottom of the dialog box. Use financial formulas Figure out mortgage payments with the PMT function Figure out how long it will take to pay off a personal loan with the NPER function Figure out how much to save with the PMT function To figure out the monthly payment for a $180,000 30-year mortgage with a 6 percent annual interest rate, use the PMT function, =PMT(6%/12,30*12,180000) The function's structure is: =PMT(rate, nper, pv, [fv], [type]) Note Arguments in square brackets [ ] are optional. PMT arguments: Rate is interest rate, 6%/12 (months). Nper is number of payments, 30*12 (months). Pv is present value, $180,000. Fv is future value, zero (0). An optional argument that need not be filled in unless it is not zero. Type is type of payment date, first or last day of the month. An optional argument assumed to be zero (0) for the last day, unless filled in as one (1) for the first day. To figure out the time to pay off a personal loan of $2,500, at $150 a month, with a 5 percent annual interest rate, use the NPER function, =NPER(5%/12,-150,2500) The function's structure is: =NPER(rate, pmt, pv, [fv], [type]) NPER arguments: Rate is interest rate, 5%/12 (months). Pmt is payment amount, $150. Pv is present value, $2,500. Fv is future value, zero (0). An optional argument that need not be filled in unless it is not zero. Type is type of payment date, first or last day of the month. An optional argument assumed to be zero (0) for the last day, unless filled in as one (1) for the first day. To figure out how much you need to save each month, at an annual interest rate of 6 percent, to have $60,000 in 18 years, use the PMT function, Donna McGauley, Instructional Technology Specialist 12 =PMT(6%/12,18*12,0,60000) The function's structure is: =PMT(rate, nper, pv, [fv], [type]) PMT arguments are listed above. Note: The pv (present value) argument is 0 because you start from nothing. You could also type this formula without the zero if you enter a second comma: =PMT(6%/12,18*12,,60000) Printing options Use print preview Fit data on one page Fit data on a specific number of pages To open print preview, click Print Preview on the File menu. Or click the Print Preview button on the Standard toolbar. Different ways to fit more data on one page: Change margins: In print preview, click Setup, and then click the Margins tab (or on the File menu, click Page Setup). Then type in the boxes or click the arrows. Reduce data size for printing: In print preview, click Setup, and then click the Page tab (or on the File menu, click Page setup). Under Scaling, click Fit to. Leave 1 in the page(s) wide by box and 1 in the tall box. Change page orientation In print preview, click Setup, and then click the Page tab (or on the File menu, click Page Setup). On the Page tab in the Page Setup dialog box, under Orientation, click Landscape. Click OK. In print preview, click Setup, then click the Page tab (or on the File menu, click Page Setup). Click Fit to. Leave 1 in the pages(s) wide by box (or put some other number in the box if the worksheet is very wide). In the tall box, enter the number of pages you want the worksheet to print on. If you have a big worksheet with a lot of data, and the Adjust to percentage is 50% or less when you try to fit it to one page, try changing the value in the pages(s) wide by box from 1 to another number. Then check print preview to see the result. If your worksheet is too long to fit on one page, delete the number in the tall box. That will allow the worksheet to fit all columns on each page while extending to several pages lengthwise as you add more rows. Create page breaks On the View menu, click Page Break Preview. Point to the dotted blue line (the automatic page break) that you want to change. Drag the line to the location where you want the new page break to be. To reset an individual page break, drag the line to the left and off the print area. To reset all page breaks at once, right-click Donna McGauley, Instructional Technology Specialist 13 and then click Reset All Page Breaks. Print just a portion of a worksheet Print multiple worksheets at the same time Print column or row titles on every page, or row and column headings on every page Add headers and footers Print cell gridlines Center data on the page Print formulas Select the area you want to print. Then, on the File menu, click Print. Under Print what, click Selection. If you want to save your selected area for printing again, on the View menu, click Page Break Preview. Select the area you want to print again. On the File menu, point to Print Area, and then click Set Print Area. This print area is saved when you save the worksheet. When you're ready to print again, on the File menu, click Print. Only the set print area will be printed. To clear the set print area, on the File menu, point to Print Area, and then click Clear Print Area. Hold down CTRL and click the tab of each worksheet that you want to print. On the File menu, click Print. In the Print dialog box, under Print what, click Active sheet(s). On the File menu, click Page Setup. Then click the Sheet tab. In the Rows to repeat at top box, enter the rows that contain the column titles. For example, $1:$1 is the first row. In the Columns to repeat at left box, enter the columns that contain the row titles. For example, $A:$A is the first column. In the Page Setup dialog box, on the Sheet tab, select the Row and column headings checkbox. That will print the alphabetical column headings at the top of each column and the numerical row headings on the left of each row. In print preview, click Setup, and then click the Header/Footer tab. To create a custom header or footer, click the Custom Header or Custom Footer button. Then click in the Left section, Center section, or Right section box. You can enter your own text in any section. (If your text contains an ampersand (&), you must enter two, like this: &&.) If you're not sure what the buttons in the custom header and custom footer dialog boxes do, click the buttons to see what is inserted. The buttons insert code with an ampersand. For example &[Date]. To delete custom headers and footers, select the code and delete it. In print preview, click Setup. Or on the File menu, click Page Setup. Click the Sheet tab. Under Print, select the Gridlines check box. In print preview, click Setup. Or on the File menu, click Page Setup. Click the Margins tab. Under Center on page, select the Horizontally and Vertically check boxes. On the Tools menu, point to Formula Auditing, and then click Formula Auditing Mode. Then print. To return to the formula results, repeat the steps above. Donna McGauley, Instructional Technology Specialist 14 Prevent formula errors from printing In print preview, click Setup, or on the File menu, click Page Setup, and then click the Sheet tab. Under Print, click the arrow beside the Cell errors as box. Select from the options in the box Use formulas to edit, correct, and proofread text Type formulas correctly Change capital letters Delete extra spaces Count characters in a cell Combine first and last names in one cell Compare cells Separate characters on the left Separate characters on the right Separate the first word out of two words Delete a formula without deleting formula results Formulas must be typed exactly as shown. Missing a comma or parenthesis, inserting an extra space, or misspelling a function name, will produce errors. For example, misspelling a function name will produce the #NAME? error instead of a formula result. If you type function names without using all capital letters, Excel will convert the function name to capitals. Start each formula by typing an equal sign (=). In cell B1, type =PROPER(A1) to change to initial capitals: Nancy Davolio In cell B1, type =UPPER(A1) to change case to all capitals: NANCY DAVOLIO In cell B1, type =LOWER(A1) to change case to no capitals: nancy davolio In cell B1, type =TRIM(A1) to remove all spaces except the one between words. TIP To change case and delete extra spaces in one step, type a nested formula in cell B1: =PROPER(TRIM(A1)) You could also type =TRIM(PROPER(A1)) In cell B1, type =LEN(A1) In cell C1, type =A1&" "&B1. The space between the quotation marks in the formula inserts a space between the two names. To reverse the order of the names, type =B1&", "&A1. The comma and the space between the quotation marks in the formula insert a comma and a space between the names. In cell C1, type =EXACT(A1,B1). Results are TRUE (the cells are identical), or FALSE. In cell B1, type =LEFT(A1,5). In cell B1, type =RIGHT(A1,6). The result is the last 6 characters from cell A1. In cell B1, type =LEFT(A1,FIND(" ",A1)-1). The result is all characters to the left of the space. Select the information that contains formulas. Click the Copy button on the Standard toolbar. Click the arrow to the right of the Paste button on the Standard toolbar, and then click Values. Press ESC to exit Paste mode. Donna McGauley, Instructional Technology Specialist 15
© Copyright 2024