Open In App

How to Create a Waterfall Chart in Excel

Last Updated: 19 Dec, 2024

J

Jay Gala
Summarize
Comments
Improve
Suggest changes
Like Article
Like
Share
Report
News Follow

Waterfall charts are a powerful visualization tool used to illustrate the cumulative effect of sequential data points, such as profits, losses, or changes over time. Widely used in financial and performance analysis, these charts provide clear insights into the contributions of individual components to a total value. Whether you’re presenting a business report or analyzing operational results, a waterfall chart in Excel can make your data more comprehensible. This guide will cover how to create a waterfall chart in Excel, along with waterfall chart customization in Excel for tailoring visuals to your specific needs.

Disclaimer: Always ensure the accuracy of your data and double-check calculations to avoid misrepresentations in analysis.

How to Create a Waterfall Chart in Excel

Create a Waterfall Chart in Excel

How to Create a Waterfall Chart in Excel

Creating a Waterfall chart in Excel is simple and helps you visualize changes in values over time or categories. Follow these steps to create a waterfall diagram in Excel:

Note: Keep in mind that creating waterfall charts is easy in Excel 2016 and newer versions because they have a built-in option for it. In older versions, it’s more complicated and takes extra time since there isn’t a direct way to add a waterfall chart.

Step 1: Prepare Your Data

Organize your data in a table with categories and values. Include starting and ending totals, as well as gains and losses in between.

Ensure that positive values (e.g., sales) are entered as positive numbers and negative values (e.g., expenses) are entered with a minus sign.

How to Create a Waterfall Chart in Excel

Enter data into the sheet

Step 2: Insert the Waterfall Chart

Highlight the Data:

  • Select the table, including both the Category and Values columns.

Insert Chart:

  • Go to the Insert tab on the Excel ribbon.
  • In the Charts group, click the Insert Waterfall or Stock Chart dropdown.
  • Choose Waterfall from the options.
How to Create a Waterfall Chart in Excel

Insert>>Waterfall Chart

Step 3: Preview the Waterfall Diagram

Excel will automatically generate a basic Waterfall Chart based on your data.

How to Create a Waterfall Chart in Excel

Waterfall Chart Created

Customizing a Waterfall Chart in Excel

After learning how to create a waterfall chart in Excel, you can enhance its clarity and appeal with waterfall chart customization in Excel. Follow these steps:

Step 1: Set the Starting and Final Balances as Totals

  • Click on the Starting Balance bar in the chart.
  • Right-click and select Set as Total. This will anchor the starting point of the chart to the baseline.
  • Repeat the same steps for the Final Balance bar to mark it as the ending total.
How to Create a Waterfall Chart in Excel

Create a Waterfall Chart in Excel

Step 2: Adjust the Colors for Clarity

  • Click on any bar representing a positive change (e.g., January Sales), then right-click and choose Format Data Series.
  • Under the Fill & Line options, change the color to green (or another color representing a gain).
  • For negative changes (e.g., January Expenses), change the color to red to indicate a loss.
  • You can choose a different color for the Starting Balance and Final Balance bars to make them stand out (e.g., blue).
How to Create a Waterfall Chart in Excel

Change the color

Step 3: Add Data Labels

  • Right-click on any bar in the chart.
  • Select Add Data Labels to display the numerical values on the bars.
  • Adjust the position of the labels (e.g., Above, Below) for better readability.

Step 4: Adjust the Gap Width (Optional)

  • Click on any bar in the chart, right-click, and choose Format Data Series.
  • Reduce the Gap Width to make the bars wider and improve the visual appearance.
How to Create a Waterfall Chart in Excel

Right-Click and Select Format Axis >> Adjust the Gap Width

Step 5: Add a Chart Title

Click on the chart title and change it to something descriptive, like “Company Profit Analysis – Waterfall Chart“.

How to Create a Waterfall Chart in Excel

Add a Chart Tittle

Step 6: Modify the Y-Axis Scale if Needed

  • Click on the Y-axis, right-click, and choose Format Axis.
  • Adjust the minimum and maximum values for the Y-axis to better fit your data range.
How to Create a Waterfall Chart in Excel

Right – Click >> Modify the Y-Axis Scale

Step 7: Review and Save Your Work

  • Review the final chart to ensure all details are correctly displayed.
  • Save your Excel file to preserve the chart.
How to Create a Waterfall Chart in Excel -

Review and Save your Work

