Use waterfall charts in Sheets

Help Center

AI Assistant

Help Center AI Assistant is now available

Got questions about Lark? Use our AI chat to find the answers.

Click and hold to drag

Got It

Try Now

Use waterfall charts in Sheets

2 min read

I. Intro

Waterfall charts are made up of multiple columns that use color and direction to differentiate between positive and negative values, with positive values stacking upward and negative values stacking downward.

They can be used to show the cumulative effect of positive and negative values on the final value, and are often used to analyze cash flow, performance reviews, and demographic changes.

II. Examples

Type	Data and chart
Sequential waterfall chart	250px\|700px\|reset 250px\|700px\|reset
Stacked waterfall chart	250px\|700px\|reset 250px\|700px\|reset

III. Steps

Select data source

Source data can contain multiple columns/rows of values (series) and one column/row of text (category). The first row is for labels and can be omitted. The columns of values can be positive or negative.

	Text 5	250px\|700px\|reset
Text 1	1
Text 2	2
Text 3	-3
Text 4	4

Change chart style

This article is about waterfall charts. For details on using charts in general, see Create and edit charts.

Basic chart settings Go to Setup > Chart type > Waterfall chart. Waterfall charts can be stacked either horizontally or vertically: The former is generally used for a single series and the latter for multiple series. Set the color scheme in Themes, which supports Gradient fill (as below). 250px\|700px\|reset Under Options, set whether to show chart title or legend, as well as: Total label: The total value for the column, and is only displayed on charts with multiple series. Connector lines: Lines that connect a column to the next column to help users better interpret the chart.	250px\|700px\|reset
Data settings When the source data contains duplicate category labels, you can select Aggregate under Setup > Range to enable aggregation by unique value. Set a subtotal: The subtotal is the net value after all positive and negative values have been added together. The subtotal of a waterfall chart is calculated from left to right, with the value furthest on the right being the final value. By default, waterfall charts display the final value on the right side of the chart, but you can deselect Add subtotal after last value in series to hide it. If the first column of the source data is the initial value, select Use first value as subtotal to display the initial value on the left side of the chart. This is useful for comparing the initial and final values. 250px\|700px\|reset The data ranges of the series and categories are automatically determined based on the number of rows and columns. If the chart generated is not as expected, you can click Switch rows/columns to adjust it.	250px\|700px\|reset
Series settings - positive/negative values Waterfall charts have three legend types: positive, negative, and subtotal. When a chart contains multiple series (multiple rows/columns of values), each series will have a corresponding positive and negative legend. By default, the names are Positive (Legend Name) and Negative (Legend Name). You can find more settings in Customize > Series. Select series: When you have multiple series, you can select a series to adjust the settings for its positive and negative values. Positive/negative value settings: You can individually set the legend names for both positive and negative values, as well as set the border and fill styles. In the chart below, the negative values are highlighted with a green border color. 250px\|700px\|reset Show data labels: Select this option to display the series' specific values. The label position, text, and formatting can also be set.	250px\|700px\|reset
Series settings - subtotal Settings for subtotals: Go to Customize > Series > Settings for subtotals to set the subtotal color and border style, as well as set whether to show data labels and connector lines. Add subtotal: In addition to the initial and final values, you can also set subtotals for the values in between. Click Add subtotal, then select the category you want to add a subtotal for. There are two ways to add a subtotal: After: This will retain the original column and add a new subtotal to the right, which is calculated from left to right. You can also use custom subtotal labels. Replacing: This will replace the original column, and its value is only a summary of the column itself, and not a cumulative calculation. Tip: If a subtotal has already been calculated for your original data, you can use Replacing to directly replace a category, and if a subtotal has not yet been calculated for the original data, then After can be used to calculate that value.	250px\|700px\|reset

Basic chart settings Go to Setup > Chart type > Waterfall chart. Waterfall charts can be stacked either horizontally or vertically: The former is generally used for a single series and the latter for multiple series. Set the color scheme in Themes, which supports Gradient fill (as below). 250px\|700px\|reset Under Options, set whether to show chart title or legend, as well as: Total label: The total value for the column, and is only displayed on charts with multiple series. Connector lines: Lines that connect a column to the next column to help users better interpret the chart.	250px\|700px\|reset
Data settings When the source data contains duplicate category labels, you can select Aggregate under Setup > Range to enable aggregation by unique value. Set a subtotal: The subtotal is the net value after all positive and negative values have been added together. The subtotal of a waterfall chart is calculated from left to right, with the value furthest on the right being the final value. By default, waterfall charts display the final value on the right side of the chart, but you can deselect Add subtotal after last value in series to hide it. If the first column of the source data is the initial value, select Use first value as subtotal to display the initial value on the left side of the chart. This is useful for comparing the initial and final values. 250px\|700px\|reset The data ranges of the series and categories are automatically determined based on the number of rows and columns. If the chart generated is not as expected, you can click Switch rows/columns to adjust it.	250px\|700px\|reset
Series settings - positive/negative values Waterfall charts have three legend types: positive, negative, and subtotal. When a chart contains multiple series (multiple rows/columns of values), each series will have a corresponding positive and negative legend. By default, the names are Positive (Legend Name) and Negative (Legend Name). You can find more settings in Customize > Series. Select series: When you have multiple series, you can select a series to adjust the settings for its positive and negative values. Positive/negative value settings: You can individually set the legend names for both positive and negative values, as well as set the border and fill styles. In the chart below, the negative values are highlighted with a green border color. 250px\|700px\|reset Show data labels: Select this option to display the series' specific values. The label position, text, and formatting can also be set.	250px\|700px\|reset
Series settings - subtotal Settings for subtotals: Go to Customize > Series > Settings for subtotals to set the subtotal color and border style, as well as set whether to show data labels and connector lines. Add subtotal: In addition to the initial and final values, you can also set subtotals for the values in between. Click Add subtotal, then select the category you want to add a subtotal for. There are two ways to add a subtotal: After: This will retain the original column and add a new subtotal to the right, which is calculated from left to right. You can also use custom subtotal labels. Replacing: This will replace the original column, and its value is only a summary of the column itself, and not a cumulative calculation. Tip: If a subtotal has already been calculated for your original data, you can use Replacing to directly replace a category, and if a subtotal has not yet been calculated for the original data, then After can be used to calculate that value.	250px\|700px\|reset