All articles with 'downloads' Tag
Hello all, prepare to be amazed! Here are 43 creative, fun & informative ways to visualize KPI data.
About a month ago, I asked you to visualize KPI data. We received 65 entries for this contest. After carefully reviewing the entries, our panel of judges have discarded 22 of them due to poor charting choices, errors or just plain data dumps. We are left with 43 amazing entries, each creatively analyzed the data and presented results in a powerful way.
How to read this post?
This is a fairly large post. If you are reading this in email or news-reader, it may not look properly. Click here to read it on chandoo.org.
- Each entry is shown in a box with the contestant’s name on top. Entries are shown in alphabetical order of contestant’s name.
- You can see a snapshot of the entry and more thumbnails below.
- The thumb-nails are click-able, so that you can enlarge and see the details.
- You can download the contest entry workbook, see & play with the files.
- You can read my comments at the bottom.
- At the bottom of this post, you can find a list of key charting & dashboard design techniques. Go thru them to learn how to create similar reports at work.
Thank you very much for all the participants in this contest. I have thoroughly enjoyed exploring your work & learned a lot from them. I am sure you had fun creating these too.
So go ahead and enjoy the entries.Continue »
In the 48th session of Chandoo.org podcast, let’s make some animated charts!!!
What is in this session?
In this podcast,
- Why animate your charts?
- Non-VBA methods to animate charts
- Excel 2013’s built-in animation effects
- Iterative formula approach
- VBA based animation
- Cartoon film analogy
- Understanding the VBA part
- Example animated chart – Sales of a new product
- Resources and downloads for you
Today is Diwali, the festival of lights. I wish you and your family a happy, bright and prosperous festive time. May your house shower with lots of light, laughter and love.
Diwali is one of my favorite festivals. It is a time when all family members get together, eat delicious food, laugh to hearts content and light up diyas (small oil lamps) to celebrate the victory of good over evil. This year, my kids (who are 6 yrs btw) are very excited about the festival. They are looking forward to lighting up diyas and crackers (fire works).
To celebrate the holiday, I made something for you.
An animated flower pot firework in Excel.Continue »
Imagine you are the head of training department at ACME Inc. You arrange training programs round the year to empower your team. It is hard work, coordinating between employees, trainers, department heads, venues and coffee machines. What if there is something to help you keep track of all this? I am not talking about getting you a shiny new iPad, you silly. I am talking about a tracker & calendar built in Excel that ties everything together (well, almost everything, you still have to fill the coffee machine.)
We are going to build a training program tracker & calendar using Excel.Continue »
Last night I asked members of our Chandoo.org facebook page to share an Excel problem you are struggling with. Francis asked,
How to save a file as .txt in vba without quotes? When I save as .txt, the file has got quotes inside of it. I used the code Print, but it didnt work because the file loses its delimitation.
Does anyone know how to solve this?
Let’s understand how to save a range as text and overcome the double quote problem.Continue »
Imagine you are looking customer data like below and want to sort them by performance. If you sort the data by any one column, you will not get full picture of performance. To understand which customers rank low on performance, you need to defined a weighed sort, the kind of sort where you assign weights to each attribute (customer age, recent purchases and rate of returns) and come up with single score to sort them all.
Sounds interesting? Watch below video to understand how to do weighted sorting in Excel.Continue »
Power Query (Get & Transform data in Excel 2016) is a must have tool, if you wrangle with data every day. Here is a quick introduction, in case you are new.
Let’s learn how to use Power Query to unpivot data.
Essentially, we are trying to go from left to right in the above picture.
Doing something like this thru either formulas or VBA can be very complex. But Power Query can get you unpivoted data in just a few clicks. Sounds interesting? Read on.Continue »
In the 45th session of Chandoo.org podcast, let’s get in to Monte Carlo simulations.
What is in this session?
In this podcast,
- Quick personal updates – 200km BRM and book delay
- History of Monte Carlo simulations
- Monte Carlo simulations – an example
- How to do simulations in Excel
- Data Tables
- Using data tables to run simulations – case study – estimating Pi value
- Things to keep in mind when setting up your simulation models
- Resources on Monte Carlo simulations in Excel
Econimist’s daily chart is a one of my daily data porn stops. They take interesting data sets and visualize in compelling ways. While the daily chart page is insightful, sometimes they make poor charting choices. For example, this recent chart visualizing how countries spend their money uses a variation of notorious bubble chart. Click on the chart to enlarge.
What is wrong with this chart?
Bubble charts force us to measure and compare areas of circles. Unless you have a measuring tape somehow embedded in your eyes and you are a walking human scientific calculator, you would find this task impossible.
So when you look at the chart and want to find out what percentage Japanese spend on restaurants or how much Americans pay for housing, your guesses will have large error margins.
Not only bubble charts are difficult to read, they are very hard to align. So when you have a bunch of bubbles, no matter how hard you try, your chart looks clumsy (see how the Russian food bubble eats in to Mexico’s bubble, as if it is too hungry 😉 )
Let’s check out a few alternatives to this chart. Read on…Continue »
We know that column charts are excellent for presenting information. But what if some of the columns are too tall and hijacking the rest. In a previous article, we discussed few of the approaches. Today let’s learn how to build a cropped chart (broken y-axis chart) using Excel, something like above.
Looks interesting? Read on.Continue »
We all know that VLOOKUP (and its cousins MATCH, HLOOKUP and LOOKUP) are great for finding information you want. But they are helpless when you want to do a case-sensitive lookup.
So how do we write case sensitive VLOOKUP formulas?
Simple. We can use EXACT formula.Continue »
Cascading drop downs enhance usability of your dashboards & interactive workbooks. A cascading drop-down is a 2 or more level selection mechanism. When you have 100s of selection choices, instead of creating one massive drop down or combo-box, you can set up multiple levels of drop downs, so that users can narrow down their selection. For example, users can select Country, State and then City using cascading drop downs.
There are many ways to setup cascading drop downs. You can use formulas coupled with either data validation or form controls. You can also use Slicers. In this video we will review these techniques.Continue »
Funnel charts are useful to visualize sales & marketing performance. In this brief video, let’s understand how to make a quick funnel chart in Excel.
Read on to learn how to make funnel charts & to download a template for your funnel analysis needs.Continue »
Here is an awesome planner template to help you manage activities over a month. It is useful for charity drives, activity planning, school schedules, marketing initiatives, project planning etc.
Read on to download a copy of the template & learn how to use it.Continue »
Pivot tables are very powerful analysis tools. They can summarize vast amounts of data with just few clicks. But they are lousy when it comes to output. Imagine the horror of putting a pivot table right inside your beautiful dashboard. One refresh could ruin the layout and create half-an-hour extra work for you.
How to combine the power of pivot tables with elegance of your dashboards?
The answer is: GETPIVOTDATA()Continue »