I. Introduction to Excel and Basic Excel Functions.

A. Opening Excel

1. Click on the Start button and roll the mouse up to Programs while holding the mouse button down.
2. On the Programs submenu choose Microsoft Excel.

B. Opening the Data Workbook

1. Choose Open on the File menu
2. Open the Data workbook.

C. Excel cells are like many calculators arranged in a grid.

The grid consists of:

1. Columns labeled by letters A…A, AA, AB,…AZ,BA,…IV for 256 possible columns.
2. Rows labeled by numbers 1…16384.
3. Cells are specified by the intersection of the column letter and row number: A1, B3,…

D. Database Terminology

1. A field is a category, subcategory, item, or a property. Fields are columns in Excel. At the top of each column is the field name.
2. A record is a single unitary instance of an item. A record should never be composed of multiple item. Records are rows in Excel. Records are sometimes given a record or sequencing number on the far left side of the row.

E. Designing a spreadsheet for data:

1. Design of a data base spreadsheet requires planning in advance. Below is a chart depicting the structure of some of the data which we will put in our spreadsheet.
2. These will be arranged from left to right as field names in row one of the data spreadsheet.
3. Put the field names in topmost row of the worksheet.
4. Do not have any blank rows nor blank columns in the data spreadsheet.
5. Put the field names from left to right in hierarchical order highest on the left, lower orders to the right. The diagram above depicts the hierarchy for the table below. Note that our single unitary item is "one student." The order of the student names in your table may not match the one below.

F. Data entry directly in the worksheet:

Scroll down to row 30, click in cell A30 and enter the following data:

30	29	KHS	Kweku	George	M	K	Kosraen	Malem	470	480	460

After typing a cell entry, press the Tab key to move to the next cell to the right. To move to the left press shift-tab.

G. Data Sorting

When a spreadsheet is designed for data the menu item Data: Sort will detect the field names and offer to sort by field name.

1. Select Sort from the Data menu.
2. Set up your sort as follows:

Click on OK.

A sort can be done on any column. When done on a TOEFL score column, sorting allows determination of the high, low, and median score at a glance.

H. Calculating the average with the AVERAGE function

1. In cell L1 type the characters Avg
2. Click on cell K1, then click on the Format Painter toolbar button, and then click on L1 to transfer the field style to L1.
3. In cell L2 type =average(i2:k2) and then press enter. The average function averages all the cells from i2 to k2. i2:k2 is called a "range." A range includes all of the cells between the cells. A range can be a portion of a row, a portion of a column, or a rectangular area of a spreadsheet.

I. Copying a formula down a column

To copy the formula down the L column there are at least THREE methods:

1. Method number one:
   1. Click in L2. Roll the cursor over the lower right hand corner of L2, directly over the small black square. The cursor should turn into a solid black bold plus sign (seen below moved to the right for clarity). The small black square is called the smart-fill control.
   2. Click DIRECTLY ON the small black square and drag down to fill the formula down the L column. The farther one goes off the bottom edge of the spreadsheet the faster the scroll speed. To control the fill speed move only a tiny distance off the bottom edge of the spreadsheet.
2. Method number two:
   1. Click in the center of L2, drag down to the bottom of the student list. Then:
   2. Use the key combination Control-D to fill down. This invokes a non-smart fill down and is useful when Excel insists on creating an undesired series of increasing values during a smart-fill. OR
   3. Use the menu sequence Edit: Fill: Down
3. Method number three:
   1. Click in the center of L2. Release the mouse button. Hold down the shift key and use the down arrow to select the portion of the K column with the student list. Then:
   2. Use the key combination Control-D to fill down. This invokes a non-smart fill down and is useful when Excel insists on creating an undesired series of increasing values during a smart-fill. OR
   3. Use the menu sequence Edit: Fill: Down

J. Other functions

Other functions that could be put in L2 (or any other column to the right) and filled down include:

