Change formulas to values

On this page I show you how to convert formulas to values manual and with VBA code.

Manual way 1

If we want to make values of formula cells on your worksheet you must first select the cells that you want or select all the cells on the worksheet like I show you below.

1: Click on the space above the first row header and on the left of the first column header.

selectallcells

2:
CMD + a

Note: With CMD a it is possible that you must hit CMD a three times, First CMD + a selects the current region. Pressing CMD + a the second time selects the current region and it's summary rows.
Pressing CMD + a the third time selects the entire worksheet.

Now we have select the cells we can make values of the formulas like this :
  • Use CMD + c or right click on the selection and choose Copy
  • Right click on the selection and choose Paste Special
  • Click on "Values"
  • Press Esc
  • Press CMD + Arrow-Up to select A1

Manual way 2

Not many people know about this cool tip, for example if we have formulas in H2:H4 and we want to make values of the formulas in this range.

1: First we select the range
2: We move the mouse curser to right border of the selection untill we see the cursor change to a hand

mousepointer

3:
We now Right click and hold the mouse and drag the selection to the column to the right and drag it back to the H column and release the right mouse button. You will now see a new menu and if you choose the option "Copy here as values only" the formulas will be values in H2:H4. You see that you also can copy as values to another location this way.

VBA code examples

The first macro of each example use PasteSpecial xlPasteValues and the second the value property(both do the same). Remember that you must remove the worksheet protection before you can use the macro's or do this also in the macro.

Warning: Value property problem
If there are constants in the .UsedRange that have character by character formatting, the .Value = .Value will change the formatting to match the first character in that cell.

05/09/2024
Web design by Will Woodgate