How to make a empty diagram in Excel

Posted by Marcus Hammarberg on September 24, 2014
Stats
If I don't write this down I will forget it before the end of the day. That right there was the reason I started my blog, ca 900 posts ago.

A couple of days ago I was, again, creating a big diagram on a whiteboard. This particular one was pretty high and a lot of data points had to go into it. It turned out to be hard and messy to both update and read.
I have done this so many times that I've lost count. I never found a great solution.
I don't want to use a small A3 report because I want everyone to see it. And I don't want to print a big A1 sheet every day, because it's to expensive.
And I don't want to use a projector because this needs to be physical and tangible. And in front of everyone everyday.

Someone suggested to print an empty diagram in a big size and then laminate it with plastic to be able to enter the data on top of the basic diagram.
"Brilliant idea - let's try it. I'll create an empty diagram in Excel and then we print it!"
I heard myself say. And after ca 2 hours I felt the need to write this blog post
If I don't write this down I will forget it before the end of the day. That right there was the reason I started my blog, ca 900 posts ago.

A couple of days ago I was, again, creating a big diagram on a whiteboard. This particular one was pretty high and a lot of data points had to go into it. It turned out to be hard and messy to both update and read.
I have done this so many times that I've lost count. I never found a great solution.
I don't want to use a small A3 report because I want everyone to see it. And I don't want to print a big A1 sheet every day, because it's to expensive.
And I don't want to use a projector because this needs to be physical and tangible. And in front of everyone everyday.

Someone suggested to print an empty diagram in a big size and then laminate it with plastic to be able to enter the data on top of the basic diagram.
"Brilliant idea - let's try it. I'll create an empty diagram in Excel and then we print it!"
I heard myself say. And after ca 2 hours I felt the need to write this blog post
The crux of the matter is that Excel adjust the diagram to your data... And I wanted to create an empty diagram. Which looked a bit strange. But there's a little trick that makes this very easy.

Let's create an diagram like the one above; to track the number of customers per day and with two horizontal lines showing two important thresholds.

DISCLAIMER: I'm no Excel-guru. This was a pretty fast way, once I found it, to get this done. There's probably more correct ways of doing this.
  1. Open Excel (I'm using 2010 for Mac, in Swedish for some bisarr reason. Pardon my translations below) and create a new workbook 
  2. Add data like this:
    DateNumber of CustomerThreshold 1Threshold 2
    145100200
    2145100200
    380100200
     
  3. Keep doing the "dates" until date 31
  4. The data in the columns will determine the height of the Y-axis. The 200 in Threshold 2 will make sure the Y-axis goes to 200. I know that you can adjust this afterwards if you needed, but this is the quickest way. 
  5.  Select all data in the 3 last columns and then click Diagram->Line -> Line diagram which produce this ugly diagram:

  6. Now... that require some formatting:
  7. First let's add some extra helper lines that will prove useful when this is a big, on the wall diagram. 
    • Select the "Diagram layout"-tab and then Support lines and adds the ones you need. 
    • I added support lines for sub-scale for both X and Y axis
  8. Secondly let's add some nice text for the axis
    • Again select the "Diagram layout"-tab and choose Axis headings. 
    • Now you can add labels for the axis
  9. While we're at it, let's add a Diagram heading (Diagram layout-tab, then Diagram heading)
Once that is done you might have something that looks like this:
Now, let's make this an empty diagram, by not removing but hiding the "Number of customers" line. If you remove the line... the diagram collapses or expands depending on where you thresholds are set. 
But you can hide the line. Here's how:
  • Right click on the line and select "Format data series..."
  • Select "Line" and then "Color"
  • Now in the drop down for color you can select No line, which hides it
  • This leaves you with the line (that is now hidden) selected which might look a bit strange. But just click the diagram and you'll see the whole line go away. 
Finally we now have a nice empty diagram that looks like this:

What we did with this was to print it in 70x100 cm and laminated it in plastic. Now we can plot in the monthly stats and then erase it for each new month. We also thought about putting it behind glass but decided against it because that would make it much more bulky.

UPDATED: 
I got a question about the actual printing. I would advise against printing this on several sheets of paper and taping or gluing them together. It will not be straight and probably will mess up your drawing a bit, in my experience.
Instead, go to a copy shop and ask them to do this for you. Probably sets you back $4 or so. Well worth it I think. If you're Sweden go to Arkitektkopia - they are great. And if you in Södertälje go to this office and you'll get awesome treatment too. Mention my name for a cup of coffee. I'm good for it (my first job!)

I know that I will find this useful later. I hope that you will too.

Oh yeah, here's my workbook so you don't have to start from scratch. 


Published by Marcus Hammarberg on Last updated