Excel Handout

Notes on Microsoft Excel About workbooks and worksheets • Workbooks In Microsoft Excel, a workbook is the file in which you work and store your data. Because each workbook can contain many sheets, you can organize various kinds of related information in a single file. • Worksheets Use worksheets to list and analyze data. You can enter and edit data on several worksheets simultaneously and perform calculations based on data from multiple worksheets. When you create a chart, you can place the chart on the worksheet with its related data or on a separate chart sheet. • Sheet tabs The names of the sheets appear on tabs at the bottom of the workbook window. To move from sheet to sheet, click the sheet tabs. Open a workbook 1. On the File menu, click Open . 2. Use the column browser to locate the workbook. 3. Double-click the workbook you want to open. Close a workbook 1. On the File menu, click Close. • To close all open workbooks without exiting the program, hold down SHIFT and click Close All on the File menu. Saving Save a new, unnamed workbook 1. Click Save . 2. To save the workbook in a folder other than the default folder, use the column browser to locate the folder you want. 3. In the Save As box, type a name for the workbook. 4. Click Save. Save an existing workbook • Go to File menu, click Save • Or click on the save icon on the toolbar Save a copy of a workbook 1. Open the workbook you want to copy. 2. On the File menu, click Save As. 3. In the Save As box, type a new name for the workbook. 4. Click Save. Working with worksheets Insert a new worksheet 1. To add a single worksheet, click Worksheet on the Insert menu. 2. To add multiple worksheets, hold down SHIFT, and then click the number of worksheet tabs you want to add in the open workbook. Then click Worksheet on the Insert menu. 1 of 7

Rename a sheet 1. Double-click the sheet tab. 2. Type a new name over the current name. Delete sheets from a workbook 1. Select the sheets you want to delete. 2. On the Edit menu, click Delete Sheet. Insert blank cells 1. Select a range of existing cells where you want to insert the new blank cells. Select the same number of cells as you want to insert. 2. On the Insert menu, click Cells. 3. Click Shift cells right or Shift cells down. Insert columns 1. To insert a single column, click a cell in the column immediately to the right of where you want to insert the new column. For example, to insert a new column to the left of Column B, click a cell in Column B. 2. To insert multiple columns, select columns immediately to the right of where you want to insert the new columns. Select the same number of columns as you want to insert. 3. On the Insert menu, click Columns. Insert rows 1. To insert a single row, click a cell in the row immediately below where you want the new row. For example, to insert a new row above Row 5, click a cell in Row 5. 2. To insert multiple rows, select rows immediately below where you want the new rows. Select the same number of rows as you want to insert. 3. On the Insert menu, click Rows. Working with Data Enter numbers, text, a date, or a time 1. Click the cell where you want to enter data. 2. Type the data and press RETURN or TAB. Notes: • Excel considers all numbers to be values and are right aligned by default • All text is left aligned by default. • If you would like numbers to be treated as text (label) use an apostrophe before the numbers. Copy data within a row or column 1. Select the cells that contain the data you want to copy. 2. Drag the fill handle across the cells you want to fill and then release the mouse button. Existing values or formulas in the cells you fill are replaced. Edit cell contents 1. Double-click the cell that contains the data you want to edit. 2. Make any changes to the cell contents. 3. To enter your changes, press RETURN. 2 of 7

4. To cancel your changes, press ESC. Clear contents, formats, or comments from cells 1. Select the cells, rows, or columns you want to clear. 2. On the Edit menu, point to Clear, and then click All, Contents, Formats, or Comments. Delete cells, rows, or columns 1. Select the cells, rows, or columns you want to delete. 2. On the Edit menu, click Delete. Surrounding cells shift to fill the space. Check spelling 1. Select the range of cells you want to check. To check the entire sheet, click any cell. 2. On the Standard toolbar, click the More Buttons indicator (the small triangle at the end of the toolbar), and then click Spelling . Formatting Change the font or font size 1. Select whole cells or the specific text in a single cell that you want to format. 2. To change the font, on the Formatting Palette, under Font, click the font you want on the Font pop-up menu Center or align data in a cell • In cells that have the default General format, text is left-aligned, and numbers, dates, and times are right-aligned. Changing the alignment does not change the type of data. 1. Select the cells you want to format. 2. To align data, on the Formatting Palette, under Text Alignment, click a button next to Horizontal or Vertical. Change column width 1. Drag the boundary on the right side of the column heading until the column is the width you want. 2. If you want the width of the column to match the contents, go to Format menu, Column, Autofit selection Change how a number looks without changing the number • In Microsoft Excel, you can use number formats to change the appearance of numbers, including dates and times, without changing the number behind the appearance. For example, you can display a number such as .08 as 8%. 1. Select the cells you want to format. 2. On the Format menu, click Cells, and then click the Number tab. 3. In the Category list, click a category, and then select the options you want.

