All articles with 'downloads' Tag
It is election time in USA, and that means there is a whole lot of drama, discussions and of course data analysis. There are tons of cool visualizations published on all the data. Previously, we talked about “How Trump happened” chart.
Today let’s take a look at the beautiful decision tree chart by NY Times explaining what would happen if each of the 10 swing states vote for Democrats or Republicans. Go ahead and look at that chart. And when you are done playing with it, come back.
My first thought after looking at the chart is: Wow, that is cool. I wonder how we can recreate that experience in Excel?
But as you can guess, making a dynamic tree visualization in Excel is pretty hard. You can create a bubble chart mixed with XY chart to show all the nodes of the decision tree, but as this tree has 2^10 nodes at the bottom level (and 2^11-1 total nodes) our chart would look very clumsy and busy.
So, instead of replicating NY Times chart, why not make our own version that explains the data? You can reuse this idea when visualizing outcomes of several what-if scenarios.Continue »
Anyone who has made a pivot table and their grandma knows that formatting them is a pain. Let’s recap the steps to apply one of the most common formats – currency format.
- Right click on any value field
- Go to Value field settings
- Click on “Number Format” button
- Choose Currency format
- Close the boxes, one after another
Unless you get paid per click, you wont be happy with all those clicks.
Wouldn’t it be cool to just click once and apply most common format to your pivot fields?
Of course you can. Just add oneClickCurrency macro to your personal macros workbook. And then add this to your Home ribbon as a custom button and you have a one click format option for any pivot.Continue »
Over at Flowing Data, Nathan has published an interesting visualization about spread of obesity.
While the above chart is quite interesting, it doesn’t offer much insight in to the data. There are a few drawbacks,
- Understanding obesity trends for a given state over the years is hard due to the layout and format of the chart.
- Finding which states are experiencing most obesity growth rates is not possible
- Ironically, the chart itself is obese. It takes too much space to explain the data.
Nevertheless, the chart looks cool and can be reused with smaller data-sets (quarterly trends or just for a few years). So let’s recreate the same in Excel. While we are at it, let’s also build an alternative visualization to explore the obesity data.Continue »
Over the years, we have discussed a whole heap of techniques to visualize budget vs. actual charts. Today let’s take a ride on this slope again and learn another fun, silly & awesome way to depict target vs. actual progress.
Introducing biker on a hill chart
Biker on a hill!?! Don’t worry, I didn’t fall down on a descent and lose my brain. I am talking about an Excel chart to visualize target vs. actual progress on a time line with biker on a hill analogy. See the above chart, you will know.
Looks interesting? Read on to learn how to create this in Excel.Continue »
Lets take last weeks Stacked Bar/Column Chart and add some high-performance steroids.Continue »
Last week, I asked you to share an analysis problem that you couldn’t solve in Excel. We got quite a few very interesting problems in comments and email. In this post, let me explain how to solve Sara’s copy shop problem using Excel.
What is Sara’s copy shop problem?
Thanks to Caroline who posted this problem.
Sara wants to open a copy shop. Each copier costs $5,000 per year to lease. The rent & other fixed costs per month are $300. There is a $0.02 variable cost per copy. Each copier can print up to 100,000 copies per year. She plans to charge $0.11 per copy from her customers. Sara estimates that the demand can be any of the 4 values – 500, 1000, 1500 or 2000 copies per day.
- Build a model to estimate profit per given number of copiers & demand values
- Find the mix of copiers & demand values that can make maximum profit for Sara (copiers – 1 to 6, demand – 500 to 2000)
Learn how to develop a Stacked Bar chart with Indicator Arrow in this TutorialContinue »
Around 2 months back, I asked you to visualize multiple variable data for 4 companies using Excel. 30 of you responded to the challenge with several interesting and awesome charts, dashboards and reports to visualize the financial metric data. Today, let’s take a look at the contest entries and learn from them.
First a quick note:
I am really sorry for the delay in compiling the results for this contest. Originally I planned to announce them during last week of July. But my move to New Zealand disrupted the workflow. I know the contestants have poured in a lot of time & effort in creating these fabulous workbook and it is unfair on my part. I am sorry and I will manage future contests better.Continue »
Over the weekend, I got an email from Mr. E, one of my students. Mr. E works at a police department in California and as part of his work, he was looking at calls received by police. Whenever police get a call for help, multiple teams can respond to the call and go to the location. All of these dispatches are recorded. So a single call can have several such dispatches. And Mr. E wanted to findout which team responded the first. The problem?
Finding the first responded team is tricky.
Today let’s take up this problem as a case study and understand various methods to solve it. We are going to learn about writing better lookups, pivot tables, power pivot and optimization. Put on your helmets, cause this is going to be mind blowingly awesome.Continue »
First a little back story:
Last Friday (on 29th of July), we (Jo, kids & I) went on a day walk to Red Rocks. It is a rugged coastal walk near Owhiro bay in Wellington. It was a windy & cold day. So why did we brave the elements of nature on this 10km walk? To see seals of course. And we did find a few of them. We also caught glimpses of snowy peaks in Southern Island of New Zealand.
3 Seals Excel Puzzle
Now I can’t take you on the same walk thru internet. There is no Excel function that can teleport you from your office (or home) to Owhiro bay. So I made the next best thing.
An Excel puzzle with 3 hidden seals.Continue »
One of the coolest features of Excel 2016 is forecasting. Today, let’s understand how it works with a sample data set.
Watch below video to understand forecasting in Excel 2016.Continue »
Here is an interesting problem to start your day.
Let’s say you work as DNA sequencing engineer at The Enterprise. And you just unlocked the sequence that is responsible for all male problems. The early onset of baldness. The sequence code is AAAA. And you want to find out how many times this sequence is found in a sample of DNA strings, in the range B6:B19. Essentially you want the above.
So how do you write the formula?Continue »
Here is a quick & awesome way to make your dashboards sexy. Add an on / off switch to your conditional formats.
Take a look at above demo to understand what I mean.Continue »
Our newest contest is inspired from a question asked by Kaushik, one of our forum members, interesting problem.
Need to quickly visualize 3 variables ( Company, years, Financials) in a single […] chart.
Create a chart to understand multiple variable data and you could win $100 Amazon gift card. Do send your charts before 4th of July to qualify for the prizes.Continue »
My twins (Nishanth & Nakshtra) are now almost 7. They are super keen to learn how computers work. So the other day, I showed them Code.org where there are several coding exercises disguised as games. They loved those games … err coding exercises. So that got me thinking… why not make a game in Excel that teaches kids simple programming concepts.
So I built a Snowman & Hot Chocolate Maze game. In this post, let’s understand how to build such a game using Excel VBA.Continue »