Intermediate Microsoft Excel 2010- Charts and Formulas

[Pages:17]John W. Jacobs Technology Center 450 Exton Square Parkway Exton, PA 19341 610.280.2666 ccljtc@

ChesterCountyLibrary

Intermediate Microsoft Excel 2010-

Charts and Formulas

Intermediate Excel 2010 Workshop 2 Charts and Formulas

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

Page 1

Intermediate Excel 2010 Workshop 2 Charts and Formulas

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:

Page 2

Intermediate Excel 2010 Workshop 2 Charts and Formulas

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)

Page 3

Intermediate Excel 2010 Workshop 2 Charts and Formulas 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.

Page 4

Intermediate Excel 2010 Workshop 2 Charts and Formulas 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:

Page 5

Intermediate Excel 2010 Workshop 2 Charts and Formulas 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.

Page 6

Intermediate Excel 2010 Workshop 2 Charts and Formulas

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.

Page 7

 ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download