Convert formulas to values

Contents

Formulas are good. They are automatically recalculated with any change in the source data, turning Excel from an “overgrown calculator” into a powerful automated system for processing incoming data. They allow you to perform complex calculations with tricky logic and structure. But sometimes there are situations when it would be better if there were values in the cells instead of formulas. For example:

You want to capture the numbers in your report for the current date.
You do not want the client to see the formulas by which you calculated the cost of the project for him (otherwise he will understand that you have laid down a 300% margin just in case).
Your file contains so many formulas that Excel began to slow down terribly with any, even the most simple changes in it, because. constantly recalculates them (although, to be honest, I must say that this can be solved by temporarily disabling automatic calculations on the tab Formulas – Calculation Options).
You want to copy a range of data from one place to another, but when copying, all links in formulas will “slide away”.

In any such situation, you can easily remove the formulas, leaving only their values in the cells. Let’s look at a few ways and situations.

Method 1. Classic

This method is simple, known to most users and consists of using a special paste:

Select the range that contains the formulas you want to replace with values.
Copy it with the right mouse button – Copy (Copy).
Right-click on the selected cells and select either the icon The values (Values):

or hover over the command Paste special (Paste Special)to see the submenu:

From it, you can choose options for pasting values while preserving the design or number formats of the original cells.
In older versions of Excel, there are no such convenient yellow buttons, but you can simply select the command Paste special and then the option The values (Paste Special — Values) in the opened dialog box:

Method 2. Only keys without a mouse

With some skill, you can do all of the above without touching the mouse at all:

Copy the selected range Ctrl+C
Here we insert back the combination Ctrl+V
We press Ctrlto bring up the paste options menu
Press the key with the letter З or use the arrows to select an option The values and confirm the selection with the key Enter:

Method 3. Mouse only without keys or Sleight of Hand

This method requires some skill, but will be noticeably faster than the previous one. We do the following:

Select a range with formulas on a worksheet
We grab the edge of the selected area (thick black line around the perimeter) and, holding RIGHT mouse button, drag a couple of centimeters in any direction, and then return to the same place
In the context menu that appears after dragging, select Copy only values (Copy As Values Only).

After a little training, this action is done very easily and quickly. The main thing is that the neighbor does not push under the elbow and his hands do not tremble 😉

Method 4. Button for inserting values on the Quick Access Toolbar

You can speed up Paste Special by adding the Paste as Values button to the Quick Access Toolbar in the upper left corner of the window. To do this, select File – Options – Panel bquick access (File — Options — Customize Quick Access Toolbar). In the window that opens, select All teams (All commands) in the dropdown list, find the button Paste Values (Paste Values) and add it to the panel:

Now, after copying cells with formulas, it will be enough to click on this button on the quick access toolbar:

In addition, by default, all buttons on this panel are assigned a keyboard shortcut. Alt + figure (press sequentially). If you press the key Alt, then Excel will tell you the number that is responsible for this:

Method 5. Macros for the selected range, the whole sheet or the entire workbook at once

If the word “macros” doesn’t scare you, then this will probably be the fastest way.

Macro to convert all formulas to values in the selected range (or multiple ranges selected at the same time as Ctrl) looks like that:

Sub Formulas_To_Values_Selection() 'convert formulas to values in the selected range(s) Dim smallrng As Range For Each smallrng In Selection.Areas smallrng.Value = smallrng.Value Next smallrng End Sub

If you need to convert the current sheet to values, then the macro will be like this:

Sub Formulas_To_Values_Sheet() 'convert formulas to values in the current sheet ActiveSheet.UsedRange.Value = ActiveSheet.UsedRange.Value End Sub

And, finally, to turn all the formulas in the book on all sheets, you will have to use this construction:

Sub Formulas_To_Values_Book() 'Convert formulas to values in the whole book For Each ws In ActiveWorkbook.Worksheets ws.UsedRange.Value = ws.UsedRange.Value Next ws End Sub

The code of the necessary macros can be copied into a new module of your file (click Alt+F11 to get into Visual Basic, next Insert – Module). You can then launch them through the tab Developer – Macros (Developer — Macros) or keyboard shortcut Alt+F8. Macros will work in any workbook as long as the file where they are stored is open. And remember, please, that the actions performed by the macro cannot be undone – use them with caution.

Method 6. For the lazy

If it breaks doing all of the above, then you can do it even easier – install the add-on PLEX, where there are already ready-made macros for converting formulas into values and doing everything with one click of the mouse:

In this case:

everything will be as fast and simple as possible
you can roll back an erroneous conversion by undoing the last action or by combining Ctrl+Z as usual
unlike the previous method, this macro works correctly if the sheet has hidden rows/columns or filters are enabled
any of these commands can be assigned to any keyboard shortcut you like in the PLEX Hotkey Manager

What are macros, how to use, copy and run them
How to copy formulas without link shift
How to count in Excel without formulas