Making previous cells static in Excel

Occasional Visitor

Hello there,


I run data through a program, and afterwards, it inputs the values to Microsoft Excel, displaying an 18 month table. The problem, however, is that data from several months ago might change randomly (be that from data changing, or a correction), and the goal is to keep the original values at what they were prior to changing (keep previous values historical to the months they were assigned). I will attach an image as an example. You will see the highlighted portion of what I would like to have change month by month when I generate the report, but I do not want the previous months to change.




So to sum it up, how can I keep previous cells static (for previous months), while just having the current month change. The tricky part will be having each new month bump the old one up the table, so to have all months move up one cell for the next month, while remaining the same. 


Hopefully I am being concise enough here. I have seen some online suggestions about pressing F4, but it does not seem to work, especially when I am trying to move each month up the table to allow the new month's data to come in.


Thank you kindly!

1 Reply


To do this, you would have to activate the "Calculate" tab under "File/ Options"

and activate "manually" instead of "automatically".

Go to File  --> Options --> Formulas --> Calculation options --> Manually

Then your cell references will only be updated if you go to the corresponding cell with your formula and press "Enter" again (not in the cell, but in the formula bar of the cell ...).

But that applies to the entire worksheet.


But if you only want to automatic replace a formula with its result in certain cells, I don't know - I almost don't think that your request can be dealt with using the Excel function.

But I can't tell you for sure.

On the other hand, your project should be solvable using VBA.


Another option would be to do this manually every time:

a) Select the range of cells that you want to convert into fixed values, or CTRL + A to select all cells of the active worksheet.

b) Press the key combination CTRL + C to copy the contents of the cells to the clipboard.

c) Navigate to the START tab and click in the EDIT menu area on the small arrow under the INSERT menu item. Activate the VALUES symbol from the submenu.

d) Alternatively, the command is also available if you call up the context menu with the right mouse button.

e) After clicking on the VALUES entry, all cells in the previously marked area or in the entire worksheet are converted into values ​​during the copying process.


Additional Informations:

Replace a formula with its result

You can convert the contents of a cell that contains a formula so that the calculated value replaces the formula.




I would be happy to know if I could help.



I know I don't know anything (Socrates)