=average(i2:k2) Finds the mean of the values in the range.
=count(i2:k2) The number of cells containing values in the range.
=max(i2:k2) Displays the smallest value in the range.
=median(i2:k2) Returns the median of the range.
=min(i2:k2) Displays the smallest value in the range.
=mode(i2:k2) Returns the most common value in a range.
=stdev(i2:k2) Displays the standard deviation of the range.
=sum(i2:k2) Returns the sum of the values in a range.

K. Arithmetic operations in cells

In the event that one needs to weight a cell differently, arithmetic operators can be used with cell addresses to accomplish this task. The five operators are:

1. Plus +
2. Times *
3. Minus -
4. Divide /
5. Exponentiation ^

Weighted averages can also be calculated. Suppose one wants to double the weight on the reading score in order to calculate the average. Then the following formula would be used in L2:

=(2*i2+j2+k2)/4

Do not forget the leading equals sign!

L. The Status bar

The status bar at the bottom of the Excel window will tally sums and averages for selected cells. Right clicking on the value displayed causes the display of a menu that lets one change the tallying function. By setting the task bar to average and selecting all the individual averages we can obtain the overall average for all students.

Notes: Included in the Excel workbook are other sample spreadsheets. The tabs Gradebook and Pers exhibit spreadsheets that are structurally similar to the Entrance spreadsheet. The Gradebook spreadsheet has the following structure:

II. If-Then function, choose function, and frequency

A. An Excel Workbook is comprised of one or more worksheets. Our workbook is called Data. Our worksheet is called Entrance.

B. If the average function is not already in column K, click in cell i2 and enter =AVERAGE(i2:k2)

Fill down to L30.

C. Click on the cell M1 and enter the word IEP

D. The Basic If-Then function

The If-Then function consists of three parts: a condition, what to do when the condition is true, what to do when the condition is false. The structure of the function is as follows: =IF(condition,true,false). Note that the three parts are separated by a comma. In the If-Then function letters of the alphabet must be surrounded by quotes, numbers do not need to be surrounded by quotes.

1. In the cell M2 type =IF(L2>469,"Natl","IEP") Type carefully, computers are extremely literal. The sequence is equals IF open-parentheses L2 greater-than (shift-period) 469 comma quote Natl quote comma quote IEP quote close-parentheses
2. Fill down by the method you prefer (refer to notes from two weeks ago in this regard). Take a look at the results, note cases where the condition is met and not met. Conditional operators include =, >, and <. These operators can be combined as in >= and <=.

Note that the above function is not accurate.  There are more than two possible destinations for students.  State campus, IEP, and national campus.  To handle more than two outcomes we can use a nested If-Then function.

E. The nested If-Then function

In the following exercise, the students below 300 on the TOEFL will be assigned a 0, the students eligible for the IEP will be assigned a 1, students eligible for the national campus and needing EN 079 will be assigned a 2, and students eligible for the national campus and not needing EN 079 will be assigned a 3.

Click on the cell N1 and enter the word Eng

The following is a nested If-Then function. The nested If-Then function parses data in data bins. We will use a nested If-Then function to automatically assign a grade point value to each average.

1. Type the following function into N2 being careful to include each comma and parentheses:

=IF(L2<400,0,IF(L2<470,1,IF(L2<550,2,3)))

2. The above is a nested If-Then function. For those who have worked in other computer languages, the above is equivalent to:

  If L2 < 400 
  Then N2 = 0
  Else If L2 <470 
    Then N2 = 1
    Else If L2 < 550
      Then N2 = 2
      Else N2 = 3
      End If
    End If
  End If

Thus if L2 is less than 400 then N2 is assigned a value of 0 and the rest of the decision tree is not executed. If L2 is 400 or higher, the second If-Then condition is checked and so forth.

3. Fill the formula in N2 down to N30 by the method of your choice. The result is that Excel has assigned a numeric value for each student's entrance program. The use of numbers is intentional: words could have been used but the frequency function, which we will look at later, cannot handle alpha characters, hence the choice to use numeric values in column N.

This nested function is still NOT fully correct.  To enter the IEP and the national campus the student must score above 400 in each individual TOEFL subcategory (Listening, Reading, and Structure).  This requires either deeper nesting levels or the use of conditional operations such as the AND operator.  Math scores also need to be taken into account.  If your needs appear to be this complex, please contact me individually.

