In this tutorial, friend will find out a few different means of copying formulas in Excel - just how to copy formula under a column, to all of the selected cells, copy a formula precisely without an altering cell referrals or formatting, and also more.
You are watching: You can replace a formula with its function so it remains constant
Copying recipe in Excel is one of the easiest tasks that is normally done in a mouse click. Ns say "usually" due to the fact that there deserve to be very certain cases that need special tricks, like copying a selection of formulas without an altering cell recommendations or beginning the very same formula in lot of non-adjacent cells.
Luckily, Microsoft Excel offers numerous ways to carry out the very same task, and also it is true for copying formulas. In this tutorial, we are going to talk about different means to copy formulas in Excel so that you could choose the one best suited for her task.
How to copy formula down a column
Microsoft Excel carry out a yes, really quick method to copy a formula under a column. You just do the following:Enter a formula in the height cell.Select the cell with the formula, and hover the mouse cursor over a small square in ~ the reduced right-hand edge of the cell, i beg your pardon is called the Fill handle. Together you perform this, the cursor will readjust to a thick black cross.Hold and drag the fill take care of down the shaft over the cells where you want to copy the formula.
In a similar manner, you can drag formula into adjacent cells to the right, to the left or upwards.
If the formula has relative cell references (without the $ sign), they will automatically readjust based top top a relative position of rows and columns. So, after copying the formula, verify that the cell references have got changed properly and also produce the result that girlfriend want. If necessary, switch between absolute, relative and also mixed references by using the F4 key.
In the over example, to make sure the formula was copied correctly, let"s select some cabinet in shaft C, speak C4, and also view the cell recommendation in the formula bar. Together you can see in the screenshot below, the formula is all appropriate - family member to row 4, exactly as it need to be:
How come copy a formula under without copy formatting
Copying a formula down by dragging the fill manage not only copies the formula, but also the source cell formatting such as font or lift color, money symbols, the variety of displayed decimal places, etc. In many cases, this works simply fine, yet sometimes it deserve to mess up the existing layouts in cells wherein the formula is being duplicated to. A typical example is overwriting alternate row shading like in the following screenshot.
For prevent overwriting the existing cell formatting, drag the fill handle as demonstrated above, release it, click the Auto fill Options drop-down menu, and select Fill without Formatting.
Copy formula come the whole column
As you"ve just seen, the fill manage makes copying formulas in Excel really easy. But what if you have to copy a formula under a ten-hundred-line sheet? Dragging the formula over thousands of rows does not look favor a good idea. Luckily, Microsoft Excel offers a pair of quick solutions because that this case as well.
Double-click the plus authorize to fill the whole column
To apply the formula come the whole column, double-click the add to sign rather of dragging it. For those who have skipped the first section the this tutorial, the in-depth steps follow below.
To copy one Excel formula come the entirety column, do the following:Input your formula in the top cell.Position the cursor come the lower right corner of the cell through the formula, wait until it turns into the add to sign, and also then double-click the plus.
Note. Double-clicking the add to sign copies the formula down as much as there is any data come the left. In other words, as lengthy as you have row headers or any other data in any of the left-hand columns, Excel continues to copy the formula. As soon as one empty heat occurs, the auto to fill stops. So, if her worksheet contains any type of gaps, friend will have to repeat the above process to copy the formula listed below an north row:
Create an Excel table to copy a formula to every cells in a shaft automatically
Among other great features of Excel tables such as predefined styles, sorting, filtering and banded rows, automatically calculated columns is what provides an Excel table a truly wonderful device for assessing groups of connected data.
By entering a formula right into one cell in a table tower (just any type of cell, no necessarily the peak one), you create a calculated column and also have her formula instantly replicated to all various other cells in that column. Unlike the fill handle, Excel tables have actually no problem with copying the formula across the whole column also if the table has one or much more empty rows:
To transform a selection of cell to an Excel table, simply choose all the cells and press Ctrl + T. If you like a visual way, select the range, go to the Insert tab > Tables group on the Excel ribbon, and click the Table button.
Tip. If girlfriend don"t really desire an Excel table in your worksheet, come can create it temporary, to do the work with recipe easier, and also then girlfriend can convert the table ago to a usual range in a second. Just right-click the table and also choose Table > Convert come Range in the paper definition menu.
Copy a formula come non-adjacent cells / ranges
It goes without saying the the fill manage is the fastest method to copy formula in Excel. But what if you desire to copy her Excel formula in non-contiguous cells or past the end of the resource data? just use the old great copy & paste way:Click the cell through the formula to pick it.Press Ctrl + C to copy the formula.Select a cell or a variety of cells whereby you want to paste the formula (to choose non-adjacent ranges, press and hold the Ctrl key).Press Ctrl + V to paste the formula.Press get in to finish the pasted formulas.
Note. The copy/paste shortcuts copy the formula and formatting. To copy the formula there is no formatting, choose an proper Paste option on the ribbon or in the right-click menu, as demonstrated in copy an Excel formula without formatting.
Enter a formula into multiple cells with a single key stroke (Ctrl + Enter)
In instances when you should input the exact same formula in more than one cabinet on a worksheet, surrounding or non-adjacent ones, this an approach can it is in a time-saver.Select all the cells wherein you desire to get in the formula. To select non-contiguous cells, press and hold the Ctrl key.Press F2 to enter the modify mode.Input your formula in one cell, and also press Ctrl + enter instead that Enter. That"s it! The formula will certainly get copied to every one of the selected cells, and also Excel will adjust relative cell recommendations accordingly.
Tip. You deserve to use this an approach to enter any data, not just formulas, in multiple cell at a time. A few other approaches are described in the complying with tutorial: exactly how to get in the same data into all selected cells at a time.
How to copy an Excel formula but not formatting
As you already know, as soon as copying a formula down a tower in Excel, you can use the fill Without Formatting option that allows you copy the formula however keep the existing formatting that the location cells. Excel"s Copy & Paste feature offers even an ext flexibility with regard to paste options.Select the offer containing the formula.Copy the cell by pressing Ctrl + C. Alternatively, appropriate click the cell and select Copy native the context menu, or click the Copy button on the Home tab > Clipboard.Select every the cells to i m sorry you desire to copy the formula.Right-click the selected cells and also choose Formulas under Paste Options:
For more paste options, click the arrowhead below the Paste switch on the ribbon. Because that example, friend can choose Formulas & Number Formatting to paste only the formula and the number formatting such together percent format, currency format, and also the like:
Tip. If you are not sure which paste choice works best for you, hover the mouse over different icons to watch a preview that this or that dough option.
Copy formula in Excel without transforming references
Excel formulas rarely happen in a spreadsheet in solitude. In many cases, you go into a formula in one cell, and also then copy that to various other cells in the same obelisk or row, to execute the very same calculation top top a team of data. And if her formula contains relative cell referrals (without $), Excel immediately adjusts lock so that each formula operates on data top top its very own row or column. Most of the time, this is specifically what you want. For example, if you have actually the formula =A1*2 in cabinet B1, and you copy this formula to cabinet B3, the formula will adjust to =A3*2.
But what if you desire Excel to copy the formula exactly, without transforming the cell recommendations along the way? depending on your certain task, pick one that the complying with solutions.
Copy or move a single formula without an altering cell references
If you should copy or move just one formula, making precise copy is easy.Select the cell with the formula you want to copy.Select the formula in the formula bar making use of the mouse, and press Ctrl + C to copy it. If you desire to move the formula, press Ctrl + X to cut it.
Alternatively, you can go into the editing mode and copy the formula in the cell as text:Select a cell through the formula.Press F2 (or double-click the cell) to go into the modifying mode.Select the formula in the cell utilizing the mouse, and press Ctrl + C come copy it.
Tip. To quickly copy a formula indigenous the above cell with no referral changed, pick the cell whereby you desire to dough the formula and also press Ctrl + ".
Copy a selection of recipe without changing cell references
To relocate or copy a range of Excel recipe so that no cell referrals are changed, use among the adhering to methods.Method 1. Usage absolute or combined cell references
If you have to make one exact copy that formulas with relative cell recommendations (like A1), the best means would be an altering them come absolute references ($A$1) to deal with the recommendation to a provided cell, so that it remains static no matter where the formula moves. In various other cases, you might need to usage mixed cell references ($A1 or A$1) come lock one of two people a shaft or row. Doesn"t make lot sense so far? Okay, let"s consider the adhering to example.
Supposing, you have a table that calculates the fruit prices in EUR based on the USD price in obelisk B and the exchange price in cabinet C2:
As you have the right to see in the screenshot above, the formula includes an absolute cell referral ($C$2) to resolve the exchange rate to cell C2, and a loved one cell recommendation to cabinet B5 because you want this referral to change for each row. And also this approach works fine as long as the formulas remain in tower C.
But let"s watch what happens if girlfriend need, say, to move the EUR prices from column C to obelisk F. If you copy the recipe in a usual method by copying/pasting the cells, the formula from cabinet C5 (=B5*$C$2) will adjust to =D5*$C$2 once pasted in cell F5, making her calculations every wrong!
To resolve this, just change a relative reference (B5) come a mixed reference $B5 (absolute column and relative row). By putting the dollar authorize ($) in front of the pillar letter friend anchor the reference to column B, no issue where the formula moves.
And now, if girlfriend copy or relocate the recipe from tower D to tower F, or any other column, the column referral won"t change due to the fact that you locked it by the dollar sign ($B5).
The concept of Excel cabinet references might be difficult to master from the outset, but trust me it"s worth your time and also effort due to the fact that it will conserve you much much more time in the long run. Because that example, see how you can calculate the whole table through a single formula by using blended cell references.
However, if you already have a ton of recipe with family member cell references in her Excel sheet, and also you require to conveniently make precise copy the those formulas but you don"t feel favor you can gain the referrals right, among the complying with methods can be a solution.Method 2. Copy Excel formulas without transforming references via NotepadSelect all the cells through the formulas you desire to copy or move.Press Ctrl + C come copy the formulas, or Ctrl + X to reduced them. Usage the latter shortcut if you desire to move formulas to a new location.
Notes:You have the right to paste the formulas just in the same worksheet where your initial formulas are located, unless the references incorporate the paper name, otherwise the formulas will be broken.The worksheet need to be in formula view mode. Come verify this, go to the Formulas tab > Formula Auditing group, and check if the Show Formulas button is toggled on.After pasting the formulas, push Ctrl + ` come toggle off the formula watch mode.Method 3. Copy formulas specifically by utilizing Excel"s Find and Replace
To copy a variety of Excel recipe without changing their cell references, you have the right to use the Excel Find and Replace feature in the complying with way.Select the cells v the formulas the you want to copy.In the Find & Replace dialog window, type the equal sign (=) in the Find what box. In the Replace with box, input part symbol or a string of personalities that is not used in any type of of your formulas, favor ", # or .
The purpose of this step is to rotate formulas right into text strings, which will protect against Excel from an altering the cell references throughout the copy process.
Note. perform not usage an asterisk (*) or question mark (?) for replacement, due to the fact that these are wildcard characters in Excel and using them would certainly make later steps an ext difficult.
Click the Replace All button and close the Find and Replace dialog. All the formulas in the selected variety will turn into text strings:
Shortcuts come copy Excel formula to other cells
1. Copy a formula down
Ctrl + D - Copy a formula native the cell above and adjusts the cabinet references.
For example, if you have a formula in cell A1 and also you want to copy it to cell A2, choose A2 and press Ctrl + D.
2. Copy a formula to the right
Ctrl + R - Copy a formula native the cabinet to the left and also adjusts the cabinet references.
For example, if you have actually a formula in cell A2 and you desire to copy the to cabinet B2, select B2 and press Ctrl + R.
Tip. Both that the over shortcuts deserve to be offered to copy formulas to multiple cells too. The cheat is to pick both the resource cell and target cells before pressing the shortcut. Because that example, if you want to copy the formula from A1 to the following 9 rows, choose cells A1:A10 and also press Ctrl + D.
3. Copy a formula down exactly
Ctrl + " - duplicates a formula native the cell above to the currently selected cabinet exactly and leaves the cabinet in modify mode.
This is a quick method to make precise copy the a formula without changing cell references. Because that example, come copy a formula from cell A1 come A2 so that no references are changed, choose A2 and also press Ctrl + ".
See more: Bud Not Buddy Chapters 9 12 Answers, Bud, Not Buddy Chapter 9
Don"t confused the shortcut Ctrl + " (Ctrl + solitary quote) that exactly duplicates a formula from the cell above with Ctrl + ` (Ctrl + grave interval key) the activates display formulas setting in Excel.
Well, this is all I have to say about copying formulas in Excel. If you recognize some other approaches to easily move or copy formula in Excel sheets, please carry out share. I say thanks to you because that reading and also hope to see you on ours blog next week!