How to Create a Pictograph in Excel using 5 Simple Steps

HomeFormulasHow to Create a Pictograph in Excel using 5 Simple Steps

If you know how to present data in an understandable and effective way, you can deliver your message strongly. For this purpose, one of my favorite things is a PICTOGRAPH. And in Excel, creating a pictograph is quite simple and easy.

how to make a pictograph in excel using bar chart

Today in this post, I’d like to share with you a simple step by step process to create a pictograph in Excel. Here my idea is to present the number of employees from various age groups in a company.

Now without any further ado, let’s get started.

Simple Steps to Create a Pictograph in Excel

We have to use below data for this chart, you can download it from here to follow along.  As I said it’s the total employees in a company age wise and we need t present these groups using this chart.

data to create a simple pictograph in excel

But before we start, we need an icon to use in this chart and you can download it from a free icon site.

  • First of all, just select your data to create a bar chart. For this, go to Insert ➜ Charts ➜ 2-D Clustered Bar Chart.
    insert-a-clustered-bar-chart-to-create-a-pictograph-in-excel
  • After that, select the data bars in the chart and go to Right Click ➜ Format Data Series Option.
    go-to-data-series-option-to-create-a-pictograpgh-in-excel
  • From here, go to “Fill” section and select “Picture or Texture Fill” option and once you do this, you’ll get an insert picture option.
    go-fill-section-to-add-picture-to-a-pictograph-in-excel
  • Now it’s time to insert the picture and we have three options for this. Click on the “File” option and upload the image you have downloaded.
  • Once you insert the image, you have further three options to show that picture in the chart. [Select the third option]
  1. Stretch – A single image will stretch in data bar.
  2. Stack – You will get stacked images in the data bar.
  3. Stack and Scale with – Images will be shown as per data.

Boom! our chart is ready to rock.

ready to use simple pictograph in excel

Note: There is an option to specify unit/picture for the chart. You can use it if the values per bar in your chart are large (more than 20). The thing is that the picture icons will become smaller with more numbers and this option helps in that case.

use unit per picture to stream line image in excel pictograph

You can also use a different picture for different bars, just like I have used in the below chart.

using different pictures in pictograph in excel

Select each data bar separately and insert a picture one by one for all the bars.

Dynamic Pictograph in Excel

I always love to create interactive charts and for this time I want to convert this PICTOGRAPH into a chart in which we can use data in an dynamic way. Just like below.

our ready to use dynamic pictograph in excel

Look at the below data where we have same company’s employees data, but department wise and we need to create a dynamic chart here which we can operate with option button.

data to create a dynamic pictograph in excel

You can download this file from here to follow along and now use below steps to create a dynamic pictograph.

  • First of all, create a different table with two columns, just like I have below.
    create-a-new-small-table-to-create-a-dynamic-pictograph-in-excel
  • Now, insert four option buttons in your worksheet. For this go to Developer Tab ➜ Controls ➜ Insert ➜ Option Button.
  • After that, connect those option buttons with a cell. In my case I’m connecting them with cell A8.
    connect-option-buttons-with-a-cell-to-control-dynamic-pictograph
  • Since we need to control data (department wise) with option buttons make sure to name all the buttons according to the department name.
  • From here, insert below formula into the first cell (heading) to last cell of the second column of the new table. 

=VLOOKUP(A9,A1:E6,$A$8+1,0)


insert-formula-to-create-a-dynamic-table-for-pictograph

  • At this point, we have a dynamic table where we can get data by using option buttons.
    now-dynamic-table-is-ready-to-create-a-pictograph-in-excel.gif
  • In the end, select this table and insert a bar chart and convert it into a pictograph using the steps you have learned.

Hurry! our dynamic pictograph is here.

our ready to use dynamic pictograph in excel

Sample Files

Download this sample file from here to learn more.

Conclusion

Pictograph is one of my list of the advanced Excel charts.  The best thing which I like about this chart is we can use any kind of picture in it, there is no limitation about it.

And you don’t need to worry about the picture you have added, it remains there in the clip-board. I’m sure this chart will help you to present your data in a better way.

And now tell one thing. Have you ever tried to create a chart like this before? Please share your views with me in the comment section, I’d love to hear from you. And, don’t forget to share this tip with your friends, I’m sure they will appreciate it. 

More Charting Tips and Tutorials

 

17 thoughts

Leave a Comment

Your email address will not be published.

  1. Thanks a lot sir. Its a very good representation technique but can it be exported to PowerPoint such that you dont have to use keypads or mouse to click but automatically happens when using pointer?

    Reply
  2. Puneet , it’s my first time visit to your page , i can see passion in presenting the knowledge to others , I really enjoyed exploring and watching your words

    Reply
  3. Very nice one Puneet. I have a small doubt while creating these option buttons, do I need to create each option button separately?

    Reply
  4. the ‘Download this sample file from here link’ does not appear to be working. Please help I am really looking forward to experimenting with this chart it looks awesome.
    Thank you

    Reply
  5. Sir, Thanks for your post. but i am struggle with option box connect with cell . finally i refer website and make it clear. Please do mention in this post.

    Reply
  6. Hey Puneet, How can i make graph like the one showing at start of topic at this page “Employee Structure Age Wise”?

    Reply