Excel Charts in Publisher

Document Sample
Excel Charts in Publisher Powered By Docstoc
					                Excel Charts in Publisher
Scenario: your boss wants to know how well each of his three salespeople performed
in the first three months of the year. You have to create a report in Publisher but the
                             chart will be created in Excel.

1. Create a table for your chart

To start with, you need a table in Excel that you can use to generate your chart.

If you are working with a table in Word or Publisher, you can copy and paste the
table into Excel.

For this tutorial, we will create a table of sales figures over a number of months.

1. Click on cell B2

2. Type Jan

3. Move the mouse pointer to the fill handle

                                                     fill handle

4. Drag to the right across Row 2, until the abbreviation “Mar” is displayed in the
info box (the yellow box).

5. Remove the marking by clicking anywhere on the Excel worksheet.

This feature of Excel is called a Custom List. Custom lists are already set up for
months (full names or abbreviations) and for days of the week (full names and
abbreviations. To see this in action, try typing “Mon” or “Monday” into a cell and
dragging the fill handle up, down, left or right. You can create your own custom lists
if you like (go to Tools, select Options and then click the Custom Lists tab).

                                      Page 1 of 21
2. Inserting a Chart

Now you have the months in cells B2 – D2, enter the rest of the data, as shown below:

1. Click on Cell A3

2. Enter “Adam” (without the inverted commas) and press the TAB key on your
keyboard (you should now have moved across to B3).

3. Enter “1000” (without the inverted commas).

4. Press Tab again

5. Continue entering data until your table looks like this:

6. Click on any cell in the table and then start the Chart Wizard by pressing the
button on the toolbar:

7. You see from the top of the chart wizard that you are going to be taken through a
four-step process:

In the first of these four steps, Excel wants to know what type of chart you want to

                                      Page 2 of 21
3. Choose your chart type

In the column on the left hand side, there is a list of Chart Types (e.g. Column, bar,
line and so on)

On the right, there are sub-types where you can choose from several variations on the
chart type.

Choose the standard column graph and click the Next button

                                     Page 3 of 21
4. Which Data Range?

Excel needs to know what data to include in your chart. You don’t want to make any
changes here because you want Excel to include all the data that you put in your table.

Excel also wants to know if you want your chart to compare the rows or the columns.

If you select Rows, Excel will compare how each salesperson performed during each
month (Jan, Feb, Mar).

If you choose Columns, Excel will produce a chart that shows how good each month
was for each of the three salespeople (Adam, Connolly and Carter).

Select Columns to see this effect but then re-select Rows because we want to
compare the salespeople against each other.

                                     Page 4 of 21
5. Editing the Series

If you click the Series tab, you can exclude rows or columns from the chart.

For example, if you do not want to include a particular salesperson’s data, you could
remove his/her name here.

You can also add other rows or columns of data from elsewhere in your spreadsheet.

If you had selected Columns for the Data Range, you would have the choice of
adding or removing a month.

Because we want to include the data for all three salespeople, don’t make any changes
on this screen. Just click Next:

                                     Page 5 of 21
6. Add Information about the Chart

Your chart has to be self-explanatory. Therefore, you will want to give it a title and
also to label the x (horizontal) and/or the y (vertical) axes. You will also want to
display a key (legend).

1. Add a title for the chart and add a label to the y axis, as below:

2. Click on the Legend tab and move the legend to the bottom of the chart, as below:

3. Then click Next:

                                       Page 6 of 21
7. Put your Chart into Publisher

You eventually want to take your completed chart into Publisher but first you have to
finish the chart wizard and save your Excel file.

1. You want to create the chart in the current sheet, so just click OK and the wizard
will create your chart.

2. Save your work by selecting Save As in the File menu. Give your Excel file a
sensible filename.

3. Exit from Microsoft Excel.

4. Start up Microsoft Publisher.

5. Start with a Blank Page.

6. From the Publisher menu bar, click Insert.

7. Choose Object from the Insert menu.

8. Select Create from File:

9. Click on the Browse button.

                                     Page 7 of 21
10. Select your Excel file, double-click it.

11. Click Insert.

12. Click OK.

Something like this should appear on your Publisher document:

You should immediately notice that you have a problem. You have, not only the
chart but also the data table that it was generated from. How can you get rid of the

Fortunately, because you inserted the chart as an Object, you can easily edit it in
Excel, without leaving Publisher!

                                      Page 8 of 21
8. Changing an Embedded Object

Your chart is what is known as an Embedded Object.

1. To edit an embedded object, click the Right Mouse Button anywhere on the

2. Select Change Object/Microsoft Excel Worksheet/Open from the pop up menu.

3. Your Chart should now open in Excel.

At the end of the Chart Wizard, you opted to create the chart as an object
in Sheet 1 of your Excel Workbook. A better thing to do would be to
give the chart a sheet of its own. This way, you won’t get the gridlines
and the table behind your chart in Publisher.

Fortunately, you can change your mind, even though the Wizard has

                                   Page 9 of 21
9. Giving the chart a sheet of its own

First you have to select your chart. Click in a white area of the chart. Make sure you
don’t click on the legend or the title by mistake.

You will now notice that you have a Chart Menu in the menu bar of Excel.

1. Open the Chart menu

2. Select Location

3. You should now see the following screen, which you’ve seen before:

This time, select As new Sheet and then click OK.

                                    Page 10 of 21
4. You should now have something that looks like this:

As you can see at the bottom-left corner of the Excel window, your chart is now a
sheet of its own.

5. Now you realise that you can edit the chart, you decide to change the appearance of
your chart further. Try double-clicking the Legend, the Chart Title, the Axis Title,
the chart background and the chart itself. You get dialog boxes that allow you to
change things like colour schemes and fonts. You can make a real mess of your chart
in this way. Choose an appearance that you think the boss would like. Hint: not like
the example below:

                                    Page 11 of 21
6. One final detail – you notice that the legend isn’t wide enough and it isn’t centred.
To make the legend wider, click on it and “drag” the black handles. To move the
legend, click on the legend and drag.

7. You finally decide that you want to update your Publisher document, so open the
File menu and select Update.

8. You should now Exit from Excel. You should be returned to Publisher and the
gridlines and data table should be gone from behind the chart.

                                     Page 12 of 21
10. Adding text in Publisher

Now the chart is in Publisher, you can create a publication about the sales figures.
You’re not going to make a full sales report, but you are going to practice adding text
and graphics.

1. Open the File menu and select Save As. Give your publication a sensible filename
and click Save.

2. Re-size the chart so that it fits on the page (click the chart and drag the black

3. All text goes in a text box. Click on the text button:

and create a text box:

Write a heading like Company Sales Figures in the text box.

5. You will find that you cannot read the text you are writing. Press F9 (on the
keyboard) and you will zoom in. Press F9 again to zoom out.

You can also zoom in and out by adjusting the “zoom control” at the bottom-left of
the Publisher window (using the F9 key is easier and quicker).

6. Highlight the text in the text box and change the font to Arial and the size to 24
point. Centre the text.

                                      Page 13 of 21
11. Adding graphics to Publisher

1. Make sure the text box is no longer highlighted, by clicking on a white space
anywhere in the publication. The black handles should disappear.

2. Open the Insert menu on the Excel toolbar.

3. Select Clipart.

4. Choose some suitable clipart for your publication and insert it into your document.
Publisher has a facility for you to Search for the clipart you want.

If you have GIF, BMP or JPG files from the Internet, from a graphics application or
from a scanner, you can insert these into your publication by selecting From File
from the Insert/Picture menu.

5. Save your work again (press CTRL+S to do a quick save).

                                    Page 14 of 21
12. Changing the Values for a Chart

Just when you thought you had the file complete, saved and ready for the boss, Miss
Adam comes into the office in a very agitated state. She has made a mistake when
entering the turnover values for January. She entered £4,000 instead of £1,000.

1. Once again, you have to open the embedded chart in Excel. Click the Right Mouse
Button on the chart and the pop-up menu should appear.

1. Select Change Object

2. Select Microsoft Excel Worksheet

3. Select Open

The chart should now re-open in Excel.

4. Move to Sheet 1 by clicking the tab at the bottom-left of the Excel window.

                                    Page 15 of 21
5. You can now change Miss Adam’s figures for January from £1,000 to £4,000.

5. Return to Chart 1 and you should see that Excel has corrected the Chart.

You now realise that you are frequently going to have to go through the same process.
Every month, new sales figures will need to be added to the chart and salespeople
may need to be added and removed.

In fact, just as you are changing the sales figures for Miss Adam, the new figures for
April land on your desk. How are you going to add these for your chart?

                                    Page 16 of 21
13. Inserting new features into an old chart

Now you are going to add an extra column of data to your data table and change the
data range of your chart.

1. Go to Sheet 1 where your data table is located.

2. Click on cell D2

3. Click on the Fill handle (see section 1 of this tutorial) and drag to cell E2. The
column heading Apr should be automatically inserted because of the Custom List.

4. Enter the sales figures for April, as below:

5. Return to Chart 1

6. From the Chart menu, select Source Data.

You will now be able to add to the data being displayed in the chart

                                      Page 17 of 21
7. You will notice from the dashed line around the source data, that the figures for
April are not yet included in the chart. To remedy this, drag the mouse so that the
whole table is highlighted, as below:

8. Click OK and you will be returned to the chart, where you will see that the figures
for April are now included.

It’s a good job that Excel allows you to update charts so easily because the company
has just hired a new salesperson.

                                     Page 18 of 21
14. A New Salesman

The new salesperson is one Mr E Cartman and he has already made £4,500 of sales in
his first month.

You will add the new salesperson to the chart in the same way that you added a new

1. Click on Sheet 1 to edit your data table.

2. Add Cartman’s figures for April, as below:

3. Return to Chart 1

4. From the Chart menu, select Source Data.

5. As before, drag the dashed line around the entire table, so that Cartman’s figures
are included in the data range. Then click OK to see your chart updated.

                                     Page 19 of 21
15. Sorting cells from A to Z

You can edit your data table in other ways and the chart will be updated when you do

For example, it might be a good idea to list the salespeople in alphabetical order.

1. Click on Sheet 1 to return to the data table

2. Mark the table by dragging the mouse over the range of cells.

3. Click on the Sort Ascending button.

4. You should see that the list of salespeople is now sorted in alphabetical order
according to the salespeople’s surnames.

5. Click the Chart 1 tab and you will see that your chart has been updated to reflect
this change in the data table.

                                     Page 20 of 21
16. Changing the Chart Type

Finally, you decide to change the colour scheme and the chart type, to make the chart
more visually appealing.

1. Section 9 of this tutorial described how you change the visual appearance of the
chart by double-clicking on areas of the chart to bring up a Format dialog box.

2. You decide that you also want to change the chart type. You can do this by
selecting Chart Type from the Chart menu.

3. Experiment with the different chart types and select one that would be suitable for
your purpose.

4. When you’ve finished, remember to select Update from the File menu to update
the chart in Publisher. Finally, Exit from Excel. Finally, Save and Print your
completed publication in Publisher.

That’s the end of this tutorial! Hopefully you have learnt a great deal about Publisher,
Excel and you should now understand the nature of embedded objects in Microsoft
Office applications.

                                     Page 21 of 21