F. The choose function selects the nth item from a list.

The syntax for choose is =choose(n,firstItem,secondItem,thirdItem,fourthItem,…) where n must be a cell address that contains an integer. The nth item is chosen by the integer. In cell O1 type "Status".

1. Click in cell O2.

Enter =CHOOSE(N2+1,"SC","IEP","Natl 079","Natl")

Note the N2+1: there is no zeroth item allowed in choose. The N2+1 converts a value of 0 to 1 (there is no zeroth element of a list in Excel.)

2. Fill down to O30.

Note that in the above example O7 corrects the error in the assignment of Kwasi Likiaksa given in M7.

G. The Frequency function: An array function

1. Inserting a new worksheet in the workbook

1. From the Insert menu choose Worksheet.
2. Click with the RIGHT mouse button on the tab Sheet1 and choose Rename.
3. Type Dist for the name of this worksheet.
4. Click on OK

2. The basic frequency function syntax is =FREQUENCY(dataCells:binCells)

The syntax if the data and the bins are on different worksheets is

3. Labelling

1. In A1 type the word Status.
2. In B1 type the letters Eng
3. In C1 type the word Count.

4. In the cells A2 to A5 enter SC, IEP, Natl 079, and Natl.

5. In the cells B2 to B5 enter 0, 1, 2, 3.

6. The frequency function we will be using is an "array" function which means it will occupy more than one cell. We must select all of the cells the frequency function will occupy.

Select the cells C2 to C5. It ought to look something like this:

7. Type =frequency(

8. then left click with the mouse on the Entrance worksheet tab

9. Drag the mouse to select N2 to N30 on the Entrance worksheet. Dragging can be done either from the top to the bottom or the bottom to the top: from N2 to N30 or N30 to N2. Dragging from the bottom sometimes help control "overshoot."

10. Type a comma ,

11. left click on the Dist worksheet tab

12. Drag the mouse to select B2 to B5

13. Type a close parentheses ). The result, if the worksheets were named Entrance and Dist, will be as seen below except that the N29 will be N30:

14. Hold down the control and the shift key, then with the control (Ctrl) and shift key still down, press the enter key. The Control-Shift-Enter key combination tells Excel that the formula is an array and to enter the formula into all the selected cells. If you make an entry error with an array function, you cannot edit an individual cell: the formula is in no one cell. Deleting and re-entering the array formula is the easiest way to fix an error in an array formula.

15. When you have done the above, the formula you will have constructed will look like =FREQUENCY(Entrance!N2:N30,Dist!B2:B5).

When viewed in the cell Excel will add brackets to remind you that the formula is an array formula: {=FREQUENCY(Entrance!N2:N30,Dist!B2:B5)}

H. The reason for using the frequency function is that the function creates a dynamic distribution. Try going to the Entrance worksheet and changing a student’s TOEFL numbers, then return to the Dist worksheet and note that the change is reflected in the counts.

III. Charting

A. Design

Design for charting is like designing for data. Include a field name row at the top of the data. Make the leftmost ("first") column a column of labels. The Dist worksheet is set up this way already. The counts in your spreadsheet may be different.

B. Column chart

1. What many call a "bar chart" Excel calls a "Column chart" or column graph.

2. To make a column chart, select the cells A1 to A5.

3. Hold down the Control (Ctrl) key and select the cell C1 to C5. The control key allows the selection of non-adjacent data. Always include all label rows and columns in the selection, this will cause automatic labeling of the chart axes.

2. Click on the Chart wizard button in the tool bar.

3. Drag the mouse to form a small dotted line square on the worksheet to set the location of the chart on the worksheet. Releasing the mouse should cause a chart wizard dialog box to pop up.

4. Click on Next to leave step 1.

5. Click on Column chart option in the middle of the top row of step 2 and then on Next.

6. Click on format option 6 in the upper left corner of step 3 for a basic column chart and then on Next.

7. This first of two options dialog boxes is important. This is where one can specify whether the data is in rows or columns. If one has more than one label row or column, this is the place to specify multiple label rows or columns. We have only one label column if we used the Control key properly. Excel should have chosen the following set-up:

