How to quickly replace formulas with values ​​in an Excel spreadsheet

Often, users of the Excel spreadsheet editor are faced with the task when it is necessary to implement the replacement of formulas with values. This procedure can be carried out in various ways. In the article, we will analyze many methods that allow you to replace the formula with values ​​​​in a spreadsheet document.

First method: standard replacement of formulas with values

This is the most famous and common way to replace formulas with values. Detailed instructions look like this:

1. Initially, we select the data range in which we want to implement the replacement of formulas with values.
2. We implement data copying. To do this, right-click on the selected range and click on the “Copy” element in the context menu. An alternative option is to use the key combination “Ctrl + C”.
3. Right-click on the selected range. Here you can select an icon called “Values”.

4. Hover your mouse over the “Paste Special” element to expand the list of functions.

5. Here are all possible variations of pasting values ​​with the preservation of the formatting of the original cells.

It is worth noting that in earlier versions of the spreadsheet editor there are no such additional icons, so all operations are performed through this window:

Second Method: Using Spreadsheet Editor Hotkeys

All of the above manipulations can be implemented using special hot keys of the spreadsheet editor. Detailed instructions look like this:

1. We implement copying the desired range using the key combination “Ctrl + C”.
2. We also paste back here using the key combination “Ctrl + V”.
3. We click on “Ctrl” to display a small menu that will offer pasting variations.
4. We click on “Z” on the layout, or use the arrows to select the “Values” parameter. We carry out confirmation of all the actions taken using the “Enter” key located on the keyboard.

Third method: replacement with the mouse

This method allows you to carry out the procedure for replacing formulas with values ​​much faster than the above methods. Only a computer mouse is used here. Detailed instructions look like this:

1. We select cells with formulas on the worksheet of a spreadsheet document.
2. We take the frame of the selected fragment and, holding the RMB, we drag it two cm in any direction, and then we return to the starting position.
3. After this procedure, a small special context menu was displayed, in which you need to select an element that has the name “Copy only values”.

Fourth Method: Using a Special Button to Paste Values

You can significantly speed up the Paste Special procedure by adding a special Paste as Values ​​button to the Quick Access Toolbar. Detailed instructions for adding and using it look like this:

1. We move to the “File” subsection.
2. Select the element with the name “Parameters”.
3. Left-click on the Quick Access Toolbar.
4. A window appears on the screen in which you can add various items to the Quick Access Toolbar. We open the list called “All teams”.
5. In the list that opens, we find an element called “Insert Values” and add it to the Quick Access Toolbar using the “Add” button. 
6. Ready! We have added this button to the Quick Access Toolbar.
7. Now it remains only to copy the range of cells in which the formulas we need are located, and then click on the newly added button on the Quick Access Toolbar.

It is worth noting that by default, all buttons on the Quick Access Toolbar are assigned the key combination “Alt+digit”. After clicking on «Alt» on the top panel, a number will appear indicating this button.

Fifth Method: Using Custom Macros

Using macros is the fastest way to replace formulas with values. This is how a macro looks like that allows you to replace all formulas with values ​​in the selected range:

This is how a macro looks like that allows you to replace all formulas with values ​​on the selected worksheet of a spreadsheet document:

This is how a macro looks like, allowing you to replace all formulas with values ​​on all worksheets of a spreadsheet document:

Detailed instructions for using macros look like this:

1. Using the key combination “Alt + F11” open the VBA editor.
2. Open the “Insert” subsection, and then left-click on the “Module” element.
3. Here we paste one of the above codes.
4. User macros are launched through the “Macros” subsection, located in the “Developer” section. An alternative option is to use the Alt + F8 key combination on the keyboard.
5. Macros work in any spreadsheet document.

It is worth remembering that manipulations implemented using a macro cannot be undone.

Conclusion and conclusions about quickly replacing formulas with values ​​in a spreadsheet document

We found out that there are many options that allow you to implement the replacement of formulas with values ​​in a spreadsheet document. You can implement this procedure using Paste Special, keyboard shortcuts, a computer mouse, personal macros, and a special button added to the Quick Access Toolbar. Each method has its own advantages and disadvantages. The user can independently choose for himself the most convenient and fastest method that allows you to implement the replacement of formulas with values ​​in a spreadsheet document.