A few years ago I came across a graph in Excel that had no data linked to it. There were curves on the graph but no table or list of data anywhere on any work sheet. I wondered how that might have happened and did a bit of research.

Secret Graph

What I found was that we can set up a completely blank graph (chart if you prefer) by clicking on any empty cell on any work sheet and selecting Insert XY Scatter or any other type of graph … and your blank graph will have been created like this:

excel_profs-0005

Now you can create a series that you build into the graph but not into the work sheet, as you can see in the following image:

excel_profs-0004

Select the graph and type in the formula bar the following:

=SERIES(“Sales”,{1,2,3,4,5,6},{100,102,105,100,99,98},1)

That tells Excel that series 1 is called Sales, the X axis values are 1,2,3,4,5,6 and the Y axis values are 100,102,105 …

Press Enter now and you will see your magic chart:

excel_profs-0003

Click on an point on your sales data and you will see the series in the formula bar …

data_series_graph_one … it’s the ,1 at the end of the series that tells us it’s series 1

Click on the edge of the graph again and you will find that the formula bar is empty again so now type in the following:

excel_profs-0002

This is series 2, Costs with the same X axis values as Sales and the costs for the Sales you have already entered. Press enter and you will see your revised graph:

excel_profs-0001

Add a chart title, axis labels and so on now and here you are, a secret graph! I made mine look rather smart:

excel_profs-0006

Graph Your Name

Having learned the above, it dawned on me quite a while later that I could use that knowledge to put just about anything I wanted on a graph and that’s what I started to do! I learned to put my name on a graph … let’s begin with something really simple to start with the establish the principles.

  • Imagine for some reason I want a graph that shows a square in the bottom left hand corner …
  • Imagine then that I want to draw a face on the graph … I know … why do that???
  • Imagine finally that I want to write my name on a graph …

Graph with a Square

Start by creating a matrix that includes your square … you don’t NEED to do this but it helps a great deal. Just put it somewhere and use it for reference. Once you have drawn your graph you can delete this matrix:

2016-06-19_14-43-12

This matrix tells you how to set up the SERIES in your empty graph. In this way

2016-06-19_14-43-40

The matrix mimics the graph and the dots mimic the data points. All we do is create the empty graph and in the formula bar type what you see in the formula bar graphic above. What does it mean? It means

On the X axis point 1 contains 5 values, point 2 contains 2 values … and the values are, on the Y axis:

1,2,3,4,5 for point 1

1,5 for point 2 and 3 and 4

1,2,3,4,5 for point 5

This is series 1 and I have called it Box

Make sure you understand this and then try the following which IS a bit more complex and could be answered in at least two ways.

Graph with a Face

Here is the matrix for this one

2016-06-19_14-50-56

Here is the series that this converts into:

2016-06-19_14-58-22

And finally the graph:

2016-06-19_14-58-41

It really doesn’t take long to do this once you’ve got the hang of it. Why you might do it is another thing, of course!

Here is the second method I mentioned: this time the glasses, nose and mouth are different colours and sizes … I could have changed the marker as well but didn’t this time!

multi_colour_face

How did I do that? Any ideas?

The answer is to split the image into three in this case, like this:

excel_profs-0007 excel_profs-0008 excel_profs-0009

Now, this is in danger of getting repetitive so I will illustrate how to get your name onto a graph by showing you a matrix of just part of a name:

duncan_part_matrix

You can guess how to finish that … put your own name there! Notice I have used different symbols for each letter which means that each letter has to have its own series … don’t do that if it’s too much bother … or if the series gets too long to manage!

In this example, I put the D and the u in the same series but then I formatted the graph so that it doesn’t necessarily look like a graph at all:

duncan_part_graph

There you go … feel free to finish my name graph!

If you want to make your graph look even smarter, make your matrix a lot bigger so you can add more data points and make curves smooth and so on!

Download my spreadsheet from here … empty_graph

 

Duncan Williamson

%d bloggers like this: