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. 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. Rule #3: Slap a title on it Give your chart a meaningful, clear title. Few examples of good and bad titles shown below. 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). 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. Few ways to add wow factor to your chart without messing it up: Highlight a particular column (for example max value, min value etc.) using different series technique. Use descriptive titles, clever data labels to show more information. Use drawing symbols or shapes to enhance the message of chart. Make your chart interactive to give users control. 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. 5 more rules to make better charts How to select right type of chart for any situation? 14 skills you need as a chart maker Never use simple numbers in your dashboards More charting principles & examples.

Sort by Birthday [Quick tip]

Lets 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: Add a column next to original dates. Lets call this Birthday. Then, calculate birthday in current year for everyone. Assuming DOB is in B1, Formula for birthday (in current year) would be,=DATE(YEAR(TODAY()),MONTH(B2),DAY(B2)) This formula gives you a date which has same year as TODAY(), same month & day as original date. Then, fill down the formula for all rows. Now sort this new column (Birthday) in chronological order. You are done! 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: Sorting sideways Sorting in Olympic medals table style, Formula 1 racing style Rounding and sorting data Checking if a list is sorted using formulas Sorting text values using formulas 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?

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 1. 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: How does the dashboard work? The basic techniques used in this dashboard are, A bubble chart with world outline map in the background (same as Olympic medal chart) Option buttons to select the course Conditional formatting to highlight selected course Text boxes to display statistics for selected course Tables, INDEX, MATCH, LARGE formulas in the background to do the magic. 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.

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. To find providers with lowest value: Find the least amount for each service. Assuming the services are in the range C5:G5, use =MIN(C5:G5) to get this. Give a name to list of providers. I call mine as providers Using INDEX, MATCH formulas find the provider name with lowest amount. Like this: =INDEX(providers, MATCH(minimum_value, C5:G5, 0)) Bingo. You have the answer. Bonus tip #1: Highlighting lowest values. If you just want to highlight the lowest values, use conditional formatting. Select first row of numbers. Go to Home > Conditional Formatting > Top / Bottom rules > Bottom 10 items Set to Bottom 1 and specify formatting as you want. Using format painter, copy the conditional formatting, one row at a time. Done! 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. Finding maximum value subject to conditions – MAXIF Finding average of top 5 values , Closest 2 values, weighted averages More quick tips & formula tutorials

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

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. 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. 2. Insert a combo box control to select a region Since 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. Go to developer ribbon and insert combo box form control. Right click on the combo box and go to format control. Set up input range to list of regions in your data. 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. 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, 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 the first point: Select the first point of then values (you need to click once on it, take 3 deep breaths, click again and sacrifice a goat). Press CTRL+1 to format the data point. Go to Marker options and select built in marker and use the circle symbol. Formatting the last point: Select the last point (same as above, but this time sacrifice a chicken) Format the data point. Go to line style, select End type and choose arrow. Formatting the horizontal axis: Select horizontal (x) axis and press CTRL+1 Set axis minimum to 1, maximum to 6. 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. 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 chart, waterfall 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. Budget vs. Actual charts – 14 alternatives (a variation of then vs. now) Us vs. Them – an interactive chart More Budget vs. actual charts How to make your charts interactive?