Show
An even faster & more flexible waterfall chart 👉The new Zebra BI for Office is just been released. And it brings the latest reporting best practices to your Excel & PowerPoint. We’ve also prepared 20 financial & sales report templates based on the latest reporting best practices. Enjoy! Prior to its release, the built-in Excel waterfall chart was the most highly anticipated chart type in the financial community. Unsurprisingly, it has continued to enjoy widespread popularity to this day. Read on to learn why and how you can create one yourself. Waterfall charts 101A waterfall chart (also known as a cascade chart or a bridge chart) is a special kind of chart that illustrates how positive or negative values in a data series contribute to the total. In other words, it's an ideal way to visualize a starting value, the positive and negative changes made to that value, and the resulting end value. In a waterfall chart, the first column is the starting value and the last column is the end value. The floating columns between them are the contributing positive or negative values.
Some people like to connect the lines between the contributions to make the chart look like a bridge (giving us the bridge chart name), while others leave the columns floating. Uses of waterfall chartsWaterfall charts are popular in the corporate and financial environment because they are very useful for a visualization of the positive and negative movements within a measured quantity or KPI, such as your Monthly Net Profit or Cash Flow. Other examples of quantitative analyses, where waterfall charts are used, include:
In a nutshell, use a waterfall chart whenever you want to show how a starting value increases or decreases through a series of positive or negative changes. Tip: While the most typical waterfall chart is the one with a starting and ending value, you can also create subtotals as visual milestones in the series. These show up as full columns. For example, you might want to use Net revenue and Gross Income as two checkpoints between Gross Revenue and Net income starting and ending values. How to create a waterfall chart in ExcelBefore Office 2016 creating waterfall charts in Excel was a notoriously difficult process. Note that I used the word "creating" and not "inserting". That's right - you did not insert a waterfall chart, you created it. ... using tutorialsTo create a waterfall chart in Excel 2013 and earlier, you had to define additional data series (with complicated formulas) in the data table and then make them invisible in the chart. And we're not talking about 1 invisible series. If the waterfall chart dipped below zero at one point, you needed at least seven additional series! Here are just some of the many tutorials on creating a waterfall chart in pre-2016 Excel:
... using templatesTo get around having to follow this long process every time, people often resorted to using a waterfall chart template:
Of course, using templates is not ideal. If your data has a different number of categories, you have to modify the template, which again requires additional work. Ideally, you would create a waterfall chart the same way as any other Excel chart: (1) click inside the data table, (2) click in the ribbon on the chart you want to insert. ... in Excel 2016Microsoft decided to listen to user feedback and introduced 6 highly requested charts in Excel 2016, including a built-in Excel waterfall chart.  No more templates, additional series, formulas or tinkering with the charts. 2 clicks and your awesome waterfall chart is inserted. Or is it? While the addition of waterfall charts in Excel 2016 is a great step forward, the current functionality still leaves much to be desired. 10 steps to a perfect excel waterfall chartHere are some ways that can help you create better Excel waterfall charts and some things that are still missing. 1. Remember to set the totalsLet's say we want to have this data table visualized with a waterfall chart: EBITDA of our fictional company for the years 2015, 2016 and the individual contributions of 7 small business units to the change from 2015 to 2016. This shouldn't be too hard. Click inside the data table, go to "Insert" tab and click "Insert Waterfall Chart" and then click on the chart. Voila: OK, technically this is a waterfall chart, but it's not exactly what we hoped for. In the legend we see Excel 2016 has 3 types of columns in a waterfall chart:
This is correct, but in the chart there are no Total columns, only Increase and Decrease. The first and last columns should be Total (start on the horizontal axis) and to set them as such, we have to double-click on each of them to open the Format Data Point task pane, and check the Set as total box. You can also right click the data point and select Set as Total from the list of menu options. Finally, we have our waterfall chart: 2. Ditch the clutter on your visualizationData visualization best practice is to remove ALL elements from the visualization that are not absolutely necessary (if you're interested, you can learn more about this in our webinar: Data visualization in depth). Similar to other Excel charts, the default Excel waterfall chart also suffers from having too much clutter. The legend, the vertical axis and labels, the horizontal grid lines - none of them contribute to the reader's better understanding of the data. If anything, they are a distraction. So, let's remove all unnecessary elements and write our key message to the title. It’s a shame that the chart title cannot be inserted automatically from a cell.
Great, this is much better. But it required additional work that would not be required if Excel defaults were better. In Zebra BI for Excel, we’ll show you how to do it better. 3. Break the axis to highlight contributionsThis limitation is especially noticeable in waterfall charts, because waterfall charts have essentially two different types of data:
A common problem is that contributions are often very small compared to totals. This is also apparent in our example (see image above). First, a point of order: this chart correctly visualizes the situation as the contributions really ARE that small compared to totals. Our 2016 result is essentially the same as our 2015 result. This visualization is also completely in line with IBCS Standards. However, users (and their bosses) are sometimes more interested in contributions than in totals and the relationship between the two. In this case the only viable option would be to break the vertical axis and have the totals start at some value larger than 0. Let’s say 35,000. This highlights individual contributions, but risks guiding unaware readers to false conclusions about the data. You can again resort to using tutorials and templates:
Another, somewhat simpler option is to do the following:
This is the chart we end up with: Now the contributions are much more prominent, but there's no obvious indication that the vertical axis does not start at zero which is really bad because the user does not draw the correct conclusion from the visualization. 4. Add relative contributions in percentagesWhen analyzing contributions you're often more interested in relative contributions (in percentages of the total) than in absolute contributions. Unfortunately, if you want to do that in a default Excel waterfall chart, you're out of luck - you're stuck with displaying absolute contributions only. Look to the end of this article to see how easy this is to do in Zebra BI. 5. Highlight differences between totalsAnother thing that you're not able to do in an Excel waterfall chart is display the total difference between 2015 and 2016 in our example. Sure, you can see in the chart that the 2016 column is higher than the 2015 column (especially now that we cut the vertical axis). But by how much? Unless you can do complex subtractions in your head, you don't know the exact number. There’s also no way to display the relative difference in percentage. Since this difference between totals is rather important, it's definitely a major feature that's missing in Excel waterfall charts. Click here to see how Zebra BI does it. 6. Use vertical waterfall chartsWe know from the How to Choose the Right Business Chart article that horizontal charts (i.e. the charts that have a horizontal category axis) are used to display time-related data. For everything else, we should use vertical charts instead. Waterfall charts are no exception. Strangely, in Excel 2016 there is no way to insert a vertical waterfall chart. While this feature has been requested, there's no indication whether it will be implemented and when. So if you wanted to visualize an income statement with a vertical Excel waterfall chart, you'd again have to resort to using templates and tutorials like this one … We prepared a demonstration in Zebra BI, so you can see how to create an income statement with vertical waterfall charts. 7. Add (some) subtotalsSince we're on the subject of visualizing income statements - in a typical income statement there are some categories that are actually sums of several other categories. For example: you can choose to calculate a sum of all Operating Expenses (OpEx). This better visualizes the relationship between "Revenue" and "Earnings before interest and taxes" (EBIT). EBIT = Revenue - OpEx. In a table this is easy to do - just write a formula and you're done. When you create a waterfall chart in Excel? Not so much. It's apparently so hard to do it manually that there's not a single tutorial or template available on the internet. You can, however, enter subtotals and designate them as such in your waterfall chart. However, you need to calculate them yourself to make sure they are correct. You can see how Zebra BI automatically creates subtotals in this handy animated gif at the end of this article. 8. Customize your chart with colorsThe default color scheme in Excel could be better. Visit the Chart Design tab and open the Change Colors gallery. Here, you can select a color palette. You can also choose a different theme on the Page Layout tab. To adjust how the colors are used, click the Colors button and select Customize Colors at the bottom of the list. You can set it up to display positive values in green and negative values in red, which is a common approach in financial reporting. 9. Turn connector lines on or offConnector lines connect columns to show the movements in values in the chart. You can turn them on or off by right-clicking a data series to open the Format Data Series pane, and checking/unchecking the Show connector lines box. 10. Scale your chartsFinally, we arrive at one major feature that's missing in Excel from the very beginning: scaling multiple charts. While this problem is not limited to waterfall charts, it's too important not to mention it here. Making sure that all related charts in a report or dashboard are on the same scale is one of the most important concepts in data visualization. If you don't synchronize scales, don't even insert the chartsAll too often you see two Excel charts side by side with completely different scales. While each of them is an adequate data visualization on its own, you must make sure they are scaled once you put them side by side! Otherwise don't even insert the charts and just leave the data in a table. So, how do you synchronize scales of Excel charts? While the procedure is not particularly hard, it is time consuming. It's a similar procedure that we used to break the axis. Say we have these two default Excel waterfall charts and we need to scale them: The first step is to re-add Vertical Axis on both charts.
This is what we have so far: Now we have to adjust the scale of the right chart to be the same as the left. Right click on the vertical axis and click "Format Axis...", then under Axis Options write "600" under Bounds >> Minimum. Remove vertical axis from both charts (right click on the vertical axis and click "Delete") and we have our correct visualization: OK, that wasn't too bad. Now what if you have a monthly report with 6 waterfall charts on it? Would you do this procedure for 6 charts every month when the data changes? I guess not. Of course, you can automate this, but you have to use VBA to do it. If you don't want to use VBA, maybe this article from 2012 by Jon Peltier will help you ... An even faster & more flexible waterfall chart 👉The new Zebra BI for Office is just been released. And it brings the latest reporting best practices to your Excel & PowerPoint. We’ve also prepared 20 financial & sales report templates based on the latest reporting best practices. Enjoy! There has to be a better way!If you don't have time to tweak default Excel waterfall charts and would like to add those advanced features to your waterfall charts with a few clicks, take a look at the animation below: I'm sure you'll agree that compared to other methods with following tutorials and using templates linked in this article this looks so easy it's scary! By the way, you can do this in Excel 2013 and Excel 2010 as well. Adding relative contributions and difference highlightsNow let's try to do something that you cannot do with default Excel waterfall charts - relative contributions in % and difference highlights: You can easily switch between variances displayed in relative and absolute values. You can also control whether an increase is a positive or negative event. We know that in some cases, like when it comes to costs, an increase is a negative development. Another useful feature we added is highlighting differences between starting and ending values. You can switch between relative and absolute variance or display them both for even better information density. In this example, you can see that the increase between EBITDA in 2015 and 2016 was 610k or 1.5%. Creating an income statement with vertical waterfall chartsHow about inserting two scaled vertical waterfall charts in an income statement? 2 clicks and you got it. 🙂 Pay attention to the subtotal visualization for OpEx! The cool thing about this subtotal is that it’s a calculation showing the total of all costs, making for a much more readable chart. Small multiples with waterfall chartsWe saved the best for last: here's 8 waterfall charts in 2 clicks: You only need to select a single cell in your data and select the horizontal waterfall chart type from the Contribution button. Zebra BI then creates several charts and gives you control over layout and everything else. Why should you avoid using this chart type if you are printing it as part of a report?Why should you avoid using this chart type if you are printing this as part of a report? It will use a lot of ink from your printer.
Is pie negative?Pie charts aren't designed to display negatives. Excel outputs the absolute value of all values. So, -20 is output in the pie chart as a slice with 20% of the pie's circumference. The only way to tell the number is negative is to reference the legend.
What should be your starting point in changing chart elements quizlet?What should be your starting point in changing chart elements? Select the chart, and then select the + sign to the right.
Which of the following chart types show how two numeric data series are related to each other?Scatter charts show the relationships among the numeric values in several data series, or plot two groups of numbers as one series of xy coordinates. Scatter charts are typically used for displaying and comparing numeric values, such as scientific, statistical, and engineering data.
|
If there are negative values in your data, why would you not want to use this chart type?
zusammenhängende Posts
Urheberrechte © © 2024 ketiadaan Inc.
