Excel made Easy Mary Kemp and Jenny Swafford kempm@blountk12.org - swaffordj@blountk12.org Blount County Schools Maryville, TN www.blountk12.org What is Excel? Excel is the spreadsheet application of the Office 2000 programs. A spreadsheet is a grid of rows and columns containing numbers, text, and formulas. The purpose of a spreadsheet is to solve problems that involve numbers. Besides calculating accurately and rapidly, spreadsheets are flexible. They can be easily changed without having to redo the whole spreadsheet. A spreadsheet can also be useful for displaying any kind of information in a columnar format. I. The Excel Workbook An Excel file is called a workbook. Each workbook contains 3 worksheets (but more can be added). The worksheets are named on a bottom tab (Sheet 1, Sheet 2, Sheet 3); however, you can rename them by double-clicking and typing a new name. To move through the worksheets, click the tabs. Each worksheet consists of rows (identified with a number) and columns (identified with letters), which intersect at a cell. Every cell has an address that consists of the column and row (A1, B3, and so on). When a cell is selected it appears to have an outline. Extra worksheets, rows, and columns can be added under Insert on the Menu bar. Draw toolbar Menu bar Formula bar Formatting toolbar Standard toolbar Scroll bars Worksheet Cell Reference Column headings are the letters across the top of the worksheet. Row headings are the numbers down the side of the worksheet. Select a row or column by clicking on its heading. Menu bar contains text, which allows you to select commands. Standard and Formatting toolbars contain buttons that provide quick access to common commands. (For more toolbars click View…Toolbars and check those desired. To move toolbars, click and drag the toolbar handle at the far left of the toolbar.) Formula bar – when information is entered into a cell, it appears in the Formula bar. Scroll bars and directional arrow keys allow you to move vertically or horizontally inside a worksheet. Introduction to Excel 3/17/04 Page 1 Move from cell to cell in the following ways: • Hit the Enter key to move down to the row below • Hold down the Shift key, then hit the Enter key to move up to the row above • Hit the Tab key to move over to the column to the right • Hold down the Shift key, hit the Tab key to move back to the column to the left • Move the cursor to any cell and click there • Use the arrow keys to move up, down, left, or right. II. Entering Data Text is always left-aligned in a cell. Click inside the cell, type the text, and press enter. Notice the data appears in the Formula bar. If the text is too long for the cell, it will spill over into the next cell, as long as there is no data in that cell. To widen a column for data, double-click the right border of the column heading. Text can also be wrapped in a cell. Right-click the cell and select Format Cells. Click the Alignment tab and click to check Wrap text. The column height will adjust to fit. Other alignment choices are available from this pop-up box, along with other formatting options. When Entering Numbers it may be necessary to format the cell to have the number appear as you desire. To do so, right-click the cell and select Format Cells. Click the Number tab and select the number category you desire. If you enter a number and it appears in the cell as all pound sign (#######) it means the cell isn’t large enough for the data. Double-click the right border of the column to resize it. Some number formatting options are available on the Formatting toolbar. Click to select the cell (or a whole column or row) and then click the appropriate button. Decrease Decimal Increase Decimal Percent Currency AutoComplete allows you to enter the same data without retyping it. When you begin typing data you have previously entered in the spreadsheet, it will automatically appear in the cell. Press enter and the data will be inserted. AutoFill allows you to enter a series of data. Type in the first (and sometimes second) data of the series and click the corner (the cursor becomes a black cross ). Drag it across the columns or down the rows where you wish to place the series. For example, the days of the week, months of the year, numbering a series of names, etc. Editing Data – Entries may be edited by clicking the cell and entering the correct information. You do not need to highlight or delete. Typing replaces whatever is in the cell. If you do not want to replace the data, but simply need to correct some part of it, move the cursor into the Formula bar. When your cursor turns into an I-Beam, click and edit within the formula bar. Excel also contains a spell checker, which can be found on the Standard toolbar. Undoing or Redoing an action can be accomplished by clicking the undo or redo tool Introduction to Excel 3/17/04 Page 2 button on the Standard Toolbar. You can see a list of actions and select the one you wish to undo or redo. This tool is not available once the file is closed. Copying Data moves it to a temporary storage area called the clipboard until more data is copied or cut. Click the cell and use the copy button on the Standard toolbar to make a copy of the data. Select the cell in the area you would like to place the copy and click the paste button on the Standard toolbar. The Edit menu or keyboard shortcuts (Copy-Ctrl C, Paste Ctrl V) can also be used. Cutting Data removes it to a temporary storage area called the clipboard until more data is copied or cut. Deleting Data is accomplished by selecting the cell(s) that contain the undesired data and pressing delete on the keyboard. Merge and Center – Cells in a worksheet can be merged to create one cell. 1. Click-and-drag to highlight a range of cells to be merged. 2. Click the Merge and Center button. 3. If data is only in one cell, it will be centered in the new larger cell. If data is in more than one cell, a pop-up box will warn that only data in the upper-left cell will be kept. Formatting Data Data in a spreadsheet can be formatted much the same as in MS Word. Cells containing data should be selected before formatting. Use the Formatting toolbar to change font or size, text style, alignment, color, etc. Freezing Column and Row Headings It is often helpful to freeze headings so they can be viewed as they are related to the data. 1. Click a cell below the desired row or beside the desired column. 2. On the menu line click Window…Freeze Panes. Click Unfreeze Panes to unlock the headings. Adding Borders and Gridlines 1. Highlight the area that contains the data. 2. Click the arrow on the Borders button on the formatting toolbar. 3. Click to choose a format on the pop-up menu. 4. For more choices, right-click on the selected cells and click Format Cells from the pop- up menu. Then click the Border tab. 5. To fill the area with a color, click the Fill Color button on the formatting toolbar. Sorting Data Sort a column in ascending or descending order by selecting the column or cells, then click the appropriate button on the Standard toolbar. For more advanced sorting, see p. 9. Print Preview To see what a page will look like when printed, click the Print Preview button. Introduction to Excel 3/17/04 Page 3 Creating a New Workbook 1. On the menu click File. 2. Select New. To create a blank workbook click General tab. To use a workbook from the templates choose Spreadsheet Solutions tab. You can also create an instant new workbook by clicking the New button on the standard toolbar. Saving and Naming a Workbook 1. On the menu click File. 2. Select Save As… 3. Select the drive and/or folder in the Save in box. 4. In the File Name box type the name for your workbook. Excel creates a three-letter extension of .xls at the end of the file name. Opening an Existing Workbook 1. On the menu click File. 2. Select Open and locate the drive/folder where the file was saved. A file can also be opened using the open button on the standard toolbar. Moving Among Open Workbooks To switch back and forth between open workbooks, click the corresponding Excel button on the Task bar at the bottom of your screen. Adding and Deleting Worksheets To insert a worksheet within a workbook click Insert…and select Worksheet. To delete a worksheet, click Edit…Delete Sheet. Then click OK. Adding and Deleting Columns and Rows To insert a column or row, select the column or row just after where a new column or row is needed. On the menu bar, click Insert…Column or Row. The new column or row will be inserted to the left or just above the selected column or row. To insert numerous columns or rows, click-and-drag to select the number of columns or rows to be added. Then click Insert…Column or Row. Columns and rows can be deleted by selecting the column or row, then right-clicking the mouse and selecting delete (or click Delete under the Edit menu). Moving and Copying a Worksheet The worksheet to be moved and/or copied should be active. On the menu bar click Edit…Move or Copy sheet. Make selections in the Move or Copy box and click OK. To move a worksheet within a workbook click its name tab and drag it to the new position. Renaming a Worksheet To change the worksheet's name, double-click the tab that shows Sheet 1 (or whatever number the sheet contains) and type its new name. Introduction to Excel 3/17/04 Page 4 Formulas perform calculations on the data entered. You can perform addition (+), subtraction (-), multiplication (*), and division (/). Every formula must start with an equal sign. When writing equations, use the cell address. To find the average of 3 numbers in cells A1, B1, C1, you could type the following formula on the formula bar: =(A1+B1+C1)/3 Entering Formulas Method 1 1. Select the cell in which you want the formula's calculation to appear. 2. Type the equal sign = 3. Type the formula. For example: =A2+B4 4. The formula will appear in the Formula bar. 5. Press Enter and Excel will calculate the result. Method 2 1. Click the cell where to answer is to appear. 2. Type an = sign. 3. Click the first amount cell. 4. Press the operation key on the numeric keyboard. (+, -, *, /) 5. Click the next data cell. If any additional cells are included you must press the operation key between each cell click. 6. Press Enter and Excel will calculate the result. Sums The sum of a range of cells can be viewed by selecting the cells and looking at the status bar. Right-click the status bar to change the option to show the average, minimum number or maximum number. Status bar Introduction to Excel 3/17/04 Page 5 III. Calculations AutoSum – To add a column or row of cells, highlight the cells (plus at least one additional cell) and click the AutoSum tool on the standard toolbar. The range will be shown in the cell. Press enter to see the sum. To view all formulas in a worksheet, under Tools on the menu bar click Options, then the View tab. In the Options pop-up window select Formulas and click OK. All formulas on the worksheet will be displayed. Order of Calculations: 1. Exponents 2. Multiplication (*) and Division (/) 3. Addition (+) and Subtraction (-) 4. Parentheses can be used to change the order of calculations. Excel will calculate the data inside the parentheses first. Copying Formulas allows you to adjust the formula to the data in the cell. To copy the formula: 1. Click the cell that contains the formula you wish to copy. 2. Click Edit…copy. Then click the cell you want to copy the formula to and click paste. If you only want to copy the formula and not the data select paste special. 3. To copy a formula to a neighboring range of cells use the fill handle ( ). Entering Functions 1. Click to select the cell where the answer to the function will go. 2. From the Insert menu, select Function 3. In the Paste Function pop-up window, select the Function category on the left and the Function name on the right. 4. Click OK, and then OK again in the next dialog box that appears. It is just a summary of what the function asks Excel to do. The function will be placed in the cell and the answer will be displayed. 5. Excel has many functions that can be accessed from the Paste Function window. Introduction to Excel 3/17/04 Page 6 IV. Creating Charts 1. Highlight area to be displayed in chart. 2. Select the Chart Wizard in the Standard Toolbar. 3. Select the type of Chart desired. 4. Click and hold the mouse pointer down on the Press and Hold… button to see how the chart will appear. If you don’t like the look, select another chart type. 5. Click Next and choose options to make changes in the chart under the Data Range and Series tabs. 6. Click Next and choose more options to make changes in the chart. 7. Click and type the chart title, the type of information that will be displayed on the bottom of the chart in the X axis, and the he type of information that will be displayed on the side of the chart in the Y or Z axis. 8. Click the other tabs for more options that can change the way the chart is displayed. 9. Click Next and select whether the chart is to be displayed as New sheet or Object in existing sheet 10. Click Finish. Once the chart is created, changes can be made by double- clicking in different areas, or by right-clicking and then clicking on one of the choices from the pop-up menu. Introduction to Excel 3/17/04 Page 7 V. Printing Click File…Page Setup to select the following: Page – Orientation (select Portrait or Landscape), Scaling (Reduces or Enlarges the worksheet), Paper Size (8 1/2 X 11 inches is the default setting), Print Quality (the higher the number, the better the print), First Page Number (the default is the set the starting page as number 1) Margins – Top, Bottom, Left, Right (adjust margin settings), Header, Footer (to set up the header/footer, click View Headers and Footers); Custom Headers and Footers can also be set up to show the time, date, etc. Sheet – Print Area (enter the range of cells you want to print to print only a portion of the worksheet, Print Titles (prints a row or column of entries that you want repeated as titles on every page, Print select gridlines or have color spreadsheets printed in black-and- white. Page Order indicates how data should be printed. A Range is a defined area inside a worksheet. You can define a range by highlighting the desired cells. To print a specific area of a worksheet you must define the range. 1. Highlight the area. 2. Select File…Print Area. 3. Select Set Print Area. 4. Then select Print from the Standard Toolbar. Using Print Preview To see what a page will look like when printed, click the Print Preview button. VI. Help 1. Click the ? on the Standard toolbar to activate Microsoft Excel Help. 2. Under the Answer Wizard tab, type in a question and select Search. Then double-click the answer that best describes what you’re trying to do, or select See More. Click Options to print. 3. Under the Contents tab, look for a topic in alphabetical order. 4. Under the Index tab, search through by typing the first few letters of what you’re looking for or use Find (you’ll have to build the Find Index the first time you use it). 5. Use Online help if you are connected to the Internet by clicking Help on the Menu bar, and selecting Office on the Web. Introduction to Excel 3/17/04 Page 8 6. A helpful Excel website: www.bettersolutions.com/excel/excel.htm Introduction to Excel 3/17/2005 Page 9 VII. Advanced Sorting 1. To sort across all columns in the spreadsheet, you must first select the data; click anywhere on the spreadsheet, then press and hold the Ctrl key and press the A key (Ctrl + A) to select the entire worksheet. 2. On the menu bar, choose Data – Sort 3. If the file has a header row, make sure Header Row is marked 4. The file can be sorted by up to three choices, in either ascending or descending order. 5. Another helpful sort is AutoFilter; select all (Ctrl + A) the data in the spreadsheet. 6. On the menu bar, choose Filter – AutoFilter. 7. Notice that there are now drop-down arrows at the end of each column header. When the down arrow is selected, it displays a list of every item in the column. Choosing one of these items will change the spreadsheet to show only rows in the spreadsheet with that item. Once the information is “auto-filtered”, you can select and copy, then paste just this information into a new worksheet. AutoFilter can be very useful in manipulating data in an Excel spreadsheet.