All articles with 'charting' Tag

Awesome chart to visualize Salary Increases for 3,500+ people [Tutorial]

Published on Aug 17, 2017 in Charts and Graphs, R programming
Awesome chart to visualize Salary Increases for 3,500+ people [Tutorial]

Game for some charting awesomeness?

Off late, I have been doing a lot of data analysis and visualization on performance ratings, salary hike, gender pay equality etc. Today let me share you an awesome way to visualize massive amounts of data.

Scenario: Your organization of 3,686 people recently went thru annual performance ratings & review process. At the end of it, everyone was offered some salary increase (from $0 to $24,000 per year). You have 7 business groups. How do you tell the story of all these salary hikes in one chart?

How about the one above?

Ready to know how to create this in Excel? Read on.

Continue »

Employee Performance Panel Charts – Excel vs. R [video]

Published on Aug 10, 2017 in Charts and Graphs, R programming
Employee Performance Panel Charts – Excel vs. R [video]

Recently, I had to make a bunch of panel charts. After wrangling with Excel (and a tiny bit of VBA) to create them, I wondered if we are suffering needlessly by being too loyal to Excel. I switched to R and could create these panel charts in almost no time (well, first I had to learn how to pivot the data using dplyr). Today, let me share the experience.

Continue »

Joyplot in Excel

Joyplot in Excel

Over on Twitter, I came across this beautiful chart, aptly titled – Joyplot. It is the kind of chart that makes you all curious and awed. So I did what any Excel nerd would do. Recreated it in Excel of course. This post takes you thru the process.

Take a look at final outcome above. Read on to learn more.

Continue »

Start your bar charts from zero – Excel geeks screaming at you from mountain top

Published on May 15, 2017 in Charts and Graphs
Start your bar charts from zero – Excel geeks screaming at you from mountain top

Here is a simple but vital charting rule.

Start your bar (or column) charts from zero.

To illustrate why you should do this, let me share a personal example.

Over the weekend, the Jon Peltier visited Wellington. He is staying with Jeff (who occasionally guest blogs on Chandoo.org). On Sunday, we all decided to hike up a small mountain near my house for a leisurely family picnic.

While on the top of the mountain, Jo (my wife) took a few pics of us three Excel geeks.  As we were standing on a sloping mountain face this is how the pictures look.

Looking at the picture on left, you would confidently say that I am way shorter than other two. But picture on right tells a different story.

Continue »

Modelling Inventory Run Rate & Cash Flows using Excel

Published on Apr 19, 2017 in Analytics, Charts and Graphs
Modelling Inventory Run Rate & Cash Flows using Excel

Imagine you run an office furniture company. You want to stop reordering two brands of furniture – Relaxer (a type of chair) and Boca Top (a type of table). You currently have 20,000 Relaxer chairs and 5,000 Boca Tops. These are valued at $200,000 and $100,000 respectively. When sold, they will yield $100,000 and $25,000 gross profit. You are hoping to sell them off in 2 or 3 years. You forecast that we can sell off these as per some yearly schedule.

You need to analyze this and prepare a cash flow model.

Let’s learn how to answer such open ended questions using various analysis techniques in Excel.

Continue »

Sand Pendulums – Lissajous Patterns in Excel

Published on Apr 3, 2017 in Charts and Graphs, VBA Macros
Sand Pendulums – Lissajous Patterns in Excel

Few days ago, I saw a beautiful homemade science experiment on Sand Pendulums on Bruce Yeany‘s YouTube channel. Go ahead and check it out. It is a cool project to do with your kids.

I will try this experiment with kids during school term holidays around Easter. But first, I wanted to try the simulation in Excel.

Simulating sand pendulum pattern in Excel

Take a look at the final simulation above. This is what we will create in Excel.

Continue »

Designing awesome financial metrics dashboard [tutorial]

Designing awesome financial metrics dashboard [tutorial]

In this amazing guest post, the winner of our 2016 dashboard contest – Chandeep – Explains how he constructed the jaw dropping beauty (shown above) using Excel, creativity, love and sweat. Grab a full cup of coffee (or whatever liquid fancies you) and read on. Take lots of notes and play with the ideas in Excel while reading to maximize your learning.

Thanks Chandeep.

Continue »

How to add a line to column chart? [Charting trick]

Published on Nov 2, 2016 in Charts and Graphs
How to add a line to column chart? [Charting trick]

Let’s say you work in super hero factory as floor manager. You are looking at the recent time sheet data submitted by your underlings and want to know who works more. So you did what any self respecting floor manager does. You made yourself a large cup of hot chocolate, whipped open Excel and created a column chart.

But now, you want to add a line to it at 6:00 PM (or some other arbitrary  point) so you can clearly see which superheros are over working.

So how do you go about it?

Continue »

Interactive Decision Tree Visualization in Excel [Trump vs. Hillary in Swing States]

Published on Oct 11, 2016 in Charts and Graphs, Pivot Tables & Charts, VBA Macros
Interactive Decision Tree Visualization in Excel [Trump vs. Hillary in Swing States]

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 »

Visualizing target vs. actual progress – Biker on a hill chart

Visualizing target vs. actual progress – Biker on a hill chart

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

Tada!!!

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 »

Stacked Bar/Column Chart with Indicator Arrows – Advanced

Published on Sep 15, 2016 in Charts and Graphs, Excel Howtos, Huis, Posts by Hui
Stacked Bar/Column Chart with Indicator Arrows – Advanced

Lets take last weeks Stacked Bar/Column Chart and add some high-performance steroids.

Continue »

Stacked Bar and Indicator Arrow Chart – Tutorial

Published on Sep 12, 2016 in Charts and Graphs, Excel Howtos, Huis, Posts by Hui
Stacked Bar and Indicator Arrow Chart – Tutorial

Learn how to develop a Stacked Bar chart with Indicator Arrow in this Tutorial

Continue »

Correlation vs. Causation [Charting Chatter]

Published on Aug 3, 2016 in Charts and Graphs
Correlation vs. Causation [Charting Chatter]

Here is a trap that is easy to fall in to. Confusing correlation as causation. As analysts, it is our job to see the data as it is rather than imply causation that doesn’t exist.

Let’s sample a chart, recently featured in Economist’s graphic detail under the title Measuring well-being.

Continue »

Introduction to Forecasting in Excel 2016 [Charts & Visual Analysis]

Published on Jul 27, 2016 in Charts and Graphs
Introduction to Forecasting in Excel 2016 [Charts & Visual Analysis]

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 »

How to visualize multiple variables over several years? [Contest]

Published on Jun 24, 2016 in Charts and Graphs, Excel Challenges
How to visualize multiple variables over several years? [Contest]

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 »