intermediate microsoft excel 2007- charts and …...excel 2007- charts and formulas intermediate...
TRANSCRIPT
John W. Jacobs Technology Center 450 Exton Square Parkway
Exton, PA 19341 610.280.2666 [email protected]
www.ccls.org Facebook.com/ChesterCountyLibrary
Intermediate Microsoft Excel 2007-
Charts and Formulas
Intermediate Excel 2 Workshop
Charts and Formulas (rev2)
Page 1
Workshop Topics:
Charts and Formulas
o Create multiple charts from a single worksheet and links to another
file
o Change the appearance and location of a chart
Formulas
o Use date, autosum, logical, and lookup functions
o Understand relative and absolute addressing
Outline of Workshop:
Charts and Formulas
o Autosum formula for rows and columns
o Insert column chart
o Modify the column chart using the chart tabs
o Use formulas to arrange data for other chart types
o Insert pie chart
o Modify the pie chart using the chart tabs
o Use formulas and links (from another file) to prepare data for other
chart types
o Insert line chart (Optional – if time permits)
o Modify the line chart using the chart tabs (Optional – if time permits)
o Copy a chart
o Move a chart to a separate worksheet tab
o Make changes by hiding rows
Formulas
o Date functions: NOW()
o Logical functions: IF SUMIF
o Lookup function: VLOOKUP (Optional – if time permits)
o Review relative and absolute addresses
Intermediate Excel 2 Workshop
Charts and Formulas (rev2)
Page 2
Autosum formula for rows and columns
1. Go to H3 in the “Graph of Monthly Expense” tab. 2. Click on the FORMULAS worksheet tab and select the AUTOSUM command (SUM). 3. Copy the formula down to the H7 cell. 4. Go to cell B8. Repeat the AUTOSUM command for B8 and copy across to the H8 cell.
The screen should look the same as the following:
Intermediate Excel 2 Workshop
Charts and Formulas (rev2)
Page 3
Insert column chart
Steps to create column chart
1. Highlight range (e.g.-A2 to G7) 2. Go to INSERT TAB 3. Click on COLUMN 4. Click on 3-D CLUSTERED 5. Click on LAYOUT 3 in Chart Layouts 6. Position chart and resize 7. Name the chart.
Modify the column chart using the chart tabs
Design Tab (Try the chart layout and style options)
Intermediate Excel 2 Workshop
Charts and Formulas (rev2)
Page 4
Layout Tab
Format Tab
Use formulas to arrange data for other chart types
1. Go to cell J3. 2. 2. Enter the following formula: =A3 3. Copy the formula in cell J3 to cells J4 through J8. 4. Go to cell K2. 5. 2. Enter the following formula: =H2 6. Copy the formula in cell K2 to cells K3 through K8.
Intermediate Excel 2 Workshop
Charts and Formulas (rev2)
Page 5
The screen should look the same as the following:
Insert Pie Chart
Pie Chart
1. Highlight range (e.g.-J2 to K7)
2. Go to INSERT TAB
3. Click on PIE
4. Click on 3-D PIE
5. Click on DATA LABELS in LAYOUT TAB
6. Click on BEST FIT
7. Position chart and resize
The screen should look the same as the following:
Intermediate Excel 2 Workshop
Charts and Formulas (rev2)
Page 6
Modify the pie chart using the chart tabs
Design Tab (Try the chart layout and style options)
Layout Tab
Format Tab
Use formulas and links (from another file) to prepare data for other chart types
1. Go to cell B11.
2. Start a formula by entering: “=”
3. Click on the other Excel file named “Sales1” on the task bar at the bottom of the screen.
4. Go to cell B3 in the “Sales1” file screen and enter one left click on the mouse.
5. Press the ENTER key on the keyboard.
6. Adjust the absolute to relative address by deleting the “$” symbol in the formula.
7. Copy the formula in cell B11 to cells C11 to G11.
8. In cell B12, enter a formula that copies the data in cell B8.
9. Copy the formula in cell B12 to cells C12 to G12.
10. In cell B13, enter the following: =B11-B12
11. Copy the formula in cell B13 to cells C13 to G13.
12. Enter the Autosum function (for the rows) in Cells H11 to H13.
Intermediate Excel 2 Workshop
Charts and Formulas (rev2)
Page 7
The screen should look the same as the following:
Insert line chart (Optional – if time permits)
1. Highlight Cells A10 to G11.
2. Hold the “Ctrl” key down and highlight the cells A13 to G13.
3. Go to INSERT TAB.
4. Click on LINE.
5. Click on “Line with Markers” option (1st option in row 2).
6. Click on LAYOUT 3.
7. Position chart and resize 8. Name the chart.
Intermediate Excel 2 Workshop
Charts and Formulas (rev2)
Page 8
The screen should look the same as the following:
Modify the line chart using the chart tabs (Optional – if time permits)
The options are available in the Design, Layout, and Format tabs see the references above.
Copy a chart
1. Highlight the PIE chart on the screen.
2. On the HOME TAB, click the copy command.
3. Go to cell M2.
4. On the HOME TAB, click the paste.
5. Position chart and resize
Intermediate Excel 2 Workshop
Charts and Formulas (rev2)
Page 9
The screen should look the same as the following:
Move a chart to a separate worksheet tab
1. Highlight the PIE chart recently copied.
2. On the DESIGN TAB, click the Move Chart command icon.
3. Click on the “New Sheet” option and then “OK.”
4. The PIE chart was moved to the “Chart 1” worksheet tab.
The screen should look the same as the following:
Intermediate Excel 2 Workshop
Charts and Formulas (rev2)
Page 10
Make changes by hiding rows
1. Go to the “Graph of monthly expense” worksheet TAB.
2. Move the mouse to the row # 3 indicator. A appears.
3. Right click and choose “Hide” as the option.
4. Observe the differences in the charts.
The screen should look the same as the following:
Intermediate Excel 2 Workshop
Charts and Formulas (rev2)
Page 11
Formulas
Date Functions: NOW()
1. Go to the “WS Formulas” worksheet tab. 2. Go to the cell A1. 3. Click on the FORMULAS TAB. 4. Click on the “Date and Time” option. 5. Click on the NOW function. 6. Cell A1 includes the following function: =NOW() 7. The date and time are recorded as a time stamp. 8. View the other options under “Date and Time.”
Logical functions: IF
1. Go to the cell E3. 2. In cell E3, we wish to have the following IF statement evaluate the difference in the yearly
amount (Column T) vs. the budget amount (Column U): =IF(T3<U3,"Under","Over") 3. Review APPENDIX “A” 4. Remain in cell E3 and click on the “fx” button to the left of the formula bar. 5. Have EXCEL help to find the IF statement by typing the word “IF”
in the search area. Then click on “GO.” 6. Select the “IF” function and click “OK.” 7. The screen should now show the “Function Argument” window. 8. Let EXCEL help you fill in the 3 parts of the IF statement. Then
click on “OK.” 9. Click on E3 and then the FORMULA BAR to view the color coding
of the function as an additional way to understand the IF statement.
Intermediate Excel 2 Workshop
Charts and Formulas (rev2)
Page 12
Logical functions: SUMIF
1. Go to the cell H17. 2. In cell H17, we wish to have the following SUMIF statement
reference the expense categories in column “G” and add the expense amounts in the subsequent columns of numbers in column “H” through column “S” by matching the expense categories in column “G:” =SUMIF($G$3:$G$15,$G17,H$3:H$15)
3. Review APPENDIX “B” 4. Remain in cell H17 and click on the “fx” button to the left of
the formula bar. 5. Have EXCEL help to find the SUMIF statement by typing the
word “SUMIF” in the search area. Then click on “GO.” 6. Select the “SUMIF” function and click “OK.” 7. The screen should now show the “Function Argument”
window. 8. Let EXCEL help you fill in the 3 parts of the SUMIF
statement. Then click on “OK.” 9. Click on H17 and then the FORMULA BAR to view the color
coding of the function as an additional way to understand the SUMIF statement.
Intermediate Excel 2 Workshop
Charts and Formulas (rev2)
Page 13
Lookup function: VLOOKUP (Optional – if time permits) 1. Go to the cell B3. 2. In cell B3, we wish to have the following VLOOKUP statement
reference the named city in column “A” and search the referenced table (W2 through Y11) to locate and return the value for the State in the indicated column (2) of the table on the same table row: =VLOOKUP(A3,$W$2:$Y$11,2)
3. Remain in cell B3 and click on the “fx” button to the left of the formula bar.
4. Have EXCEL help to find the VLOOKUP statement by typing the word “LOOKUP” in the search area. Then click on “GO.”
5. Select the “VLOOKUP” function and click “OK.” 6. The screen should now show the “Function Argument” window. 7. Let EXCEL help you fill in the 3 required parts of the VLOOKUP
statement. Then click on “OK.” 8. Click on B3 and then the FORMULA BAR to view the color
coding of the function as an additional way to understand the VLOOKUP statement.
Review relative and absolute addresses
1. Review APPENDIX “C”
IF function
This describes the formula syntax and usage of the
IF function in Microsoft Office Excel.
=IF(logical_test, value_if_true, [value_if_false])
The IF function in cell E3 is: =IF(T3<U3,"Under","Over")
Description
The IF function returns one value if a condition you specify evaluates to
TRUE, and another value if that condition evaluates to FALSE. For
example, the formula =IF(T3<U3,"Under","Over") returns "Under" in cell
E3 if the expense in cell T3 (Total expense for the year) is less than the
expense in cell U3 (Budget for the year), and "Over" if T3 is greater than
U3. This function can be used to help the reader to determine a favorable
or unfavorable situation versus the budget.
COMPARISON OPERATORS
You can compare two values with the following operators. When two values are compared by using these operators, the result is a logical value either TRUE or FALSE.
COMPARISON OPERATOR MEANING EXAMPLE
= (equal sign) Equal to A1=B1
> (greater than sign) Greater than A1>B1
< (less than sign) Less than A1<B1
>= (greater than or equal to sign) Greater than or equal to A1>=B1
<= (less than or equal to sign) Less than or equal to A1<=B1
<> (not equal to sign) Not equal to A1<>B1
APPENDIX A – IF Function
SUMIF function
This describes the formula syntax and usage of the SUMIF function in
Microsoft Office Excel
Description
=SUMIF($G$3:$G$15,$G17,H$3:H$15)
In this formula in cell H17, you can apply the criteria to one range (G3 to
G15 where one of the expense categories matches the expense category
listed in G17) and sum the corresponding values in a different range
(Columns H to S). For example, the formula in cell H17 is
=SUMIF($G$3:$G$15,$G17,H$3:H$15) and sums only the values in the
range from H3 to H15, where the corresponding cells in the range from G3
to G15 equal the same value that is in cell G17 (Rent). The formula in
column “H” was then copied to columns “I” through “S”. Please note that
the use of relative and absolute addresses in the formula allow for the
summations by columns based on the selection evaluation that takes place
in column “G” only.
APPENDIX B – SUMIF Function
Relative and absolute cell address
Copy a formula:
The following table summarizes how a reference type updates if a formula that contains the reference is copied two cells down and two cells to the right.
FOR A FORMULA BEING COPIED:
IF THE REFERENCE IS: IT CHANGES TO:
$A$1 (absolute column and absolute row)
$A$1
A$1 (relative column and absolute row)
C$1
$A1 (absolute column and relative row)
$A3
A1 (relative column and relative row)
C3
The formula in cell H17 is
=SUMIF($G$3:$G$15,$G17,H$3:H$15). When this formula is
copied across the row and down the column, the absolute range
from G3 to G15 does not change. The selection criteria of G17
will change the relative address of the row 17 but will not change
absolute column designation of G. The range from H3 to H15 will
change columns but not rows when copied.
APPENDIX C – Relative and Absolute cell address