The final waterfall chart will clearly show the financial journey from the starting balance to the final balance, with incremental gains and losses for each month displayed in between. This visualization helps you easily understand the impact of each financial event on the overall profit.

Also Read:

Conclusion

Creating a Waterfall chart in Excel is a straightforward way to analyze and visualize changes in values over time or across categories. This powerful tool helps you clearly identify contributions to totals, whether in financial data, performance metrics, or other key analyses. By knowing Excel’s built-in features, you can customize your chart to suit your needs and present data in an easy-to-understand format. Mastering the Waterfall chart will not only enhance your data analysis but also improve the clarity and impact of your presentations.

How to Create a Waterfall Chart in Excel – FAQs

What is waterfall chart in Excel?

Waterfall charts in Excel are a way to visualize how a series of positive and negative values affect a starting amount. Each bar represents a change, with one color for increases and another for decreases. The bars appear to float above a baseline, making it easy to see how each value adds up to the final total.

These charts are often used in finance to track things like revenue and expenses, helping you understand how different factors impact the overall result.

How do you create a waterfall structure in Excel?

Just click and drag to highlight the cells with the data you want to include, making sure to select both the categories and their matching values. Once your data is selected, go to the “Insert” tab in the Excel toolbar. There, you’ll see a variety of chart options to choose from.

What is the purpose of a waterfall plot?

A waterfall plot is commonly used to illustrate how two-dimensional data evolves over time or in relation to another variable, like rotational speed. It’s also frequently utilized to represent spectrograms or cumulative spectral decay (CSD).

How to do a Waterfall Chart in Excel?

Step 1: Select your data 

Step 2: Click Insert > Insert Waterfall or stock chart > Waterfall chart.

What are the benefits of using the Waterfall Chart?

The main advantage of using a waterfall chart is that it has a clean and uncomplicated format which makes performance analysis quite easy and helps the user to observe the cumulative effect of individual changes.

What is Pocket Price Waterfall Chart?

In the waterfall chart, only two highlighted columns are present (Start and finish), and the Pocket Price Waterfall chart has many highlighted columns.

What are the Connector lines in the Chart?

Connector lines Connect the end of each column to the beginning of the next column to maintain the flow of data in the chart.



Article Tags :

J

Jay Gala
News
Improve
Discuss
Do you want to advertise with us?Click here to know more

R

Riya

Interactive Waterfall Chart Dashboard in Excel

article_img
In 2016, they introduced the Excel waterfall chart. The chart illustrates how the value varies over time, either increasing or decreasing. Positive and negative values are by default color-coded. Unfortunately, Excel 2013 and older versions do not support the waterfall chart type. An Excel waterfall chart sometimes referred to as an Excel bridge chart is a unique kind of column chart used to display how the starting position of a certain data series varies over time, whether it be a growth or decline. The first value is represented by the first column in the waterfall chart, and the last value is represented by the last column. They reflect the total amount in their entirety. How does a waterfall chart look?How Do I Make an Excel Waterfall Chart? Example 1: There is a company named "ABC" showing its sales data in the table below. Create a waterfall chart for this. MonthJanuaryFebruaryMarchAprilMayJuneJulyAugustSeptemberOctoberNovemberDecemberSales10000950012000800090001000010500999970009500800011000 Answer: Step 1: Enter all the data in an excel sheet. Data in an excel sheet Step 2: Select all the data. Go to the insert tab on the top of the ribbon where then in the charts group se
Read More

R

Riya

Creating a Gantt Chart With Milestones Using a Stacked Bar Chart In Excel

article_img
One of the most common and effective methods of displaying activities (tasks or events) plotted against time is a Gantt chart, which is frequently used in project management. On the left side of the chart is a list of the activities, and at the top is a suitable time scale. A bar is used to symbolize each activity, and the location and length of the bar correspond to the activity's beginning, middle, and finish dates. The following elements are crucial to any effective Gantt chart: The task list, which can be divided into groups and subgroups, runs vertically along the left side of the Gantt chart to define project activity.Timeline: Displays months, weeks, days, and years horizontally across the top of the Gantt chart.Dateline: On a Gantt chart, a vertical line displays the current date.Bars: On the right side of the Gantt chart, horizontal markers indicate tasks and display status, length, and start and finish dates.Milestones: Yellow diamonds that identify significant occasions, dates, choices, and outputsDependencies are thin grey lines connecting activities that must occur in a specific order.The percentage of work that has been completed or the color of the bars can be used t
Read More

R