If not, then adjust the settings to match the above dialog box.

Click on Next.

8. In step 5 options dialog box select No under "Add a legend?" For a single data set a legend is unnecessary. If desired, type in a chart title, x-axis and y-axis label. Click on finish.

9. If the Pie chart option had been taken at step 2, then a pie chart would have been the result. The pie chart option 7 includes percentages.

C. Pie chart for population

1. To make a pie chart, select the data including the field names row and the labels column.

2. Click on the Chart wizard button in the tool bar.

3. Drag the mouse to set the location of the chart on the worksheet.

State	Pop
Chuuk	52870
Kosrae	7354
Pohnpei	33372
Yap	11128

4. Click on Next.

5. Click on pie chart option and then on Next.

6. Click on option 7 for a basic labeled pie chart and then on Next.

7. This first of two options dialog boxes is important. This is where one can specify whether the data is in rows or columns. If one has more than one label row, this is the place to specify multiple label rows. It is likely that no adjustments need to be done for this graph: Excel will likely "guess" correctly based on the existence of a field name row and the labels column. Click on Next when done.

8. In this second options dialog box select No under "Add a legend?" For a single data set a legend is unnecessary. Type in a chart title. Click on finish.

9. Displayed is a table and column chart with two label columns.

Genus	Species	Number
Genus1	Species1	30
Genus1	Species2	40
Genus 2	Species3	15
Genus 3	Species4	20

C. Other types of charts

1. There are many types of charts available. The math teacher is likely to find the x-y scatter diagrams useful for graphing functions. Put the x values in the first column and the y-values or a function in the second column. Excel will, with a little thought, make specialty graphs that are not immediately evident from an inspection of the chart wizard. In attempt to stretch thinking, the following will produce a population pyramid type chart.

2. Select the data including the field names row and the labels column. Note the use of negative values for the first column.

3. Click on the Chart wizard button in the tool bar.

4. Drag the mouse to set the location of the chart on the worksheet.

5. Click on Next.

6. Click on Bar chart option and then on Next.

7. Click on option 8 for then on Next.

8. This first of two options dialog boxes is important. This is where one can specify whether the data is in rows or columns. If one has more than one label row, this is the place to specify multiple label rows. It is likely that no adjustments need to be done for this graph: Excel will likely "guess" correctly based on the existence of a field name row and the label column. Click on Next when done.

9. In this second options dialog box select Yes under "Add a legend?" The wizard may already have selected yes. For two or more data columns a legend is informative. A field name row is necessary for Excel to correctly set up the legend. Type in a chart titled, x-axis, and y-axis labels. Click on finish.

10. Double-click on the chart to select the chart. A hashed line should appear around the chart, or, alternatively, the chart should appear in its own window. Excel is now in graphing mode. The menus are different in graphing mode. This modality (regular versus graphing) is a source of confusion for many learning to use Excel. From the Format menu choose Chart Type.

11. Click on the Options button in the Chart Type dialog box.

12. Click on the Options tab at the top of the Format Bar Group dialog box.

13. Set the Overlap to 100 and the gap width to 0. Click on OK. Click outside the area of the graph to turn off the hash mark border. This also takes Excel out of the graphing mode and into the regular mode.

IV. Intermediate Excel: Subtotals.

Using Excel to generate subtotals by course and section.

A. Suppose we wanted to know whether our TOEFL averages differed from one state to the next or wanted to know the relative TOEFL performance of the students by state or gender. Excel can sort and summarize such data using subtotals.

B. In order for Excel to generate subtotals the data must be sorted by the criterion to be summarized. 

1. Click in cell A1 of the Entrance sheet of the Data workbook. Do not select any cells: sort will make the presumption that only the selected cells are to be sorted and will mix up the data.
2. Choose Sort from the Data menu. Excel should have detected the field names and the radio button "My List has… Header Row" should already have been selected by Excel. Good initial design for data is important to proper sorting and subtotaling (covered the first week of class).
3. Sort by St (State) and Then By Sex.
4. Click on OK.

C. Set up the subtotals.

