I spend a lot of time building models on Excel. I have 4 rules that help me get things done fast.

Highlight the selection (A1:A6 in my example above), then type 'Alt H F I S'. This will take you to a little Series pop-up menu that will let you specify the step value (in this case 1) and then you can just hit 'Enter' and it will do exactly the same thing as dragging down the selection. Ah yes, such relief! Method 2: Drag-and-drop the formula until it reaches the last row of data set. Even less ideal, you can drag the bottom right corner of the cell down and basically wait until the window scrolls to the last row of the data set. The downsides of this method: You’re still using your mouse or trackpad.

1. Never use the mouse. The keyboard is much faster.
2. Never type in data. You can always import it.
3. Avoid manual labour. Use Excel to automate the task.
4. Make your data visually obvious.

Let’s look at Rule #1: Never use the mouse.

Using the keyboard can be 10 times faster than the mouse. It takes time to move one hand from the keyboard to the mouse, locate the item you want to click at, move the mouse there, adjust it finely so it’s pointing at the exact spot, and then click it. For example, to insert text without formatting, I’d just go Alt-E, S, enter. It takes half a second. It took me 5 seconds with a mouse. (I timed 10 continuous attempts in both cases.)

A factor of 10 speed advantage like that is good for two reasons: it saves you time, and it doesn’t distract you from what you’re doing (provided the keyboard shortcuts have become a habit.)

For newbies: To use menus using keyboard shortcuts, first go to Start – Settings – Control Panel – Display – Appearance tab – Effects button – Hide underlined letters for keyboard navigation until I press the Alt key. Make sure it is turned off. To use a menu, let’s say “Insert – Row”. look for the underlined letter on the menu bar (the “I” on “Insert”), press Alt and the underlined letter (Alt-I in this case), and look for the underlined letter on the next menu item (“R” on the “Rows” in this case) and press that letter. So, Alt-I-R is the shortcut to insert a row. Now, just practice Alt-I-R, Alt-I-R, Alt-I-R repeatedly.

Shortcuts next to the menu are quicker, where they exist. For example, the Ctrl-C next to the Edit-Copy menu.

Apart from the arrow keys, Ctrl-S, Ctrl-X, Ctrl-C, Ctrl-V, the keys I use frequently are:

