Waterfall charts are a great way to visualize how different parts contribute to the whole. They can be used to show how a budget is allocated, how sales contribute to revenue, or how different expenses contribute to a total cost. In this article, we’ll show you how to construct a waterfall chart in Excel. Keep reading to learn more bout this useful visual chart type!
How do you create a waterfall chart in Excel?
To create a waterfall chart in Excel, you need to first gather your data in two columns: one for input values and one for output values. In our example below, we will use monthly sales figures as our input values and total profit as our output value.
Next, we will create three new columns: one for positive values, one for negative values, and one for totals. In our example, column A will contain our input values, column B will contain our positive values (i.e., monthly sales), column C will contain our negative values (i.e., monthly costs), and column D will contain our total profit (i.e., monthly sales – monthly costs).
Now it’s time to create our waterfall chart! We will do this by using Excel’s built-in “waterfall chart” function. To begin, select your data range (A1:D9 in this example) and insert a waterfall chart by clicking on the Insert tab > Charts > Waterfall Chart. Excel should automatically populate your chart with default settings.
How do you add labels to a waterfall chart in Excel?
You can add labels to a waterfall chart in Excel by following these steps:
- Create your waterfall chart as usual.
- Select the text box next to the first label on the chart. This will select all of the text boxes for that row of labels.
- Right-click and choose Format Object from the menu that appears.
- In the Format Text Box dialog box, click on the Font tab if it’s not already selected. Make sure that you have selected Italic from the Effects drop-down list; then click on OK to close the dialog box.
How do you make the columns in a waterfall chart equal in width?
To make the columns in a waterfall chart equal width, you will need to first determine the total width of your chart. Once you know the total width, you can then divide it by the number of columns (plus one for the title) to get the column width. To create your waterfall chart, follow these steps:
Create a new Excel worksheet and enter headings in row 1: “Label,” “Type,” and “Amount.”
In cell A2, enter “Total Income.”
In cell B2, enter “Total Expenses.”
In cell C2, enter “Net amount of income.”
Select cells A2 through C2 and insert a waterfall chart type by clicking on the Insert tab > Charts > Waterfall Chart icon or typing Ctrl + W on your keyboard). Your cursor should now be in cell D1.
Enter the title for your waterfall chart in cell D1 (“Waterfall Chart Demo”).
Drag Cell A2’s handle down to populate Cells A3 through A10 with labels representing different types of income (e.g., Salary, Commission, Interest).
Drag Cell B2’s handle down to populate Cells B3 through B10 with labels representing different types of expenses (e.g., Rent, Utilities, Food).
Starting in Cell C11, begin entering positive and negative values corresponding with each label type as shown below:
To ensure that all columns are equal width, select any column other than Column D (the title column ) and drag its right border until it is flush with Column E.
How do you format this kind of chart?
Begin by creating your basic column chart as you normally would. In the first row of your data, enter the labels for each column. For the second row, enter the values for your original data set. In subsequent rows, enter the decrease (or increase) in value for each subsequent set of data. Finally, enter the new values in the last row.
Once you have created your basic column chart, select it and navigate to Charts > Waterfall Chart > More Options. This will open a dialog box with additional options for formatting your waterfall chart. Selecting Show End Points will add lines to represent each end point of your waterfall graph (original value, decrease/increase in value, and new value). You can also adjust the colors and widths of these lines as desired.
Waterfall charts are important because they help to overview the cumulative effect of individual contributions to a larger goal. They can be used to track progress over time and to illustrate how different components contribute to an overall result.
