Unit 6 - Introduction to Spreadsheets
Spreadsheet programs deal primarily with numeric data entered in rows and columns and include features that make it easier to manipulate the data. Calculations can be made and updated automatically, special functions can analyse the data, the information can be sorted, and graphs can be drawn easily. Excel is the spreadsheet program in Microsoft Office. To start Excel, double click the Microsoft Office folder on the desktop and double click the 'Microsoft Excel 2010' icon (the large green X). If the icon is not on the desktop, select Start->All Programs->Microsoft Office->Microsoft Excel 2010.
The main part of the Excel window contains a workbook. A workbook has its own title bar. A
contains one or more
labelled sheet1, sheet2, and so forth. A worksheet is divided into rows and columns. There is a maximum of 16,384 columns (going up and down) and each column has a letter assigned to it starting with A and going to IV. (A, B, C, ..., Z, AA, AB, ..., AZ...BA...). The rows (going across the screen) are numbered from 1 to 1,048,576. The space at the intersection of each row and column is called a cell and contains one piece of information a number, a word, or a formula. Each cell has a unique name made up of the column and row it is in. The cell in the upper left corner of the worksheet is in the first column and the first row. It is referred to as cell A1. The cell immediately below A1 is in the first column and the second row so it is named cell A2.
The cell with the darker black box around it is the active cell. Anything you type will be entered into the active cell. When a worksheet is first opened the active cell is A1. Move the cursor around the active cell and note how it changes shape. There are three shapes a large white plus sign, a skinny black four headed arrow, and a skinny black plus sign. Each one will perform a specific action. The large white plus will select cells and groups of cells when you hold down the left mouse key. The four headed arrow will move the cell contents to a new location. The skinny black plus sign only appears when placed over the black square in the bottom right of the highlighted cell. This will continue a numbering system or continue the formula entered in neighboring cells. We will try this later in this section.
These are the ways to change which cell is the active cell:
o Press Enter to move the active cell down one row.
o Press Tab to move the active cell to the right one column.
o Hold Shift key and press the Tab key to move one active cell to the left.
o Use the arrow keys to move the active cell one cell up, down, right or left.
o Click with the mouse on any cell to make it the active cell.
If you make a mistake, select the cell that needs to be corrected. You can simply retype the correct information. If new information is entered in a cell that already has something in it the old information is erased and the new information put in its place.
You can also edit information after you enter it. At the top of the worksheet, look for a long bar that contains the contents of the active cell. This is the Formula Bar (also called Edit Bar). This is what it looks like
The name of the current active cell is in a box on the left. The Formula Bar is for editing the cell contents. If there is something in the active cell, it will appear in this area.
To edit in the Formula Bar:
o Move to the cell you want to edit.
o Click the mouse in the Formula Bar.
o Edit the data and hit Enter.
NOTE: Always press enter after editing the cell contents in the Formula bar.
When text is too long to fit in a cell, Excel overflows into the cell on the right if that cell is empty and you see the text. If the cell on the right is occupied the overflowing text is not visible. View the Formula Bar to see the entire contents of an active cell.
When numbers are entered into a cell, the screen display is right aligned. Excel does not truncate the display of numbers that are too long for the cell. Instead, it either fills the cell with pound signs (#########) to indicate that the number in the cell is too long to be displayed or shows the number in scientific notation (as a number and power of 10).
Saving Excel Workbooks & Renaming Worksheets
As in Word, you should initially save your workbook on the desktop; then when finished working on the spreadsheet copy the file to your USB drive. If you quit Excel without saving your work, that work will be lost. Excel files are saved with the extension .xlsx. To save a file in Excel select the File Tab->Save. The save window in Excel is the same as it is in Word. Select the desktop or other location you wish to save your document. When you save a .xlsx file, you are saving a workbook. The workbook can contain many pages, each page is called a
. At the bottom of the current worksheet are tabs labelled 'Sheet1', 'Sheet2', and 'Sheet3'. The current worksheet is Sheet1. Before saving your workbook you should give your sheets more descriptive names:
o Right click on the 'Sheet1' label.
o Choose 'Rename' from the Context Menu. The worksheet name is highlighted.
o Type a new name and press Enter.
This action can be done for all of the sheets.
In the above figure, Row 9 and Column E are empty, awaiting totals. You can use Excel formulas to sum the columns and rows. Excel formulas start with the equal sign (=) to let Excel know you are entering a formula and not text. Excel operators include: + for addition; - for subtraction; * for multiplication; / for division; and ^ for exponentiation.
Formulas are evaluated from left to right according to the standard order of operations. Exponents are calculated first, multiplication and division second, and finally addition and subtraction. You can override this by parentheses. This is the same as the order of operations in algebra and the () are used to change the order. You may use either numbers or cell names in the formulas. If you use a cell name, the formula will be recalculated when the contents of the cell change. To sum the sales of red balloons enter the data from the above figure in an Excel spreadsheet. In cell E3
o Make E3 the active cell.
o Type =B3+C3+D3 and press Enter.
The answer to the formula is shown in E3. To view the actual formula, make E3 active cell and look in the 'Formula Bar'. The formula is displayed in the 'Formula Bar' but the worksheet shows the results of the formula. If you change the 5 in cell B3 to 12 and hit Enter, the formula in E3 is automatically recalculated and the new result displayed on the worksheet.
Formulas can be copied from one cell to another so that Excel will adjust the cell names in the formula to reflect the different row and/or column.
To copy the formula in E3:
o Make E3 the active cell.
o Right click in the cell and the 'Context Menu' appears
o select 'Copy'
o Left click in the E4 cell to activate it
o Now right click in cell E4 and select one of the paste options
The formula in E3 is copied to E4 and calculated. The result is displayed in E4. Since the formula was pasted one row down from its original position, Excel adjusted all the row numbers in the cell names. The Formula bar shows that the formula in E4 is =B4+C4+D4.
If E4 still has a broken line around it, press Esc to make the line disappear. This line is called a marquee and shows what is in the clipboard to be copied. Formulas may also be copied to a group of cells. Make E4 the active cell and right click and select 'Copy'.
After copying a formula, select the range of cells where the formula will be pasted. To highlight more than one cell, make the top or leftmost cell the active cell. Hold down the shift key and use the arrows to move the active cell to the bottom or rightmost cell in the range. To highlight cells E5 through E9, click on E5 and hold down the shift key while pressing the down arrow four times. Note that cells E6 through E9 are now blue while cell E5 remains white with a black box around it. Now select Edit->Paste.
Using the Fill function to manipulate formulas and numbers in cells:
o Open an Excel spreadsheet
o In cell A1 enter '1', in cell A2 enter '2', in cell A3 enter '3'
o Highlight all 3 of the above cells using the large white plus
o Place the cursor over the square in the bottom right of the highlighted cells to get the skinny black plus sign
o Left click and hold while you drag the plus sign down six or so cells
o Note Excel has filled in the values following the 1,2,3... pattern
This can be used with formulas, even numbers, odd numbers, every third number, and other input. Be careful with formulas if the referenced cells do not follow the initial pattern you will get errors. This is an exercise in one of your homeworks.
Relative & Absolute Referencing
The Excel formulas above used relative addressing for cells. This is the default form of addressing. Some computations require absolute addressing. Assume this first worksheet:
Lets suppose that you have a coupon for all of your multimedia purchases for the month of August. You want to know how much money you saved. In cells B3 through B6 are the individual expenses. The coupon is for 7% off or 0.07. Cells C3 through C6 will hold the amount saved. In cell B9 is 0.07 the percent saved. To find the value of the savings for Hulu multiply B3*B9; this works out correctly. However, if we try to copy this formula into cell B4 it will not work, it moves cell B9 to cell B10 and this cell is vacant. Cell B9 must be kept static or in Excel speak be a 'Absolute Cell Reference'.
To create an absolute cell reference enter the formula as usual into cell C3; =B3*B9. The value that remains constant is the percent off coupon for 7%. This value is located in cell B9. In the 'Formula Bar' place the cursor next to 'B10' and press the F4 key. Dollar signs will appear before the B and before the row number in this case 9. This indicates an Absolute Cell Reference and its position will not change as the formula is copied but the other value B3 will change like before.
Worksheets are much easier to read if they are neat. The columns should be wide enough for all the information in them. Columns with similar information should be the same size. All of the numbers in a column should have the same number of decimal places. And, column headings should line up with the information in the column. Borders, colors, and font features may also be added to visually group similar information.
Column widths are measured in characters. To change the width of any column using a visual approximation, position the mouse on the dividing line in the column heading, between A and B, until the crossbow symbol (the vertical line with the horizontal double headed arrow) becomes visible. Click and hold to drag the crossbow to the desired width. To change the width of the first column to exactly 15 characters, click on any cell in the first column. Select the 'Home Tab' then click the 'Format' drop down menu
. Select 'Column Width' and in the 'Column Width' menu box enter 15 & press OK.
You can make adjacent columns the same size by highlighting the cells in the columns you want to change. Highlight cells D, E, F, G by placing the cursor in the in column headings until a thick black downward facing arrow appears. Left click and hold and drag from cell D to cell G; they should become highlighted. Then repeat the steps above Home Tab->Format->Column Width-> and then enter a value.
Worksheets are easier to read if all the numbers in the same column have the same number of decimal places. Numbers can be displayed in many different formats depending on what they represent for example 'Percentage', 'Currency', 'Accounting', 'Scientific', 'Text' yes 'Text' etc. Though a bit confusing 'Text' can be a number format. (The 'Text' format will be important on the final!). Looking at figure one we see all the raw percentages of the test scores in decimal form and with varying length. To make all the values have a length of three
o Select columns B through D as described above
o Right click to get the context menu
o Select 'Format Cells...'
o Select the 'Number' tab
o In the 'Category...' options box select 'Number'
o For the 'Decimal places:' field put 3
o Click OK
Number formatting can be applied to a block of cells. To select a block, click the mouse in the upper left cell of the block. Left click and hold the mouse in the upper left cell of the block of cells and then drag to the lower right cell of the block. Now go to the 'Home Tab' and there find the 'Number' tools menu (the picture to the left). Here are some of the same formatting options as in the 'Context Menus'. Formatting options like increase/decrease the number of decimal places or change the number format from percent to currency. To access the full menu of number formatting options click on the small grey arrow in the lower right hand corner of the 'Number' box.
Changing how a number is displayed on the worksheet does not change the value that is saved for that cell. For example, with decimal places set to 2, the value displayed may be 2.28 but the value in the Edit bar for that cell is really 2.275.
To display numbers as a percentage:
o Highlight the desired block of cells.
o Click the arrow in the lower right hand corner of the 'Number' Ribbon menu option
o Choose the tab that says 'Number'
o Click on the 'Percentage' and set the decimal places to 0.
o Click OK.
Inserting Rows & Columns
Rows and columns may be inserted or deleted from a worksheet. Formulas are automatically adjusted. Rows are inserted above the selected row and columns are inserted to the left of the selected column. To insert a row in a worksheet, highlight the entire row by moving the cursor over the row heading until a right facing black arrow appears & left click. That row should be highlighted. Right click in the highlighted area to get the Context Menu. Select 'Insert' and a new blank row will appear above the selected row. The formatting for the new row matches the neighboring cells. However, formulas do not copy over cleanly and you will need to check the new row's data and formulas to make sure they are correct.
To insert columns, select Insert->Columns and follow the same procedure as with rows.
Column headings should be aligned the same way as the data in the column. In the Test Scores example the first column has text data and is left-aligned. The column heading should also be left-aligned. The values in the second, third, & fourth columns are numbers representing the test scores and are right-aligned. Column headings of numeric data should also be right-aligned even though they are text.
To format headings:
o Highlight the range of cells you wish to format.
o Select the 'Home Tab'
o Click the right alignment button in the 'Alignment' tools on the Ribbon
When you center text across a group of cells, as in a title of a table, the text will always be referenced by the leftmost cell.
In tables the top row of cells is typically used for the title of the table. To make the title appear in the center of the table we must 'Merge' the cells in the top row that have data beneath them. This is accomplished by highlighting the cells to be merged and selecting the 'Home Tab'->'Merge & Center'
drop down button on the 'Alignment' tool & select 'Merge & Center'. The title will now be centered over the table.
Font & Background Colors & Borders
To modify the format of text, start by highlighting the desired cell or range of cells.
o Select the 'Home Tab'
o On the Ribbon under the 'Font' tool select the Font dropdown box
o Click on the desired font
This same action can also be accomplished through the 'Context Menu' that appears when you right click on the highlighted cells. Select 'Format Cells' then on the 'Format Cells' dialog box select the 'Font Tab' and click the desired font. Remember there are more than one way to do things in Excel as well as Word, PowerPoint, and Access. Some tables alternate row colors to make it easier to keep the rows separate.
To do this:
o Highlight the column heading and data of the table to be modified, leave out the title
o Select the 'Home Tab'
o Select 'Format as Table'
o Select a predefined format
o Click in the table anywhere then right click for the Context Menu
o Select 'Table' from the menu
o Select 'Convert to Range'
o Click 'Yes'
Borders of different sizes can be used to separate parts of an Excel worksheet. You must add borders if you want the cells defined when you print the worksheet or else only the cell contents appear. If you are using different types of borders in the same worksheet or adding borders around overlapping parts, add borders to the smallest area first and work outward to the largest one.
The worksheet below has light borders around the rows with values and headings and a heavy border around the entire table area.
To put the borders around the inside part of the table:
o Highlight the column headings and all the data
o Right click to get the 'Context Menu'
o Select 'Format Cells'
o Choose the 'Border' tab
o Select the thin line in the lower left corner of the line style options box
o Click 'Outline' and 'Inside'
o Click OK.
To add the heavy border around the entire table:
o Highlight the entire table
o Right click to get the Context Menu
o Select 'Format Cells'
o Choose the 'Border' tab
o Select the thick line in the right column second from the bottom
o Click 'Outline'
o Click OK.