Rishabh Chakraborty

Radar Chart or Spider Chart in Excel

article_img
Radar Chart is a pictorial representation of multivariate data. Multivariate data analysis in statistics is nothing but dealing with more than one outcome or observations. Radar graphs can be of two dimensions, three dimensions, or more on the basis of the multiple comparable variables used. The variables are represented on the axis starting from the same points with equal intervals on the axes. The number of axes in a radar graph solely depends on the number of variables used. The Radar Chart has various other names like spider chart, web chart, spider web chart, cobweb chart, irregular polygon, star chart, Kiviat Diagram, etc. The data from the observations in the form of tables are plotted on each axis and by joining all these points in the axes a polygon type structure is formed. So, the number of polygons is dependent on the number of observations. In this article, we will see how to plot a Radar Chart in Microsoft Excel for a given data set using two examples. Example 1 : Consider the table shown below which consists of the data of two Geek students who enrolled in our various courses. Our mentors have rated them on the basis of the student's performance in the individual
Read More

R

Ria Rawal

How to Create a Dynamic Pie Chart in Excel?

In Excel, Pie-chart is a graphical representation of different sections or sectors of a circle based on the proportion, it holds from the complete quantity. Pie-charts are generally categorized into two types: Static Pie-chart: A pie-chart created with static or fixed input values is known to be a static pie-chart. The values of these types of pie-charts don't change over time.Dynamic Pie-chart: A pie-chart created with dynamic or changing input values is known to be a dynamic pie-chart. The values of these type of pie-charts changes over time. In this article, we will discuss how to create a dynamic excel pie chart? Creating a dynamic pie chart in Excel means creating a pie-chart that automatically(dynamically) gets updated when new values are entered into the table, which is used for creating the pie-chart. Creating a Dynamic Excel Pie Chart in Excel is very easy. We can create a dynamic pie chart using two different methods: Using offset formulaUsing TableCreating a dynamic pie chart using offset formula We can create a dynamic pie chart using the offset formula. So to this follow the following steps: Step 1: Create a table and insert values in it. Apply the offset formula to ge
Read More

A

AYON KARMAKAR

How to Create a Gauge Chart in Excel?

article_img
Gauge chart is also known as a speedometer or dial chart, which use a pointer to show the readings on a dial. It is just like a speedometer with a needle, where the needle tells you a number by pointing it out on the gauge chart with different ranges. It is a Single point chart that tracks a single data point against its target. Steps to Create a Gauge Chart Follow the below steps to create a Gauge chart: Step 1: First enter the data points and values. Step 2: Doughnut chart(with First table values). Select the range B2:B7Then press shortcut keys [Alt + N + Q and select the Doughnut] or Go to Insert -> Charts -> Doughnut (With these steps you will get a blank chart). Step 3: Delete or hide the left portion of the Doughnut chart. Select the chart (left click on the chart) & double click on the left portion.Then right click -> format Data point... -> paint -> fill -> select "No fill". Step 4: Change the Angle Select the legend Icon & Change the angle to 271.Then you can change the doughnut hole size. Step 5: Set the Border & color Go to fill and line -> border -> No line.You can also change the colors of each data point. Step 6: Creating a pointer
Read More

N

Nandini Gujral

How to Create Pivot Chart from Pivot Table in Excel using Java?

A Pivot Chart is used to analyze data of a table with very little effort (and no formulas) and it gives you the big picture of your raw data. It allows you to analyze data using various types of graphs and layouts. It is considered to be the best chart during a business presentation that involves huge data. To add a pivot chart to an Excel worksheet, you need to use the "WorksheetChartsCollection.add" method.  Before Creating a Pivot Chart, one first needs to go through how to Create Pivot Table in Excel using Java. Now let's discuss the steps to create a pivot chart in an Excel file in Java using Free Spire.XLS for Java API. How to Create a Pivot Table in Excel using JavaStep 1: Load the Excel fileWorkbook workbook = new Workbook() String workbookName = "Geeks_For_Geeks.xlsx"; workbook.loadFromFile(workbookName); Step 2: Get the first worksheetWorksheet sheet = workbook.getWorksheets().get(0); Step 3: Get the first pivot table in the worksheetIPivotTable pivotTable = sheet.getPivotTables().get(0); Step 4: Add a clustered column chart based on the pivot table to the second worksheetChart chart = workbook.getWorksheets().get(1).getCharts().add(ExcelChartType.ColumnClustered, pivotTa
Read More

A

