Creating a waterfall chart in Excel 365 is a great way to visualize cumulative data, such as sales performance over time or project progress. This step-by-step guide will walk you through the process, ensuring you create an engaging and informative chart that effectively communicates your data.
Before we dive into the specifics, make sure you have Excel 365 open and your data ready. For this example, let's assume you have a table of monthly sales figures, with each month's sales in a separate column.
Preparing Your Data
Before creating the waterfall chart, you need to prepare your data in a specific format. This format will help Excel understand how to calculate the cumulative totals for each month.
Assuming your data is in columns A to D, with headers "Month", "Sales", "Profit", and "Expenses", follow these steps:
Adding a Running Total Column
In cell E2, enter the formula "=SUM($B2:B2)" and drag it down to E13. This will create a running total of sales for each month, starting from January.
This formula works by adding the current month's sales to the total sales from the previous months. The "$" sign in the formula ensures that the range "B2:B2" remains constant as you drag the formula down, while "B2:B13" adjusts to include the current month's sales.
Adding a Cumulative Total Column
In cell F2, enter the formula "=SUM($E$2:E2)" and drag it down to F13. This will create a cumulative total of sales for each month, including the current month's sales.
The "$" sign before "E2" ensures that the range "E2:E2" remains constant as you drag the formula down, while "E2:E13" adjusts to include the current month's running total.
Creating the Waterfall Chart
Now that your data is prepared, you can create the waterfall chart. Follow these steps:
Selecting Your Data
Select the range "A1:F13", including the headers and the data. Make sure to include the columns you've just created for running totals and cumulative totals.
Selecting the entire range ensures that the chart includes all the necessary data for the waterfall effect, including the connectors between the bars.
Inserting the Waterfall Chart
Go to the "Insert" tab in the Excel ribbon and click on "Waterfall" in the "Charts" group. This will insert a waterfall chart based on the selected data.
The chart should now display your data with a waterfall effect, showing the cumulative sales for each month. The connectors between the bars represent the changes in sales from one month to the next.
Congratulations! You've just created a waterfall chart in Excel 365. This chart is an excellent way to visualize cumulative data and track progress over time. Now that you know how to create a waterfall chart, you can use this technique to create engaging and informative charts for various data sets.