Tasks in MS-Excel
Read the 317 Text about MS-Excel. The text will probably contain new things about using Excel that you did not know. For example, how do you highlight non-adjacent cells in Excel? How can you enter a block of data quickly? What are different ways to fill down? Read the text!
To do in Excel:
Open Excel. Read in the text about Workbooks vs. Worksheets.
Type in: Monday - then use the drag box to place the successive days of the week in adjacent cells. Try this with the words: Mon.; January; Jan. Fill down rows and across columns.
For example, type in the 12 months of the year into consecutive columns starting at C12 through N12. Use January, February, etc.
Copy and paste the table below into Excel.
A / B / D / E / F / G1 / 1 / 1 / A / A / A
2 / 3 / B / C
Once in Excel:
After Pasting the table into Excel. Once in Excel, the entire table should still be highlighted – keep it highlighted.
First pull down: Format à AutoFormat…. then scroll to “none”
This will eliminate any formatting that may have come from Word. This is a good idea to always do after copying a table from Word into Excel.
Next, delete the Heading row (A B C …). This row is not needed in Excel and is confusing!
Highlight the cell(s) in each column and use the drag box with each of the following and see what happens.
After doing this you should know how to quickly consecutively number 500 rows in Column A (1, 2, 3, … ,500).
• Copy the next table to Excel. Once in Excel use Format à Autoformat... None.
A / B / DJanuary 2, 2003 / January 2, 2003
Use Format à Cells… to change the format of the date.
Use the two different fill à down methods to fill down the date. Notice the difference.
Read in the text for another way to copy the “same date” in successive cells.
How do you change the format of the date?
How do you change the format of any contents that are in a cell?
SAME ANS: Use the pull down: FORMAT à Cell…
How do you delete the contents of a cell?
How do you delete the format of a cell?
How do you delete both contents and format?
SAME ANS: One-way is to use: EDIT à Clear à All, or Format, or Contents
Formatting cells in Excel
Look carefully at what is available through the pull down: Format à Cells… There are many useful choices here!
Functions in Excel
The word “Function” refers to a specific feature in Excel. To see all of the different functions that are available, use the pull down: INSERT à Function… Another way to see to functions is to press the [fx] key on the toolbar. Note the different function categories and all the specific functions under each category. The category “All” will list all functions that are available.
Copy the table below to Excel and use the Function feature to complete the descriptive statistics. Learn how to "fill" the formulas to adjacent columns/rows.
Hint to highlight a table: Place the cursor anywhere in the table, hold down the Option key and double click.
Sit-ups / Push-ups67 / 35
75 / 44
50 / 50
60 / 35
79 / 47
40 / 20
45 / 15
80 / 61
74 / 55
69 / 39
Sum
Average
Standard Deviation
Minimum
Maximum
• Writing your own formulas in Excel (not using Functions)
Know what “Functions” refers to in Excel and do not use them when doing the following.
Know how to write a formula in Excel to do things such as:
• Add, subtract, multiply, square, square root
• Write a formula to calculate percent
• In the next table, use the Functions to calculate the mean and standard deviation for Tests 1 and 2.
Hints:
• Copy and paste the Word table below to Excel. While the table is still highlighted in Excel, do the next 2 steps.
• FORMAT à AutoFormat… None.
• Now is also a good time to FORMAT à Columns… Autofit… so more columns will appear on the screen. Sometimes you will have to change what cells are highlighted.
• Once in Excel delete the row with the Column letters A, B, C, etc. Then eliminate the column with the Row numbers 1, 2, 3, etc.
• Do what it says for Columns D through J.
• Remember that all formulas in Excel start with an = sign.
• Write a formula to calculate percent. [Remember that there is no Function to calculate percent.]
A / B / C / D / E / F / G / H / I / J1 / Add both test / Average / Repeat E / Repeat F / Repeat G / Percent / Percent
2 / Scores together / Test#1 & #2 / then / then / then / For Test #1 / For Test #2
3 / Test#1 / Test#2 / square / Multiply by 3 / Square root / (Max. 80 pts) / (Max. 90 pts.)
4 / 67 / 55
5 / 75 / 44
6 / 50 / 50
7 / 60 / 75
8 / 79 / 47
9 / 40 / 70
10 / 45 / 55
11 / 80 / 61
12 / 74 / 55
13 / 69 / 69
14 / Mean
15 / StDev
The next table is an example of writing the formula to calculate a z-score (the z-score formula is below). First, use Functions to calculate the mean and standard deviation for each set of test scores.
z-score = (X – mean of all X’s)/ standard deviation of all X’s
To do:
In column K write the formula to calculate z for each value in test #1
In column L write the formula to calculate z for each value in test #2
A / B / C / … / K / L1 / z-score / z-score
2 / Test#1 / Test#2
3 / Test#1 / Test#2 / data / data
4 / 67 / 55
5 / 75 / 44
6 / 50 / 50
7 / 60 / 75
8 / 79 / 47
9 / 40 / 70
10 / 45 / 55
11 / 80 / 61
12 / 74 / 55
13 / 69 / 69
14 / Mean
15 / StDev
[Side Note about MS-Word. Is there a floating page in the middle of the cells above? If so, what does this mean? Can you get rid of a floating page break? How do you move the floating page break?]
Possible Problems when copying and paste a table from Word to Excel:
• In MS-Word: The User accidentally highlighted a ¶ symbol either above or below the table. This is why it is good to use the easy (and safe) way to highlight a table in other ways than scrolling! Examples: place cursor within the table, hold down Option key and double click; or make cursor into a down pointing arrow hold down Option key and double click.
• In MS-Word, the table did not have all cells/columns equal width. Remember that in Word, adjacent cells can be merged into one (but this feature is not available in Excel). If cells were merged in Word, the table can be copied into Excel, but it is very difficult (impossible) to work on that table in Excel. To correct this problem:
1. Either fix the cells in Word before copying to Excel; or
2. Once in Excel (and the table is still highlighted)
Format à Cells… Alignment unselect Merge Cells
Charts in Excel
Numerical data, typically called quantitative data, can be shown as a picture in Excel using the available chart feature. Insert à Chart…
Some of popular charts used to show numerical data are:
Column, bar, line, pie, scatter, and area
Some of the uses of the charts are:
Scatter plot is used to show correlation and regression
Column chart can be used to show pre- and posttest data side by side
Line charts can be used to show how an individual performed over time
Be able to make a particular graph in Excel. Enter the data below and make a chart that looks “exactly” like the chart below. Notice:
No title.
Label both axes.
Add a legend and change the titles in the legend.
Change the color and pattern of different sections in the chart.
Take off the gray background color (make it white).
Take off the outer most border that is around the chart.
• Copy and paste the appropriate table of data to Excel. Notice the different ways that the data is arranged. One of these will not work!
• Then make a chart according the example below.
• Then copy the chart to word. Have the chart both linked and not linked
Subject No. / January / February / March112 / 10 / 13 / 11
113 / 12 / 12 / 16
114 / 14 / 11 / 14
115 / 15 / 16 / 12
116 / 12 / 13 / 13
117 / 13 / 12 / 15
January / February / March
112 / 10 / 13 / 11
113 / 12 / 12 / 16
114 / 14 / 11 / 14
115 / 15 / 16 / 12
116 / 12 / 13 / 13
117 / 13 / 12 / 15
Subject No. / January / February / March
A / 10 / 13 / 11
B / 12 / 12 / 16
C / 14 / 11 / 14
D / 15 / 16 / 12
E / 12 / 13 / 13
F / 13 / 12 / 15
• Use some of the same quantitative data and make these different charts.
Insert à Chart… bar, line, pie, and area
Clean-up the final chart in the same manner as above.
Some of the uses of the charts are:
Column chart can be used to show pre- and posttest data side by side
Line charts can be used to show how an individual performed over time
Scatter plot is used to show correlation and regression, and this illustrated next.
Excel’s Scatter PPlot
A scatter plot is a “special” chart used to show relationships and regression. Excel can make a scatter plot. The data required for this chart is two columns of data with an equal number of rows.
Enter the following data into Excel. However, before you type anything remember that cells can be copied and pasted from Word to Excel. Also, do you remember the easy (and safe) way to highlight this entire table? Ans: Move the insertion bar anyplace within the table, hold down option key and double click.
Height / Weight50 / 155
60 / 190
74 / 250
71 / 200
55 / 149
58 / 180
65 / 185
77 / 220
72 / 195
65 / 180
66 / 178
68 / 200
• Make a Scatter chart:
Use the two columns of data and make a scatter plot. The first copy of the scatter plot below is probably what your first attempt looks like. However, take a close look at the graph and notice some of the problems it has; i.e. the legend that makes no sense, all data points are in the upper corner, gray color, and a box around the entire graph. These things need to be “cleaned-up.”
The following is a poor example of a graph. This is a non-cleaned up graph.
• Clean-up Excel charts for reports
MS-Excel is just a "tool" to help a person write a final report and also to understand numerical data. In most cases a person will use the Excel output to help prepare a final report (that is usually done in MS-Word).
• Now, clean-up your original scatter plot so it looks like the one below.
On the “cleaned-up” Figure notice:
No border (box) appears around the outside
Everything is in black & white
Data points are black
No gray background
The title was typed in MS-Word (Figure titles appear below the Figure).
No legend (because it is not needed here)
Scales were changed
The regression formula and R2 value were added
Figure 1. Scatter plot between the height and weight of Cal Poly kinesiology majors N=12).
More about Charts:
1. Once the Chart is made, the User can return to the Chart Wizard at any time. Simply click on the Chart Wizard button.
2. Be Careful about labeling columns that do contain data you want in the chart. The first table below would produce a chart with the Subject No. being used as data (which is not correct). The second table would work fine. Notice the difference.
Actually the next Table would also work fine because the Subject No. is not numerical.
3. Normally, the default of Series in: Rows or Columns will be correct; but look at this feature just in case the Chart is not correct, or the User has the original data arranged in a different order. See the window below.
ANSWERS ANSWERS
How do you highlight non-adjacent cells in Excel?
ANSWER: Click on one cell(s) then hold down the Command key and click on other cell(s). This also works in tables in MS-Word.
How can you enter a block of data quickly? Answer: Highlight a block of cells and then immediately type in a number followed by either the tab key or return key, and repeat.
What are different ways to fill down? Answer. Highlight and use pull down Edit à Fill à Down/Right or click on the cell and then drag the handle in the lower right hand corner of a cell. Notice that when “filling down” a date (January 1, 2003); the two methods of fill down produce different results.
Note: The table was split so it is easier to read the contents within the cells on the screen.
A / B / C / D / E / FAdd both test / Average / Repeat E
Scores together. / Test#1 & #2 / then
Test#1 / Test#2 / Total / square
67 / 55 / =(B4+C4) / =(B4+C4)/2 / =((B4+C4)/2)^2
75 / 44
50 / 50
G / H / I / J
Repeat F / Repeat G / Percent / Percent
then / then / For Test #1 / For Test #2
Multiply by 3 / Square root / (Max. 80 pts) / (Max. 90 pts.)
=(((B4+C4)/2)^2)*3 / =((((B4+C4)/2)^2)*3)^.5 / =(B4/80) / =(C4/90)
Note: Use the Drag box or Fill à Down to copy the formula in the rest of the cells in each column.
In column K write the formula to calculate z for each value in test #1
In column L write the formula to calculate z for each value in test #2
Z= X – mean of all X’s/ standard deviation of all X’s
K / LZ score for Test #1 / Z score for test #2
=(B4-$B$14)/$B$15 / =(C4-$C$14)/$C$15
Note: The $ signs make the cell location absolute (will not change when filling). Read about absolute cells in the Text.
[Side Note in MS-Word. If you see a dotted line through the cells of a table (assuming you are in View – Normal), then there is a floating page break. The printer will "break" the table there and print part of the table on two different pages. This should be avoided! The floating page break cannot be removed, but the page break can be moved. One way is to place a hard page break (also called a forced page break) immediately above the table; INSERT à Break à Page (or SHIFT/Enter keys).