Ankur Pal

How to Create a Dynamic Chart with Drop down List in Excel?

article_img
The Dynamic Charts are the chart that gets updated itself when the range of underline data changes. In these types, of charts the dynamic range is used as the source data of the chart. So, as the data changes the dynamic range gets updated instantly which further updates the chart according to the new data range. These charts are very useful when we have a very large dataset and we need to perform a comparative analysis of that dataset. For example, in analyzing the revenue generated by different products of the company over a year. In this tutorial, we will look into a Dynamic chart with a drop-down list which will get updated itself, when we change the data of the drop-down list. Making Dynamic Chart with Dropdown List Now we are going to create a dynamic chart with a drop-down list for the revenue generated by different apps over a period of six months. (assume revenue in Cr.) Step 1: Create Dataset In this step, we will be inserting random revenue generated by various apps of google into our excel sheet. Below is the screenshot of the random data that we will use for the dynamic chart. Step 2: Insert a drop-down list In this step, we will insert a drop-down list. For this go t
Read More

J

Jay Gala

How To Create a Tornado Chart In Excel?

article_img
Tornado charts are a special type of Bar Charts. They are used for comparing different types of data using horizontal side-by-side bar graphs. They are arranged in decreasing order with the longest graph placed on top. This makes it look like a 2-D tornado and hence the name. Creating a Tornado Chart in Excel: Follow the below steps to create a Tornado Chart In Excel: Step 1: Open Excel and Prepare your Data table. Start writing your data in a table with appropriate headers for each column. Once this is done, make sure you select either of the columns and set all of its values to negative i.e., if the original value in Header Xyz is 2000, then change it to negative two thousand or "-2000" as shown below. Step 2: Select the entire table using L-Shift and the arrow keys. Then, go to the Data section from the top bar and select "Sort." Now, set the "Sort by" value to the Header of your Negative column and set the "Order" as "Largest to Smallest" as shown below, and click OK. Sorting the data Step 3: Representing the above data in Bar Chart Sorted data Now, select the entire table. Go to "Insert" and select Bar. Under "Bar", select the Stacked bar option and wait for your tornado char
Read More

J

Jay Gala

How to Create a Waffle Chart in Excel

article_img
The Waffle Chart emerges as a captivating masterpiece, transforming raw numbers into visual symphonies. Whether you'll illustrate work progress as a percentage of completion or showcase the balance between goals achieved and targets set, the waffle Chart unveils a canvas of insights with a mere glance. What is a Waffle ChartA waffle chart is a square grid chart that fills up to a certain percentage. It is used to visualize different types of data in a very simple yet effective way. One similar example of a waffle chart could be the Battery symbols on our modern-day smartphones. A waffle chart is a wonderful method of representation of numeric data, and it can be used as a replacement for a Pie Chart. Advantages of Waffle Chart The Waffle Chart has a number of advantages, rendering your data visualization journey not only captivating but profoundly effective. A few advantages are mentioned below: Visual interesting:Waffle Charts are like eye candy for your data. They turn boring numbers into a colorful, eye-catching pattern that's impossible to ignore. Numbers that speak loud and clear: They're like the superheroes of clarity. You can understand them with just a quick glance. Find
Read More

J

Jay Gala

How to Create a Tolerance Chart in Excel?

article_img
A tolerance chart shows how a particular data item compares to the maximum and minimum permissible values. In this article, we'll analyze the average results of the students of a class using a tolerance chart. Tolerance Chart Steps for creating a Tolerance Chart Follow the below steps to create a Tolerance chart in Excel: Step 1: The first step is to make your data table. This table must have three main attributes, Minimum, Maximum, and Range. Here, the range is the difference between the Maximum and the Minimum values. You can use this formula and apply it to all corresponding cells. Take a look at the following image for better clarity. Step 2: Next, select the entire table and click on Insert. Note: The menus may look different for different versions of Excel, but the basic principles remain the same. Step 3: Find Stacked Area Chart under the Charts sub-section, and click on it. You may delete the legend on the right-hand side if you want. Stacked Area Chart Step 4: Now, from the table, select all the values from the 'min' row. Then click Ctrl + C, to copy. Now, click on the chart and hit Ctrl + V, to paste. This will result in an additional layer being added to the stacked cha
Read More
three90RightbarBannerImg
We use cookies to ensure you have the best browsing experience on our website. By using our site, you acknowledge that you have read and understood our Cookie Policy & Privacy Policy
Lightbox