| Introduction A four column spreadsheet with calculations in the fourth column and summary information at
the bottom can be used to introduce many concepts of spreadsheets. The spreadsheet is setup
with titles, column headings, the first column containing labels for each of the following numeric
columns, and summary information at the bottom.
Materials & Software
Objectives
- Setting up a spreadsheet
- Navigate from cell to cell in a spreadsheet
- Entering and formatting titles
- Entering and formatting column titles
- Entering and formatting data in a spreadsheet
- Making basic math calculations
- Replicating of a formula
- Using math functions to summarize the data
- Use lines to format the spreadsheet
- Use margins in print preview to adjust the printing of the sheet
Prerequisite Skills
- Use File: Open, Save, Save As, and Print options
- Basic understanding of math concepts
Time Allotted
One class period.
How to Begin (Directions & Procedures)
Navigating a Spreadsheet
- The moving the mouse and clicking will move the cursor to a new cell.
- The arrow keys will move the cursor from cell to cell.
- Enter will move the cursor to the cell below.
- Tab will move the cursor to the cell to the right.
- Ctrl-Arrow will move the cursor to the next blank cell or filled cell.
- PageUp or PageDown will move a screen at a time.
- F5 will let you goto a specific cell or location.
- Home will take goto the first of a row.
- End will take goto the last filled cell in the row.
- Ctrl-Home will goto to the first cell in the document.
- Ctrl-End will goto the cell of the last column and row that is filled.
- F2 will move the cursor to the end of contents of a cell for editing.
- Dragging the mouse will highlight cells.
- Cells can also be highlighted by using the shift key and the any of the cursor
movement keys.
- Ctrl & mouse click will let you highlight non-contiguous areas.
Title Section
- Enter the first line of the title in the top row in the first column. (cell A1)
- Enter subtitles in the next lines.
- The titles and subtitles may overlap in the next cells.
- Format the titles as needing with font, font size, bolding, and italics.
- To center the titles, highlight the cells for the number of columns the spreadsheet will be.
Choose Format, Cell..., click the Alignment tab, and in the Horizontal list box
choose Center Across Selection.
- Leave a blank row underneath the titles for "white" space to separate the titles from the
column headings.
Column Headings
- In each column type the heading for the column.
- If the column heading is a lot longer than the column data, the heading can be typed in
two different rows or it can be made to wrap in the cell.
- To wrap text in the cell, choose Format, Cells..., and then the Alignment tab, and then
in the Text Control section check the Wrap Text box.
- Sometimes the height of the row needs to be adjusted. This can be done by placing the
cursor in the cell that needs to be adjusted and choosing Format, Row, Height..., then
entering the desired height of the row.
- The height of a row can also be adjusted by placing the mouse at the bottom of the row
number on the left of the page until the mouse pointer changes to a two headed arrow.
The mouse can then be drug down until the row is high enough.
- The alignment of the column headings over the rows may also need to be adjusted. This
can be done with the Right, Center, and Left alignment tools on the toolbar. If the
heading is over a column of text the heading can be aligned to the left or centered. If the
heading is over number data it can be aligned to the right or centered. Look at the
heading when it is finished and make sure it looks nice.
- The column heading can also be made to look separate from the data by using bold,
italics, or underlining.
Table Data
- Text Column. Generally the first column contains the labels that describe the following
columns.
- The labels should follow right underneath the column heading without leaving a blank
row.
- The first column labels should align on the left of the cells.
- When all of the column labels are entered, adjust the with of the column so the cell is 2
or 3 spaces wider than the longest item in any of the cells.
- The column width can be adjusted by moving the mouse to the top of the sheet and
putting the mouse between column A and B until the mouse changes into a double
arrow, at that point the column width can be changed by dragging the column as wide as
it needs to be.
- The column width can also be changed by highlighting the cells, or columns that need to
be widened, then selecting Format, Columns, Width... and enter the desired width in
the number of spaces.
- If more than one cell or column is highlighted all the column widths will change.
- If the text in part of the cells needs to be indented, the cells can be highlighted and then
the Increase Indent tool can be clicked on the toolbar to indent the text.
- Numeric Columns. The second and third columns usually contain numeric data. The
numbers are entered and then formatted as needed.
- Numeric data almost always needs to stay aligned on the right side of the cell.
- Numeric data should be formatted with commas, dollar signs, or percents to show what
kinds of numbers they are. The toolbar has shortcut tools for dollars, commas, and
percents.
- All numbers in a column should have the same number of decimal places to make the
numbers easier to read. The number of decimal places can be adjusted by using the
Increase Decimal and Decrease Decimal tools on the toolbar.
- By highlight all the numbers that need to be formatted first, all of the numbers can be
changed a the same time.
- By right clicking the mouse on a cell or highlighted group of cells the Format Cells menu
option will appear and cells can be formatted from the menu to several different formats.
- On the menu bar, Format, Cells..., and choosing the Number tab will you choose from
the following numeric formats: General, Number, Currency, Accounting, Date,
Time, Percentage, Fraction, Scientific, Text, Special, and Custom. Each choice
may give additional options.
- When dollar format is used it is usually used only on the first row of numbers and the
last.
- When entering column data do not leave any blank columns! It makes it difficult to
create charts and to do calculations later on.
Creating Formulas for Calculations
Formulas in spreadsheet make it much easier to update data so it
automatically recalculates the answers. Formulas also make it so the
calculation can be readily copied so it doesn't have to be entered several
times. Spreadsheet all the use of the following math operations: + add,
-subtraction, * multiplication, / division, and ^ exponent.
- Place the cursor in the cell where the answer of the calculation is desired.
- Start the formula with an = sign. An equal sign appears in the cell and on the Edit line
under the menus. Example: =
- Click on the cell with the first number to be calculated. On the Edit line and in the cell
the reference for the cell appears to the right of the = sign. Example: =B6
- Enter the symbol from the keyboard for the math operation to be performed: +, -, *, /,
or ^. The symbol is entered and the cursor moves back to the original cell. Example:
=B6-
- Click on the cell with the second number to be calculated in the formula. The cell
reference is entered in the formula. Example: =B6-C6
- If the calculation for the formula is finished hit the Enter key and the formula will
calculate the answer and display it in the cell. If additional operations and numbers are
needed in the calculation repeat steps 4 and 5 then hit the Enter key.
- Check the answer to make sure it is calculated right.
- Formula Replication. If the same calculation is to be repeated it can be replicated
instead of being entered again and again. There are three ways to replicate formulas:
Edit and Fill Down or Fill Right, keyboard shortcut to fill down and right, and mouse
drag and fill.
- Method 1. With the first formula setup to calculate, click the mouse on the cell and
highlight down so all the cells where the new formula needs to go is highlighted. Then on
the menu click on the Edit, Fill Down. The formula is replicated down and the formula
in each cell is adjusted so it calculates the cell in the same relationship the first cell did.
- Method 2. Highlight from the formula down so all the cells where the formula needs to
go are highlighted, then on the keyboard type Ctrl-D. The formula is replicated down.
- Method 3. Move the mouse to the bottom right hand corner of the cell with the formula
in that you want to replicate. The mouse changes to a plus sign. When the mouse looks
like a plus sign it can be dragged to the cells where you want the formula and it will be
replicated in the cells.
- A formula can be copied to the right by using the same methods. Edit, Fill Right from
the menu. Ctrl-R from the keyboard. Dragging the plus sign to the right.
- Check your answers to make sure all calculations seem correct.
- If the cell has been previously formatted the format will be copied also.
Summary Functions
Functions are calculations which perform several calculations in one
command. Summary functions used in calculations include: =SUM(cell
range), =AVERAGE(cell range), =MAX(cell range), or =MIN(cell range).
The SUM function is used to add a row or column of numbers. The
AVERAGE functions is used to calculate the average of a range of cells.
The MAX function is used to find the largest number in a range of cells.
The MIN function is used to find the smallest number in a range of cells.
- Adding a Range of Numbers. To add a column of numbers, place the cursor in the
cell where you want the answer and then click the AutoSum tool on the toolbar. It is the
funny shaped "E". If the area highlighted contains the cells you want to add just hit the
Enter key. If the wrong cells are highlighted, highlight the correct cells by dragging the
mouse over the cells, or using Shift and the Arrow keys to highlight the area and hit
Enter. The formula for the SUM function will look like: =SUM(B6:B10). The cells in
parentheses are the range of cells starting at the first cell then a colon and then the last
cell.
- The AutoSum tool will look for a set of numbers above the cell and then to the left of the
cell for numbers to add. It will highlight a contiguous block of cells until a blank cell is
found.
- The AVERAGE, MAX, and MIN function are all setup the same.
- Move the cursor to the cell where the answer is wanted.
- Type "=" then the function name and the left "(". Example: =AVERAGE(
- Next highlight the area to be summarized. The cell will display the range highlighted.
Type the right ")". Example: =AVERAGE(B6:B10)
- Hit the Enter key. The answer will be displayed.
- Check the calculation for accuracy.
Border Lines
- Lines can be used to show or separate different areas of the spreadsheet.
- Highlight the area where the line is wanted. Click on the arrow to the right of the Border
tool on the toolbar or select Format, Cells... and click the Border tab. Select the
button for the position where the lines is wanted in relationship to the cell. (top, bottom,
right, left, or outline)
- Lines should be used underneath numbers that you are calculating on.
- A double line is used after the last set of numbers or totals in accounting to show that it
is done.
Sorting Rows
- Highlight the area that needs to be sorted. Make sure the rows are highlighted all the
way across. If the whole row is not highlighted, when sorting is done the associated
information on the rest of the row will be left out of sync.
- Goto sort....
Resources
Extension Activities
- Setup a personal budget on a spreadsheet
- Use a spreadsheet to organize data for a report
- Use a spreadsheet to collect data for a math problem
|