MVP #28: How To Make A Professional And Dynamic Excel Chart

Today's your lucky day. I'm going to up your Excel skills dramatically in this single post. You'll almost hug me. And for the superbusy ones, here's the actual Excel file with the chart made.

In the end, you'll be able to make the Chart above. And with the added functionality to change State dynamically.

So let's select Kaduna state.

Voila! The entire chart has changed to Kaduna's without me redoing the chart. 

So how do you make a chart like this? It's amazingly easy. And I will be holding your hands throughout (hence, the numerous screenshots you'll see).

So here's the original table with the data I used.

You can create yours or copy the one in the file I gave you the download link. 

So let's begin the simple task before us.
From scratch this is what you'll do. No color formatting for now.

Same as this:

I like to remove Gridlines (those background lines in Excel). Here's how to do that. Go to View tab and untick Gridlines.

Now that we've got the skeleton in place.
Let's prepare the chart part. 

Click on the cell below State. And goto Data tab ==> Data Validation ==> Data Validation

A dialog box comes up.

Change the Allow box from Any value to List
And select the states in the Source box. In the end, you should have something similar to this:

Great! We are almost done.

You've just added the ability to select state. 
Check it out. Whoever sees this will be wowed.

Now let's populate the Men, Women, Children and Cars. Dynamically too.

We'll use a formula called VLOOKUP It's super easy to use and understand.
It simply checks takes the selected state, look it up in the big table and return whatever cell you want on that state row.

Very easy. Done for Men.
Let's do for Women, Children and Cars

Cool.

Let's now insert a Chart. 
Select the small table.

Goto Insert tab and insert a chart.

We'll use a 3-D chart. It's beautiful.
And here's what you'll get.

Let's do my favorite formatting.
Remove the Legend (that stuff at the extreme right showing a small blue rectangle and "Rivers" beside it). Under Layout tab.
Show Data Table below chart. Also under Layout tab.
Choose a cool background. Under Format tab.

If you can't see the Layout and Format tab, click on the Chart you just inserted, they will appear.

Let's change the color of the chart walls to white.
Right click on the space just above the cars' bar. Click on Format Walls. Change background color to White.
Then right click on the bottom space between the Children's bar and Cars' bar. Click on Format Floor.
Change background color to White.

And congratulations!
You're done. You've made a dynamic and professional chart.

Change the state and watch the chart change. You can now add color to the small table too.

You can forward your hug to my GTB account. I'll make sure I send you a personal thank you note.