- Charts and Graphs
- Last updated on April 10, 2019
Chandoo
Share
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…
- Examining the grammy bump in Excel
- How people spend money? – Panel chart
- Awesome Budget vs. Actual chart template
- More charts & dashboards
This post is inspired by a forum question from Yaswanth92.
Share this tip with your colleagues
Get FREE Excel + Power BI Tips
Simple, fun and useful emails, once per week.
Learn & be awesome.
- No Comments
- Ask a question or say something...
- Tagged underarea charts, butterfly chart, charting, donut charts, downloads, list posts, pie charts, screencasts, stacked bar, sunburst chart, treemap, waterfall charts
- Category:Charts and Graphs
PrevPreviousTop 10 Excel Formulas for any situation
NextVLOOKUP multiple matches – trickNext
Welcome to Chandoo.org
Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME.Read my story • FREE Excel tips book
Want an AWESOME Excel Class?
Excel School made me great at work.
5/5
– Brenda
FREE Goodies for you...
100 Excel Formulas List
From simple to complex, there is a formula for every occasion. Check out the list now.
20 Excel Templates
Calendars, invoices, trackers and much more. All free, fun and fantastic.
13 Advanced Pivot Table Skills
Power Query, Data model, DAX, Filters, Slicers, Conditional formats and beautiful charts. It's all here.
Get started with Power BI
Still on fence about Power BI? In this getting started guide, learn what is Power BI, how to get it and how to create your first report from scratch.
Weighted Average in Excel [Formulas]
Learn how to calculate weighted averages in excel using formulas. In this article we will learn what a weighted average is and how to Excel’s SUMPRODUCT formula to calculate weighted average / weighted mean.
What is weighted average?
Wikipedia defines weighted average as, “The weighted mean is similar to an arithmetic mean …, where instead of each of the data points contributing equally to the final average, some data points contribute more than others.”
Calculating weighted averages in excel is not straight forward as there is no built-in formula. But we can use SUMPRODUCT formula to easily calculate them. Read on to find out how.
How to get non-adjacent columns with FILTER function in Excel
What is XLOOKUP and how to use it in Excel?
Extract BOLD portion of a cell in Excel using getBoldText() function
How to fix SPILL Error in Excel Tables (3 easy solutions)
- Excel for beginners
- Advanced Excel Skills
- Excel Dashboards
- Complete guide to Pivot Tables
- Top 10 Excel Formulas
- Excel Shortcuts
- #Awesome Budget vs. Actual Chart
- 40+ VBA Examples
Related Tips
Charts and Graphs
Make an Impressive Interactive Map Chart in Excel
Charts and Graphs
How to Create a Dynamic Excel Dashboard in Just 5 Steps
Charts and Graphs
How to create a fully interactive Project Dashboard with Excel – Tutorial
Charts and Graphs
Charts and Graphs
6 Must Know Line Chart variations for Data Analysis
Charts and Graphs
