# 🏆 Waterfall Chart - Excel **URL:** \ **Category:** Excel Templates\ **Created:** [July 1, 2022, 1:34am UTC](https://community.tiller.com/t/waterfall-chart-excel/13286 "2022-07-01T01:34:02Z")\ **Posts on this page:** 14\ **Page:** 1
**Author:** ![jpfieber](https://sea2.discourse-cdn.com/flex020/user_avatar/community.tiller.com/jpfieber/32/4791_2.png) [@jpfieber](https://community.tiller.com/u/jpfieber)\ **Post date:** [July 1, 2022, 1:34am UTC](https://community.tiller.com/t/waterfall-chart-excel/13286/1 "2022-07-01T01:34:02Z")
# Overview I use a ‘Waterfall’ chart to show category or group totals for selectable time periods, and make how expenditures fit within income more apparent. I have previously made a Google Sheets version available [here](https://community.tiller.com/t/waterfall-chart/9731). ![Waterfall](https://us1.discourse-cdn.com/flex020/uploads/tillermoney/original/2X/2/2f2154b2c51e9172ce9345cc6a0c300ef5f98159.png) **How did you come up with the idea for your workflow?** My wife doesn’t understand numbers. To help her understand where our money is going, I need to provide visuals. Pie charts and Bar charts are OK for comparing categories to each other, but I wanted to include how expenditures fit within earnings. While figuring out how to do that, I stumbled across the ‘Waterfall’ chart type, included in Google Sheets. A waterfall chart shows how values add or subtract from a starting value. When referencing a sorted query, the chart uses vertical bars showing how your income was generated, and then a cascade of increasingly larger subtractions for each category or group, with a subtotal at the end showing the difference between your income and expenses. # Installation 1. Download the [Waterfall workbook](https://1drv.ms/x/s!AuS8hLFTBEB6mXpew4GDjQGxREL3?e=T4HiBK). 2. Follow [these instructions](https://help.tillerhq.com/en/articles/6500407-how-to-move-a-new-worksheet-into-your-tiller-powered-excel-workbook) to copy the downloaded template into your Excel workbook and to connect the formula references to your local workbook data. (The instructions are for the desktop version of Excel, the web version is not recommended for the installation but does work once the Account Reconciliation sheet is installed.) At this point your new template should be functional and linked to your local workbook’s data. # Setup If you’d like to use the Payroll period option that is explained below, enter the description you use for your Paycheck (or a portion of it) in cell R3. # Usage You can change the period of time you’re seeing transactions from using the ‘Period’ dropdown. You can change if you’re viewing by category or group using the ‘View By’ dropdown. If you don’t enter the description of your paycheck transactions in R3, the paycheck options in the Period dropdown won’t work. Note that the ‘Paycheck’ options will be most useful for those with a single major pay source. - Current Paycheck - Enter your paycheck ‘Description’ in R3 (either the entire description or a unique portion). Period starts on the date of the first transaction with a description that contains R3, and extends until now. - Previous Paycheck - Enter your paycheck ‘Description’ in R3 (either the entire description or a unique portion). Period starts on the date of the second transaction with a description that contains R3, and extends until the day before the first transaction with a description that contains R3. - Since Last Sunday - Period starts on the most recent Sunday and extends until now. - Since Start of Month - Period starts on the first day of the current month and extends until now. - Previous Month - Period starts on the first day of last month and extends until the last day of last month. - Since Start of Year - Period starts on the first day of the current year and extends until now. - Previous Year - Period starts on the first day of last year and extends until the last day of last year. - Number of Days - After selecting this period, B5 will become visible so you can enter a number. The period starts the number of days you entered before now, and extends until now. - All - Period starts on the date of your oldest transaction and extends until now. You can also change the ‘View By’ dropdown in B6 to change between Categories and Groups. # Permissions **Is it ok for others to copy, use, and modify your workflow?** Yes, please, and please provide feedback on any issues you encounter and/or suggestions on how to make improvements! # Notes I’ve replicated almost all the functionality of the Google Sheets version of this template. The things I’m still hoping to add are: - Change the colors of the bars within the chart - Add a final ‘Subtotal’ bar that shows the difference between what you earned and what you spent. ---
**Author:** ![yossiea](https://sea2.discourse-cdn.com/flex020/user_avatar/community.tiller.com/yossiea/32/4643_2.png) [@yossiea](https://community.tiller.com/u/yossiea)\ **Post date:** [July 1, 2022, 1:49am UTC](https://community.tiller.com/t/waterfall-chart-excel/13286/2 "2022-07-01T01:49:34Z")
Looks really nice! It’s a bit of a switch to switch from Google brain to MS brain. ---
**Author:** ![jpfieber](https://sea2.discourse-cdn.com/flex020/user_avatar/community.tiller.com/jpfieber/32/4791_2.png) [@jpfieber](https://community.tiller.com/u/jpfieber)\ **Post date:** [July 1, 2022, 1:53am UTC](https://community.tiller.com/t/waterfall-chart-excel/13286/3 "2022-07-01T01:53:27Z")
Thanks! Yea, that’s for sure. I feel like the Google product is better, but that may change as I become more comfortable with the advanced features in Excel. A definite learning curve! Thanks for your Transaction Tracker template, it helped nudge me forward into trying to convert one of mine. Hopefully I can get them all cross-platform eventually! ---
**Author:** ![yossiea](https://sea2.discourse-cdn.com/flex020/user_avatar/community.tiller.com/yossiea/32/4643_2.png) [@yossiea](https://community.tiller.com/u/yossiea)\ **Post date:** [July 1, 2022, 6:42pm UTC](https://community.tiller.com/t/waterfall-chart-excel/13286/4 "2022-07-01T18:42:47Z")
Same here. Excel seems to be coming along and they have formulas in beta that should be really helpful, such as lambda and others. I am a big fan of Leila Gharani on YouTube and she oftentimes shows what is in new with Excel. ---
**Author:** ![randy](https://sea2.discourse-cdn.com/flex020/user_avatar/community.tiller.com/randy/32/16_2.png) [@randy](https://community.tiller.com/u/randy)\ **Post date:** [July 7, 2022, 12:51am UTC](https://community.tiller.com/t/waterfall-chart-excel/13286/5 "2022-07-07T00:51:03Z")
Great work knocking this out, @jpfieber! It’s so cool to have this amazing visualization available in both Excel and Sheets. _We are excited to award you our second 2022 Microsoft Excel Builders Challenge spot award: a $200 gift certificate. 👏_ Keep up the great work! ---
**Author:** ![dirk.van.assendelft](https://sea2.discourse-cdn.com/flex020/user_avatar/community.tiller.com/dirk.van.assendelft/32/8514_2.png) [@dirk.van.assendelft](https://community.tiller.com/u/dirk.van.assendelft)\ **Post date:** [July 14, 2022, 5:42pm UTC](https://community.tiller.com/t/waterfall-chart-excel/13286/6 "2022-07-14T17:42:19Z")
> [@yossiea](#): > > Leila Gharani She has an incredible series on YouTube. I find her an invaluable resource on cool new features in Excel. [Leila Gharani - YouTube](https://www.youtube.com/channel/UCJtUOos_MwJa_Ewii-R3cJA) ---
**Author:** ![dmelideo](https://sea2.discourse-cdn.com/flex020/user_avatar/community.tiller.com/dmelideo/32/9355_2.png) [@dmelideo](https://community.tiller.com/u/dmelideo)\ **Post date:** [December 18, 2022, 5:26pm UTC](https://community.tiller.com/t/waterfall-chart-excel/13286/7 "2022-12-18T17:26:48Z")
I loaded the waterfall chart and find its output very interesting. I am seeing a possible glitch in how it handles data or maybe originating from my Tiller workbook. Cell C30 doesn’t populate with a value, thus graph not loaded for this datapoint. The correct data is within hidden columns AD and AG which would indicate data is being properly pulled from my transactions. I have played with forcing manual transactions and the same cell is always empty. The math within the cell appears to be same as all in the column. Unfortunately I can’t suggest a fix. ---
**Author:** ![jpfieber](https://sea2.discourse-cdn.com/flex020/user_avatar/community.tiller.com/jpfieber/32/4791_2.png) [@jpfieber](https://community.tiller.com/u/jpfieber)\ **Post date:** [December 18, 2022, 6:03pm UTC](https://community.tiller.com/t/waterfall-chart-excel/13286/8 "2022-12-18T18:03:19Z")
Strange. The first thing I would try is to select B13:C100 and hit Delete to clear out anything that might be in those cells (they should be blank so the results can spill into them). If that doesn’t work, I’d probably need to see your spreadsheet to figure out what’s going on. If you’d like to share it with me, send a Message directly to me and we can work out the logistics. ---
**Author:** ![dmelideo](https://sea2.discourse-cdn.com/flex020/user_avatar/community.tiller.com/dmelideo/32/9355_2.png) [@dmelideo](https://community.tiller.com/u/dmelideo)\ **Post date:** [December 20, 2022, 8:26pm UTC](https://community.tiller.com/t/waterfall-chart-excel/13286/9 "2022-12-20T20:26:49Z")
Thank you. I tried as you suggested and data refilled with same empty cell at C30. I started with B13 which left B12:C12 with core income information. I tried deleting those 2 cells and the dataset stayed blank. I appreciate the help but not certain if I’m comfortable sharing my spreadsheet unless I take some time to enter theoretical values in the source tabs. The category information is correct in X71:Z71 and AB50:AD50 and group information is correct in AF19:AG19. Preceding includes correct values and group sum as well. I am carrying 71 categories. Again, appreciate the feedback ---
**Author:** ![jpfieber](https://sea2.discourse-cdn.com/flex020/user_avatar/community.tiller.com/jpfieber/32/4791_2.png) [@jpfieber](https://community.tiller.com/u/jpfieber)\ **Post date:** [December 20, 2022, 8:40pm UTC](https://community.tiller.com/t/waterfall-chart-excel/13286/10 "2022-12-20T20:40:03Z")
You could try deleting the sheet and re-adding it, which would fix any issues in case there was a change to the sheet. If that doesn’t work, then there must be something about the data that is triggering this behavior. Can you tell what is supposed to appear in that cell? Does it break when viewing by Category, or by Group, or by both? Also, if you filter the dates differently, will it work for some periods but not others? That might help narrow down if there’s a particular transaction that’s causing problems. ---
**Author:** ![us1](https://sea2.discourse-cdn.com/flex020/user_avatar/community.tiller.com/us1/32/12025_2.png) [@us1](https://community.tiller.com/u/us1)\ **Post date:** [April 11, 2023, 8:33pm UTC](https://community.tiller.com/t/waterfall-chart-excel/13286/11 "2023-04-11T20:33:09Z")
Hello @jpfieber . Thank you for sharing this tool! I imported it today, and I am having a similar issue that @dmelideo reported. When I filter for Category, I have a blank value in C47, so waterfall is showing it as $0 when it should instead have a negative value. I can confirm that it has a value in AD14. This is for “Previous Month” Period, “Category” View By. If I change View By to “Group”, all my data loads. If I change my Period to “Since Start of Year”, my blank category’s Amount becomes populated, but a different Category now becomes empty. Any troubleshooting ideas? ---
**Author:** ![jpfieber](https://sea2.discourse-cdn.com/flex020/user_avatar/community.tiller.com/jpfieber/32/4791_2.png) [@jpfieber](https://community.tiller.com/u/jpfieber)\ **Post date:** [April 12, 2023, 12:05am UTC](https://community.tiller.com/t/waterfall-chart-excel/13286/12 "2023-04-12T00:05:07Z")
I’m having a hard time following what’s happening. If you could post some screen shots, or better still, share your template with me I can find where things are going wrong. ---
**Author:** ![us1](https://sea2.discourse-cdn.com/flex020/user_avatar/community.tiller.com/us1/32/12025_2.png) [@us1](https://community.tiller.com/u/us1)\ **Post date:** [April 12, 2023, 2:57am UTC](https://community.tiller.com/t/waterfall-chart-excel/13286/13 "2023-04-12T02:57:44Z")
@jpfieber , I made a change that may have fixed my issue, but I’d love to see if this makes sense to you since it’s your formula (this is a very advanced formula for me). It did resolve the immediate issue I was having, but I’d have to test various filter combinations to ensure I do not experience any unexpected empty cells in column C. On Cell B12, I changed the formula from =IF($B$6=“Category”,IF(FILTER($AC$4:$AD$1000,$AB$4:$AB1000\<\>“(hidden)”)=0,“”,SORT(FILTER($AC$4:$AD$1000,$AB$4:$AB1000\<\>“(hidden)”),2,-1)),SORT(FILTER($AF$4:$AG$100,$AF$4:$AF$100\<\>0),2,-1)) to =IF($B$6=“Category”,IF(FILTER($AC$4:$AD$1000,$AB$4:$AB1000\<\>“(hidden)”)=“”,“”,SORT(FILTER($AC$4:$AD$1000,$AB$4:$AB1000\<\>“(hidden)”),2,-1)),SORT(FILTER($AF$4:$AG$100,$AF$4:$AF$100\<\>0),2,-1)) ---
**Author:** ![kylemichaelseaver](https://sea2.discourse-cdn.com/flex020/user_avatar/community.tiller.com/kylemichaelseaver/32/12251_2.png) [@kylemichaelseaver](https://community.tiller.com/u/kylemichaelseaver)\ **Post date:** [April 12, 2023, 12:22pm UTC](https://community.tiller.com/t/waterfall-chart-excel/13286/14 "2023-04-12T12:22:13Z")
Very Nice work! A nice visualization for people who need to “see the reality” of what’s going on!