Google Excel Repet the Formula Again
If you have a formula that you would like to copy to an entire column in Google Sheets, there is a very easy and fast mode of doing this, by using "autofill" which is referred to as "fill downward" when being used to apply formulas to a column. You can also copy/fill formulas horizontally through a row, by using "fill right".
To copy a formula downwards an entire column in Google Sheets, follow the steps below:
- Enter a formula into the jail cell that is at the top of the cavalcade/range that you lot want to re-create formulas to
- Select the cell with the formula in it, so click on the make full handle, and hold the click (The fill up handle looks like a cantankerous / plus sign, and it appears when hovering your cursor over the tiny bluish square at the lesser right corner of a cell selection)
- Drag the make full handle downwardly to the bottom of the column/range that you want your formulas to re-create into
- Release your click when you have reached the final prison cell that you want the formula copied into
- Alternative method: Yous can also copy and paste cells that have formulas in them to copy formulas. Click on a cell with a formula in it, press Ctrl + C on the keyboard to copy it, and so select another cell, and press Ctrl + V on the keyboard to paste the formula
This commodity shows how to copy formulas in Google Sheets, merely click here if you desire to learn how to copy formulas in Excel.
When you hover your mouse over the modest blueish square that displays at the lesser right corner of a cell option, your cursor volition turn into a plus sign (cantankerous), and this is what is called the "fill up handle".
This picture is an example of what it will expect similar when your cursor is in the correct location to use autofill / fill downwards.
At the bottom right of cell C2 (blue cell), you can see that the cursor looks like a plus sign.
After in this article you volition be able to see a full example of copying/filling the formula that is shown in cell C2 downwards the column into the cells beneath it.
Learn how to create dashboards in Google Sheets
Automated cell reference aligning when copying formulas
When using autofill with formulas, Google Sheets will automatically adjust the cell references in the formulas to business relationship for the formulas being placed within a different column/row.
As a quick example of this reference adjustment that happens when copying formulas, which I will go over in particular later on… observe the formula that is entered into jail cell C2 in the picture in a higher place: =A2+B2
When this formula is copied into the cell below it, the copied formula will have adapted its row references, and will at present be: =A3+B3
Or if this formula were copied into the cell to the right of it, the copied formula would accept adapted its column references, and would then be: =C2+D2
At that place is a mode to prevent these references from changing if you desire, but we will salvage that for later.
Filling formulas vs. Filling a series with autofill
There are a variety of ways to use autofill in Google Sheets, but in this commodity we will specifically exist using autofill to copy formulas.
However, autofill can also exist used to create a series / sequence of values, such as quickly creating a list of numbers or sequence of dates. If you want to acquire how to employ autofill / fill downward to create a series of values, and then check out the commodity linked beneath.
"Automatically Create a Series of Values"
Autofill vs. ARRAYFORMULA
If you are searching for a way to employ a formula to an entire column, you might accept been searching for a single formula that can handle that job without having to re-create multiple formulas, and this can exist washed by using the ARRAYFORMULA office.
But in this article nosotros will stick to using autofill / make full down to copy formulas.
Copying to an entire column vs. a range of cells
In the examples below we will exist copying formulas downward a range of cells, such every bit C3:C17, which nosotros will refer to equally a "column"… since when it comes to real earth spreadsheet applications, where nosotros usually accept headers in our sheet, if we are talking virtually applying formulas to a "column", we are often referring to a range of cells inside that column.
If you actually want a formula in every single jail cell of a cavalcade, it can be as simple as copying your formula, selecting the unabridged cavalcade, and then pasting the formula into the entire column (more on this below).
To describe the examples in this article I will stick to the traditional manner of didactics how to autofill formulas, which is to click and drag the fill handle until the desired jail cell/range has been reached / copied to.
Just in your case you may accept hundreds or fifty-fifty thousands of rows, and then beneath I'll too mention some different shortcuts that yous can use to brand things faster, particularly when y'all have a large number of columns or rows to exist filled / copied to.
Autofill vs. Copying and Pasting Formulas
It's skillful to note that using autofill on formulas is effectively the same equally copying and pasting cells with formulas in them from one location to some other (by using Ctrl + C to copy, and Ctrl + V to paste).
Just similar with autofill, copying and pasting a formula will automatically conform the prison cell references when moving from one column/row to another.
When you copy a cell that has a formula in it and paste it to a range of multiple cells, it will re-create that formula to each cell in the range with an adjusted reference, but like autofill does.
The betoken is that autofill and copy/paste basically do the same thing.
Spreadsheet Shortcuts
The shortcuts below can help you quickly select cells and re-create them to other locations.
If you don't want to utilise shortcuts and prefer simply dragging the fill handle, this is perfectly fine.
Cell option shortcuts
Before I become over the autofill shortcuts, I'll show you a few ways to speedily select a range of cells that y'all want to copy formulas into.
An of import role of being able to effectively use the autofill shortcuts that are explained in the department below, is being able to chop-chop select the cells that you want to re-create formulas to before using the shortcut.
These selection shortcuts will as well be helpful if you are copying and pasting formulas to a range of cells with Ctrl +C and Ctrl + V.
Click and drag
Y'all can select multiple cells by clicking the offset cell in your range, then while holding your click, drag the cursor in the management that y'all want to aggrandize your range, until you have reached the prison cell that is at the finish of the desired range.
Ctrl + Click
A way of quickly selecting a big range of cells, is to select the commencement cell in the range that you want to select (meridian / left cell), and and then while holding the "Ctrl" key, click the last cell that is in the range that y'all want to select (bottom / right jail cell).
This will instantly select the entire range of cells that begins and ends with the cells that you clicked on.
Shift + arrow keys
To select multiple cells by using the arrow keys, select the first cell in the range that you want to select, and so hold the shift key on the keyboard, and and then press the arrow key that points in the direction that you lot want to expand your selected range to.
You can repeatedly press the arrow primal(s) to increase the size of the range by a column or row each time the key is pressed, or y'all tin can hold down the pointer key(s) to expand the range more quickly.
Shift + Ctrl + arrow keys
This keyboard shortcut will allow you lot to quickly select a large range of cells, and is particularly useful for using the make full downwards keyboard shortcut, as information technology volition allow you to start from a designated cell and chop-chop select all of the cells below it.
When holding both the Shift primal and the Ctrl key, pressing an arrow key will expand your cell selection to where the cavalcade or row ends.
If there is not information in the cells, the choice will expand to the last cell of the sheet… and if at that place is information in the cells already, then the choice will aggrandize to the edge of where the data stops (until reaching an empty cell).
In other words this method allows you to rapidly select "bricks" of data, or to select unabridged rows/columns when no information is nowadays.
Selecting entire columns or rows (every single cell)
Equally mentioned above, y'all may have situations where yous really want to copy a formula into every single cell of a row or cavalcade.
You lot can select an entire column or row by clicking on the number itself on the left side of a given row, or past clicking on the letter of the alphabet itself at the elevation of a given column. This volition instantly select every single jail cell in that row or column.
As will be explained beneath, the fill up down shortcut copies the formula/contents that are in the topmost cell of the selected range, and so when selecting an entire column with the intention of using the fill up down shortcut to copy formulas, you'd want to make sure that your formula was in the top cell.
In other words, if you had a header row and and so used the fill downwardly shortcut while the unabridged cavalcade was selected, it would copy your header into every jail cell of the column.
To fill up every unmarried cell of the column with formulas by using the copy and paste method, simply copy the cell with your formula in information technology, select the entire column, and and then paste.
But as previously mentioned, in the real world nosotros usually have header rows, and we often simply want our formulas to extend so far… and then this is why we are using a range of cells to copy to in the examples provided below, such as C3:C12.
The "make full down" and "fill right" autofill shortcuts
Now that you know how to quickly select cells, you can efficiently utilise the fill up downwards and fill correct shortcuts.
Fill down keyboard shortcut: (Ctrl + D)
To utilise the fill downward keyboard shortcut to copy formulas, select the range of cells that you want to copy formulas into, where the top cell of your selection contains the formula to be copied. Then press the keys Ctrl +D. This will copy down the formula that is in the peak cell of the selection, through the whole range/column selected.
Fill right keyboard shortcut: (Ctrl + R)
There is also a shortcut for using autofill horizontally. This is called "fill up right", and the shortcut is Ctrl + R.
"Fill correct" will copy the formula that is on the far left of the selection, through the whole range/ row selected.
Double click fill down shortcut
Another shortcut that comes in very handy, is a shortcut that allows you to double click the fill handle to make full down automatically.
When you double click the make full handle of a cell, if there is already data in the adjacent columns (to the left or the correct), Google Sheets will automatically fill downwards the formula/contents of that cell until the bottom edge of the adjacent data is reached.
This is helpful when you want to copy down formulas to the aforementioned row where the adjacent data set up stops vertically.
How to copy a formula downward a column in Google Sheets
So allow's finally get started with going over examples.
First we will outset simple, past copying / filling a formula that refers to a jail cell from another column.
The picture below shows a list of names in cavalcade A, and we want to display those same names in column C.
Formula: The formula beneath is entered initially into prison cell C3, and so copied into the range C3:C12, for this example
=A3
We want to re-create this formula into the cells below prison cell C3, and to do this follow these steps:
- Select prison cell C3
- Click and drag the fill handle downwards until reaching cell C12
As shown in the picture below, afterwards dragging the fill up handle downwards the formula was copied into the range C3:C12, and the list of names in column A can now be viewed in column C.
You can besides encounter that when selecting prison cell C12, the reference in the formula has adjusted and so that the formula in this cell is now =A12.
How to fill a row with formulas in Google Sheets (Fill Right)
Now allow's wait at an example that uses autofill horizontally. Here will apply "fill right" to fill a row with formulas that refer to cells in another row.
The motion picture below shows a listing of dates in row 1, and we want to display those same dates in row 3.
Formula: The formula beneath is entered initially into cell B3, and and then copied into the range B3:H3, for this example
=B1
Nosotros want to copy this formula into the cells to the right of cell B3, and to exercise this follow these steps:
- Select cell B3
- Click and drag the fill handle right until reaching cell H3
As shown in the picture below, later on dragging the fill handle correct the formula was copied into the range B3:H3, and the list of dates in row one can now be viewed in row three.
How to make full a column with formulas (Combining First and Last Name)
In this instance we volition fill a column with formulas that combine start and concluding name. Combining names is a common task that requires a formula to be copied down a column in Google Sheets.
The picture below shows a list of first names in cavalcade A, and concluding names in column B.
Nosotros want to combine those names then that they appear in "Last, First" format in column D.
Formula: The formula below is entered initially into prison cell D3, and so copied into the range D3:D12, for this example
=B3&", "&A3
Nosotros want to copy this formula into the cells below cell D3, and to practise this follow these steps:
- Select cell D3
- Click and drag the fill handle downwards until reaching prison cell D12
Equally shown in the motion picture below, after dragging the fill handle downward the formula was copied into the range D3:D12, and the list of outset/final names that were in columns A and B are now combined in column D.
(For more than details on using this formula to combine names/lists, read cheque out this article on combining columns.)
How to repeat a formula (Addition and Multiplication)
Permit'south see an example of using this same fill down method with a different type of formula.
This case shows how to echo a formula in Google Sheets when you are using addition and multiplication. Calculation and multiplying whole columns of information is a common task that requires formulas to exist copied down in Google Sheets.
The movie below shows a list of numbers in column A, and a different list of numbers in column B.
The formula(s) in column D volition add together these 2 lists of numbers, and the formula(s) in cavalcade Eastward volition multiply these two lists of numbers.
We will fill/copy both of these formulas at the same time in this case.
Formula: The formulas below are entered initially into cells D3 and E3, and and then copied into the ranges D3:D12 and E3:E12, for this example
=A3+B3 is entered into cell D3
=A3*B3 is entered into cell E3
We want to copy these formulas into the cells below where they are entered, and to do this follow these steps:
- Select cells D3 and E3
- Click and drag the fill handle down until reaching row 12
As shown in the picture below, after dragging the fill handle down the formulas were copied into the cells below row 3, and the add-on and multiplication formulas are now entered into columns D and E respectively.
How to fill down a formula in Google Sheets (IF function)
Hither is yet another instance that shows how to fill up down a formula in Google Sheets, which uses the IF role to demonstrate rapidly copying formulas.
The picture beneath shows a student grade report, where the student names are in cavalcade A and their grades are listed in cavalcade B.
The IF formula that is entered into cell C3 (blue cell), volition brandish the word "Passing" if the grade in cell B3 is more than 0.vi (60%)… and if the class is not more than 0.half dozen the formula will display the word "Fail".
Formula: The formula beneath is entered initially into prison cell C3, and then copied into the range C3:C12, for this example
=IF(B3>0.6,"Passing","Fail")
We desire to copy this formula into the cells below prison cell C3, and to practice this follow these steps:
- Select cell C3
- Click and elevate the fill up handle downwards until reaching cell C12
Every bit shown in the picture below, after dragging the fill handle downwardly the formula was copied into the range C3:C12, and the indication of whether a student is passing/failing can at present be viewed in column C.
How to autofill a table of formulas in Google Sheets
You can besides use autofill to fill an unabridged tabular array with formulas, where the formulas will be copied in both the horizontal and vertical directions at the same time.
In this example we volition fill up a table with multiplication formulas to create a "Times Table".
The picture below shows a table with a list of numbers that increments down column A, and across row 1. We want to copy our initial formula that is entered into jail cell B2 into the whole table of cells, then that each of the formulas multiplies the values that are displayed on the elevation/left side of each column/row.
We volition have to make a special adjustment to the formula references, to make certain that certain parts of the reference do non adjust when the formula is copied.
Observe the dollar signs ($) that I have included in the formula, which lock the column/row indicated direct after information technology from adjusting when cells/formulas are copied. This is what is chosen an accented reference.
In this case, I have implemented these dollar signs / absolute references so that when the formulas are copied to the right, the formulas volition go on referring to the numbers that are listed in column A, but will still adjust horizontally as the numbers in row 1 increase.
Much the same, when the formulas are copied downwardly, the formulas will keep referring to the numbers that are listed in row one, but will still adjust vertically as the numbers in cavalcade A increment.
Formula: The formula below is entered initially into prison cell B2, and and so copied into the range B2:K11, for this example
=$A2*B$1
We want to copy this formula into the cells below and to the right of prison cell B2, and to practice this follow these steps:
- Select cell B2
- Click and elevate the fill handle downwards and to the right until reaching cell K11
As shown in the motion-picture show beneath, after dragging the make full handle downwardly and to the right, the formula was copied into the range B2:K11.
At present yous know how to speedily and easily copy formulas into columns and other specified ranges in Google Sheets!
Source: https://www.spreadsheetclass.com/copy-a-formula-down-an-entire-column-in-google-sheets/
0 Response to "Google Excel Repet the Formula Again"
Postar um comentário