Charts in Microsoft Excel

[Pages:4]Charts in Microsoft Excel?

A brief overview, prepared by Pamela Peterson Drake, James Madison University

Creating a chart in Microsoft Excel? requires a few, simple steps:

Step 1: Create the worksheet that contains the data. Step 2: Highlight the data to be graphed and then "Insert" a "Chart" using that data. Step 3: Customize the chart to your preferences by double-clicking on an item and then changing its

format.

Step 1: Create the worksheet that contains the data.

Suppose you wish to create a chart in Microsoft Excel? that describes a company's profit ratios over a period of years. You've gathered and input the following data into your worksheet:

A

B

C

D

1

Operating

Year

income Net income Revenues

2 2000 $ 11,006 $ 9,421 $ 22,956

3 2001 $ 11,720 $ 7,346 $ 25,296

4 2002 $ 8,272 $ 5,355 $ 28,365

5 2003 $ 9,545 $ 7,531 $ 32,187

6 2004 $ 9,034 $ 8,168 $ 36,835

Calculate the profit margins in additional columns that use this data:

A

B

C

D

E

F

1 Year

Operating

Operating Net profit

income Net income Revenues profit margin margin

2 2000 $ 11,006 $ 9,421 $ 22,956

3 2001 $ 11,720 $ 7,346 $ 25,296

4 2002 $ 8,272 $ 5,355 $ 28,365

5 2003 $ 9,545 $ 7,531 $ 32,187

6 2004 $ 9,034 $ 8,168 $ 36,835

For example, the entry into cell E2 is =b2/d2

And the entry into cell F2 is =c2/d2

Appropriate copying and pasting of these formulas will produce the cell values:

A

B

C

D

E

F

1 Year

Operating

Operating Net profit

income Net income Revenues profit margin margin

2 2000 $ 11,006 $ 9,421 $ 22,956

47.944% 41.039%

3 2001 $ 11,720 $ 7,346 $ 25,296

46.331% 29.040%

4 2002 $ 8,272 $ 5,355 $ 28,365

29.163% 18.879%

5 2003 $ 9,545 $ 7,531 $ 32,187

29.655% 23.398%

6 2004 $ 9,034 $ 8,168 $ 36,835

24.526% 22.175%

Step 2: Highlight the data to be graphed and then "Insert" a "Chart" using that data. Highlight the data you want to chart:

A

B

C

D

E

F

1 Year

Operating

Operating Net profit

income Net income Revenues profit margin margin

2 2000 $ 11,006 $ 9,421 $ 22,956

47.944% 41.039%

3 2001 $ 11,720 $ 7,346 $ 25,296

46.331% 29.040%

4 2002 $ 8,272 $ 5,355 $ 28,365

29.163% 18.879%

5 2003 $ 9,545 $ 7,531 $ 32,187

29.655% 23.398%

6 2004 $ 9,034 $ 8,168 $ 36,835

24.526% 22.175%

Then click on "Insert" and then choose "Chart" The Chart Wizard should appear and you will choose your chart / graph from the types shown.

To make sure that you have labels on your x-axis, select the tab in the dialog for "Series". In the Category (X) axis labels, click on the spreadsheet icon in the far right of the blank space, , and then select the X-axis labels:

A

B

C

D

E

F

1 Year

Operating

Operating Net profit

income Net income Revenues profit margin margin

2 2000 $ 11,006 $ 9,421 $ 22,956

47.944% 41.039%

3 2001 $ 11,720 $ 7,346 $ 25,296

46.331% 29.040%

4 2002 $ 8,272 $ 5,355 $ 28,365

29.163% 18.879%

5 2003 $ 9,545 $ 7,531 $ 32,187

29.655% 23.398%

6 2004 $ 9,034 $ 8,168 $ 36,835

24.526% 22.175%

And then click the spreadsheet icon after you make your selection. Click on "Next" and then follow the dialog box for "Chart Options" and you've got yourself a chart.

For example, without any special adjustments, the chart using the above procedure produces the following graph (that has been copied from Excel? and shrunk to fit below):

60.000%

50.000%

40.000%

30.000%

20.000%

10.000%

0.000% 2000

2001

2002

2003

2004

Operating profit margin Net profit margin

Step 3: Customize the chart to your preferences by double-clicking on an item and then changing its format.

Now you get to add your own style to the chart. You can make your graph more readable by adding axis titled, adding or removing gridlines, moving the legend around to the top or wherever you'd like, and adding data or labels to graph items.

But with a little adjustment, it could look like:

Operating and net profit margins for Microsoft Corporation 2000-2004

50% 40%

Operating profit margin Net profit margin

30% Margin

20%

10%

0% 2000

2001

2002

2003

Fiscal year

2004

Just double-click on the item you want to change and a dialog box will open up and you can "play" with the item. For example, in the above graph, I did the following:

? Format chart: Removed the border and area background; Changed the fonts ? Format axis: Changed the y-axis format

? Format data series: Changed the gap width and overlap; removed border; added shading; added fill effects

Of course, you could get carried away and do too much stuff to your chart, which will cause it to lose effectiveness. Consider the following, which is a bit too much "formatting":

Operating and net profit margins for Microsoft

Corporation

2000-2004

Operating profit margin

60%

Net profit margin

50%

40%

Margin 30%

20%

10%

0% 2000

2001

2002 Fiscal year

2003

2004

? If you would like to view the Excel? workbook that was used in this example, check out:

? Other instructional help with Excel? charting: o o o

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

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

Google Online Preview   Download