![]() ![]() The formulas I created were hideous love-children of Array Formulas, IF formulas, SIGN formulas, ROW formulas, etc… Suffice to say, sub-optimal. So I created an in-between version with special formulas for the first and last row, and then general formulas for the middle rows. I got bogged down in seriously wacky IF statements and decided the ROI wasn’t worth it (I still think it’s possible tho!). I embarked on this project trying to create one single formula that I could drag across and down my columns and rows that would just fill in the values. Step 2: Use MIN and MAX formulas to shape the data So there are more variations for our formulas to handle, which makes it more complex than the first example above. Cost of Sales in above chart), as well as positive- and negative-value columns above and below the axes. Revenue in above chart), negative-value columns that cross the x-axis (e.g. Well, you’ll notice we have positive-value columns that cross the x-axis (e.g. ![]() It doesn’t look too different from the simple one above, so what’s the big deal? ![]() In this scenario, let’s look at what happens if we have negative columns or columns that span the x-axis. You should also remove the legend as the series labels are essentially meaningless.įeel free to make a copy of the Simple Waterfall Chart Template ( File > Make a copy.). Now it’s simply a matter of selecting suitable colors for the other series, formatting axes and titles. Step 6: Format the chart for presentation ![]() Make sure you select the stacked column chart: Highlight this new table and create a chart: Insert > Chart. The completed table should look like this now (click to enlarge): The Positive and Negative columns are left blank.įor the middle rows, rows 3 to 7 in this example, put this IF formula into the Base column: In the first and last rows, rows 2 and 8 in this example, I’ve put 0 in the Base column and the count value in the Endpoints column. I’ve added color-coding to distinguish the different parts of the table. We can do better than that! Step 2: get the data into the right shapeĬreate a new data table for the waterfall chart.ĭirectly adjacent to the original data, make a copy of the row labels, then add four new columns: Base Endpoints Positive Negative, as shown below:Īdd the following formulas to the table (click to enlarge): We can’t change the color of the bars, since they’re all part of the same series: Well, we end up with a standard column chart, which doesn’t show what’s happening as clearly as the waterfall chart. What happens if we simply create a chart from the data table above? The data for our simple waterfall chart looks like this initially: Step 1: What does our waterfall chart data look like? In this case, all the bars are above the x-axis. Templates are available for all three methods, with links at the end of each section and at the end of this post. I’ll show you how to create both of these cases, starting with the easier, positive-bar case.Īfter creating the simple and complex versions manually with formulas, I’ll show you some Apps Script code to automate the majority of the process and massively speed up creating complex waterfall charts. Notice that all of the bars are above the x-axis (Case 1), which makes the data set up vastly simpler than the case when we have a mix of bars above and below the x-axis, or spanning the x-axis (see Case 2 below). The waterfall chart above is relatively easy to create in Google Sheets but does still require some data wrangling to set it up. It shows the number of staff in our department at the start of the year (left grey bar), the number of people added from other departments or as new hires (green bars), the number of people who left (red bars) and finally the balance which is the headcount at the end of the year (right grey bar). The following waterfall chart shows the headcount changes for a department, visually depicting the cumulative effect of the additions and deletions to the start value: They show the cumulative effect of a series of positive and/or negative values on an initial starting value. In this post, we’ll look at how to create a waterfall chart in Google Sheets. This original post that follows was first published in late 2016, and I’m leaving it here for anyone who wants to look under the hood at how waterfall chart data is constructed and how to do that using apps script. Now you simply highlight your data, click Insert > Chart and under the Chart type picker choose “waterfall”, as shown in the following image: Update December 2017: Google has added Waterfall Charts to the native charts in the Chart Tool of Google Sheets, obviating the need for you to manually create your waterfall charts (or use apps script) per my original post. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |