• Call Toll Free: 1-855-909-3300

How to Create Excel 2016 Waterfall Chart for Financial Statements

Business Professionals precipitate toward QuickBooks because numbers can have a tendency to blur together. This happens mostly with financial statements where Total Revenue blurs into Gross Profit, which then falls into Total Expenses and down to Net Income.

Excel 2016 introduces the use of Waterfall Chart feature to easily illustrate numbers. Since this feature is exclusively for Excel 2016, so you’ll be required to print your chart to a PDF or use some other means of sharing it. You can use other earlier versions of Excel.

To create a Waterfall Chart you need to design a Profit & Loss report in QuickBooks. For more information, see 7 steps to create profit & Loss report in QuickBooks.

To create a waterfall Chart in QuickBooks Desktop

Process 1: Minimize Report

Step 1: Click on the QuickBooks icon.

Step 2: Select Reports, Company & Financial, and then Profit & Loss Summary.

Step 3: Once the report appears on screen, click the Collapse button.

Step 4: Click the arrows next to Income, Cost of Goods Sold, Expense, and Other Income as shown in the figure. Click on other categories that appear on your report.

Step 5: Once you have got the report down to as few lines as possible. You can export the report to Excel. Your report will appear as follows:

Excel 2016 Waterfall Chart : export the report to Excel

You will notice that QuickBooks introduces some blank cells in the report. Thus, you can’t chart the data before fixing this issue.

Process 2: Remove Blank Cells in QuickBooks Report

Step 1: Click on the Income amount tab in your report.

Step 2: Select the entire report by pressing Ctrl-A.

Step 3: Copy the report to the Windows by pressing Ctrl-C.
You may also use the Copy command to serve the purpose.

Step 4: Right-click on any cell in the report and select the Values icon
OR
Select Paste Special and then double-click Values.

Step 5: Go to Excel’s Home menu, select Find & Select.

Step 6: Select Go To Special.

Step 7: Double-click on Blanks.

 Excel 2016 Waterfall Chart : Double-click on Blanks

Step 8: Press Ctrl-Minus to display the Delete
                                       OR
You can select the Delete submenu from Excel’s Home menu. Select Delete Cells.

Step 9: Double-click on the Shift Cells Left within the Delete.

Step 10: Click Format on Excel’s Home.

Step 11: Choose Autofit Column Width to widen the columns of the report.

Excel 2016 Waterfall Chart : Choose Autofit Column Width

Process 3: Clean up of Report

Step 1: Go to your worksheet and click cell A1.

Step 2: Press Ctrl-Shift-L
OR
Select the Filter command on Excel’s Data menu

Step 3: Click the filter arrow in cell B1.

Step 4: Go to the Search tab and type a left parenthesis.

Step 5: Click OK to display the rows within your data where column B is blank.

Step 6: Delete all visible rows except row 1.

Step 7: Press Ctrl-Shift-L
OR

Choose the Filter menu command again to display the remaining rows of data.

Step 8: Delete the Net Other Income

Step 9: Change the sign on Cost of Goods Sold and Expenses (as well as any other decreases) to be negative.

Excel 2016 Waterfall Chart : Change the sign on Cost of Goods Sold

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

Hope you liked this post, for more tips on QuickBooks and other business software, subscribe to our blog posts.

Leave a Reply

Techarex NetWorks Products