Ads 468x60px

Thursday, August 29, 2013

5 simple rules for making awesome column charts


For every column chart that is done right, there are a dozen that get messed up. That is why lets talk about 5 simple rules for making awesome column charts.
Tip: Same rules apply for bar charts too.

Rule #1: Start at zero

The first rule is simple. Always start your column charts at zero. When looking at column (or bar) charts, our mind measures height of each column and compares. So, if a column starts at some arbitrary point instead of zero, it can mess with our perception of how each column compares with other. Don’t believe me. See yourself.
Start your column charts (or bar charts) at zero so that there is no confusion when reading the chart.
Related: What is the most embarrassing charting mistake you made?

Rule #2: Thou shall sort

Sort your columns in a meaningful order. For example, sort them by descending order (of column heights), alphabetical order or chronological order. This will make reading the chart easy.
Sort your chart values in a meaningful order to make it easy to read.

Rule #3: Slap a title on it

Give your chart a meaningful, clear title. Few examples of good and bad titles shown below.
Give your charts meaningful titles so that they communicate better.
Related: Using smart titles & legends in your charts

Rule #4: Axis & Grid-lines vs. Labels

For most charts you can use data labels instead of axis & grid-lines. This will keep the chart clean.
If you choose to go with Axis and gridlines, then make sure they follow below guidelines.
  • Axis label text should be relatively small & dull.
  • Grid lines & axis line should be dull too.
  • Do not display too many or too little major units on axis. You can change major unit size by selecting axis and pressing CTRL+1 (or axis options pane in Excel 2013).
Try to use labels instead of axis / grid lines. This will clean up your charts a bit.

Rule #5: Too much lipstick and you have a pig

Make sure the formatting (colors, fonts, special effects, backgrounds etc.) of your chart are really subtle and meaningful. If you use too many colors, you end up with a pig. People will then focus on all these colors, fonts instead of actual data.
Always go with simple and easy formatting. Too much formatting leads to gaudy, shallow charts
Few ways to add wow factor to your chart without messing it up:
So there you go. Follow these rules and your column charts will stand tall.

Share your rules for making awesome column charts

While above rules capture the gist of making good looking column charts, there is more to learn and follow. So go ahead and share your rules and tips using comments. Teach us how you make stunning column charts (or bar charts). Post your comments below.

Make charts often? Check out these tips:

If your job involves analyzing & charting data, then check out below tips to learn more.

Monday, August 26, 2013

Sort by Birthday [Quick tip]


Sorting dates on day and month alone - Excel tipsLets start the week with a quick tip.
Lets say you have a list of employees and their birthdays. Now you want to sort this list, based on their birthday, not age.How would you do it?
Sorting by day and month alone:
  1. Add a column next to original dates. Lets call this Birthday.
  2. Then, calculate birthday in current year for everyone.
  3. Assuming DOB is in B1, Formula for birthday (in current year) would be,=DATE(YEAR(TODAY()),MONTH(B2),DAY(B2))
  4. This formula gives you a date which has same year as TODAY(), same month & day as original date.
  5. Then, fill down the formula for all rows.
  6. Now sort this new column (Birthday) in chronological order.
  7. You are done!
Employee table sorted on birthday - Excel tips
Note: if you are using tables, then use this formula.
(Assuming original date is in DOB column),
=DATE(YEAR(TODAY()), MONTH([@DOB]),DAY([@DOB]))
Related: Introduction to Tables & Structural References.
More Sorting Examples:
Homework for you:
If you think sorting by birthdays is easier than eating a birthday cake, then I have a challenge for you. Assuming a list of data of births is in the range A1:A100, write a formula to find how many birthdays are in this month?

Friday, August 16, 2013

Last day for enrollments – Join our Power Pivot class & become awesome analyst


Hi folks,
I have a quick announcement & a creative dashboard to share with you. First the announcement.

Only few hours left to join our Power Pivot course…

As you may know, I have opened enrollments for our 2nd batch of Power Pivot course few days ago. The aim of this course is to make you awesome in Excel, Advanced Excel, Dashboards, Power Pivot & Advanced Power Pivot.
We will be closing the doors of this program at midnight, today (11:59 PM, Pacific time, Friday, 16th of August).
If you want to join us, click here and enroll now.

How many people have joined the class?

At the time of writing this, we have 230 students enrolled in Power Pivot class. We are eager to share Power Pivot knowledge & techniques to as many more of you as possible. So go ahead and join us because you want to be awesome in Excel & Power Pivot. See an interesting dashboard on this further down this announcement.

5 Important things about the Power Pivot course

Hurry up, Enrollments for Power Pivot online classes closing in few hours - Join now1. This course is self-paced
That means, you can access Power Pivot & Excel lessons from anywhere at any time. You can pause, repeat or skip lessons as per your needs.
2. You will get a course completion certificate
At the end of the course, you will get a e-Certificate showing that you have completed online course on Power Pivot. Use it to show off or claim reimbursement from your boss.
3. You need Excel 2010 or Excel 2013 Pro Plus
To enjoy our Power Pivot classes, you need either Excel 2010 (any edition) or Excel 2013 Professional Plus or Office 365 Professional Plus Editions.
However, to enjoy Excel School alone, you just need Excel 2007 or above.
4. Guest lessons by Excel celebs around the world.
If Excel world has a Hollywood, then our guest faculty would be like Tom Cruise, Ashton Kutcher or Beyonce. I am honored and thrilled to present Bill Jelen, Debra Dalgleish, Zack Barresse, Kasper de Jonge, Miguel Escobar, Denny Lee, Ken Puls and others in our online classroom. Learning from these gurus is going to be legendary.
5. Special Bonuses
I have struck a deal with Rob Collie’s publishers to bring you an e-book edition of DAX formulas for Power Pivot Analysts –the definitive guide on Power Pivot measures & calculated fields. You will be able to download your copy once you join either Power Pivot class (download link will be available after we discuss DAX formulas in the class)
Apart from this, there are more bonuses – special modules on Excel, SQL & Dashboards, cheat sheet on common DAX formulas and so much more…

When will be the next batch of Power Pivot course?

As this is the first time we are running advanced power pivot course, Rob & I will be spending a lot of time fine tuning the lessons, answering student doubts & thinking up creative ways to use Power Pivot.
I am hoping to re-open Power Pivot class again in early 2014. But we may delay it if needed.
So sign-up already.

How much time I’ve got? – Count down timer using Power Pivot

We are going to close the enrollment window at today midnight (11:59 PM, Pacific Time – Friday 16th August).
So go ahead and join us before its too late.
Click here to sign-up.

Creative dashboard on Enrollment stats

As a token of thanks for all your support, here is a dashboard I made (with Excel ofcourse). It shows how many enrollments we had to this class.
(note about the data: Country is not captured for all sales due to limitation of our shopping cart software. Sales from certain countries excluded as they go thru another system).

This is how the dashboard looks like:

Power Pivot enrollments from around the world - a dashboard

How does the dashboard work?

The basic techniques used in this dashboard are,
Watch below video (16 mins) to understand how this is put together.

Download the dashboard workbook

Click here to download this dashboard workbook. Play with it to understand how it works.
And remember, enrollments for Power Pivot classes are closing tonight (at 11:59 PM, Pacific time). So go ahead and enroll if you want to become Awesome in Excel.

Thank you…

Rob & I thank you so much for your overwhelming support to this class. Your enthusiasm to learn, use and master new techniques, your kindness and your passion for Excel are truly remarkable. You inspire me everyday to learn new things and share new ideas. Thank you.
PS: Go ahead and join the class because you want to be awesome.

Monday, August 12, 2013

How to find the lowest value? [Quick tip]

Lets say you are the head of purchasing department at Big Corp Co.
You are obviously very busy. Every day starting with a large cup of coffee and ends with a big smile, as you save your company thousands of $s by negotiating best deals, finding best providers and being awesome.
Today, let me share a small Excel tip with you that will make you even more awesome.
Finding a provider with lowest value:
Lets say you are looking at a table like below and want to find-out lowest priced providers for each service.
Finding provider with lowest value - using Excel formulas
To find providers with lowest value:
  1. Find the least amount for each service. Assuming the services are in the range C5:G5, use =MIN(C5:G5) to get this.
  2. Give a name to list of providers. I call mine as providers
  3. Using INDEX, MATCH formulas find the provider name with lowest amount. Like this:
    =INDEX(providers, MATCH(minimum_value, C5:G5, 0))
  4. Bingo. You have the answer.
Bonus tip #1: Highlighting lowest values.
If you just want to highlight the lowest values, use conditional formatting.
  1. Select first row of numbers.
  2. Go to Home > Conditional Formatting > Top / Bottom rules > Bottom 10 items
  3. Set to Bottom 1 and specify formatting as you want.
  4. Using format painter, copy the conditional formatting, one row at a time.
  5. Done!
Highlighting lowest value using conditional formatting
Bonus tip #2: Handling Ties
Often 2 or more providers will tie for the bottom spot. What then?
One way to handle the ties is to show the word ties when 2 or more names have lowest value. To do this, use this formula instead.

=IF(COUNTIF(C5:G5, minimum_value)>1,"Ties", INDEX(providers,MATCH(minimum_value,C5:G5,0)))

A formula challenge for you…
Now that you know how to find the lowest value, here is a challenge for you.
  • How do you write a formula to find which provider has maximum lowest values. In this example, the name we are looking for is TATA as they have 3 lowest values.
Want to find more… look here:
If you want to find more Excel formula tips and techniques, look no further. Start your journey with this and see how deep your formulas can nest.

Tuesday, August 6, 2013

How to create a Then vs. Now interactive chart in Excel?

Posted: 06 Aug 2013 01:19 AM PDT
You have been there before.
Trying to compare last year numbers with this year, or last quarter with this quarter.
Today, let us learn how to create an interactive to chart to understand then vs. now.

Demo of Then vs. Now interactive chart

First, take a look the completed chart below. This is what you will be creating.
Then vs. Now interactive chart - How to create this in Excel

Inspiration for this chart

Before we jump in to Excel and understand how this is done, let me thank NY Times for providing the inspiration for this chart. I saw a similar chart in their climbing income ladder visualization.

Creating Then vs. Now chart in Excel

1. Arrange data

As usual, the first step is to get the data in to Excel. Structure your data like this.
Data for Then vs. Now chart

2. Insert a combo box control to select a region

Insering combo box form control and formatting it - then vs now chartSince our chart will display values for one region at a time, we need a mechanism to let user control which region is displayed. We will use a combo box control do this. Follow these steps.
  1. Go to developer ribbon and insert combo box form control.
  2. Right click on the combo box and go to format control.
  3. Set up input range to list of regions in your data.
  4. Set up cell link to a blank cell in your workbook.
Related: Introduction to form controls.

3. Fetch selected region’s data

Now that we have a combo box to select which region to show in the chart, next step is to fetch data for selected region. You can use either VLOOKUP or INDEX formulas to do it.
Using VLOOKUP formula:
Assuming region name is in D17, and data is in values table, write:
=VLOOKUP(D17, values, 2, false)
to get 2nd column (then sales) value.
More on using VLOOKUP formula
Using INDEX formula:
Assuming region number is in D16, and data is in values table, write:
=INDEX(values[then],D16)

4. Create a chart showing then to now movement

Next step is to create a chart that would show a line going from then value to now value. Lets take a closer look the line to understand how to make it in Excel.
Examining then vs now chart - a closer look at how to create it
We can create this chart with either XY (scatter) plot or line chart. Lets go with scatter plot.
In your workbook, set up a table like this:
Then vs. now values for selected region
Then, select the above and create a scatter plot. Select the scatter plot with connecting lines.

5. Formatting the chart

Since we want to show a thick circle at the beginning of then value and arrow at the end of now value, lets go ahead and do the formatting song and dance.
Formatting starting point of then vs now chartFormatting the first point:
  1. Select the first point of then values (you need to click once on it, take 3 deep breaths, click again and sacrifice a goat).
  2. Press CTRL+1 to format the data point.
  3. Go to Marker options and select built in marker and use the circle symbol.
Formatting ending point - then vs. now chartFormatting the last point:
  1. Select the last point (same as above, but this time sacrifice a chicken)
  2. Format the data point.
  3. Go to line style, select End type and choose arrow.
Formatting horizontal axi - then vs now chartFormatting the horizontal axis:
  1. Select horizontal (x) axis and press CTRL+1
  2. Set axis minimum to 1, maximum to 6.
  3. Click ok and delete the axis as we do not need it on the chart.

6. Adding “Break-up” of now values chart

This is easy, Just select fetched break-up values for selected region and create a bar chart. Format it as per your fancy.

7. Put everything together

Place the combo box, scatter plot and bar chart together in a nice fashion. Add a surrounding box shape so that everything looks like one report.
Add a descriptive title on the top. If possible, make chart title dynamic so that you can show the selected region name and % change in it.

8. Your Then vs. Now chart is ready

That is all. Your Then vs. Now chart is ready. Go ahead and flaunt it.
Final Then vs. Now chart with all bells and whistles

Download the chart workbook

Click here to download the chart workbook and play with it. Examine the formulas, chart settings and shapes to understand how this is set up.

Do you make then vs. now charts?

I think about half the charts made businesses around the world fall in to this category. I make these type of charts all the time. I use a variety of chart types to convey this information. Thermometer chartwaterfall chart and conditionally formatted tables are some of my favorite techniques.
What about you? Do you create then vs. now charts? what type of charts do you use? Please share your techniques and ideas using comments.

Learn more…

If you are not working in a cave or behind a huge stack of desks, chances are your job involves communicating for a living. Go ahead and read-up below articles to learn how to communicate with charts better, when it comes to then vs. now situations.