How to Preserve Excel Formulas and Values When Deleting Source Data
Question details
The user wants to keep the calculated total in a worksheet intact even after the source data used in the calculation formula is deleted.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Tracking stock dividends where old dividend rates are deleted or updated, but historical total values need to remain unchanged.
- Observed behavior
- Deleting the source dividend value causes the formula calculating the total to return zero or an error, erasing the historical result.
Ensure you clearly identify which cells contain your historical formulas and which contain the source data you plan to delete. If you are going to use VBA, remember to save your workbook as a Macro-Enabled Workbook (.xlsm).
Convert Formulas to Static Values (Recommended)
The simplest way to preserve a calculated result before deleting its source data is to convert the formula itself into a static number.
Highlight the column or specific cells containing your calculated totals (e.g., Column D).
Press Ctrl+C to copy the selected cells to your clipboard.
Right-click the same selection and choose "Paste as Values" (the clipboard icon with 123) from the context menu. The formulas are now replaced by their static calculated results.
You can now safely select and delete the source data in the other columns without affecting your preserved totals.
Create a Historical Dividend Lookup Table
Instead of deleting old data, store all dividend rates with their effective dates in a separate table and use a lookup formula.
Automate Value Preservation with VBA
Use a Worksheet_Change event macro to automatically freeze the total as a static value when a dividend is deleted, and restore the formula when a new one is typed.
Manage Your Financial Spreadsheets with WPS Office
Looking for a lightweight, free alternative to Microsoft Excel for tracking stocks and dividends? WPS Spreadsheet offers a highly compatible, easy-to-use interface that supports advanced formulas, lookup tables, and even VBA macros. Transition seamlessly without losing your formatting or data.
Frequently Asked Questions
Why does my total change to zero when I delete a cell referenced in the formula?
Excel formulas recalculate automatically based on the current values in their referenced cells. If you delete a value, Excel treats that empty cell as a zero. In a multiplication formula, anything multiplied by zero results in zero.
How can I paste values using keyboard shortcuts?
After copying your cells with Ctrl+C, you can use the keyboard shortcut sequence Alt > H > V > V to paste them as static values. This instantly removes the underlying formulas while keeping the numbers.
Can I keep the formula but hide the zero if the source data is empty?
Yes, you can use the IF function to handle blank cells. For example, `=IF(ISBLANK(C3), "", B3*C3)` will display an empty cell instead of a zero if the dividend cell (C3) is cleared. However, this will not preserve your historical totals.
Will pasting values permanently delete my formulas?
Yes, once you paste as values, the selected cells will contain static numbers instead of formulas. You will need to manually re-enter or copy the formula back into the cell when you are ready to perform dynamic calculations again.
