QuickBooks Tech Trends

Excel 2016’s Waterfall Chart for Financial Statements

Written by David Ringstrom

Many users gravitate toward QuickBooks because numbers can have a tendency to blur together. This can particularly be the case with financial statements where Total Revenue blurs into Gross Profit, which then cascades into Total Expenses and down to Net Income. Up until now it’s been difficult to present financial statements in chart form that can also give the user a sense of scale for the numbers. If you’re using Excel 2016, you can now use the Waterfall Chart feature to easily illustrate numbers – as long as you remain mindful of a key nuance to this type of chart. However, this feature is exclusive to Excel 2016, so you’ll need to print your chart to a PDF or use another means of sharing with someone that is using Excel 2013 or earlier.

The first step in creating a Waterfall Chart involves the Profit & Loss report in QuickBooks. For this purpose, we need the Summary version of the report. The steps differ somewhat between QuickBooks Desktop and QuickBooks Online, but you’ll see how to quickly distil either report down to its essence.

QuickBooks Desktop

Within your books, choose Reports, Company & Financial, and then Profit & Loss Summary. Click the Collapse button when the report appears onscreen. This will hide subaccounts from the report, but we still need to collapse the report further. As shown in Figure 1, click the arrows next to Income, Cost of Goods Sold, Expense, Other Income, and any other categories that may appear on your report. The goal is to get the report down to as few lines as possible. From there you’ll export the report to Excel. At this point your report will look like Figure 2.

Waterfall Chart for Financial Statements

Figure 1: Use the Collapse button to narrow down your report.

QuickBooks Waterfall Excel

Figure 2: Your report will appear as shown after exporting it from QuickBooks to Excel.

We can’t chart the data yet because of the blank cells that QuickBooks introduces into the report, but there is an easy fix, as shown in Figure 3 and Figure 4:

  1. Click on the Income amount within your report.
  2. Press Ctrl-A to select the entire report.
  3. Press Ctrl-C or use the Copy command to copy the report to the Windows
  4. Right-click on any cell in the report and select the Values icon, or select Paste Special and then double-click Values (double-clicking enables you to avoid clicking the OK button).
  5. On Excel’s Home menu, select Find & Select.
  6. Select Go To Special.
  7. Double-click on Blanks.
  8. Press Ctrl-Minus to display the Delete (Alternatively, you can select the Delete submenu from Excel’s Home menu, and then select Delete Cells.)
  9. Double-click Shift Cells Left within the Delete
  10. Click Format on Excel’s Home
  11. Choose Autofit Column Width to widen the columns of the report.

QuickBooks Waterfall

Figure 3: Use the Go To Special feature to find the blank cells.

QuickBooks Waterfall

Figure 4: We must remove the blank cells before creating the Waterfall Chart.

At this point we’re almost ready to chart our data but, as shown in Figure 5, we have one more series of clean-up steps to do:

  1. Click cell A1 within your worksheet.
  2. Press Ctrl-Shift-L, or choose the Filter command on Excel’s Data menu or under the Sort & Filter submenu on Excel’s Home menu.
  3. Click the filter arrow in cell B1.
  4. Type a left parenthesis in the Search
  5. Click OK to display the rows within your data where column B is blank.
  6. Keep row 1, but delete any other visible rows.
  7. Press Ctrl-Shift-L or choose the Filter menu command again to display the remaining rows of data.
  8. Delete the Net Other Income
  9. Change the sign on Cost of Goods Sold and Expenses (as well as any other decreases) to be negative.

QuickBooks Waterfall

Figure 5: The Filter command is a helpful feature that enables you to clean up your data.

You can optionally delete any redundant rows from your report, such as Net Other Income since its amount is identical to Other Income.

Save pagePDF pageEmail pagePrint page

About the author

David Ringstrom

David Ringstrom, CPA, is the president of Accounting Advisors, Inc., an Atlanta-based spreadsheet and database consulting firm he started in 1991. Throughout his career, David has spoken at conferences on Excel, and he currently leads dozens of webinars each year on Excel, QuickBooks, and other software. He has served as the technical editor for over 25 books, including several editions in Wiley’s QuickBooks for Dummies and Quicken for Dummies series. In addition to writing for QuickBooks and Beyond, David is the Tech Editor at Large for AccountingWEB and Going Concern. He also offers live webcasts and self-study courses through CPE Link. His freelance articles on spreadsheets have been published as far afield as Pakistan. During training sessions, you’ll often hear David state, “Either you work Excel, or it works you!”

1 Comment

  • It’s not hard to make a waterfall profit chart in Excel 2007; I’ve been doing it for years!

Leave a Comment