Day One: Introduction to Data Entry and Functions
Design
Data entry
Data form
Data sorting
AVERAGE function
Copying a formula down a column Day Two: More functions and summary functions
Other functions
Arithmetic operations in cells
Point and select range entry intoformulas
Data summaryinformation on a separate worksheet
Microsoft has made significant changes to Excel 2016s annotation features. What used to be known as comments used like electronic Post it stickies for attaching reminders to cells of the worksheet are now called notes and comments now known as Threaded Comments function as a means for conducting a conversation with coworkers and clients with whom you have shared the Excel. The Office app combines the Word, Excel, and PowerPoint apps you know and rely on, with new capabilities that harness the unique strengths of a phone to create a simpler, yet more powerful Office experience on the go. Whether using it for personal or professional reasons, the Office app is designed to be your go-to app for getting work done on a mobile device. In this article. These release notes provide information about new features and non-security updates that are included in Monthly Channel updates to Office 365 ProPlus in 2019, Visio Pro for Office 365, Project Online Desktop Client and Office 365 Business. Print comments and notes in Excel What to do if you're missing New Comment and New Note from Excel's context menu Important: Keep in mind that it's possible for others to edit your comments. Comments in an Office document are stored in the file, so anyone with edit access to your file can edit your comment. Day Three: If-Then Functions and Frequency
If Then functions
Basic If Then
Conditional operators
Nested If Then
Choose function
Frequency function Day Four: Charting
Charting
Column charts
Pie Charts
Other types of charts:Population pyramids Day Five: Subtotals
Subtotals
Set Up the Subtotals
Other Subtotal functions
Filtering Day Six: Pivot Tables
Pivot Tables
Graphing Pivot Tables
These notes are designed to be used in a workshop environment in connection with anExcel spreadsheet called Gradebook. The workshop presumes familiarity with the Windows 95user interface and prior acquaintance with a Microsoft Office application such asMicrosoft Word. Introduction to Excel
Excel cells are like many calculators arranged in a grid. Each cell is capable ofmaking mathematical calculations. The calculation can be one such as 1 + 3 or acalculation using values in other cells in the spreadsheet. Referring to other cellsrequires knowing how Excel refers to cells. A spreadsheet is an address grid with the gridconsisting of:
Columns labeled by letters A, B, C, X, Y, Z, AA, AB,AC,AX, AY, AZ, BA, BB,IU, IV for 256 possible columns.
Rows labeled by numbers 116384 (Office 97 allows more rows).
Cells are specified by the intersection of the column letter and rownumber such as F9.
Ranges which are a group of cells specified by the address of the upperleft cell and lower right cell separated by a full colon. A B C D E F G 1 Field Name 1 Field Name 2 Field Name 3 Field Name 4 Column E Column F Column G 2 Datum 1 3 Datum 2 4 Datum 3
Row Range B4:D4 5 Datum 4 6 7 8 9 Row 9
Cell F9 10 11
Column Range
A11: A16
Block range C11: E15 12 13 14 15 16 Designing a spreadsheet for data
A field is the name of a particular type of data or a property. Design of a grade bookor any other data holding spreadsheet demands planning in advance. Below is a chartdepicting the structure of some of the data fields which we put in our spreadsheet.
A field is, in a spreadsheet, a column of data.
Put the field names in topmost row of the worksheet.
Do not have any blank rows or columns.
Put the field names from left to right in hierarchical order highest on the left, lower orders to the right. The diagram above partially depicts the hierarchy for the table below.
A
B
C
D
E
F
G
H
I
J
K
L
1 Course Sect Last First
Sx
St
HS Elem
Lang
T1
T2
T3
2 MS 101 M08 Albert Abenaa
F
K
KHS Lelu
Kosraen
82
81
80
3 MS 101 M08 Aldis Adjoa
F
P
PICS PCS
Pohnpeian
75
74
73
4 MS 101 M08 Elidok Ama
F
C
CHS Puluwat
Puluwatese
93
93
93
5 MS 101 M08 George Kweku
M
K
KHS Malem
Kosraen
51
49
47
6 MS 101 M08 Jacob Kofi
M
C
CHS Iras
Mortlockese
67
66
65 Data entry directly in the Gradebook worksheet
Open up the Gradebook Excel workbook to the worksheet with grades on it.
Scroll down to the bottom of the student list. Click in cell A31 and enter thefollowing data:
31 MS 101 M08 Mensah Kwesi
M
K
KHS Malem
Kosraen
87
86
85
Be careful to type the number zero in both MS 101 and M08 (Monday 0800 hours) and not acapital O. After typing an entry, press the Tab key to move to the next cell to the right.To move to the cell to the left press Shift-Tab. Data Form
When a spreadsheet is designed for data the menu item Data: Data Form can be used fordata entry. Select Data Form from the Data menu. Click on the New button in the upperright corner. Enter the data shown below. Use the Tab key to move to the next field blank.Do NOT use the down arrow key: it will cause the Data form to 'go blank.' Thereason for this is because the down arrow generates a next New record.
Use of the tab key to move from one field to the next has been a standard in the dataentry industry for over 30 years. The Tab key will work in Excel, Microsoft Access, andfill-in-the-blank fields found on the Internet and all other data entry applications. Data Sorting
When a spreadsheet is designed for data the menu item Data: Sort will detect the fieldnames and offer to sort by field name.
Select Sort from the Data menu.
Set up your sort as follows:
Click on OK.
A sort can be done on any column. When done on a test score column, sorting allowsdetermining the high, low, and median score at a glance. Calculating the average with the AVERAGEfunction
In cell M1 type the characters Avg
Click on cell L1, then click on the Format Painter toolbar button , and then click on M1 to transfer thefield style to M1. The button may be a different color on your computer
Click in the cell M2. In the cell M2 type:
The computer screen will look something like the following:
and then press enter after completing the formula. Note that there are NO spaces in aformula. The average function averages all the cells from J2 to L2. J2:L2 is called a'range.' A range includes all of the cells between the cells. A range can be aportion of a row, a portion of a column, or a rectangular area of a spreadsheet. Copying a formula down a column
To copy the formula down the Avg column there are at least three methods:
1. For the mouse adept: using the fill handle
Click in M2. Roll the cursor over the lower right hand corner of M2, directly over the small black square . The cursor should turn into a solid black bold plus sign. The small black square is called the smart-fill control or the 'fill handle'. Click directly on the small black square and drag down to fill the formula down the Avg 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. In some versions of Excel double-clicking the fill handle automatically fills the formula down to the bottom of your data.
2. For the mouse adept: another way.
Click in the center of M2, drag down to the bottom of the student list. Then:
a. 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
b. Use the menu sequence Edit: Fill: Down
3. For those who prefer the keyboard:
Click in the center of M2. Release the mouse button. Hold down the shift key and use the down arrow to select the portion of the Avg column with the student list. Then:
a. 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
b. Use the menu sequence Edit: Fill: Down Other functions
Functions that could be put in M2 (or any other column to the right) and filled downinclude:
=average(J2:L2) Finds the mean of the values in the range.
=count(J2:L2) The number of cells containing values in the range.
=max(J2:L2) Displays the smallest value in the range.
=median(J2:L2) Returns the median of the range.
=min(J2:L2) Displays the smallest value in the range.
=mode(J2:L2) Returns the most common value in a range.
=stdev(J2:L2) Displays the standard deviation of the range.
=sum(J2:L2) Returns the sum of the values in a range.
Try entering some of these formulas in M2 and filling the result down the M column.Note that all functions begin with an equals sign. Arithmetic operations in cells
In the event that one needs to weight a cell differently, arithmetic operators can beused with cell addresses to accomplish this task. The operators are:
Addition +
Multiplication * (shift-* asterisk on the keyboard)
Subtraction -
Division /
Exponentiation (shift-6 on the keyboard)
Suppose test T1 and test T2 are worth 25 each, and test T3 is 50 of thestudents grade. Then the following formula would be used in M2:
Type this formula in M2 and fill down. Do not forget the leading equals sign! Remember,there are NO spaces in any formula.
Mathematical operators can be combined with functions to perform complex calculations.Suppose that one wanted to toss out the lowest test and find the average of the remainingtwo tests. The formula to do this would be:
Enter this formula into M2 and fill down.
This formula would work for three tests, it could be generalized to work for any numberof tests with the following formula:
Enter this formula into M2 and fill down. Be careful when typing parentheses! Point and Select Entry of Formula
Another way to enter ranges into formulas is to use the mouse to select the cells inthe range. Start by typing:
Do not forget either the = or the open parentheses, these are necessary to activate thepoint and select method of range entry. Immediately after typing the open parentheses,click with the mouse in the center of the cell J2 and hold the mouse button down. Roll themouse slowly to the right (this is called 'dragging the mouse') until you reachthe center of the cell L2. Release the mouse button. Type a close parentheses:
and press enter. This method of entry is most useful when working with formulas thatrefer to data on another sheet. Running summarycalculations on a separate worksheet
Suppose we want to know the overall average for all students in all of our classes. Wecould go to the bottom of the student averages in the M column and type =average(M2:M32),but the next time we sorted our data by average the average at the bottom would appear inthe middle of the student name list. There is a good chance we would not be able to resortit back to the bottom of the data where it started.
The underlying database principle we violated that led to the above problem is thateach row of the gradesheet must correspond to a student. The summary calculation ofaverage represented a row that was not a student. The result can be a scrambled worksheet.The way to avoid this problem is to put the summary calculations on another sheetaltogether.
From the Insert menu choose Worksheet to add a newworksheet to the workbook. A blank sheet called Sheet1 will appear (the number at the endmay differ). Click with the RIGHT mouse button (not the left one!) on thename Sheet1 and choose Rename from the menu that appears. Rename the new worksheet Stat .
In cell A1 type Overall Average. Adjust the width of the cell to fit the word by movingthe cursor between the A and B letters at the top of the column , clicking, and dragging slowly to the right. Format thetext and background as you prefer.
In the cell B1 type the following:
and then click the mouse on the Data tab at the bottom of theworksheet. Carefully drag the mouse from M2 to the bottom of the student averages(probably M32 on the worksheet.) Then type a close parentheses:
and press enter. The Stat worksheet should be set up something like the following imageat this point:
Note the range specification Data!M2:M30 (the one being used in class more likely readsData!M2:M32). The range specification now includes a reference to a separate worksheet,the Data worksheet. This is one reason giving worksheets a name that conveys meaning isimportant: it makes formulas such as the above more 'readable.' If-Then function
An Excel Workbook is comprised of one or more worksheets. The workshop workbook iscalled Gradebook .
The grades are on a worksheet called Data. . Some of the titles of other worksheets can be seen to theright of the Data tab. The arrows to the left of the Data tab help us scroll through thetabs. This is necessary only if there are many worksheets in the workbook.
If the average function is not already in column Avg, click in cell M2, enter
=average(J2:L2)
and fill down to the bottom of the list.
Click on the cell M1 and enter the word Pass
Click on the cell N1 and enter the word GP
Click on the cell O1 and enter the word Grade
A
B
C
D
E
F
G
H
I
J
K
L
M
N
O P
1 Course Sect Last First
Sx
St
HS Elem
Lang
T1
T2
T3
Avg
Pass
GP Grade The Basic If-Then function
The If-Then function consists of three parts: a condition, what to do when thecondition is true, what to do when the condition is false. The structure of the functionis as follows:
=IF(condition,true,false) .
Note that the three parts are separated by commas. In the If-Then function letters ofthe alphabet must be surrounded by quotes, numbers do not need to be surrounded by quotes.
In the cell N2 type
Type carefully, computers are extremely literal. The sequence is:
equals IF open-parentheses M2 greater-than (shift-period) 69 comma quote Promotequote comma quote Repeat quote close-parentheses
Then press the enter key.
This function first determines if the value in M2 is greater than 69. If the value is greater than 69, then the student is promoted to the nextmathematics course else the value was lessthan or equal to 69 and the student must repeat the mathematics course.
Fill down by any of the previously introduced methods.
Take a look at the results, note cases where the condition is met and not met.
There are other conditional operators that can beused. Conditional operators include =, , and . These operators can be combined asin = or =. The nested If-Then function
The following is a nested If-Then function. The nested If-Then function parses data indata bins. We will use a nested If-Then function to automatically assign a grade pointvalue to each average.
Type the following function into O2 being careful to include each comma andparentheses:
=IF(M260,0,IF(M270,1,IF(M280,2,IF(M290,3,4))))
This should look something like (the multi-colored parentheses appear only in Excel 97,they help show the nesting structure):
The above is a nest If-Then function. For those who have worked in other computerlanguages, the above is equivalent to:
Fill the formula down the GP column by the method of your choice. The result is thatExcel has calculated the grade as expressed in grade point value for each student. Thefrequency function, which we will look at later, cannot handle letters, hence the choiceto use grade point values here. The Choose function
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 celladdress that contains an integer. The Nth item is chosen by the integer.
Click in the cell P2. Enter
Note the O2+1: there is no zeroth item allowed in choose. The O2+1 converts a GP of 0to 1 (there is no zeroth element of a list in Excel. Special note for users of MicrosoftWorks only: In a single encounter with Microsoft Works 4.0 there appears to be a zerothelement in the choose function.)
Fill down the grade column. The worksheet should now look something like: A B C D E F G H I J K L M N O P 1
Course
Sect
Last
First
Sx
St
HS
Elem
Lang
T1
T2
T3
Avg
Pass
GP
Grade 2
MS 101
M08
Albert
Abenaa
F
K
KHS
Lelu
Kosraen
83
82
81
82
Promote
3
B 3
MS 101
M08
Aldis
Adjoa
F
P
PICS
PCS
Pohnpeian
77
77
77
77
Promote
2
C 4
MS 101
M08
Elidok
Ama
F
C
CHS
Puluwat
Puluwatese
94
92
90
92
Promote
4
A 5
MS 101
M08
George
Kweku
M
K
KHS
Malem
Kosraen Run windows on mac with bootcamp.
58
59
60
59
Repeat
0
F The Frequency function: An array function
Inserting a new worksheet in the workbook
From the Insert menu choose Worksheet.
Click with the RIGHT mouse button on the tab Sheet1 and choose Rename from the pop-up menu.
Type Dist for the name of this worksheet.
Click on OK.
On the new worksheet:
In A1 type the word Grade.
In B1 type the letters GP
In C1 type the word Count.
In the cells A2 to A6 enter A, B, C, D, and F.
In the cells B2 to B6 enter 4, 3, 2, 1, 0
A
B
C
1 Grade GP Count
2 A 4
3 B 3
4 C 2
5 D 1
6 F 0
The frequency function tallies the frequency with which a piece of numberical dataappears. The frequency function can count the number of occurrences of a number in a listof numbers. The list of numbers is referred to as the data cells. The bins that willaccumulate the number of occurences requires a set of index numbers that are referred toas the binIndexCells. The basic frequency function syntax is
The syntax if the data and the bins are on different worksheets is
The frequency function we will be using is an 'array' function which means itwill occupy more than one cell. We must select all of the cells the frequency functionwill occupy.
Select the cells C2 to C6. It ought to look something like this.
Type
then left click with the mouse on the Data worksheet tab
Drag the mouse to select the grade point values column (the numbers only, do notinclude the label in the first row) on the Data worksheet. The grade point values arelikely in column O on the Data worksheet.
10. Type a comma ,
11. left click on the Dist worksheet tab
12. Drag the mouse to select B2 to B6
13. Type a close parentheses ).
14. Hold down the control and the shift key, then with the control (Ctrl) and shift keystill down, press the enter key. The Control-Shift-Enter key combination tells Excel thatthe formula is an array and to enter the formula into all the selected cells. If you makean entry error with an array function, you cannot edit an individual cell: the formula isin no one cell. Deleting and re-entering the array formula is the easiest way to fix anerror in an array formula.
When viewed in the cell Excel will add brackets to remind you that the formula is anarray formula:
The reason for using the frequency function is that the function creates a dynamicdistribution. Try going to the Grades worksheet and changing a students grade pointnumber by changing the test scores T1 to T3, then return to the Dist worksheet and notethat the change is reflected in the counts. Charting
Note that Office 97 does things a little differently in a slightly different order withdialog boxes that, unfortunately, look very different.
Design for charting is like designing for data. Include a field name row at the top ofthe data. Make the leftmost ('first') column a column of labels. The Dist Unzip files windows . worksheet is set up this way already. Grade
GP
Count A
4
5 B
3
7 C
2
8 D
1
6 F
0
3 Column chart
What many call a 'bar chart' Excel calls a 'Column chart' or columngraph.
To make a column chart, select the cells A1 to A6.
Hold down the Control (Ctrl) key and select the cell C1 to C6. The control key allowsthe selection of non-adjacent data. Always include all label rows and columns in theselection, this will cause automatic labeling of the chart axes.
Click on the Chart wizard button in the tool bar . This button has slightly different appearance in Excel 95and Excel 97. From here on forward the two versions differ significantly. This documentwill follow the Excel 95 version.
Excel 95 only: Drag the mouse to form a small dotted line square on the worksheet toset the location of the chart on the worksheet. Releasing the mouse should cause a chartwizard dialog box to pop up.
Excel 97 ONLY: Excel 97 skips the above step and the next step altogether. Excel 97automatically creates a 'default' chart size that can be changed later. Excel 97relegates step 1 below to a tab hidden in the new step one.
Click on Next to leave step 1.
Click on Column chart option in the middle of the top row of step 2 and then on Next.
Click on format option 1 in the upper left corner of step 3 for a basic column chartand then on Next.
The dialog box for step 4 is the first of two options dialog boxes that are important.This is where one can specify whether the data is in rows or columns. If one has more thanone label row or column, this is the place to specify multiple label rows or columns. Wehave only one label column if we used the Control key properly. Excel should have chosenthe following set-up:
The series radio buttons should be set as follows:
Click on Next.
In step 5 options dialog box select No under 'Add a legend?' For a singledata set a legend is unnecessary. If desired, type in a chart title, x-axis and y-axislabel. Click on finish.
Excel 97 ONLY: This is step 3 in Excel 97 and the dialog box differs in the extremefrom Excel 95. Turning off the Legend requires clicking on the Legend tab and clicking onthe check mark to the left of the words 'Show legend' to turn the check markoff.
If the Pie chart option had been taken at step 2, then a pie chart would have been theresult. The pie chart option 7 includes percentages. Pie chart for population
Enter the following data below the grade distribution on the Dist tab.
State
Pop Chuuk
52870 Kosrae
7354 Pohnpei
33372 Yap
11128
Excel 95: To make a pie chart, select the data including the field names row and thelabels column.
Click on the Chart wizard button in the tool bar.
Drag the mouse to set the location of the chart on the worksheet. Microsoft Office Excel Notes
Click on Next.
Click on pie chart option and then on Next.
Click on option 7 for a basic labeled pie chart and then on Next.
This first of two options dialog boxes is important. This is where one canspecify whether the data is in rows or columns. If one has more than one label row, thisis the place to specify multiple label rows. It is likely that no adjustments need to bedone for this graph: Excel will likely 'guess' correctly based on the existenceof a field name row and the labels column. Click on Next when done.
In this second options dialog box select No under 'Add alegend?' For a single data set a legend is unnecessary. Type in a chart title. Clickon finish. Other types of charts: PopulationPyramid
Age
Female
Male
0-4
-7117
7545
5-9
-7158
7932
10-14
-7288
7656
15-19
-5893
6532
20-24
-4703
4489
25-29
-3528
3514
30-34
-3407
3393
35-39
-2949
3049
40-44
-2456
2975
45-49
-1660
1941
50-54
-1160
1111
55-59
-1091
998
60-64
-965
1013
65-69
-669
639
70-74
-627
542
75-79
-281
263
80-84
-176
137
85-89
-58
41
90-94
-30
26
95-99
-8
4
There are many types of charts available. The math teacher is likely to find the x-yscatter diagrams useful for graphing functions. Put the x values in the first column andthe y-values or a function in the second column.
Excel will, with a little thought, make specialty graphs that are not immediatelyevident from an inspection of the chart wizard. In attempt to stretch thinking, thefollowing will produce a population pyramid type chart.
Select the data including the field names row and the labels column. Note the use ofnegative values for the first column.
Click on the Chart wizard button in the tool bar.
Drag the mouse to set the location of the chart on the worksheet.
Click on Next.
Click on Bar chart option and then on Next.
Click on option 8 for then on Next.
This first of two options dialog boxes is important. This is where one can specifywhether the data is in rows or columns. If one has more than one label row, this is theplace to specify multiple label rows. It is likely that no adjustments need to be done forthis graph: Excel 95 will likely 'guess' correctly based on the existence of afield name row and the label column. Click on Next when done.
In this second options dialog box select Yes under 'Add a legend?' The wizardmay already have selected yes. For two or more data columns a legend is informative. Afield name row is necessary for Excel to correctly set up the legend. Type in a charttitled, x-axis, and y-axis labels. Click on finish.
Excel 95: Double-click on the chart to select the chart. A hashed line should appeararound the chart, or, alternatively, the chart should appear in its own window. Excel isnow in graphing mode. The menus are different in graphing mode. This modality (regularversus graphing) is a source of confusion for many learning to use Excel. From the Formatmenu choose Chart Type.
Click on the Options button in the Chart Type dialog box.
Click on the Options tab at the top of the Format Bar Group dialog box.
Set the Overlap to 100 and the gap width to 0. Click on OK. Click outside the area ofthe graph to turn off the hash mark border. This also takes Excel out of the graphing modeand into the regular mode.
Excel 97: Instead of double clicking on the chart to select the chart as notedabove, single click on the chart. Then go to the Chart menu and then double click on theactual population bars in the chart. This brings up the format data series dialog box.Click on the options tab. Set the Overlap to 100 and the gap width to 0. click on OK.Excel 97 may make a mess of the graph labels. Double click on the offending labels and settheir font size and style to reasonable values. Intermediate Excel: Subtotals
Using Excel to generate subtotals by course and section
Suppose we wanted to know whether our course averages differed from one section to thenext or wanted to know the relative performance of the students by state or gender. Excelcan sort and summarize such data using subtotals.
In order for Excel to generate subtotals the data must be sorted by the criterion to besummarized.
Click in cell A1 of the Grades sheet of the Gradebook workbook. Do not select anycells: sort will make the presumption that only the selected cells are to be sorted andwill mix up the data.
Choose Sort from the Data menu. Excel should have detected the field names and theradio button 'My List has Header Row' should already have been selected byExcel. Good initial design for data is important to proper sorting and subtotaling.
Sort by Course and Then By Sect and Then By Last (name). Click on OK. Set up the subtotals
Choose Subtotals from the Data menu in Excel.
Set at ' A t Each Change in' to Sect by clicking on thedownward pointing triangle on the right side of the ' A t Each Change in'list box.
Set the ' U se Function' list box to Average.
In the 'Add Subtotal to:' list click on the check boxes next to T1, T2, T3,Avg, and GP. Scroll the list box to see T1, T2, T3, Avg, and GP. Click on OK. Ms Excel Notes 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.
Click on button number 2 to see the individual section averages. Note the buttons withthe plus signs that appear. Clicking on a plus sign button opens up the details for asingle section. After clicking on a plus button, a minus button appears. Click on theminus button to collapse the section.
Click on button number 3 to see the list of all students.
Suppose we now wanted to see the overall student averages by state. Changing asubtotals view is a three phase process.
Remove the existing subtotals
Resort by state (the new criteria)
Reapply the subtotals.
The key concept here is that the sort order must mirror the 'subtotal at eachchange in' choice. Subtotals subtotal at each change as Excel moves down the list. Ifthe states are not in alphabetic order, then at each and every change of state from row torow Excel will insert a subtotal. Subtotals on one field cannot be resorted on anotherwithout removing all existing subtotals first, hence the first step of removing thesubtotals.
Phase one: Choose Subtotals from the Data menu and then click on the Remove All button.
Phase two: Choose Sort from the Data menu.
Sort by State and Then By Sect and Then By Last
Click on OK.
Phase three: Set up the subtotals.
Choose Subtotals from the Data menu in Excel.
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.
Set the Use Function list box to Average.
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 T1, T2, T3, Avg, and GP. Scroll the list box to see T1, T2, T3, Avg, and GP.
Click on OK. Other subtotal functions
Subtotals functions include sum (the additive total), count (how many items), theaverage, the maximum value in the subset, the minimum value in the subset, and standarddeviations among other specialized functions. The sum and average functions are the mostcommonly needed functions. Old drayton tempus three manual . On the worksheet tab labeled Inventory is an example of aninventory spreadsheet demonstrating the use of the Sum function to tally up the value ofproperty in the A204 laboratory. Click on the Subtotal control panel numbered buttons toexplore the different levels of this spreadsheet. Filtering
Filtering allows one to display subsets of the data based on specific condition.Suppose we want to display only the students who will be on the deficiency list, thosewith an average less than 70.
Start filtering by removing the subtotals. Choose Subtotals on the Data menu and thenclick 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. Click on the grey button in the M (Avg) column to see a 'drop-down' list of options. This list includes preset values one can select to filter the data. Choose (Custom), the third item down the list. The following dialog box should appear:
Use the little 'down triangle' button to the right of the equals sign in thefirst blank under the word 'Avg' to choose a less than sign.
Either select 70 from the drop down list in the next box or type 70 in the next blankbox as seen above. Click on OK.
The result is a list of students who are deficient (below 70). If one chooses printwith 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 timeselect 'Show All' on the submenu.
Filters are another powerful use of Excel provided that your spreadsheet is set up indatabase format (field names in row one and no blank rows, one physical item per row inrows two and higher). Assets larger or smaller than a given number could be displayed, orstudents with a TOEFLs above or below a specific point could be shown. The 'Topten' filter option can display just that, the 'top ten' in a category. Pivot tables
Using Excel to set up a pivot table to study course versus gender grade averages. Thissection presumes that the worksheet named Dist has already been insertedin the workbook during work on the frequency function.
Pivot tables are easiest to set up when the spreadsheet is designed for data. Fieldnames should be in row one of the table, with data below. There should be no blank rowswithin the data.
Pivot tables (known in Microsoft Access as a cross-tab table) groups data by twocategories, producing summary information such as average, sum, or count according to twoor more categorizations. As a part of this process, pivot tables have the ability to takecategories 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 onthe left below is pivoted to produce the result on the right. A B C D E F G H 1 State Sex T3 Average of T3 Sex 2 Pohnpei F 80 State F M Grand Total 3 Pohnpei M 65 Chuuk 82.5 67.5 75 4 Pohnpei F 70 Pohnpei 75 70 72.5 5 Pohnpei M 75 Grand Total 78.75 68.75 73.75 6 Chuuk F 95 7 Chuuk M 60 8 Chuuk F 70 9 Chuuk M 75 Microsoft Excel Training Notes
The result are averages based on state and sex. The data in the Sex column, F and M,has 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 asfield names in a new table (hence the use of term 'cross-tab' by MicrosoftAccess).
To start a pivot table in the Gradebook workbook, click anywhere inside the field rowor the data rows of the Grades worksheet. Do not select a cell, just have the cursor in acell inside the data to be pivot tabled.
Choose Pivot Table from the Data menu.
Click on Next in the first dialog box. The default Microsoft Excel List or Database isusually selected and is the correct selection.
Click on Next in the second dialog box. If the spreadsheet is designed properly fordata then Excel will have correctly detected the data range.
Dialog box three presents the screen where choices are made as to row and columngroups.
Drag and drop the button marked State (St) from the right hand area of the dialog boxto the area marked Row.
Drag and drop the button marked Sex (Sx) from the right hand side to the Column area.
Drag and drop the button marked Avg from the area on the right to the Data area. Double click on the 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. Click on OK.
Click on Next.
The next dialog requests a location for the pivot table. Click on the Dist worksheet tab (created during the frequency function lesson). Click in A15 (below thechart if one exists in the gradebook). Click on Finish.
The data in the resulting PivotTable summarizes averages by state and sex for thefictional College of Micronesia-FSM spreadsheet.
To make the data more presentable:
Select the data in the pivot table by dragging the mouse across the number data.
Choose Cells on the Format menu.
The dialog box should open to the Number tab. If not, click on the tab marked Number.
Click on the item Number in the Category list box.
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. Graphing Pivot Tables
The data in a pivot table can be graphed to produce charts as was covered in the secondsession. Be careful to select only the state and sex breakdown data and not the grandtotal data. Look carefully at the diagram below and the note following the diagram.
Making the selection shown must be done by dragging from C20 to A16. Dragging from A16to C20 is not possible as A16 is a button that activates when clicked.
Note that in the particular chart shown has been adjusted by rotating the chart 180from the initial position. This was done by double-clicking on the chart and then choosing3-D View from the Format menu. In the 3-D View dialog box the rotation was set to 200(initial rotation was 20).
Pivot tables are best produced by good data designs. In turn, good data designs areproduced by considering the pivot table implications. Good design requires that each datarow be a single object or instance, not a an aggregate of data. Gradebooks almostnaturally 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 studyof lizards, for example, proper design of a good table is likely to involve listing eachindividual lizard in its own row. This would be as opposed to a table where each row was alocation and the data was the number of lizards in that location. The following fictitioustable is not well thought out from a pivot table perspective. For example, although theoriginal tallies might have indicated the number of females with tails, the resultingtable has lost that information. The design is also prone to typographic errors thatresult in internally inconsistent data: examine the sum of the number of males and femaleson trees. Location Number
of lizards Number
of Females Number
of Males Num w/
tail Beach 2 1 1 0 Pond 6 3 3 5 River 8 5 3 4 Rock 7 5 2 7 Tree 10 6 5 9
A better design would have been: Location Sex With Tail Tree F 1 Tree F 1 Tree F 1 Tree F 1 Tree F 1 Tree M 0 Tree M 1 Tree M 1 Tree M 1 Tree M 1 Tree M 1 Pond F 0 Pond F 1 Pond F 1 Pond M 1 Pond M 1 Pond M 1
and so forth. The resulting table can be subtotaled or pivoted to obtainaccurate summary information. Although the original data table will be lengthy, mostreports will use the results of subtotal calculations and pivot tables. The table may havetypographic errors, but the totals will at least be consistent with the data, there willnot be any internally conflicting data. Typos will also not likely affect counts the waythey can in the earlier table.
Microsoft has made significant changes to Excel 2016s annotation features. What used to be known as comments used like electronic Post it stickies for attaching reminders to cells of the worksheet are now called notes and comments now known as Threaded Comments function as a means for conducting a conversation with coworkers and clients with whom you have shared the Excel file in real time using Office 365.
This change in functionality of how to insert a comment in Excel is all part of Microsoft Offices new multi-user software or coauthoring capability that enables those with whom you share an Excel workbook file and have granted editing privileges to multiple users (known as guest contributors) to make changes in real time to its contents. Notes
Notes on the Review tab of the Excel 2016 Ribbon now function like comments originally did. They provide the means to attach reminders to particular cells of the worksheet. Keep in mind that notes are text boxes whose size and fonts can be edited. The Notes drop-down menu on the Review tab does contain a Convert to Comments option. However, when you use it to change notes into comments (see Comments that follows), they lose any editing assigned to them. Threaded comments
Comments can now record the ongoing commentary between you and the guest contributors with whom you share the workbook open for editing in Excel 2016. As you can see, when you select the Show Comments command button on the Review tab, Excel color codes the comments and displays them in chronological order as a threaded conversation in a Comments task pane. @Mentions in comments
When sharing a worksheet, you can use the new @mention tag in a threaded comment to alert or elicit feedback from a team member with whom its being shared. When you type the @ sign followed by the first few letters of a team members first or last name in the comments text box, Excel displays their full name. When you click the Save button in the comment, Excel sends an email to the named team member with a link to your comment in the shared worksheet. When the team member opens the email and clicks the link in the message, the worksheet in the shared workbook opens in Excel online in their web browser with comment containing their @mention displayed onscreen. The team member can then respond to your comment by taking action in the shared worksheet and/or initiating a conversation by responding to its contents. File sharing and co-authoring
File sharing in Excel 2016 has become much more robust with the addition of threaded comments to Excel Online, the web-based Excel app available on both Windows and Mac platforms to users who dont have Office subscriptions and the ability to make editing changes in real time. The figures in the File Sharing Updates gallery give you a good idea of how file sharing now works in Excel 2016. As you see in, if the workbook you have open for editing has not previously been saved in the cloud in a OneDrive or SharePoint folder, Excel displays the Share dialog box where you can upload a copy for sharing. Once the file has been saved to the cloud, Excel displays the Send Link dialog box shown where you specify the team members with whom you want to share the workbook and grant editing privileges. Once you click the Send button, Excel sends email messages to these coworkers with links for opening the workbook with their Web browsers in Excel Online. Once they open the workbook in Excel Online, all their editing changes appear in your copy of the file in real time. You can then use the threaded comments feature to discuss any questionable edits.
Design
Data entry
Data form
Data sorting
AVERAGE function
Copying a formula down a column Day Two: More functions and summary functions
Other functions
Arithmetic operations in cells
Point and select range entry intoformulas
Data summaryinformation on a separate worksheet
Microsoft has made significant changes to Excel 2016s annotation features. What used to be known as comments used like electronic Post it stickies for attaching reminders to cells of the worksheet are now called notes and comments now known as Threaded Comments function as a means for conducting a conversation with coworkers and clients with whom you have shared the Excel. The Office app combines the Word, Excel, and PowerPoint apps you know and rely on, with new capabilities that harness the unique strengths of a phone to create a simpler, yet more powerful Office experience on the go. Whether using it for personal or professional reasons, the Office app is designed to be your go-to app for getting work done on a mobile device. In this article. These release notes provide information about new features and non-security updates that are included in Monthly Channel updates to Office 365 ProPlus in 2019, Visio Pro for Office 365, Project Online Desktop Client and Office 365 Business. Print comments and notes in Excel What to do if you're missing New Comment and New Note from Excel's context menu Important: Keep in mind that it's possible for others to edit your comments. Comments in an Office document are stored in the file, so anyone with edit access to your file can edit your comment. Day Three: If-Then Functions and Frequency
If Then functions
Basic If Then
Conditional operators
Nested If Then
Choose function
Frequency function Day Four: Charting
Charting
Column charts
Pie Charts
Other types of charts:Population pyramids Day Five: Subtotals
Subtotals
Set Up the Subtotals
Other Subtotal functions
Filtering Day Six: Pivot Tables
Pivot Tables
Graphing Pivot Tables
These notes are designed to be used in a workshop environment in connection with anExcel spreadsheet called Gradebook. The workshop presumes familiarity with the Windows 95user interface and prior acquaintance with a Microsoft Office application such asMicrosoft Word. Introduction to Excel
Excel cells are like many calculators arranged in a grid. Each cell is capable ofmaking mathematical calculations. The calculation can be one such as 1 + 3 or acalculation using values in other cells in the spreadsheet. Referring to other cellsrequires knowing how Excel refers to cells. A spreadsheet is an address grid with the gridconsisting of:
Columns labeled by letters A, B, C, X, Y, Z, AA, AB,AC,AX, AY, AZ, BA, BB,IU, IV for 256 possible columns.
Rows labeled by numbers 116384 (Office 97 allows more rows).
Cells are specified by the intersection of the column letter and rownumber such as F9.
Ranges which are a group of cells specified by the address of the upperleft cell and lower right cell separated by a full colon. A B C D E F G 1 Field Name 1 Field Name 2 Field Name 3 Field Name 4 Column E Column F Column G 2 Datum 1 3 Datum 2 4 Datum 3
Row Range B4:D4 5 Datum 4 6 7 8 9 Row 9
Cell F9 10 11
Column Range
A11: A16
Block range C11: E15 12 13 14 15 16 Designing a spreadsheet for data
A field is the name of a particular type of data or a property. Design of a grade bookor any other data holding spreadsheet demands planning in advance. Below is a chartdepicting the structure of some of the data fields which we put in our spreadsheet.
A field is, in a spreadsheet, a column of data.
Put the field names in topmost row of the worksheet.
Do not have any blank rows or columns.
Put the field names from left to right in hierarchical order highest on the left, lower orders to the right. The diagram above partially depicts the hierarchy for the table below.
A
B
C
D
E
F
G
H
I
J
K
L
1 Course Sect Last First
Sx
St
HS Elem
Lang
T1
T2
T3
2 MS 101 M08 Albert Abenaa
F
K
KHS Lelu
Kosraen
82
81
80
3 MS 101 M08 Aldis Adjoa
F
P
PICS PCS
Pohnpeian
75
74
73
4 MS 101 M08 Elidok Ama
F
C
CHS Puluwat
Puluwatese
93
93
93
5 MS 101 M08 George Kweku
M
K
KHS Malem
Kosraen
51
49
47
6 MS 101 M08 Jacob Kofi
M
C
CHS Iras
Mortlockese
67
66
65 Data entry directly in the Gradebook worksheet
Open up the Gradebook Excel workbook to the worksheet with grades on it.
Scroll down to the bottom of the student list. Click in cell A31 and enter thefollowing data:
31 MS 101 M08 Mensah Kwesi
M
K
KHS Malem
Kosraen
87
86
85
Be careful to type the number zero in both MS 101 and M08 (Monday 0800 hours) and not acapital O. After typing an entry, press the Tab key to move to the next cell to the right.To move to the cell to the left press Shift-Tab. Data Form
When a spreadsheet is designed for data the menu item Data: Data Form can be used fordata entry. Select Data Form from the Data menu. Click on the New button in the upperright corner. Enter the data shown below. Use the Tab key to move to the next field blank.Do NOT use the down arrow key: it will cause the Data form to 'go blank.' Thereason for this is because the down arrow generates a next New record.
Use of the tab key to move from one field to the next has been a standard in the dataentry industry for over 30 years. The Tab key will work in Excel, Microsoft Access, andfill-in-the-blank fields found on the Internet and all other data entry applications. Data Sorting
When a spreadsheet is designed for data the menu item Data: Sort will detect the fieldnames and offer to sort by field name.
Select Sort from the Data menu.
Set up your sort as follows:
Click on OK.
A sort can be done on any column. When done on a test score column, sorting allowsdetermining the high, low, and median score at a glance. Calculating the average with the AVERAGEfunction
In cell M1 type the characters Avg
Click on cell L1, then click on the Format Painter toolbar button , and then click on M1 to transfer thefield style to M1. The button may be a different color on your computer
Click in the cell M2. In the cell M2 type:
The computer screen will look something like the following:
and then press enter after completing the formula. Note that there are NO spaces in aformula. The average function averages all the cells from J2 to L2. J2:L2 is called a'range.' A range includes all of the cells between the cells. A range can be aportion of a row, a portion of a column, or a rectangular area of a spreadsheet. Copying a formula down a column
To copy the formula down the Avg column there are at least three methods:
1. For the mouse adept: using the fill handle
Click in M2. Roll the cursor over the lower right hand corner of M2, directly over the small black square . The cursor should turn into a solid black bold plus sign. The small black square is called the smart-fill control or the 'fill handle'. Click directly on the small black square and drag down to fill the formula down the Avg 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. In some versions of Excel double-clicking the fill handle automatically fills the formula down to the bottom of your data.
2. For the mouse adept: another way.
Click in the center of M2, drag down to the bottom of the student list. Then:
a. 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
b. Use the menu sequence Edit: Fill: Down
3. For those who prefer the keyboard:
Click in the center of M2. Release the mouse button. Hold down the shift key and use the down arrow to select the portion of the Avg column with the student list. Then:
a. 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
b. Use the menu sequence Edit: Fill: Down Other functions
Functions that could be put in M2 (or any other column to the right) and filled downinclude:
=average(J2:L2) Finds the mean of the values in the range.
=count(J2:L2) The number of cells containing values in the range.
=max(J2:L2) Displays the smallest value in the range.
=median(J2:L2) Returns the median of the range.
=min(J2:L2) Displays the smallest value in the range.
=mode(J2:L2) Returns the most common value in a range.
=stdev(J2:L2) Displays the standard deviation of the range.
=sum(J2:L2) Returns the sum of the values in a range.
Try entering some of these formulas in M2 and filling the result down the M column.Note that all functions begin with an equals sign. Arithmetic operations in cells
In the event that one needs to weight a cell differently, arithmetic operators can beused with cell addresses to accomplish this task. The operators are:
Addition +
Multiplication * (shift-* asterisk on the keyboard)
Subtraction -
Division /
Exponentiation (shift-6 on the keyboard)
Suppose test T1 and test T2 are worth 25 each, and test T3 is 50 of thestudents grade. Then the following formula would be used in M2:
Type this formula in M2 and fill down. Do not forget the leading equals sign! Remember,there are NO spaces in any formula.
Mathematical operators can be combined with functions to perform complex calculations.Suppose that one wanted to toss out the lowest test and find the average of the remainingtwo tests. The formula to do this would be:
Enter this formula into M2 and fill down.
This formula would work for three tests, it could be generalized to work for any numberof tests with the following formula:
Enter this formula into M2 and fill down. Be careful when typing parentheses! Point and Select Entry of Formula
Another way to enter ranges into formulas is to use the mouse to select the cells inthe range. Start by typing:
Do not forget either the = or the open parentheses, these are necessary to activate thepoint and select method of range entry. Immediately after typing the open parentheses,click with the mouse in the center of the cell J2 and hold the mouse button down. Roll themouse slowly to the right (this is called 'dragging the mouse') until you reachthe center of the cell L2. Release the mouse button. Type a close parentheses:
and press enter. This method of entry is most useful when working with formulas thatrefer to data on another sheet. Running summarycalculations on a separate worksheet
Suppose we want to know the overall average for all students in all of our classes. Wecould go to the bottom of the student averages in the M column and type =average(M2:M32),but the next time we sorted our data by average the average at the bottom would appear inthe middle of the student name list. There is a good chance we would not be able to resortit back to the bottom of the data where it started.
The underlying database principle we violated that led to the above problem is thateach row of the gradesheet must correspond to a student. The summary calculation ofaverage represented a row that was not a student. The result can be a scrambled worksheet.The way to avoid this problem is to put the summary calculations on another sheetaltogether.
From the Insert menu choose Worksheet to add a newworksheet to the workbook. A blank sheet called Sheet1 will appear (the number at the endmay differ). Click with the RIGHT mouse button (not the left one!) on thename Sheet1 and choose Rename from the menu that appears. Rename the new worksheet Stat .
In cell A1 type Overall Average. Adjust the width of the cell to fit the word by movingthe cursor between the A and B letters at the top of the column , clicking, and dragging slowly to the right. Format thetext and background as you prefer.
In the cell B1 type the following:
and then click the mouse on the Data tab at the bottom of theworksheet. Carefully drag the mouse from M2 to the bottom of the student averages(probably M32 on the worksheet.) Then type a close parentheses:
and press enter. The Stat worksheet should be set up something like the following imageat this point:
Note the range specification Data!M2:M30 (the one being used in class more likely readsData!M2:M32). The range specification now includes a reference to a separate worksheet,the Data worksheet. This is one reason giving worksheets a name that conveys meaning isimportant: it makes formulas such as the above more 'readable.' If-Then function
An Excel Workbook is comprised of one or more worksheets. The workshop workbook iscalled Gradebook .
The grades are on a worksheet called Data. . Some of the titles of other worksheets can be seen to theright of the Data tab. The arrows to the left of the Data tab help us scroll through thetabs. This is necessary only if there are many worksheets in the workbook.
If the average function is not already in column Avg, click in cell M2, enter
=average(J2:L2)
and fill down to the bottom of the list.
Click on the cell M1 and enter the word Pass
Click on the cell N1 and enter the word GP
Click on the cell O1 and enter the word Grade
A
B
C
D
E
F
G
H
I
J
K
L
M
N
O P
1 Course Sect Last First
Sx
St
HS Elem
Lang
T1
T2
T3
Avg
Pass
GP Grade The Basic If-Then function
The If-Then function consists of three parts: a condition, what to do when thecondition is true, what to do when the condition is false. The structure of the functionis as follows:
=IF(condition,true,false) .
Note that the three parts are separated by commas. In the If-Then function letters ofthe alphabet must be surrounded by quotes, numbers do not need to be surrounded by quotes.
In the cell N2 type
Type carefully, computers are extremely literal. The sequence is:
equals IF open-parentheses M2 greater-than (shift-period) 69 comma quote Promotequote comma quote Repeat quote close-parentheses
Then press the enter key.
This function first determines if the value in M2 is greater than 69. If the value is greater than 69, then the student is promoted to the nextmathematics course else the value was lessthan or equal to 69 and the student must repeat the mathematics course.
Fill down by any of the previously introduced methods.
Take a look at the results, note cases where the condition is met and not met.
There are other conditional operators that can beused. Conditional operators include =, , and . These operators can be combined asin = or =. The nested If-Then function
The following is a nested If-Then function. The nested If-Then function parses data indata bins. We will use a nested If-Then function to automatically assign a grade pointvalue to each average.
Type the following function into O2 being careful to include each comma andparentheses:
=IF(M260,0,IF(M270,1,IF(M280,2,IF(M290,3,4))))
This should look something like (the multi-colored parentheses appear only in Excel 97,they help show the nesting structure):
The above is a nest If-Then function. For those who have worked in other computerlanguages, the above is equivalent to:
Fill the formula down the GP column by the method of your choice. The result is thatExcel has calculated the grade as expressed in grade point value for each student. Thefrequency function, which we will look at later, cannot handle letters, hence the choiceto use grade point values here. The Choose function
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 celladdress that contains an integer. The Nth item is chosen by the integer.
Click in the cell P2. Enter
Note the O2+1: there is no zeroth item allowed in choose. The O2+1 converts a GP of 0to 1 (there is no zeroth element of a list in Excel. Special note for users of MicrosoftWorks only: In a single encounter with Microsoft Works 4.0 there appears to be a zerothelement in the choose function.)
Fill down the grade column. The worksheet should now look something like: A B C D E F G H I J K L M N O P 1
Course
Sect
Last
First
Sx
St
HS
Elem
Lang
T1
T2
T3
Avg
Pass
GP
Grade 2
MS 101
M08
Albert
Abenaa
F
K
KHS
Lelu
Kosraen
83
82
81
82
Promote
3
B 3
MS 101
M08
Aldis
Adjoa
F
P
PICS
PCS
Pohnpeian
77
77
77
77
Promote
2
C 4
MS 101
M08
Elidok
Ama
F
C
CHS
Puluwat
Puluwatese
94
92
90
92
Promote
4
A 5
MS 101
M08
George
Kweku
M
K
KHS
Malem
Kosraen Run windows on mac with bootcamp.
58
59
60
59
Repeat
0
F The Frequency function: An array function
Inserting a new worksheet in the workbook
From the Insert menu choose Worksheet.
Click with the RIGHT mouse button on the tab Sheet1 and choose Rename from the pop-up menu.
Type Dist for the name of this worksheet.
Click on OK.
On the new worksheet:
In A1 type the word Grade.
In B1 type the letters GP
In C1 type the word Count.
In the cells A2 to A6 enter A, B, C, D, and F.
In the cells B2 to B6 enter 4, 3, 2, 1, 0
A
B
C
1 Grade GP Count
2 A 4
3 B 3
4 C 2
5 D 1
6 F 0
The frequency function tallies the frequency with which a piece of numberical dataappears. The frequency function can count the number of occurrences of a number in a listof numbers. The list of numbers is referred to as the data cells. The bins that willaccumulate the number of occurences requires a set of index numbers that are referred toas the binIndexCells. The basic frequency function syntax is
The syntax if the data and the bins are on different worksheets is
The frequency function we will be using is an 'array' function which means itwill occupy more than one cell. We must select all of the cells the frequency functionwill occupy.
Select the cells C2 to C6. It ought to look something like this.
Type
then left click with the mouse on the Data worksheet tab
Drag the mouse to select the grade point values column (the numbers only, do notinclude the label in the first row) on the Data worksheet. The grade point values arelikely in column O on the Data worksheet.
10. Type a comma ,
11. left click on the Dist worksheet tab
12. Drag the mouse to select B2 to B6
13. Type a close parentheses ).
14. Hold down the control and the shift key, then with the control (Ctrl) and shift keystill down, press the enter key. The Control-Shift-Enter key combination tells Excel thatthe formula is an array and to enter the formula into all the selected cells. If you makean entry error with an array function, you cannot edit an individual cell: the formula isin no one cell. Deleting and re-entering the array formula is the easiest way to fix anerror in an array formula.
When viewed in the cell Excel will add brackets to remind you that the formula is anarray formula:
The reason for using the frequency function is that the function creates a dynamicdistribution. Try going to the Grades worksheet and changing a students grade pointnumber by changing the test scores T1 to T3, then return to the Dist worksheet and notethat the change is reflected in the counts. Charting
Note that Office 97 does things a little differently in a slightly different order withdialog boxes that, unfortunately, look very different.
Design for charting is like designing for data. Include a field name row at the top ofthe data. Make the leftmost ('first') column a column of labels. The Dist Unzip files windows . worksheet is set up this way already. Grade
GP
Count A
4
5 B
3
7 C
2
8 D
1
6 F
0
3 Column chart
What many call a 'bar chart' Excel calls a 'Column chart' or columngraph.
To make a column chart, select the cells A1 to A6.
Hold down the Control (Ctrl) key and select the cell C1 to C6. The control key allowsthe selection of non-adjacent data. Always include all label rows and columns in theselection, this will cause automatic labeling of the chart axes.
Click on the Chart wizard button in the tool bar . This button has slightly different appearance in Excel 95and Excel 97. From here on forward the two versions differ significantly. This documentwill follow the Excel 95 version.
Excel 95 only: Drag the mouse to form a small dotted line square on the worksheet toset the location of the chart on the worksheet. Releasing the mouse should cause a chartwizard dialog box to pop up.
Excel 97 ONLY: Excel 97 skips the above step and the next step altogether. Excel 97automatically creates a 'default' chart size that can be changed later. Excel 97relegates step 1 below to a tab hidden in the new step one.
Click on Next to leave step 1.
Click on Column chart option in the middle of the top row of step 2 and then on Next.
Click on format option 1 in the upper left corner of step 3 for a basic column chartand then on Next.
The dialog box for step 4 is the first of two options dialog boxes that are important.This is where one can specify whether the data is in rows or columns. If one has more thanone label row or column, this is the place to specify multiple label rows or columns. Wehave only one label column if we used the Control key properly. Excel should have chosenthe following set-up:
The series radio buttons should be set as follows:
Click on Next.
In step 5 options dialog box select No under 'Add a legend?' For a singledata set a legend is unnecessary. If desired, type in a chart title, x-axis and y-axislabel. Click on finish.
Excel 97 ONLY: This is step 3 in Excel 97 and the dialog box differs in the extremefrom Excel 95. Turning off the Legend requires clicking on the Legend tab and clicking onthe check mark to the left of the words 'Show legend' to turn the check markoff.
If the Pie chart option had been taken at step 2, then a pie chart would have been theresult. The pie chart option 7 includes percentages. Pie chart for population
Enter the following data below the grade distribution on the Dist tab.
State
Pop Chuuk
52870 Kosrae
7354 Pohnpei
33372 Yap
11128
Excel 95: To make a pie chart, select the data including the field names row and thelabels column.
Click on the Chart wizard button in the tool bar.
Drag the mouse to set the location of the chart on the worksheet. Microsoft Office Excel Notes
Click on Next.
Click on pie chart option and then on Next.
Click on option 7 for a basic labeled pie chart and then on Next.
This first of two options dialog boxes is important. This is where one canspecify whether the data is in rows or columns. If one has more than one label row, thisis the place to specify multiple label rows. It is likely that no adjustments need to bedone for this graph: Excel will likely 'guess' correctly based on the existenceof a field name row and the labels column. Click on Next when done.
In this second options dialog box select No under 'Add alegend?' For a single data set a legend is unnecessary. Type in a chart title. Clickon finish. Other types of charts: PopulationPyramid
Age
Female
Male
0-4
-7117
7545
5-9
-7158
7932
10-14
-7288
7656
15-19
-5893
6532
20-24
-4703
4489
25-29
-3528
3514
30-34
-3407
3393
35-39
-2949
3049
40-44
-2456
2975
45-49
-1660
1941
50-54
-1160
1111
55-59
-1091
998
60-64
-965
1013
65-69
-669
639
70-74
-627
542
75-79
-281
263
80-84
-176
137
85-89
-58
41
90-94
-30
26
95-99
-8
4
There are many types of charts available. The math teacher is likely to find the x-yscatter diagrams useful for graphing functions. Put the x values in the first column andthe y-values or a function in the second column.
Excel will, with a little thought, make specialty graphs that are not immediatelyevident from an inspection of the chart wizard. In attempt to stretch thinking, thefollowing will produce a population pyramid type chart.
Select the data including the field names row and the labels column. Note the use ofnegative values for the first column.
Click on the Chart wizard button in the tool bar.
Drag the mouse to set the location of the chart on the worksheet.
Click on Next.
Click on Bar chart option and then on Next.
Click on option 8 for then on Next.
This first of two options dialog boxes is important. This is where one can specifywhether the data is in rows or columns. If one has more than one label row, this is theplace to specify multiple label rows. It is likely that no adjustments need to be done forthis graph: Excel 95 will likely 'guess' correctly based on the existence of afield name row and the label column. Click on Next when done.
In this second options dialog box select Yes under 'Add a legend?' The wizardmay already have selected yes. For two or more data columns a legend is informative. Afield name row is necessary for Excel to correctly set up the legend. Type in a charttitled, x-axis, and y-axis labels. Click on finish.
Excel 95: Double-click on the chart to select the chart. A hashed line should appeararound the chart, or, alternatively, the chart should appear in its own window. Excel isnow in graphing mode. The menus are different in graphing mode. This modality (regularversus graphing) is a source of confusion for many learning to use Excel. From the Formatmenu choose Chart Type.
Click on the Options button in the Chart Type dialog box.
Click on the Options tab at the top of the Format Bar Group dialog box.
Set the Overlap to 100 and the gap width to 0. Click on OK. Click outside the area ofthe graph to turn off the hash mark border. This also takes Excel out of the graphing modeand into the regular mode.
Excel 97: Instead of double clicking on the chart to select the chart as notedabove, single click on the chart. Then go to the Chart menu and then double click on theactual population bars in the chart. This brings up the format data series dialog box.Click on the options tab. Set the Overlap to 100 and the gap width to 0. click on OK.Excel 97 may make a mess of the graph labels. Double click on the offending labels and settheir font size and style to reasonable values. Intermediate Excel: Subtotals
Using Excel to generate subtotals by course and section
Suppose we wanted to know whether our course averages differed from one section to thenext or wanted to know the relative performance of the students by state or gender. Excelcan sort and summarize such data using subtotals.
In order for Excel to generate subtotals the data must be sorted by the criterion to besummarized.
Click in cell A1 of the Grades sheet of the Gradebook workbook. Do not select anycells: sort will make the presumption that only the selected cells are to be sorted andwill mix up the data.
Choose Sort from the Data menu. Excel should have detected the field names and theradio button 'My List has Header Row' should already have been selected byExcel. Good initial design for data is important to proper sorting and subtotaling.
Sort by Course and Then By Sect and Then By Last (name). Click on OK. Set up the subtotals
Choose Subtotals from the Data menu in Excel.
Set at ' A t Each Change in' to Sect by clicking on thedownward pointing triangle on the right side of the ' A t Each Change in'list box.
Set the ' U se Function' list box to Average.
In the 'Add Subtotal to:' list click on the check boxes next to T1, T2, T3,Avg, and GP. Scroll the list box to see T1, T2, T3, Avg, and GP. Click on OK. Ms Excel Notes 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.
Click on button number 2 to see the individual section averages. Note the buttons withthe plus signs that appear. Clicking on a plus sign button opens up the details for asingle section. After clicking on a plus button, a minus button appears. Click on theminus button to collapse the section.
Click on button number 3 to see the list of all students.
Suppose we now wanted to see the overall student averages by state. Changing asubtotals view is a three phase process.
Remove the existing subtotals
Resort by state (the new criteria)
Reapply the subtotals.
The key concept here is that the sort order must mirror the 'subtotal at eachchange in' choice. Subtotals subtotal at each change as Excel moves down the list. Ifthe states are not in alphabetic order, then at each and every change of state from row torow Excel will insert a subtotal. Subtotals on one field cannot be resorted on anotherwithout removing all existing subtotals first, hence the first step of removing thesubtotals.
Phase one: Choose Subtotals from the Data menu and then click on the Remove All button.
Phase two: Choose Sort from the Data menu.
Sort by State and Then By Sect and Then By Last
Click on OK.
Phase three: Set up the subtotals.
Choose Subtotals from the Data menu in Excel.
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.
Set the Use Function list box to Average.
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 T1, T2, T3, Avg, and GP. Scroll the list box to see T1, T2, T3, Avg, and GP.
Click on OK. Other subtotal functions
Subtotals functions include sum (the additive total), count (how many items), theaverage, the maximum value in the subset, the minimum value in the subset, and standarddeviations among other specialized functions. The sum and average functions are the mostcommonly needed functions. Old drayton tempus three manual . On the worksheet tab labeled Inventory is an example of aninventory spreadsheet demonstrating the use of the Sum function to tally up the value ofproperty in the A204 laboratory. Click on the Subtotal control panel numbered buttons toexplore the different levels of this spreadsheet. Filtering
Filtering allows one to display subsets of the data based on specific condition.Suppose we want to display only the students who will be on the deficiency list, thosewith an average less than 70.
Start filtering by removing the subtotals. Choose Subtotals on the Data menu and thenclick 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. Click on the grey button in the M (Avg) column to see a 'drop-down' list of options. This list includes preset values one can select to filter the data. Choose (Custom), the third item down the list. The following dialog box should appear:
Use the little 'down triangle' button to the right of the equals sign in thefirst blank under the word 'Avg' to choose a less than sign.
Either select 70 from the drop down list in the next box or type 70 in the next blankbox as seen above. Click on OK.
The result is a list of students who are deficient (below 70). If one chooses printwith 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 timeselect 'Show All' on the submenu.
Filters are another powerful use of Excel provided that your spreadsheet is set up indatabase format (field names in row one and no blank rows, one physical item per row inrows two and higher). Assets larger or smaller than a given number could be displayed, orstudents with a TOEFLs above or below a specific point could be shown. The 'Topten' filter option can display just that, the 'top ten' in a category. Pivot tables
Using Excel to set up a pivot table to study course versus gender grade averages. Thissection presumes that the worksheet named Dist has already been insertedin the workbook during work on the frequency function.
Pivot tables are easiest to set up when the spreadsheet is designed for data. Fieldnames should be in row one of the table, with data below. There should be no blank rowswithin the data.
Pivot tables (known in Microsoft Access as a cross-tab table) groups data by twocategories, producing summary information such as average, sum, or count according to twoor more categorizations. As a part of this process, pivot tables have the ability to takecategories 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 onthe left below is pivoted to produce the result on the right. A B C D E F G H 1 State Sex T3 Average of T3 Sex 2 Pohnpei F 80 State F M Grand Total 3 Pohnpei M 65 Chuuk 82.5 67.5 75 4 Pohnpei F 70 Pohnpei 75 70 72.5 5 Pohnpei M 75 Grand Total 78.75 68.75 73.75 6 Chuuk F 95 7 Chuuk M 60 8 Chuuk F 70 9 Chuuk M 75 Microsoft Excel Training Notes
The result are averages based on state and sex. The data in the Sex column, F and M,has 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 asfield names in a new table (hence the use of term 'cross-tab' by MicrosoftAccess).
To start a pivot table in the Gradebook workbook, click anywhere inside the field rowor the data rows of the Grades worksheet. Do not select a cell, just have the cursor in acell inside the data to be pivot tabled.
Choose Pivot Table from the Data menu.
Click on Next in the first dialog box. The default Microsoft Excel List or Database isusually selected and is the correct selection.
Click on Next in the second dialog box. If the spreadsheet is designed properly fordata then Excel will have correctly detected the data range.
Dialog box three presents the screen where choices are made as to row and columngroups.
Drag and drop the button marked State (St) from the right hand area of the dialog boxto the area marked Row.
Drag and drop the button marked Sex (Sx) from the right hand side to the Column area.
Drag and drop the button marked Avg from the area on the right to the Data area. Double click on the 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. Click on OK.
Click on Next.
The next dialog requests a location for the pivot table. Click on the Dist worksheet tab (created during the frequency function lesson). Click in A15 (below thechart if one exists in the gradebook). Click on Finish.
The data in the resulting PivotTable summarizes averages by state and sex for thefictional College of Micronesia-FSM spreadsheet.
To make the data more presentable:
Select the data in the pivot table by dragging the mouse across the number data.
Choose Cells on the Format menu.
The dialog box should open to the Number tab. If not, click on the tab marked Number.
Click on the item Number in the Category list box.
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. Graphing Pivot Tables
The data in a pivot table can be graphed to produce charts as was covered in the secondsession. Be careful to select only the state and sex breakdown data and not the grandtotal data. Look carefully at the diagram below and the note following the diagram.
Making the selection shown must be done by dragging from C20 to A16. Dragging from A16to C20 is not possible as A16 is a button that activates when clicked.
Note that in the particular chart shown has been adjusted by rotating the chart 180from the initial position. This was done by double-clicking on the chart and then choosing3-D View from the Format menu. In the 3-D View dialog box the rotation was set to 200(initial rotation was 20).
Pivot tables are best produced by good data designs. In turn, good data designs areproduced by considering the pivot table implications. Good design requires that each datarow be a single object or instance, not a an aggregate of data. Gradebooks almostnaturally 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 studyof lizards, for example, proper design of a good table is likely to involve listing eachindividual lizard in its own row. This would be as opposed to a table where each row was alocation and the data was the number of lizards in that location. The following fictitioustable is not well thought out from a pivot table perspective. For example, although theoriginal tallies might have indicated the number of females with tails, the resultingtable has lost that information. The design is also prone to typographic errors thatresult in internally inconsistent data: examine the sum of the number of males and femaleson trees. Location Number
of lizards Number
of Females Number
of Males Num w/
tail Beach 2 1 1 0 Pond 6 3 3 5 River 8 5 3 4 Rock 7 5 2 7 Tree 10 6 5 9
A better design would have been: Location Sex With Tail Tree F 1 Tree F 1 Tree F 1 Tree F 1 Tree F 1 Tree M 0 Tree M 1 Tree M 1 Tree M 1 Tree M 1 Tree M 1 Pond F 0 Pond F 1 Pond F 1 Pond M 1 Pond M 1 Pond M 1
and so forth. The resulting table can be subtotaled or pivoted to obtainaccurate summary information. Although the original data table will be lengthy, mostreports will use the results of subtotal calculations and pivot tables. The table may havetypographic errors, but the totals will at least be consistent with the data, there willnot be any internally conflicting data. Typos will also not likely affect counts the waythey can in the earlier table.
Microsoft has made significant changes to Excel 2016s annotation features. What used to be known as comments used like electronic Post it stickies for attaching reminders to cells of the worksheet are now called notes and comments now known as Threaded Comments function as a means for conducting a conversation with coworkers and clients with whom you have shared the Excel file in real time using Office 365.
This change in functionality of how to insert a comment in Excel is all part of Microsoft Offices new multi-user software or coauthoring capability that enables those with whom you share an Excel workbook file and have granted editing privileges to multiple users (known as guest contributors) to make changes in real time to its contents. Notes
Notes on the Review tab of the Excel 2016 Ribbon now function like comments originally did. They provide the means to attach reminders to particular cells of the worksheet. Keep in mind that notes are text boxes whose size and fonts can be edited. The Notes drop-down menu on the Review tab does contain a Convert to Comments option. However, when you use it to change notes into comments (see Comments that follows), they lose any editing assigned to them. Threaded comments
Comments can now record the ongoing commentary between you and the guest contributors with whom you share the workbook open for editing in Excel 2016. As you can see, when you select the Show Comments command button on the Review tab, Excel color codes the comments and displays them in chronological order as a threaded conversation in a Comments task pane. @Mentions in comments
When sharing a worksheet, you can use the new @mention tag in a threaded comment to alert or elicit feedback from a team member with whom its being shared. When you type the @ sign followed by the first few letters of a team members first or last name in the comments text box, Excel displays their full name. When you click the Save button in the comment, Excel sends an email to the named team member with a link to your comment in the shared worksheet. When the team member opens the email and clicks the link in the message, the worksheet in the shared workbook opens in Excel online in their web browser with comment containing their @mention displayed onscreen. The team member can then respond to your comment by taking action in the shared worksheet and/or initiating a conversation by responding to its contents. File sharing and co-authoring
File sharing in Excel 2016 has become much more robust with the addition of threaded comments to Excel Online, the web-based Excel app available on both Windows and Mac platforms to users who dont have Office subscriptions and the ability to make editing changes in real time. The figures in the File Sharing Updates gallery give you a good idea of how file sharing now works in Excel 2016. As you see in, if the workbook you have open for editing has not previously been saved in the cloud in a OneDrive or SharePoint folder, Excel displays the Share dialog box where you can upload a copy for sharing. Once the file has been saved to the cloud, Excel displays the Send Link dialog box shown where you specify the team members with whom you want to share the workbook and grant editing privileges. Once you click the Send button, Excel sends email messages to these coworkers with links for opening the workbook with their Web browsers in Excel Online. Once they open the workbook in Excel Online, all their editing changes appear in your copy of the file in real time. You can then use the threaded comments feature to discuss any questionable edits.