Excel Charts - Pictograph Chart Tutorials

 

A pictograph chart uses images rather than bars to represent amounts of data. This can make the chart easier to visualize. If I want to compare apples to oranges I can actually see the number of apples compared to the number of oranges rather than looking at different colored bars. This is especially helpful for younger audiences.

You could create your own images to use in a pictograph or you can simply use images from Microsoft’s clipart. You should use simple images that do not have backgrounds.

Work along with the tutorials below to create these Pictograph charts.

Excel Pictograph Chart for Cats and Dogs

Pictograph Chart with Ribbons Jump to the tutorial for this award banner pictograph chart.

Exercise for Dog and Cat Grooming Pictograph Chart

JoJo’s Pet Store does animal grooming. She groomed the number of dogs and cats for July and August as shown in the spreadsheet below.

  1. Create a new workbook named Pictograph.
    Click on the ribbon’s Page Layout tab. In the Themes group click on the Themes button. Select the Austin theme.
    Enter the data as shown in the spreadsheet.

    Worksheet Data for Dog and Cat Pictograph Chart

  2. Select cells A3:C5
    On the ribbon click on the Insert tab, in the Charts group click on the Column button.
    Click on the first chart (Clustered Column) in the 2-D Column section.

    Insert Column Chart

    Your chart should look similar to the one below.
    Click on the bar for July Dogs. Because Dogs are part of a Series the August Dog's bar is selected as well.

    Dogs and Cats Bar Chart

  3. We will get our images from the Microsoft Clipboard. On the Ribbon, on the Insert Tab, in the Illustrations group, click on the Insert Tab. In the Illustrations group click on the Clip Art button. Enter Dog in the Search for textbox.
    Select Include Office.com content

    Clip Art Search

    Click on the Go button
    You will probably want to widen the Clipboard pane so that you can view more images at a time.

    The images displayed in the search have too much detail. We want simpler images. Clip Art Search for the word Dog


    Click on the down arrow for the media file types. Select only Illustrations.
    Click on the Go button again.     		Select Illustrations from Clip Art


    Scroll through the images until you find the Point Setter dog. Move your cursor over the dog. Click on the button with the arrow next to it to bring up a context menu. Select Copy from the context menu. Clip Art Illustrations select Dog


    Click on the dog series for July. The dog series for July and August should be selected. Press Ctrl + V. The images are stretched to the size of what the columns were. Bar Chart for July and August with Dog Illustration


    Right-click on one of the dogs then select Format Data Series Select Format Data Series


    Click on Fill on the left side of the Format Data Series window. Under Fill select Picture or texture fill Select Stack and scale with Enter a 1 for the units/picture. A 1 will display 1 image for every unit on the graph. If you enter a value less than 1, more than one image will be displayed for every unit. Click on the Close button at the bottom right corner of the window. Select Fill with Picture or Texture


    Your chart should now show 1 dog for each unit on the vertical axis. Bar Chart using stacked images of a dog


  4. We will do something similar for the cats.
    Click on one of the columns that represent the cat values. This should select both columns.
    This time instead of bringing up the clipboard right-click on one of the selected columns.
    Select Format Data Series.
    In the Series Options pane select Fill.
    In the right pane under Fill select Picture or Texture Fill.
    Select Stack and Scale with. The textbox should contain a 1 for units/picture.
    Under Insert from: click on the Clip Art… button.


    This brings up the Select Picture window. It is similar to the clipboard but it has a disadvantage in that you can’t select the type of media such as when we selected only illustrations from the clipboard. Let’s narrow the search by entering Black Cat instead of just Cat. Click on the Go button. Click on the Black Cat as shown here then click on the OK button. Search Clip Art for Black Cat


    Click on the Close button for the Format Data Series window.

    Your chart should now look similar to this.  Pictograph Chart for Cats and Dogs


    Expand the size of the Pictograph Chart
    Increase the size of the chart by moving your cursor to the bottom left corner of the outer chart area, when the cursor turns into a double arrow, hold down the shift key and drag it down and left. Since you have the shift key held down the proportions of the chart will remain the same.

    Under the Chart Tools group, click on the Layout tab. In the labels group click on the Chart Title button then select Above Chart. Select Above Chart


    Triple-Click on the Chart Title text that is displayed above the chart. Key Grooming for July and August. Right-click on the Chart Title button then select Format Chart Title. This brings up the Format Chart Title window.
    Click on Fill in the left pane. Under Fill in the right pane select Gradient fill. Select Gradient Fill


    In the Gradient stops area click on the color bar with the three stops on it. Click on the left most stop then click on the color button. Select the fifth color (Green, Accent 1, Lighter 40%) in the fourth row of the Theme Colors. The Position should be 0%. Click on the middle stop then click on the color button. Select the color(Green, Accent 1, Lighter 60%). The Position should be 50%. Click on the right stop then click on the color button. Select the third color(Green, Accent 1, Lighter 80. The Position should be 100%. Practice sliding the stops back and forth to see what affect that have on the color. Click on the Add Gradient Stop button Add Graident Stop button. Try changing the color for the new stop. When you have finished checking how it works click on the Remove Gradient Stop button Remove Gradient Stop Button. Use Gradient Color Stops


    Let’s give the title a 3-D look by adding a shadow below it. Click on Shadow in the left pane. Click on the Presets button. Select the second object in the first row in the Outer area. Set the Distance to 7pt. Click on the Close button. Shadow 3D button


    Right-click on the legend then select Font. Change the Font size to 20. Click on the OK button. Note: There is no direct way to increase the size of the Legend key. The size of the Legend Key is determined by the Font size. Your Chart should now look similar to the chart below.

    Excel Pictograph Chart for Cats and Dogs

Exercise 2 for Pictograph Chart - Award Banners

In this exercise, we use multiple images for each unit in a Pictograph. This Pictograph will only use a single image. Students are assigned ribbons on the chart to represent that they have finished all of their school work on time.

  1. Create a new worksheet named Pictograph2 that looks like the one below.

    2. Excel Pictograph Banner Chart

  2. The total number of times that each student has cleaned their area needs to be totaled. Select the cell range B3:Q7. Click on the Autosum button.
  3. Select cells A3:A7 then hold your Ctrl key and select cells Q3:Q7.
    On the ribbon, on the Insert Tab, in the Charts group, click on the Bar button.
    Select the first button in the 2-D Bar group.
    Your Chart should now look similar to the one below.

    This table only has one Series. Right-click on any one of the bars then select Format Data Series. Series 1 for Pictograph Chart


    1. Click on Fill in the left pane.
    2. Select Picture or texture fill.
    3. Under Insert From click on the Clip Art button.
    4. Type first prize blue ribbon in the Search text box.
    5. Click on the Go button.
    6. Click on the first image
    7. Click on the OK button.
    8. Select Stack and Scale with.
    9. Type .8 for the units. This will put more than one image for each unit.
    		 Select Fill with Picture Enter search text for Select Picture dialog box


    Click on Series Options in the left pane.
    Change the Gap Width to 42%. This decreases the distance between the images from one student to another. It also makes the images larger.
    Click on the Close button.     		Select Series Options from Format Data Series dailog box


  4. Click on the Chart legend. Press the Delete key.
  5. Drag the top left corner of the Chart to cell A9.
    Move your cursor to the bottom right corner of the chart until your cursor turns into a double arrow. Hold down the shift key and Drag to the bottom right until you have stretched the chart out to row 33.
    Right-click on one of the ribbons in the chart then select Add Data Labels. This puts the total for each student to the right of their bar.

    Pictograph chart with data