• F2: edit the current cell
• F4: repeat the last action (very useful)
• Ctrl-Z, Ctrl-Y: Undo, Redo
• Ctrl-1: Format cells
• Ctrl-Shift-down arrow: Select all filled cells below selection (also works with other arrow keys)
• Ctrl-PgUp/PgDn: Shift between tabs
• Ctrl-Home/End: Go to top-left, or bottom-right of the sheet (or cell, if you’re editing a cell)
• Ctrl-`: Show formulae
• Shift-Space, Ctrl-Space: Select row, select column
• Alt-Enter: To create a new line while you’re typing in a cell
• Alt-E-I-S-Enter: Select a set of cells and fill a continuous series of numbers in it
• Alt-E-S-Enter: Paste unformatted text
• Alt-D-G-G: Group a set of rows (use Alt-D-G-U for ungroup)
• Alt-D-G-S: Show a collapsed group (use Alt-D-G-H to collapse a group)
• Alt-O-D: Conditional formatting
• Alt-F8: Macros

Microsoft Excel is an amazing tool to handle large data without having issues. There are various Excel options that make the work easier, but many people are not familiar with these features. So one of these options is to remove the Excel formula of the sheet without losing the data. In case you are also finding a method to remove the Excel formulas quickly and that too without losing the data, then don't worry because there are multiple ways to do it easily.

In this tutorial, we are going to explain the techniques to remove formulas from the excel sheets and keeping the data(value) preserved by applying the following methods:

1. Remove formula using Paste Special
2. Remove formula using Mouse Trick
3. Remove formula using Paste Values
4. Remove formula using the Home Tab
5. Remove formula using Keyboard Shortcuts
6. Find & Remove formula using Keyboard Shortcuts
7. Remove Formula from Multiple Sheets (at the same time)

1) Using Paste Special Method

So here is the way to use Paste Special method in MS Excel for removing formulas:

1. Open the excel sheet to which you want to remove the formula.
2. Select and Copy all the cells which have the formulas that you want to remove.

1. Right-click and select the Special Paste option

1. Next, in the Paste Special box, select the Value option and click OK.

1. Now you see the formula has been removed from the cell, and the data will remain the same.

2) Remove Formula using Mouse Trick

Please follow the steps below to remove a formula through the mouse trick.

1. Open the excel sheet to which you want to remove the formula.
2. Select the cells to which have the formulas that you want to remove.

1. Take the cursor to the right edge of the column.

1. Hold the right key of your mouse, drag the cursor to a little right, and bring it back to the cell.
2. Now right click on the mouse and select 'Copy here as a value only.'

1. This 'Copy here as a value only' keeps the value and will remove the formula.

3)Remove formula using Paste Values

Please follow the steps below to remove a formula by the Paste Values option.

1. Open the excel sheet to which you want to remove the formula.
2. Select all the cells that have formulas that you want to remove.

1. Right-click on it and select Copy to copy all the selected cells.

1. After that, right-click again and go to the Paste Options and select 'Values.'

1. Now you will notice that only values are retained, and the formulas are gone!

4) Remove Formula Using the Home Tab

You can use the following steps for removing a formula through Home Tab.

1. Open the excel sheet to which you want to remove the formula.
2. Now select all the cells that have formulas that you want to remove.

1. Right-click on the selected cells and select Copy.

1. Now go to the Home tab and click the downward arrow below the paste option.

1. Next, click on the Paste Values.

1. Now you will notice all the formula from the cell has gone, and values have replaced it.

5) Remove Formula Using Keyboard Shortcut

In case you comfortable using the Paste as Value option keyboard shortcut, then please follow the steps below:

1. Open the excel sheet to which you want to remove the formula.
2. Select all the cells that have formulas that you want to remove.

1. Next, press Ctrl+C to copy all selected cells containing the formula.

1. Now press ALT + E + S + V + Enter to select Paste as Values option.

6) Find & Remove Formula Using Keyboard Shortcuts

In the above scenarios, you already knew which cells contain formulas. Let's go to an example where you have first to find the cells containing formulas and then remove formulas in Excel on each cell one by one using keyboard shortcuts. Please follow the steps below:

1. Open the excel sheet to which you want to remove the formula.
2. Press Ctrl + G on your keyboard to open a dialog box and then select Special.

1. Now select the Formulas option from the list and click OK. All the cells containing formulas will be selected.

1. Next, highlight all the cells containing formulas with color.

1. Now Press Ctrl + C to copy the one cell containing the formula.

1. Press Now press ALT + E + S + V + Enter to select Paste as Values option.
2. The formula has now been replaced with value.
3. Next, repeat these steps for all other highlighted cells.

Drag A Formula In Excel Without Mouse Windows 10

7) Remove Formula from Multiple Sheets (at the same time)

Walking through the above methods, we learned the technique to remove the formulas from a single sheet at a time. But what if we want to perform the same operations on multiple numbers of sheets. This task will consume a lot of time for the person performing the task.

In this section, we will look at the technique that will teach us how to remove formulas and keep data in excel for more than one sheet at the same time.

To do this, we have first to group the sheets to which we want to apply the operations. Following is the way we can do so:

How To Drag Excel Without Mouse

1. Click the Name tab of the first sheet
2. Hold the SHIFT button & simultaneously click on the Name tab of the last sheet

Note: You will notice that the color of all the sheet names will turn whiter because these sheets are now grouped. Now whatever operation you will perform on any one of the sheets will happen to all grouped sheet

1. Next, go to sheet one and select all the cells containing the formula.

1. Press Ctrl+C or Right-click and click on the Copy.

Drag A Formula In Excel Without Mouse Pad

1. Now, right-click and go to the 'Paste' Option and select 'Values.'

Since all the sheets are grouped together, therefore, the operations performed on sheet 1 will be applied to all other grouped sheets, we can assume that formulas have been removed from all the sheets, and only values exist after that.

Note: Now after completing the task please turn off the grouping feature. Right-click on any sheet and click UNGROUP sheets.

Conclusion

Throughout this article, we discussed in detail different approaches to delete the formulas and preserve the values. We have also learned the way on how to apply the same kind of operations to multiple sheets at the same time.

Drag A Formula In Excel Without Mouse Traps

So, this will help the person to save his time and allow him not to disclose the formulas to any third party or person.