Let’s say you manage a fund or charity. You get money from various places and you use that money for various reasons. How do you tell the story of source vs. uses of funds? In this post, let’s review 14 charting options.
Source vs. Use of funds – Sample Data for this problem
Let’s say you have 3 sources and 3 uses, as shown below. For all these charts, we will use the same data, but may add some simple calculations or change the layout to get the charts.
As I made quite a few alternatives, I broke them in to two kinds of charts.
- Simple charts: These are easy to make, easy to read and scale well when you have lots of sources & uses.
- Exotic charts: These are somewhat tricky but fun to make, look exotic and raise a few eyebrows in the meeting room.
Simple Charts for Source vs. Use of funds
#1 – Waterfall chart
Waterfall chart is perfect for this kind of data. Simply layout your data like below (you can use SUBTOTAL or SUM formulas to calculate intermediate totals), select everything and insert waterfall chart from Insert ribbon. Mark total columns “as total” and you are done.
#2 – Stacked column chart
Set up your data in two columns as shown aside and create stacked column chart. Format to your taste. Simple and effective.
#3 Couple of Pies
Use the same data format as “stacked column chart” but instead make two pie charts. If you have just a few options for sources and uses, Pie charts work great.
#4 – Mirrored bar chart
As you can see, sources and uses are two sides of the same coin, so we can go with a mirror metaphor like above. To create this chart, you need data in the format shown below. Once you have the data, just select it and insert a stacked bar chart. Format as you see fit and the mirror is ready.
#5 – Dollar background – stacked chart
This is same is option #2, but with a fancy dollar bill for plot area background and gap width for columns set to 0. When you make the fill colors for series semi-transparent, you get this effect.
#6 – Touching stacked bars…
Again, same format of data as “stacked chart #2”, but we make a stacked bar chart and then set gap width to 0 to get this effect. This works very well and makes it easy to compare.
#7 – Donuts
While I am not a fan of donuts or pies, they work great when you have small set of data like this. Set up your data like #2 stacked chart and instead make donut chart. Format and you get this.
#8 – Half a pie each…
This works very well if source total = use total. Just set up your data like this and make a normal pie chart. Format sources and uses in two colors like above and draw a line in the middle. You get above chart.
#9 – Sunburst chart
This is essentially similar to donuts (option #7) but we need data in a different structure (see aside). Sunburst charts work very well even when you have lots of items in each category or even sub-categories. Keep in mind though, sunburst charts are new to Excel 2016, so if you have older versions, you can’t get these.
#10 – Treemap chart
Same as sunburst charts, treemaps are a new chart in Excel 2016. They can work well when you have many items or even sub-categories.
Exotic charts for Source vs. Use of funds
#11 – Hourglass chart
The idea is Sources flow to Uses. So we make an hour glass shape with regular bar chart (with invisible series on left to push the items to center).
To create this, set up your data like below.
Now, simply create a regular stacked bar chart. Select Gap series, make it invisible (fill color > none), format source and uses series. You are done.
#12 – Butterfly chart
Sources and Uses as wings of butterfly. This is really an area chart, with an invisible series at the bottom to create the effect of butterfly wings. To make this chart, set up your data like below:
Now, Make an area chart. Move gap series to top (so it is really at the bottom when laid out), then make it invisible. See this GIF to understand how it all works to make the butterfly wings.
#13 – Waterfall lines
Instead of waterfall bars, we make this with lines. Unfortunately, this step chart is the trickiest one to create as we use XY plot with error bars to get the steps. The technique has too many steps, so I am not explaining them here, but if you are curious, see this tutorial.
#14 – Cube chart
Here the idea is Sources and Uses are two faces of a cube. Again, this is actually an area chart, but invisible gap series at bottom and a top layer. To make this, set up your data like below (remember, the horizontal axis has to be dates, else you will not get Cube effect).
To be honest, I am not a fan of cube chart, as it is tricky to label things and won’t work for more items. But I wanted to give it a try.
Download all the 14 charts…
I made a simple workbook with all 14 examples. Click here to download the file and play with it to learn. May be try making something on your own and post that in the comments section?
What is your favorite chart?
My favs are #1 (waterfall), #12 (butterly chart) and #8 (half pies).
What about you? What is your favorite chart among all these? Post in comments. Also feel free to make your own and share them so we can all learn.
Like charts & visual story telling? Check out these amazing examples
If you are in to data analytics & visualizations, you are going to love Chandoo.org. We got tons of examples and tips. Start with these…
This post is inspired by a forum question from Yaswanth92.
23 Responses to “Learn Top 10 Excel Features”
What it looks like if excel without formula?? 🙂
It would be not excel it would just be fancy tables in which you could just use power point. (Chandoo) would Access be an alternative?
Awesome piece of work!!!
Great article.
Chandoo - my biggest interest in the article was the awesome word-graphic at the top - where did you go to get it done into a shape?
@Rich.. thank you. I used http://www.tagxedo.com/ to generate this word cloud. I took all the comments in the original post, pasted them in tagxedo website and set up the shape etc.
Awesome Chandoo.. You need always needs coffee to start up with. BTW , how did u created the Heart Shaped picture filled with High Repetitive text in it .. Please put it on your Next blog ...
Chandoo, good article. I’ve added a link to it from Connexion – our collection of the most useful and interesting spreadsheet-related articles from the web. See http://www.i-nth.com/resources/connexion
Hi,
Just one small question. Where the hell have been I in the past for not discovering this website sooner?
I've lost a job interview recently where even though I had the subject knowledge, I was not upto their mark in Excel.
Thank you for all the free tips, guidance and for creating this forum environment.
[PS: I've just been through the site for the 1st time, and have signed up for the newsletter. You can expect pretty stupid questions from me soon]
Hy Chandoo, you always inspire me with to explore something new in excel. This data structure table is only for excel 2007 or compatible to 2010. I recently installed latest excel version 2013 in my System and experience problems regarding operating according to previous one. I'm waiting your article relates to that excel version.
Thanks
Awesome article Mr. Chandoo and that is a awesome heart shaped pic you created. Great tips as well.
[...] Learn Top 10 Excel Features | Chandoo.org – Learn Microsoft Excel Online. [...]
Chandoo is awesome..
Thanks, i got better, And i always get 90.50 in my grade card but now i get 96.50 i improved because of the tutorials you gave, Thank You Very Much Chandoo Guy.
Hi chandoo, i am intersted in seeing the video or step by step done procedure of analysing the comments and presenting in the data percentage steps. I think this one would be first step in finding out how generally happens data calculation. Thank you.
As well i would like to know how to get that black shape art of your face which i see in chandoo. I am interested in making it for me.
Nice to see the features considered by Excel users to be most useful. It might be a good idea to also analyze StackOverflow Excel questions to see what keywords appear most often.
Here are my top 10 Excel Features (for advanced users):
http://www.analystcave.com/excel-10-top-excel-features/
Thanks a ton for this it totally helped with my homework ????
Very good effort
Thank you for this. Lots of learning in the links you've provided for this septuagenarian.
Pls send me new post
Dude, your humor ? ?
Loved your work.
Hello Sir,
I am Sanjeev Khakre and i from Indore City, India , I am your big follower and i have watch your videos and learnt a lots of excel trick or function and many more . thanks so much for all of your excellent support.
Your excel knowledge is real awesome.
Thanks
Sanjeev
Your work is excellent but pls willing to know more details about the features of microsoft excel
Chandoo Would Access be a better alternative than VB?