How to Fix Incorrect Cash Flow Projections and #REF! Errors
Question details
The user needs to fix a cash flow projection worksheet that displays inaccurate results, positive numbers in red, and #REF! errors.
- Product
- Spreadsheet
- Device & OS
- not provided
- Scenario
- Reviewing or updating a financial cash flow projection worksheet.
- Observed behavior
- The worksheet shows #REF! errors where formulas are broken, and positive figures are incorrectly formatted in red text.
Before making changes, create a copy of your cash flow workbook so you can safely test formula corrections and formatting changes without risking your original data.
Fix #REF! Errors and Cell References
Identify and replace broken cell references that cause #REF! errors in your cash flow calculations.
A #REF! error indicates that a formula refers to a cell that is not valid. This most commonly happens when rows, columns, or entire sheets referenced by the formula have been deleted.
Select the cell displaying the #REF! error and look at the Formula Bar at the top of the screen.
Find the part of the formula that says '#REF!'. This represents the deleted or moved data that the calculation relies on.
Highlight the '#REF!' text in the Formula Bar and click on the correct cell or range that contains the necessary cash flow data.
Press Enter to save the corrected formula. If applicable, drag the fill handle from the bottom-right corner of the cell to update adjacent periods in your projection.

Correct Conditional Formatting for Positive Figures
Adjust conditional formatting rules that are incorrectly displaying positive cash flow numbers in red.
Verify Cash Flow Calculation Logic
Ensure that opening balances, signs (inflows vs. outflows), and dates are correctly structured to prevent inaccurate totals.
Use WPS Spreadsheet for Accurate Financial Projections
WPS Spreadsheet provides powerful formula auditing tools and intuitive conditional formatting options to help you build and troubleshoot error-free cash flow projections effortlessly.
- 1. Open your workbook: Launch WPS Spreadsheet and open your cash flow projection file.
- 2. Audit formulas: Go to the Formulas tab and click 'Error Checking' to automatically find and navigate through #REF! errors.
- 3. Trace formula links: Use the 'Trace Precedents' tool under the Formulas tab to visually inspect how your cash flow cells are linked together with arrows.
- 4. Fix formatting: Navigate to Home > Conditional Formatting > Manage Rules to quickly correct any rules making positive numbers appear red.

Frequently Asked Questions
Why do #REF! errors suddenly appear in my cash flow spreadsheet?
#REF! errors occur when a formula refers to a cell that is no longer valid. This almost always happens if you delete a row, column, or worksheet that the formula was relying on for its calculation.
How can I quickly find all #REF! errors in a large workbook?
You can press Ctrl+F to open the Find dialog, type '#REF!' in the search box, and click 'Find All'. This will generate a list of every cell in your worksheet containing this error, allowing you to click through and fix them one by one.
Why are my positive cash flow numbers showing up in red or with parentheses?
This is typically caused by an incorrect Conditional Formatting rule or a custom Number Format applied to the cells. You can check the cell formatting by pressing Ctrl+1 and reviewing the 'Custom' category, or by checking Home > Conditional Formatting > Manage Rules.
Can I hide #REF! errors instead of fixing them?
While it is highly recommended to fix the underlying calculation in financial documents, you can hide errors by wrapping your existing formula in the IFERROR function, like this: =IFERROR(your_formula, 0). This will display a zero instead of the error.