1. Choose Subtotals from the Data menu in Excel.
2. Set at "Each Change In" to St (State) by clicking on the downward pointing triangle on the right side of the Each Change In list box.
3. Set the Use Function list box to Average
4. In the "Add Subtotal to:" list click on the check boxes next to List, Read, Struct, Avg.
5. Click on OK.

D. Note the change in the worksheet: a new panel has appeared on the left, the subtotals control panel. There are three levels shown at the top by three numbered buttons. Click on button number 1 to see only the Grand Average.

E. Click on button number 2 to see the individual state averages. Note the buttons with the plus signs. Clicking on a plus sign button opens up the details for that section. After clicking on a plus button, a minus button appears. Click on the minus button to collapse the details.

F. Click on button number 3 to see the list of all states and all students.

G. Suppose we now wanted to see the overall student averages by sex. We must first remove the existing subtotals, resort by sex, and then reapply the subtotals. The key concept here is that the sort order must mirror the "subtotal at each change in" choice. Subtotals subtotal at each change as Excel moves down the list. If the students are not in gender order, then at each and every change of sex from row to row Excel will insert a subtotal. Subtotals on one field cannot be resorted on another without removing the subtotals first, hence the first step of removing the subtotals.

1. Choose Subtotals from the Data menu and then click on the Remove All button.
2. Choose Sort from the Data menu.
3. Sort by State and Then By Sect and Then By Last
4. Click on OK.

H. Set up the subtotals.

1. Choose Subtotals from the Data menu in Excel.
2. Set at "Each Change In" to State by clicking on the downward pointing triangle on the right side of the Each Change In list box.
3. Set the Use Function list box to Average
4. In the "Add Subtotal to:" list the check boxes may still be checked from the earlier exercise. If they are not checked, then click on the check boxes next to GP, T1, T2, and T3. Scroll the list box to see T1, T2, and T3.
5. Click on OK.

V Filtering

Filtering allows one to display subsets of the data based on specific condition. Suppose we want to display only the students whose TOEFL average is above 475.

Start filtering by removing the subtotals. Choose Subtotals on the Data menu and then click on the "Remove All" button.

Choose Sort on the Data menu and sort by last name and then by first name. Click on OK.

Scroll to the top of the worksheet. From the Data menu choose Filter. On the submenu that pops up choose Autofilter. Small grey buttons with triangles in them should appear in the top row of your spreadsheet (below left).

Click on the grey button in the L (Avg) column to see a "drop-down" list of options (above right). This list includes preset values one can select to filter the data. Choose (Custom…), the third item down the list. A dialog boxes similar to the one shown below should appear:

When using Excel 97 set up the dialog box as shown above.

The Excel 95 dialog box will look a little different.  In Excel 95 Use the little "down triangle" button to the right of the equals sign in the first blank under the word "Avg" to choose a less than >= sign.

Type 475 in the next blank box as seen above. Click on OK.

The result is a list of students whose average is above 475. If one chooses print with a filter on then only the names shown will print.

Getting back all of your data: Choose Filter again from the Data menu and this time select "Show All" on the submenu.

Filters are another powerful use of Excel provided that your spreadsheet is set up in database format (field names in row one and no blank rows, one physical item per row in rows two and higher). Assets larger or smaller than a given number could be displayed, or students with a TOEFLs above or below a specific point could be shown. The "Top ten" filter option can display just that, the "top ten" in a category.

VI. Intermediate Excel: PIVOT tables.

Using Excel to set up a pivot table to study course versus gender grade averages.

A. Pivot tables are easiest to set up when the spreadsheet is designed for data. Field names should be in row one of the table, with data below. There should be no blank rows within the data.

B. Pivot tables (known in Microsoft Access as a cross-tab table) groups data by two categories, producing summary information such as average, sum, or count according to two or more categorizations. As a part of this process, pivot tables have the ability to take categories in data rows and turn them into field names. The result feels like a "rotation" of the data, hence the name Pivot table. As an example, the data in columns A and B below is pivoted to produce the results in F3:G5.

The result are averages based on state and sex. The data in the Sex column, F and M, have become field names in a new row one of a table. The data has, in a sense, been "pivoted" or "rotated" up out of a column and tabulated across in a new table (hence the use of "cross-tab" by Microsoft Access).

B. To start a pivot table in the Gradebook workbook, click anywhere inside the field row or the data rows of the Data worksheet. Do not select a cell, just have the cursor in a cell inside the data to be pivot tabled.

C. Choose Pivot Table from the Data menu.

D. Click on Next in the first dialog box. The default Microsoft Excel List or Database is usually selected and is the correct selection.

E. Click on Next in the second dialog box. If the spreadsheet is designed properly for data then Excel will have correctly detected the data range.

F. Dialog box three presents the screen where choices are made as to row and column groups. The dialog box may have a few additional field buttons not seen in the image as a result of work done earlier on the work book.

1. Drag and drop the button marked State from the right hand area of the dialog box to the area marked Row.
2. Drag and drop the button marked Sex from the right hand side to the Column area.
3. Drag and drop the button marked Avg from the area on the right to the Data area.

G. Double click on the Sum of Avg button in the Data layout area at the center of the screen. From the PivotTable Field pop-up dialog box choose the function average. Note that all of the basic functions are available including sum, average, and count among others. These are the same functions we encountered on the first day. If the Number... button is present, then the format of the numbers displayed can be set from the dialog box that this button activates.  Click on OK.

H. Click on Next in the Step 3 of 4 pivot table wizard.

I. The next dialog requests a location for the pivot table. Either use the worksheet scroll bar to scroll down to A55 or type A55 in the PivotTable Starting Cell box. Click on Finish.

J. The data in the resulting PivotTable summarizes averages by state and sex for the fictional College of Micronesia-FSM spreadsheet.

K. To make the data more presentable:

1. Select the data in the pivot table by dragging the mouse across the number data.
2. Choose Cells… on the Format menu.
3. The dialog box should be on the Number tab.
4. Click on Number.
5. Note the default number of decimal places is preset to 2. This can be changed. For now, click on OK and the numbers in the table will be displayed to two decimal places.

L. The data in a pivot table can be graphed to produce charts as was covered in the second session.

If you are graphing pivot tables in Excel 97 please let me know.  Excel 97, and probably Excel 2000, have an odd inability to select the labels in a pivot table.   There is a work-around to this problem that I can cover with you one-on-one.

Techie Nerd Notes

Note that in the particular chart shown above many adjustments have been made to the default chart settings for a 3-d column graph. Other adjustments include the gap width, gap depth, color choice, 3-D rotation and perspective values. Note too the adjusted the vertical scale: the gaps are smaller than the chart would suggest. The Chuukese male average is not less than half that of the Chuukese female, although the column is less than half as high.

Actually, the above chart was not done in Excel: it was done in Word using Microsoft Graph 5.0. Using Microsoft Graph 5.0 produces Word documents that are much smaller than those generated by embedding Excel charts in Word documents.  Later the chart was image captured using Alt-PrtScn and pasting into Paint on a Windows 98 machine.   Paint 98 can save GIFs, which is how the above image was placed into this HTML page!

M. Pivot tables are best produced by good data designs. In turn, good data designs are produced by considering the pivot table implications. Good design requires that each data row be a single object or instance, not a an aggregate of data. Gradebooks almost naturally generate good data designs: a single student in a single class per row (a "student-seat"). In other applications forethought may be necessary. In a study of lizards, for example, proper design of a good table is likely to involve listing each individual lizard in its own row. This would be as opposed to a table where each row was a location and the data was the number of lizards in that location. The following fictitious table is not well thought out from a pivot table perspective. For example, although the original tallies might have indicated the number of females with tails, the resulting table has lost that information. The design is also prone to typographical errors: examine the sum of the number of males and females on trees.  This error (6 + 5 = 10 ) is an intentional error.  Such an error could not occur when using the second table as no summary data is presented in that table.

A better design would have been:

… and so forth. The resulting table can be subtotaled or pivoted to obtain accurate summary information. Although the original data table will be lengthy, most reports will use the results of subtotal calculations and pivot tables.