IT 7360: Technology for Educators
Software Help Sheet
Unit 4: Making Life Easier
Creating Spreadsheets Using MS Excel
Unit 4: Making Life Easier
Creating Spreadsheets Using MS Excel
A spreadsheet program is ELECTRONIC PAPER -- also called a WORKSHEET. With MS Excel, you can have multiple worksheets contained in a WorkBook (a single Excel file).
The workplace is divided into COLUMNS and ROWS.
COLUMNS traditionally are designated by one or two letters (starting with A). Columns are vertical. (up and down).
ROWS are generally identified by numbers (starting with 1). Rows go across.
The INTERSECTION of a row and column is called a CELL.
A CELL is typically referred to by its column and row designation (A1 or Z56 or AA345 or VK987) which is its unique ADDRESS within the total spreadsheet.
A cell may contain one of three types of entries:
2. Labels (alphabetic or alphanumeric data)
3. Formulas (expressions of calculations
or relationships among cells)
To enter information in a cell, first, select the cell by clicking on it.
Next, type the data.
Then, either press the Enter key or the Tab key. This will move you to another cell to continue your data entry.
Enter some Sample Data:
Enter the following data in your spreadsheet to play with.
| Jan | May | Aug | Oct | |
| Houston |
70
|
85
|
98
|
75
|
| Portland |
50
|
65
|
70
|
55
|
| NYC |
20
|
55
|
85
|
60
|
Formatting Data
Data entered into cells can be formatted in much the same way as in word processing.
You can format
Format the data you have entered by adjusting the font and size of the labels, as well as the font of the data.
Entering Formulas
In order for the spreadsheet to do a calculation for you, you must enter formula into a cell.
Formulas begin with an equal sign.
Formulas use references to other cells in the form of addresses in order to indicate which values to use in the calculation.
A reference identifies a cell or a group of cells, using the cell address (column and row headings)
Cell references can be Relative or Absolute.
"A relative reference is like giving someone directions that explain where to go from where that person started – ‘ go up two blocks and over one.’ A relative reference tells Microsoft Excel how to find another cell, starting from the cell containing the formula." Relative references look like C7 or H4.
"An absolute reference is like giving someone a street address – ‘3812 Atlantic Street.’ An absolute reference tells Microsoft Excel how to find a cell based on the exact location of that cell in the worksheet. An absolute reference is designated by adding a dollar sign ($) before the column letter AND the row number. References such as $D$9 and $F$16 are absolute references."
To enter a formula:
First, type an equal sign =
Enter a cell reference (either type in the cell address or click on that cell A1
Enter an operator +
Enter a second cell reference A2
Continue as needed.
Press Enter.
When complex formulas, remember the rules for "Order of Operations."
Formulas can be copied and pasted from one cell to another, just like values. Remember, however, if you are using relative referenced formulas, that the referenced cells will also change. This is a good thing when you want to use the same formula to perform the same operation on multiple rows of data, row by row.
The cell will show the result of the calculation. The actual formula will show in the data entry bar, at the top of the spreadsheet. It should look something like:
=A1+A2+A3
or
=(A1/A2)*A3
Practice: Formulas
In the cell to the right of Houston’s October temperature, enter a formula to calculate the average temperature in Houston for all 4 months.
If your math skills escape you, try something like the following:
=(C13+D13+E13+F13)/4
where C13 is the cell for Houston’s January temperature, and so on.
Using Functions
Predefined formulas are called FUNCTIONS. Functions are commonly used formulas that the writer of the software was kind enough to include for you instead of making you type in the same formula each time you need it--Things like SUM and AVERAGE.
To use a function, select the cell in which you wish to place the formula/function.
From the menubar, select Insert, then Function.
Select a Function Category, and then a Function Name.
Enter (or highlight) the cell or range of cells on which to perform the calculation.
Press OK.
The cell will show the result of the calculation. The actual formula/function will show in the data entry bar, at the top of the spreadsheet. It should look something like:
=SUM (D5: D9)
Practice: Functions
In the same cell in which you entered a formula to calculate
Houston’s average yearly temperature, try pasting a function to do the
same operation.
Once you have successfully used the AVERAGE function to calculate Houston’s average temperature, you can easily do the same for the other cities. Instead of using the Paste Function command, however, you can simply use the Fill Down command.
Select the cell that contains the function for Houston’s average temperature. Holding down the Shift key, select the two cells below it so that you can also calculate the average temperatures for Portland and New York City. You should now have three cells in the same column selected.
From the menubar, select Edit-Fill-Down. This will
copy the formula, with its relational references to the two cells below.
Sorting Data
One of the great features of a spreadsheet application is its ability to easily sort data.
Enter a column of values or labels.
Select the range in which you’ve entered the data.
From the menubar, select Data, and then Sort.
Indicate the manner in which you would like your selection sorted.
Press OK.
A caution: often data entered into a spreadsheet is organized into relationships – e.g., a row would contain the temperatures for a given city, each column representing a different day. If you sort independent of these relationships, you will dislocate your data (separate the value from the day or city). To prevent this, always select data for sorting by using the row or column headings (Letters or numbers). This will allow you to maintain the integrity of your data.
To sort the data you’ve entered above, select the rows containing the data for all three cities, but not the row containing the Month labels, nor the Average column, drag your mouse down the numbers at the beginning of the three rows.
From the menubar, select Data, and then Sort.
Let’s sort the data to see which city has the lowest average temperature for the month of August. Select the column that contains the August data.
Click on OK.
If you’ve selected your rows correctly, your spreadsheet should now look like the following:
.
| Jan | May | Aug | Oct | |
| Portland |
50
|
65
|
70
|
55
|
| NYC |
20
|
55
|
85
|
60
|
| Houston |
70
|
85
|
98
|
75
|
If it does not appear like that above, from the menubar, select Edit-Undo Sort, and try again being careful to select the rows by the row numbers.
Creating a chart
Another strength of most spreadsheet programs is the ability to create visual depictions of data (charts and graphs).
To create a chart of our average temperature data, select cells that contain the temperature data, as well as the labels of cities and months (5 columns x 4rows) but not the average column.
From the menubar, select Insert-Chart.
Printing your Chart
From the menubar, select File – Print Preview.
If all is to your liking, and you really do wish to print your chart,
select Print.
Printing your Worksheet
To return to your worksheet, at the bottom left of your window, click on Sheet 1 to make your worksheet the active window.
To print only the part of the worksheet that contains the weather data, select the range of cells that contains the data, as well as the city and month labels and the average temperature column (6 columns x 4 rows).
From the menubar, select File-Print Area-Select Print Area. A dotted line will appear around your selected area.
To preview your print, from the menubar, select File-Print Preview.
Notice that your small selection of cells appears to the upper left corner of the page. To format how your printed page will look, and to make it more professional in appearance, you’ll need to work with the Page Setup. Click on Setup from the button bar that appears at the top of your screen.
If it hasn’t been selected already, click on the Page tab. Set the Page Orientation to Landscape. Set the Scaling to 150% of normal size.
Click on the Margins tab. At the bottom of the windoid, select Center Horizontally. At the top of the windoid, set the top margin to 3 inches.
Click on the Header/Footer tab.
Click on Custom Header and give your data a title in the Center section. Select the text that you’ve typed and click on the text tool (the large A) and format your text to 18 point in an attractive font. Then click on OK.
Click on Custom Footer. Click in the Left section and then click on the small Excel button (with the Excel logo) to file stamp your print. This will print the name of your file in the lower left corner of you page. Select the text and format it, using the text tool, to 9 point. Click in the Right section, and then click on the date stamp (looks like a calendar page with a small 8 and 7 on it). Select the text and format it, using the text tool, to 9 point. If you’d like, enter you name in the Center section. Click on OK.
Click on the Sheet tab. Since we did not format borders to our cells, click on Gridlines. Click on OK.
You can now either print your page (by clicking on Print) or exit Print Preview and return to your worksheet. To exit Print Preview, click on Close at the top of the screen.
Some closing remarks about spreadsheets…
The most important thing a spreadsheet does for us - its real power lays in-- AUTOMATIC UPDATING.
If you alter the entry in one cell, any other related cell (say a formula) also changes in kind.
If you had to list the advantages of computer spreadsheets:
• Recordkeeping -- a big scratchpad -- a place to record information
-mailing lists• complex calculation worksheets
-telephone numbers
-inventories
-"a rudimentary database"
-save storage space - hundreds of pages of records are easily stored on a diskette
-a grade book is another example
-probably 95% of all spreadsheets in existence are for this purpose
-classic example is a loan-amortization table.• "What if…" Applications
–bacterial growth curves
–statistical calculations.
put a worksheet together and alter a number to see how it effects the calculations. In our example above, change the temperature in Houston for August to 30. How does that effect the average temperature?
Sample classroom applications of spreadsheets
• High School Math
• Hypothesis testing (What if…)
• Budgets
• Sports Statistics
• Anticipating Grades
• Tracking the Weather
• The Magic Square (3 X 3 -- each cell contains one of the digits from 1 to 9, without repetition - the challenge is to place the digits so that all row sums and column sums equal 15)
• Fund-raisers - keep records
• Surveys - record your own research
• Energy Usage
