How-to Make a Beer Mug Goal Chart as an Excel Dashboard Component

Time for some beer and Excel.  Last month I saw that a User was wondering how to make Beer Mug Goal Chart for their company in order to track progress toward a celebration.  Today, I am going to show you how to make your own Beer Goal Chart (maybe I should have called it a beer goggle chart Smile) that can be used as an Excel Dashboard component or maybe just as a company status chart or a fun challenge chart.  Whatever you want to use it for, you can use this technique to make many infographic type charts in Excel.

Here is what the chart can do all with standard functionality.  Here the chart with 10%, 50%, 90% and 115% (notice that the chart at 115% shows foam above the beer.  Pretty cool, huh?)

imageimageimageimage

Looks real enough for what we need to do.  Now let’s get to it and see how we can make this custom infographic beer chart that will update itself without the need to draw anything when you change the values.  All we need to do is use standard Excel charts.  Enough talk, get to making the chart already!

 

The Breakdown

1) Find image graphic for Beer Mug, one for the Beer and one for the Foam

2) Insert graphics into Excel Beer Mug Goal spreadsheet

3) Copy and crop mug into 2 graphics, and crop beer and foam graphics

4) Create a chart data range and beer level cell in the spreadsheet

5) Create Excel 2D Clustered Column chart

6) Copy and paste beer much goal chart graphics into 2D Clustered Column chart

7) Move Beer and Beer position series to the Secondary Axis

8) Change the Current Beer Level Chart Type to 2-D Clustered Column

9) Change Current Beer Level Gap Width and Series Overlap

10) Clean up the chart junk

11) Change primary vertical axis minimum, maximum and number format

 

Step-by-Step

1) Find image graphic for Beer Mug, one for the Beer and one for the Foam

First thing we need to do is to find a graphic that we can use in our Excel Chart.  We are looking for several graphics.  One, an empty beer mug, two, a mug with beer, and three, a mug of beer with foam on the top.

If you cannot find these pictures in Excel or in Clip Art, I suggest looking for images that are available and licensed for use on the web.  If you can’t find an image that are licensed for use, then you should visit some of the large stock photo sites and purchase images.

Here are the photos I used for the empty mug of beer:image

Here is the photos that I used for the beer graphics:image

Here is the photos that I used for the beer foam:image

Once you have a few pictures saved to your computer, now you can start the process.

 

2) Insert graphics into Excel Beer Mug Goal spreadsheet

First we need to insert the beer graphics into your spreadsheet.  You can do this by going to the Insert ribbon and then choose Picture or Clip Art.  image

From there find your image on your computer and then press the insert button on the dialog box.

 

3) Copy and crop mug into 2 graphics, and crop beer and foam graphics

Okay, now each of our pictures are not exactly what we want.  So you need to crop the pictures in order for them to work the way we wanted them.  So we will need to make a copy of one and then crop each of them.

First things first, we need to make a copy of the empty mug.  Simply click on the empty mug, then press Ctrl+C and then Press Ctrl+V and you should now have 2 empty mug graphics.

Next we need to crop each picture.  To crop a picture, first select the picture and go to the Format menu and choose the Crop button from the Size group.  Your picture should now have black crop selectors showing in the picture like you see below.image

You will need to crop each of the pictures into the following graphics:

a) Empty Glass from the base of the inside of the glass to the BOTTOM of the mug:image

b) Empty Glass from the base of the inside of the glass to the TOP of the mug:image

If you need to know if you have cropped them right, simply put both the top and the bottom together in the Excel Spreadsheet and see if they line up.  I have moved them slightly off so that you can see that they are actually 2 different pictures.  If I didn’t, you would swear that it is just one picture and not two in the spreadsheet.image

c) Beer only in the form of a long rectangle:image

d) Beer Foam only:

image

We now have our graphics ready and can start building our chart.

 

4) Create a chart data range and beer level cell in the spreadsheet

One of the keys to this Excel Chart tip and trick is to set up our data in the right way.

We need to setup our data for the chart and also a cell that will control the beer (goal) level for the dashboard graphic.  It will look like this:image

Lets review each of the cells

The lower mug of –20% will be for the bottom of the mug graphic that we cropped above.

The upper mug of 101% will be for the top of the mug graphic that we cropped in the above step

The Beer positions of 0% are how we will position the graphics so that the beer and the beer mug are in the same space.  Without these the beer and the mug would be floating in different spaces.

The Current Beer Level of 100% is a formula that looks at our yellow spreadsheet cell of E1 and if it is equal to or greater than 100%, then limit it to 100% or else just use the value in cell E1.

B5  =IF(ISNUMBER(E1),IF(E1>1,1,E1),0)

The Beer Foam series is how much above and beyond the goal of 100% the company has attained.  If you see foam, you know it will be a real party because we have exceeded our goals.  The formula for this cell will be equal to the difference of cell E1 minus cell B5 that is above 100%

B7 =IF(B5=1,E1-B5,0)

Make sure you set cell E1 equal to the current beer achievement level.  This is the only cell you need to change.

 

5) Create Excel 2D Clustered Column chart

We can now make our Excel Beer Graph.  Highlight cells A1:B7 and then go to the Insert ribbon and  image

choose the 2-D Clustered Column Chartimage

Your chart should now look like this:image

Not quite what we want, so we need to press the Switch Rows/Columns button on the Design ribbon.  Your chart should now look like this:image

Check out this post if you don’t know:

Why Does Excel Switch Rows/Columns in My Chart?

 

6) Copy and paste beer much goal chart graphics into 2D Clustered Column chart

Okay, we are really close, now lets add our beer graphics.

First select your Upper Empty Beer Mug and Press Ctrl+C to copy it.

image

Then select your chart and then select the Upper Mug and press Ctrl+V to paste it.image

Repeat this step for the Empty Bottom Beer Mug graphic by selecting it and pressing Ctrl+C to copy.

image

Then select the Lower Mug data series in the Excel Company Goal Chart.image

Now we need to copy and paste the Beer to the Current Beer Level series.  image

Following the same steps your chart will look like this:image

Finally, we need to repeat this for the Beer Foam Excel Chart Data Series.  Select your foam graphic and press Ctrl+C to copy it.

image

Then select the Beer Foam in the Excel Graph and press Ctrl+V to paste it:image

Looks close, but why is the beer floating above the beer mug?  Because we need to move the beer and foam to the secondary axis.

 

7) Move Beer and Beer position series to the Secondary Axis

We need to move the following series to the secondary axis.  You do this by selecting the series you want to move to the secondary Excel horizontal axis and then press Ctrl+1 to bring up the Format Data Series dialog box.  Then choose Secondary Axis in the Plot Series On in the Series Options:image

You need to move all 3 of the Position Beer series and also the Current Beer Level.

Your chart should now look like this:image

If you are having problems selecting the Position Beer Series that are equal to Zero, check out this post:

How-to Select Data Series in an Excel Chart when they are Un-selectable?

8) Change the Current Beer Level Chart Type to 2-D Clustered Column

Since our beer mug has a handle, it throws the beer to the left of the graphic so we need to move our beer as well.  To do this, select the Current Beer Level series in the chart.  Then go to the Design ribbon and choose the Change Chart Type button in the Type group.  Then from the dialog box, select Column types and choose the 2D Clustered Column (NOT the Stacked Column)image

Your chart will now look like this:image

OOPS!!!   Looks like I poured the beer and missed the mug Sad smile  No worries, we can fix this.  We need to move the Current Beer Level Series either up or down above or below the Position Beer series.  In our case we need to move it up one level in our series order.  You can do this by selecting the chart, then go to the Design ribbon and press the Select Data button.image

You will then see the Select Data Source dialog box.  Select your Current Beer Level and then press the up arrow as you see below:SNAGHTML4eae7d8

Now are chart looks very close but will need a little more tweaking:image

9) Change Current Beer Level Gap Width and Series Overlap

Even though our beer mug is close, it is not perfect so we need to slide the beer over a little by making the following adjustments.  Click on the Current Beer Level Series and press Ctrl+1 to bring up the Format Data Series dialog box.  Then change the Gap Width to 180% and the Series Overlap to 45%.SNAGHTML4f6d1e8These numbers are not set in stone, so tweak them until the beer looks like it is in the empty mug.

Here is what mine looks like now:image

 

10) Clean up the chart junk

Things are looking too crazy, so lets clean things up before we finish.

Delete the Legend

Delete the Horizontal Axis (has the number 1 in the middle of the beer)

Delete the Secondary Vertical Axis

Your chart should now look like this:image

 

11) Change primary vertical axis minimum, maximum and number format

For the final step, lets adjust the Vertical Axis so that our graphics will match the Zero to 100% gridlines.

Right click on the Vertical Axis and Press Ctrl+1 and you will see the Format Axis dialog box with the Axis Options selected. Change your Minimum to –0.20 and change your Maximum to 1.20SNAGHTML4bcd541

Then select the Number options on the left and then select Custom from the Category choices and put this

0%;;0%

in the Format Code box and press the Add button:SNAGHTML4bddc33

If you want to learn more about custom number formats, check out these postings:

How-to Format Chart Axis for Thousands or Millions

Remove The Zero Point or Make a Hole in an Excel Radar Chart

And there you have it, here is our final chart.image

Change the numbers and see how the foam goes away and how it rises above the mug.imageimage

You can use this technique to create many different kinds of company goal charts.  And they update dynamically when you change the current level without drawing shapes or other objects.

Now if you want to see it in action, check out this Excel Beer Mug Company Goal Video Tutorial.

Video Tutorial

 

Free Excel Dashboard Chart Template Download File:

Goal-chart-using-clip-art-beer-mug-column-chart.xlsx

 

If you like what you see, the please follow this 2 part equation:

1) Please tell a friend.  Send them a link to this chart tutorial.

2) Please leave me a comment for other readers to know what you think.  Thanks!

Steve=True

24 COMMENTS

    • Hi Bhavik,

      I had a hard drive crash and will have to see if it is in the backup. Hopefully I can find and post for you.

      Thanks

      Steve=True

  1. I absolutely loved this! I am creating a dashboard tracking to daily sales targets and overall monthly targets. I thought it would be fun to add this on and wanted to use the logo of our company to show the levels….however, this only works if you use rectangular shapes right? Having circular or odd shaped clip art does not appear to work. Do you have any tricks to maneuver past those?

    • Hi CG, thanks for the awesome comment. There may be a way around it. Since using a logo may not be easily dicernable to the human eye when you are 0-5%, Can you break up the shape into 5% blocks? If you do this, you may not want to put the horizontal gridlines across the chart in case. Does that help?

      Steve=True

    • Hi Baz,

      I was successful!!

      I have added a link to the blog post where you can download the free dashboard template file on the updated blog post. Just search for Beer 🙂

      Steve=True

    • Hi CC,

      It gets tricky as the shape of the bottle is not standard and tapers at the top.

      But I am sure it can be done.

      I will add a post on this in the future.

      Steve=True

      • You may be able to compensate for the taper by adding a shape with the same color as the background over the chart to hide the column where it would fall outside of the bottles shape.

        • Hi Pete, I agree, you can stop the square pieces at the narrowing of the bottle, but it will take some doing. Challenging, but not impossible. Will be a future challenge 🙂

  2. Here’s an idea… Make a copy of the beer mug and set the transparency of that copy to 75% or so. Use ‘Send to Front’ etc so your images are layered as follows: Original mug (back), beer, (middle) and transparent mug (top). You can still see the beer but it looks more realistic!

    • Hi Christine, thanks for the great comment. We could make one, but you wouldn’t see the treasure until it flows over the top. How would we see the treasure if it was only half full? Let me know your thoughts and we can try and make it. Steve=True

  3. Hi Steve,

    Very curious as to how I can adjust this into numbers as opposed to a percentage. Having trouble, please let me know if you can help. Feel free to email me directly. Thanks!

LEAVE A REPLY

Please enter your comment!
Please enter your name here