Menu
Hi Apgar,In Excel for mac V16.X when using Waterfall chart, you can use the Chart Designand Format tabs to customize the look of your chart. If you don't see these tabs, click anywhere in the Waterfall chart to display them on the ribbon.Meanwhile, please confirm the version and build of your Excel application. To confirm, inExcel, go to the Excel menu and chooseAbout Excel.Thanks,Neha - If you feel a reply works for you, please kindly vote or mark it as it will be beneficial to other community members reading this thread. Hi Apgar,When I created Waterfall chart in Excel V16.10, I saw the same behavior as you.I don’t see any information about format pane when checked this about Waterfall Chart on mac.Since Waterfall chart is recently introduced in Excel for mac V16.x, it is possible that Format pane is not available.
![]() ![]()
Adding a waterfall chart is performed similar to other empower charts, however its data entry differs slightly. In an Excel® table an “X”.
In this situation, I’ll suggest you provide your feedback inNote: V16,11 and 16.12 are insider fast version, for more information, please see. To join insider fast build, open any app, and then selectCheck for Updates on the Help menu. Check the box toJoin the Office Insider program and select fast in the drop-down.Regards,Neha - If you feel a reply works for you, please kindly vote or mark it as it will be beneficial to other community members reading this thread. Hi Apgar,As you are aware, the Waterfall chart is added in recent version.
I checked from my end and the format pane is not available in the latest version. In the official support, there’s no indication of Format Pane.I’ll suggest you provide your feedback in or click the smiley icon from Excel app.Regards,Neha- If you feel a reply works for you, please kindly vote or mark it as it will be beneficial to other community members reading this thread.
This is a guest post from Aaron Henckler.Waterfall charts are great, especially for visually showing the contribution of parts to a whole. While there are several tutorials on how to make a waterfall chart online the end products of these tutorials rate low on the visually appealing scale.The principle problem with these charts is the separation between the elements of the waterfall.
They are always either pushed together (Example A) or left apart, without element connectors (Example B):Example AExample BMany users of waterfall charts employ the separated (default) version (example B) opting to add in element connectors manually via InsertShapesLine on the Excel tool bar. The frustration with this approach is that all too often the values of the chart elements will need to be updated or changed forcing user to manually readjust each of their connector lines in turn.With some simple charting trickery in Excel 2007 one can easily make a waterfall chart with connectors that will update automatically as element values are changed. First of all, great post.
Second, an extra thought on the usefulness of waterfall charts in general:Waterfall charts are best employed when a stacked bar (or, as I cringe, a pie chart) won't suffice because some of the 'contributors' contribute negatively. This example is very helpful to get the basic technique down, but some extra math and series would need to be added to accommodate common waterfall chart applications (cash flow analyses, marketing mix, etc.).Jon's add-in does this, I think (at least, based on the screenshots).
It can be done without an add-in, but it takes a moment to get the math down on what you're referring to as 'Base Values' in this example (the transparent spacer series). Hi, nice tutorial, thanks Aaron and Chandoo. This also works in 2003, although some of the stages are very slightly different.One possible improvement - some of the connecter lines may look as if they are slightly out of step with the blocks - in the final chart the one between 'north' and 'east' looks slightly too high. I also got this when I followed the tutorial. You can play around with borders for the element series - either turn them off completely, or make them the same colour as the fill colour, and play around with the line weight. If that doesn't work, consider adding in a small number to the connector values to offset them slightly.
Great add, Bob. Two more things:1) The up/down bars are based on the first and last series provided. You can add the line connectors like Aaron's chart has by adding a series for each line segment to the middle of the data table.
Could be an easier way to do this, but hey. This worked.2) I didn't like that the first datapoint (2008 in my example) and last (2009) were the same color as up bars. I see those as absolutes, and the up/down bars should be for the changes (Chg1-4). So, I added an absolute volume series (named Vol) and changed its chart type to Bar.3) Using the same trick that Aaron explains, you can also add data labels. I didn't.Bob's (in rows instead of columns):Label 2008 Chg1 Chg2 Chg3 Chg4 2009Start 0 100 110 100 55 0End 100 110 100 55 125 125Mine:Label 2008 Chg1 Chg2 Chg3 Chg4 2009NoteStart 100 110 100 55 UpDownVol 100 125Type=BarLine 100 100 Type=LineLine 110 110 Type=LineLine 100 100 Type=LineLine 55 55 Type=LineLine 125 125Type=LineEnd 110 100 55 125 UpDownObviously, more cumbersome to set up but. Thanks master!!I've actually gone a step further, and combined the chart with a data validation filter, and the result was really impressive, a dynamic waterfall chart, without a single macro line!!!Commenting this with a colleague form Finance, he mentioned that in this case (showing info from offices and getting to a region total), waterfall might induce to a confusion, as one might be tempted to understand that a certain office's numbers is based on the previous shown, and reluctantly, i had to agree with that vision.Still, I love the results!!!Rgds,Martin.
I wish I could use waterfall charts with pivot tables and Dynamic number of data elements (which is why I use pivot tables). My main issue is related to data elements.
I have charts I update every month for every project. So each project has its own set of charts (same charts with each project). However, each project might have 0 to 12 data elements. And every month the number of data element can change for each project.
I do not see how I could do waterfall charts without a lot of work when I change the number of data points. I've been running charts like this for a couple of years in Excel 2002. I have it set up as a template for others in my prior department to use in their presentations. It's quite versatile and works for them well.but the labels all fail in Excel 2007. I've got labels that are not manually linked as described above, but rather I've created addtional series to hold the labels. The Y value is the Y value of the series I'm labeling, and the x-value is the TEXT version of the gap number I want to label. Then I set the label to show the x-value.
Since the x values are text, they align with the other series' x axis and it has been working flawlessly. Any way to automate labeling like this in Excel 2007? Shy of an add-in? I wanted to automate this process so wrote a little sub to take care of it. However it won't handle negative values or a falling waterfall chart (only rising).
How could I show more visually the third component of the waterfall chart is heavily dependent on the first and second?I am trying to 'build a story' that the starting point (jump off point) for this year to next (final number) is dependent on three main components A, B and C. But C is dependent on success of A and B. So while the waterfall chart shows that A, B and C are component of from getting from this year to the next but it does not show the relationship between A and B with C.Any idea how to do this more visually and powerfully?Thanks! How to you get the waterfall chart to work when the variable goes below the x axis? Lets say you are looking at how sales have translated into negative profits.
You have the main sales bar and progressively with expense categories it goes below the line. The series that goes below the line will be part of the same series that you are removing in previous items e.g cost of sales to create the floating effect. Therefore I am left with nothing below the line as that series is artifically removed.
![]()
Hopefully that makes sense.
![]() Comments are closed.
|
AuthorWrite something about yourself. No need to be fancy, just an overview. Archives
January 2023
Categories |