|
Week 4, TC ITSE 1294
The nice thing about Excel is the layout is very similar to Word; same icons, same menu items, etc. And if you have used tables in Word, you already have an idea of what spreadsheets do. The biggest difference is that a spreadsheet table can manipulate numbers much better than just adding a row, as you can in Word... and Excel does all the math.
As spreadsheets are new to many of you, I decided to spread the instruction on them over two weeks. This week you read about spreadsheets, and next week you practice spreadsheets, using the Practical PC book, and perhaps my Sample page, before you start your lab.
Lesson: Entering Data into an Excel Worksheet
Understanding Excel Data Types
Spreadsheets are designed to calculate formulas, analyze numerical data, and display information in charts. Instead of typing in just words, which are called labels in a spreadsheet, you may also enter numbers, called values, or manipulate the numbers with functions and formulas.
Entering Text
Rows are divided into cells, into which you can type labels, values, formulas, or functions. Select a cell with your mouse, or use the arrow keys, the type a word or phrase, and then hit the Enter key. Text is left aligned, by default.
| Test 1 | Test 2 | Test 3 | Test 4 | Test 5 | |
| Student 1 | |||||
| Student 2 |
Tips on Entering Column and Row Labels
If you are typing labels, hitting Enter moves you down one row, while Tab moves you one column to the right.
Adding Comments to Cells
You may also add the equivalent of a post-it note on a cell, called a comment. Choose Insert/Comment and type in your note, then click someplace else and it shrinks to a little marker. To read it again, place the mouse pointer on the marker.
Entering Numbers
To enter values, just type the numbers in a cell. They will right align.
| Test 1 | Test 2 | Test 3 | Test 4 | Test 5 | |
| Student 1 | 99 | 96 | 94 | 87 | 26 |
| Student 2 | 99 | 100 | 89 | 93 | 99 |
Entering Dates and Time
If you type in 04/09/2005, Excel recognized that as a date. If you type in 10:00 p Excel recognizes that as a time. You may always change the way the time or date is formatted, by choosing Format/Cells
Copying (Filling) the Same Data to Other Cells
If you point at the lower right corner of a cell, the cursor turns to a +. If you were to click and drag that, it would copy the value to the next cell. This has variations, which are covered in the next section.
Entering a series of Numbers, dates, and other data
If you enter a 1 in a cell, then enter a 2 in the cell below it, the Copy function works differently. If you select the two cells by dragging across them, then point at the lower right corner of the bottom cell, the cursor again turns to a +. If you were to click and drag that pointer down 5 cells, it would place a 3 in the next cell, a 4 in the following cell, etc. This works only if Excel recognizes a pattern, such as Months, Days, and numerical patterns.
A shortcuts option box will help you if Excel doesn't see the right pattern.
Take advantage of AutoComplete
If you are constantly putting Test as a label, Excel will also recognize this as a pattern, and will complete what it thinks you are typing. If this AutoComplete is what you want, just hit Tab or Enter. If not, just keep typing and the AutoComplete will go away and be replaced by the word(s) you enter.
Lesson: Performing Simple Calculations in Excel
Understanding Excel Formulas
To add two numbers, Excel needs to know that you are entering a formula, not just values. Formulas begin with '='. Enter in =2+2 and press the enter key... The solution appears in the cell. Click on the cell holding the solution, and the formula bar shows you what generated the answer.
You may also use your high school algebra, and enter =(2+2)/8 to get .5. Order of operations are discussed later in the book.
You may also use =A1+A2 to add the values currently held in Cell A1 (top left) and A2 (right next to it).
Order of Operations
| Operation | Normal Math | How Entered |
| Parens | (2+2) + 8 | =(2+2)+8 |
| Exponents | 53 | =5^3 |
| Multiplication | 2 x 2 | =2*2 |
| Division | 4÷2 | =4/2 |
| Addition | 2+2 | =2+2 |
| Subtraction | 2-2 | =2-1 |
Entering Formulas
Aside from entering =31+A, you could also type =31+ then using the mouse, click on A1. Press Enter when finished.
Using the Status Bar AutoCalculate Feature
If you highlight a series of cells, on the status bar appears the sum of the numbers. This is only a preview feature. You may also right click the sum to get other calculations.
Note the Sum=7 preview on the status bar
Editing Formulas
If you decide to change a formula that has been entered, select the cell, then make the changes in the formula bar. Or select the cell, and click [F2]
Lesson: Performing Calculations with Functions
What are functions
Functions are stored formulas, so instead of typing in =A1+A2+A3, you could do =SUM(A1:A3), which means add values in A1 through A3.
There are many useful functions, such as AVERAGE, COUNT, plus financial, statistical, database, etc. Using Excel's Help, read Function Reference. You may quickly tell Excel you want to use a function by clicking the fx next to the formula bar.
Using AutoSum
If you click under a column of numbers, and press the [å] button, Excel will automatically Sum those numbers.
Using the Insert Function Feature
As shown above, you may click the fx button to enter a function, but you may also choose Insert/Function from the menu. Dialog boxes will assist you with complex functions. Drag across cells to input them into the dialog box, and use different ranges of numbers (such as A1:A3 andA17). If you have trouble seeing the cells to click on, click the Collapse button on the Number text box, choose the number, the click the Expand button to get back to the dialog box. (Just like minimizing any window...)
Lesson: Creating Charts
Understanding Charting Terminology
Add the following to an Excel Spreadsheet:
| Grade | Count |
| A | 7 |
| B | 6 |
| C | 2 |
| D | 1 |
Select all the cells except the labels at the top, and using the chart wizard,
select pie chart, and then Finish. 
Now you are ready to understand some terms.
The pie wedges represent data series, in this case, the number of As (7) or Bs (6).
Categories would be the labels. They aren't in this chart, because they weren't selected.
Some charts are on a grid, the horizontal and vertical lines are called axis. The dividing lines are called gridlines.
The legend indicates which color is for A, for B, etc.
Working with different Charts Types
Chart Wizard representations of Column, Bar, Line, Pie, Scatter and Area charts.
Pie is for parts of a whole; Bar and Column are for comparing values at a point in time; Line, Area and Scatter show trends.
Creating and Saving a Chart
Instead of jumping to Finish, as we did earlier, There are many steps you may add to control whether or not your data is in rows or columns, includes categories or not, controlling the legend, etc. Experiment!
Moving and Resizing a Chart
Once a chart is created, it can be dragged and resized. Click on the outside border to move, or click and drag one of the square box edges that show up after being clicked to resize. Hold the shift key down to keep it proportional.
Printing a Chart
If you wish to print just the chart, and not the whole workbook, select the chart, and then choose File/Print. The Selected Chart button should already be selected... choose [OK]
Additional information on Excel, to prepare you for the next lab, is here.
About the labs
These labs are straight forward, requiring only careful attention to
the book.
Make sure you have done ALL 
and 
New Detailed Instructor notes on Excel
Tips and tricks to Test Taking
Click here for tips on taking tests, as you prepare for the Mid-Term, which needs to be completed early next week.
About this weeks discussion
Go to the class 1294 Groups site and reply to the thread titled "1294 Week 4 Discussion Topic"How can you use Excel's 'What if' when you are planning a budget?' ( Additional information on the Groups web page )
© R. Craig Collins, 2004
We rate with RSACi. Last Updated Dec. 17, 2004