3 of 7

Add or remove a currency symbol 1. Select the cells you want to format. 2. On the Format menu, click Cells, and then click the Number tab. 3. In the Category list, click Currency. 4. On the Currency symbol pop-up menu, do one of the following: 5. To add a currency symbol, select the one you want. Apply conditional formats to cells 1. Select the cells you want to format. 2. On the Format menu, click Conditional Formatting. • To use values in the selected cells as the formatting criteria, click Cell Value Is, select the comparison phrase and then type a value in the appropriate box. You can enter a constant value or a formula; you must include an equal sign (=) before the formula. • To evaluate data or a condition other than the values in the selected cells, use a formula as the formatting criteria. Click Formula Is in the pop-up menu on the left, and then enter the formula in the box on the right. The formula must evaluate to a logical value of TRUE or FALSE. 3. Click Format. 4. Select the font style, font color, underlining, borders, shading, or patterns you want to apply. 5. To add another condition, click Add, and then repeat steps 3 through 5. Merge cells to span several columns or rows 1. To merge cells in a row, column, or both, select the cells you want to merge. 2. On the Formatting Palette, select the Merge Cells check box under Text Alignment. Split a merged cell into separate cells 1. Click the merged cell. 2. On the Format menu, click Cells, and then click the Alignment tab. 3. Clear the Merge cells check box. Working with Formulas Enter a formula 1. Click the cell in which you want to enter the formula. 2. Type = (an equal sign). 3. Enter the formula. 4. Press RETURN. Enter a formula that contains a function 1. Click the cell in which you want to enter the formula. 2. To start the formula with the function, click Edit Formula Edit Formula button in the formula bar. 3. Click the down arrow next to the Functions box Functions box. 4. Click the function you want to add to the formula. If the function does not appear in the list, click More Functions for a list of additional functions. 5. Enter the arguments. 6. When you complete the formula, press RETURN. 4 of 7

The difference between relative and absolute references • Relative references When you create a formula, references to cells or ranges are usually based upon their position relative to the cell that contains the formula. In the following example, cell B6 contains the formula =A5; Microsoft Excel finds the value one cell above and one cell to the left of B6. This is known as a relative reference. • When you copy a formula that uses relative references, Excel automatically adjusts the references in the pasted formula to refer to different cells relative to the position of the formula. In the following example, the formula in cell B6, =A5, which is one cell above and to the left of B6, has been copied to cell B7. Excel has adjusted the formula in cell B7 to =A6, which refers to the cell that is one cell above and to the left of cell B7. • Absolute references If you don’t want Excel to adjust references when you copy a formula to a different cell, use an absolute reference. For example, if your formula multiples cell A5 with cell C1 (=A5*C1) and you copy the formula to another cell, Excel will adjust both references. You can create an absolute reference to cell C1 by placing a dollar sign ($) before the parts of the reference that do not change. To create an absolute reference to cell C1, for example, add dollar signs to the formula as follows: =A5*$C$1 CHARTS Creating a chart • Charts are visually appealing and make it easy for users to see comparisons, patterns, and trends in data. For instance, rather than having to analyze several columns of worksheet numbers, you can see at a glance whether sales are falling or rising over quarterly periods, or how the actual sales compare to the projected sales. • You can create a chart on its own sheet or as an embedded object on a worksheet. You can also publish a chart on a Web page. To create a chart, you must first enter the data for the chart on the worksheet. Then select that data and use the Chart Wizard to step through the process of choosing the chart type and the various chart options. Chart Types • Area: An area chart emphasizes the magnitude of change over time. By displaying the sum of the plotted values, an area chart also shows the relationship of parts to a whole. • Column: A column chart shows data changes over a period of time or illustrates comparisons among items. Categories are organized horizontally, values vertically, to emphasize variation over time. • Bar: A bar chart illustrates comparisons among individual items. Categories are organized vertically, values horizontally, to focus on comparing values and to place less emphasis on time. • Line: A line chart shows trends in data at equal intervals. • Pie: A pie chart shows the proportional size of items that make up a data series to the sum of the items. It always shows only one data series and is useful when you want to emphasize a significant element. How worksheet data is represented in a chart A chart is linked to the worksheet data it’s created from and is updated automatically when you change the worksheet data. • Axis values Microsoft Excel creates the axis values from the worksheet data. Note that the axis values in the example above range from 0 to 140000, which encompasses the range of 5 of 7

