Spreadsheet 1

  1. Ask for a worksheet from Mr. Wilson.
  2. Using the tutorial below, fill in the worksheet with the relevant details.
  3. Hand in your finished worksheet to Mr. Wilson.

You can find the complete tutorial here:



Intro: Interface

If you have worked with Windows programs before, you already know a lot about Excel's window. It has all the normal window parts, like the Status bar, Title bar, scroll bars. The Excel window is actually a lot like what you saw in Word 2007/10. If you are not familiar with Word, you should consider working at least the first project of Working with Words: Word 2007/10 before continuing. The lessons in Working with Numbers assume that you are somewhat experienced with basic word processing skills.

Excel Interface - labeled (Excel 2010)

Blank Excel worksheet maximized in the Excel window (Excel 2010)


Spreadsheet Terms

TermExplanation
spreadsheetDocument that is entirely made up of rows and columns. Used to list and analyze data.

Icon: ConfusionPoint of Confusion: To add to the confusion in the world, people tend to use the word spreadsheet in two ways: 

  • the entire Excel workbook file
  • an individual worksheet in a workbook
workbookThe basic document for Excel. The default workbook name is Book1.xlsx where the extension xlsx comes from Excel spreadsheet. The x was added to be different from the older Excel format that uses just xls for the extension. 
 
worksheetExample: pay records (Excel 2010)A single sheet of data. One or more worksheets make a workbook. 

Think of them as pieces of paper that are stacked on top of each other to form the workbook. The default workbook contains three worksheets, named Sheet1,Sheet2Sheet3. You will want to change these names to something more interesting and helpful!

The maximum number of worksheets in a workbook depends on your computer's memory.

A worksheet, also called just a sheet or spreadsheet, can have up to 1,048,576 rows by16,384 columns with up to 32,767  characters in a single cell. This is enough for most purposes! Smiley face 
 

workbook windowThe document window inside an Excel window. (See next section for illustrations)
 
columnsNamed with letters in the following pattern: A, B, C,…Z, AA, AB, AC,…AZ, BA, BB, BC,…BZ, CA,…IA, IB,…IV... XFD, which is the last column possible.
rows  Named with numbers from 1 to 1,048,576. 
 
cellIntersection of a row and a column on a worksheet.
 
active cellSelected cell (2003)Has a dark border around it and the row and column headers are colored.
 
This is the cell that receives your keystrokes and commands. You make a cell the active cell when you select it, by clicking it or by moving into it with keys. The TAB or the arrow keys are handy for moving from cell to cell.
gridlinesDialog: Page Setup > Sheet tab > Gridlines (Excel 2010)The gray lines that form the cells. By default they don't print. 

If you want to print the gridlines, there is a checkbox in the Page Setup dialog

sheet tab

Tabs for workbook budget-2010.xls (Excel 2010)Each worksheet has a tab at the bottom of the workbook window with the name of the worksheet on it.

active worksheet The worksheet that receives your keystrokes and commands. 
It has a white sheet tab with its name in bold. 
If you group two or more worksheets together, you can have more than one active sheet. This is a cool feature, when it is not a disaster because you did not realize that your typing was going to show up on more than one sheet!
 
workspaceThe area below the ribbon that holds your documents

   


The Worksheet & Formula Bar

 The Formula Bar is a feature that is special to a spreadsheet program. To help keep life confusing, the term Formula Bar is used both for the whole bar you see above the cells and also for just the text box on the right above the cells, which displays what is in the selected cell. In older versions of Excel, you had to do all editing in the Formula Bar but recent versions let you edit directly in the cell. Much better!

The Name Box shows the cell reference for the current cell or for the upper left cell of the current selection or the name of the selected cell or range of cells.

The buttons to the right of the Name Box apply to the Formula Bar.

  • Button: Cancel - Formula Bar (Excel 2010) Cancel: Clears any changes.
  • Button: Enter - Formula Bar (Excel 2010) Enter:  Accepts changes and exits Edit mode. More typing will replace cell contents.
  • Button: Insert Formula (Excel 2010) Insert Formula: Opens a dialog where you can choose one of Excel's pre-defined formulas. That dialog then opens the Function Arguments dialog, which has text boxes for you to fill in. No risk of typing errors messing up the formula you meant to enter!

Worksheet Terms

TermExplanation
headings The gray buttons at the top of columns (letters) and at the left end of rows (numbers). For a selected column or row the heading is colored gold.
enter dataSelect the cell, type your data, and press the ENTER key or click the Enter buttonButton: Enter - Formula Bar (Excel 2010) on the Formula bar. What you typed is now contained in the cell. 
Name Box  At the top left above the sheet cells and headings. Used to display cell references and to assign and display names for a cell or a range of cells. 
range  Dragging to select range A2:C5 (Excel 2010)A rectangular set of cells, referred to by using the upper left and lower right cell references with a colon between them, like A2:C5 for the range illustrated at the right. The absolute reference for the range would be $A$2:$C$5. 

You select a range by dragging, for example from the upper left cell to the lower right cell. As you drag, the Name Box shows the number of rows and columns that are selected. Once you quit dragging, the Name Box displays the upper left cell only.
Formula barExample: Formula bar showing a formula (Excel 1010)Shows the contents of a selected cell, whether it is plain text, numbers, or a formula. Sometimes the whole bar that contains the Name Box and the formula text box is called the Formula bar. Sometimes just the text box that shows the formula or text is meant. 
formula  Looks rather like part of an algebra equation, like =SUM(A4:D7) or =AVERAGE(C3, F5, H10). Most formulas use cell references to get the values to calculate with. 

cell reference or  
relative reference
 

The usual way we refer to a cell, using the letter of the cell's column followed by the number of the cell's row, like B3 or AD345.  

TipCool feature: If you move the contents of a cell, any formula that contains a relative reference to that cell is changed to match the new cell reference. A very handy feature - most of the time.

absolute referenceWhen you don't want the cell reference to change in a formula as things are moved around, you must use an absolute reference, by putting a $ in front of both the column letter and the row number, like $B$3 or $AD$345.

WarningOver-writing cell contents: If you select a cell and start typing, what you type replaces what is already in the cell!

Edit in place: Double-click the cell. You can use the same editing methods you've used in Word - arrow keys to move the cursor, BACKSPACE and DELETE keys to remove characters, etc.

Edit in Formula bar: Select the cell and then click in the Formula bar and edit there. This is the only way older spreadsheets would allow you to work with the data. Data was only displayed in the cell. Typing had to be done in the Formula bar. Awkward!

TipHidden contents -  Cell Width: What happens when the data in a cell is wider than the cell? The contents of a cell will overlap the cell to the right if that cell is empty. If the cell to the right is not empty, what you see will be cropped to fit the size of the cell that it is in. None of the cell data is lost. You just can't see it. Cells A2 and A3 below have text that overlaps the empty cells to the right. But A3 shows only what will fit because the cell next to it, B3, is not empty. Compare what you see in cell A3 to what is in the Formula bar. The Formula bar shows all the text in the selected cell.  You will learn what to do about this awkward situation later.

Cells with overlapping text (Excel 2010) 
Cell B3 is not empty, which hides part of the text in A3 as you can see in the Formula bar.

TipHidden contents -  Cell Height: A cell with text wrapping can be too short to show all of the text. You may not be able to tell by looking!

Cell with more text than shows because of text wrapping (Excel 2010)
Cell A3 is too short to show all of the text that is wrapping to the cell, as you can see in the Formula bar.