•

•

•

values on the worksheet. Unless you specify differently, Excel uses the format of the upper-left cell in the value range as the number format for the axis. Category names Excel uses column or row headings in the worksheet data for category axis names. In the example above, the worksheet row headings 1st Quarter, 2nd Quarter, and so on appear as category axis names. You can change whether Excel uses column or row headings for category axis names or create different names. Chart data series names Excel also uses column or row headings in the worksheet data for series names. Series names appear in the chart legend. In the example above, the row headings Projected and Actual appear as series names. You can change whether Excel uses column or row headings for series names or create different names. Data markers Data markers with the same pattern represent one data series. Each data marker represents one number from the worksheet. In the example above, the rightmost data marker represents the Actual 4th Quarter value of 120000.

Embedded charts and chart sheets • You can create a chart on its own chart sheet or as an embedded chart on a worksheet. Either way, the chart is linked to the source data on the worksheet, which means the chart is updated when you update the worksheet data. • Embedded charts An embedded chart is considered a graphic object and is saved as part of the worksheet on which it is created. Use embedded charts when you want to display or print one or more charts with your worksheet data. • Chart sheets A chart sheet is a separate sheet within your workbook that has its own sheet name. Use a chart sheet when you want to view or edit large or complex charts separately from the worksheet data or when you want to preserve screen space as you work on the worksheet. Create a chart 1. Select the cells that contain the data that you want to appear in the chart. 2. If you want the column and row labels to appear in the chart, include the cells that contain them in the selection. 3. Click Chart Wizard . 4. Follow the instructions in the Chart Wizard. Create a chart from nonadjacent selections 1. Select the first group of cells that contain the data you want to include. 2. While holding down z, select any additional cell groups you want to include. 3. The nonadjacent selections must form a rectangle. 4. Click Chart Wizard . 5. Follow the instructions in the Chart Wizard. Change category (X) axis labels • To change category (X) axis labels on the worksheet, click the cell that contains the label name you want to change, type the new name, and then press RETURN. • To change category (X) axis labels on the chart, click the chart, and then click Source Data on the Chart menu. In the Category (X) axis labels box on the Series tab, specify the worksheet range you want to use as category (X) axis labels. You can also type the labels you want to use, separated by commas, for example: Division A, Division B, Division C 6 of 7

Change data series names or legend text • To change legend text or data series names on the worksheet, click the cell that contains the data series name you want to change, type the new name, and then press RETURN. • To change legend text or data series names on the chart, click the chart, and then click Source Data on the Chart menu. On the Series tab, click the data series names you want to change. In the Name box, specify the worksheet cell you want to use as the legend text or data series name. You can also type the name you want to use. Add data labels to a chart The chart type associated with the selected data series or data point determines the type of data label you can add. 1. To add data labels to a data series, click the data series. • To add a data label to a single data point, click the data series that contains the data marker you want to label, and then click the data marker for the data point you want to label. 2. On the Format menu, click Selected Data Series or Selected Data Point. 3. On the Data Labels tab, select the options you want. Change data labels • To change data labels on the worksheet, click the cell that contains the information you want to change, type the new text or value, and then press RETURN. • To change data labels on the chart, click once on the data label you want to change to select the data labels for the entire series, and then click again to select the individual data label. Type the new text or value, and then press RETURN. PRINTING Change the page orientation 1. Click the worksheet. 2. On the File menu, click Page Setup, and then click the Page tab. 3. Under Orientation, click Portrait or Landscape. Set the size of the paper 1. Click the worksheet. 2. On the File menu, click Page Setup, and then click the Page tab. 3. Click Options. 4. On the Paper Size pop-up menu, click the size of paper you want. Set page margins 1. To set page margins for one sheet, click the worksheet. 2. On the File menu, click Page Setup, and then click the Margins tab. 3. In the Top, Bottom, Left, and Right boxes, enter the margin size you want. Tips • To see how the page margins will affect the printed document, click Print Preview before the document is printed. To adjust the margins in print preview, click Margins , and then drag the handles. • To apply the same page margins to new worksheets or workbooks, you can create a sheet template or you can create a workbook template.

7 of 7