When we are using Excel, one of the actions that we will surely use are the formulas, which, as we have already seen in how to make formulas in Excel, are a resource that is as useful as it is necessary when using the calculation tools par excellence: Microsoft Excel ..
Knowing how to use the formulas in Excel, together with the use of the functions, and the graphic section, is probably one of the most important parts in learning Excel. Knowing how to formulate in Excel will give us the possibility to make calculations and obtain valuable data that will help us to represent and analyze the information in depth, objectively, and to be able to make the most correct decisions.
Through the formulas in Excel, we will be able to perform simple calculations and addition, subtraction, multiplication and division operations; But we are also going to be able to do more complex calculations with the use of functions, which we are going to explain in detail, and in a simple way..
In Excel, order is important, and organization is key to being able to carry out our mission, successfully and without errors. Organize the work, the data in Excel will be the basis to be able to build data tables, reports and dashboards;
Organizing the data in tables is important because in this way we will be able to better use the Excel resources . As we already explained how to make formulas in Excel, one of those resources that we are going to be able to take advantage of is being able to do, for example, summations of an entire column or row thanks to the use of the sum or autosum function.
The organization of the data goes far beyond being an advice or a recommendation. Although we have said at the beginning, and on other occasions, that the use of formulas is probably one of the most important aspects in learning Excel, when we are making tables and doing calculations, we are going to find a way to progress much faster. in our task. Thanks to the formulas, thanks to the organization of the data in tables, and thanks to the copy and paste (and drag) function, which as we are going to see now is especially useful when working with formulas..
Why is it important to copy and paste formulas in Excel?
Copying and pasting, as we are going to explain, is a very important Excel functionality, considering three key factors when it comes to working productively and reliably: Speed, consistency and editing.
- Speed: Using Copy and paste formulas in Excel, we will be able to quickly apply the formulas in other cells; It saves us from having to write each formula by hand. When, for example, we carry out calculations in a column, we will be able to carry out a formula in the first cell, and later we will be able to paste it in all the cells of the column. This is one of the reasons why working in an orderly manner in Excel and in columns is key.
- Consistency in calculations, avoiding errors: When we copy and paste formulas, we are applying the same "structure" of the formula. In this way we avoid making formulation errors, we would be applying the same formula logic to the cells where we are pasting it. This characteristic makes it a very useful and productive resource to work on. It will be especially useful when we are making simple and complex calculations as we do not have to perform the same formula over and over again.
- Changes and editions in the formulas faster: If we need to update a formula, being able to apply this change to other cells will not be a problem, since as we have seen, we will not have to make the change in all the formulas. We would make the change in one of the formulas, and then we would copy and paste it into the cells where we want to update the formula.
Important considerations when copying and pasting formulas
When we talk about copy and paste the same formula, we should keep in mind that
Excel copies and pastes the same “formula logic”, copy and pastes the “formula structure” . In practice, when we paste a formula that has a formula reference, Excel will adapt to the cell where we are pasting the formula.
When we copy and paste, Excel will not perform the same calculation with the same cells, but will use logic to adapt the formula to the cell where we are pasting it, taking as a reference in the formula, those cells that are inside the same row or the same column.
And what if we have fixed values in the formula? In case the formula contains fixed values, when copying and pasting the formula these values are maintained, since it is not possible to apply a logic or a structure in this case.
We are going to see below how to copy and paste cells in Excel, step by step and with examples to better see how it works in practice, and how we can take advantage of this Excel functionality.
1 How to copy and paste formulas in Excel
In order to copy and paste a formula in Excel, we are going to use the mouse or mouse on our keyboard, to place ourselves in the cell that contains the formula and press the Ctrl + C keys on our keyboard.
Next, we place ourselves in the cell where we want to paste the formula and press the keys of our keyboard Ctrl + V.
How You can see in the image , when copying and pasting the formula in the cell below, Excel applies the logic to be able to copy the structure and apply it in the cell where we are pasting it . In the image above we can see how the references of the cells of the formula that we have pasted have changed and adapt to the place where we are pasting the formula.
Thus, in the image we can see that in the cell where we have pasted the formula, to calculate the income, the logic of multiplying the number of products sold by the product price is maintained ; But the referenced cells in this case are being taken from the same row where the formula we have pasted is located.
When we copy a formula, we can paste this formula into another cell, or we can select a range of cells and paste the formula into multiple cells at once. This is a functionality that allows us to go even faster, since we do not have to go cell by cell pasting the formula, but before pressing the "Ctrl + V" keys to paste the data, what we will do is select with the mouse of our computer the cells or range of cells where we want to paste this formula.
Let's see below, what steps we must follow to be able to copy and paste formulas in Excel using an example.
Steps to copy and paste formulas in Excel
We are going to make a formula, specifically a multiplication, to be able to calculate the income of a company. To carry out this operation, we are going to multiply the cells of the column of the number of sales, by the columns that contain the price of the product.
- We are going to carry out the formula in a cell, and we begin by writing the “=” sign.
- After the "=" we choose the cells that are in the same row where we are making the formula, in the columns No. of Sales, and price, separating as we already know both cells referenced in the formula with the operator or multiplication sign (* ), as we show you in the image:
By pressing the “Enter” or “Intro” key on our keyboard, we will see the calculated value as a result of the multiplication that we have just performed.
Next, we are going to position ourselves with the keyboard or using the mouse in the cells where we have just performed the multiplication, and we are going to press the Ctrl + C keys to copy the formula. Using the mouse, we are going to select the cells where we want to paste the formula, as in the image:
After selecting the data range, we press “Ctrl + V” and we will see how all the cells that we have selected are filled.
Now we are going to verify that the operation we have carried out is correct. Select one of the cells where we have pasted the data and double click, or press the "F2" key on our keyboard. As we see in the image below, as we have explained previously, Excel has used the logic and structure of the formula to be able to apply it to all the cells that we have selected. Thus, as in the image, we see in each cell where the formula has been pasted, it takes the sale number and the price, but from the same row.
In the example, we have been able to see how “copying and pasting” formulas works in practice. Thus we have been able to see how when pasting the formula, logic is used so that the calculation is correct. We have also been able to see how we can paste the formula in a range of cells, to be able to go faster when building our table when we are performing calculations, and we have also seen how to check that the formula has been pasted correctly in each cell.
2 How to drag or stretch formulas
Another of the actions that we will be able to carry out when we have made a formula is to "drag" or "stretch" it to adjacent cells. In general, we will use the functionality of being able to drag a formula vertically, that is, to cells that are immediately above or below. In this way, as long as we are working or building a data table in Excel with headers and columns, when dragging the formula up or down Excel will copy the structure of the formula but will adapt it to the cell where we are pasting the formula, as we have explained before in the example.
Important:
We can also stretch the formulas to cells that are on one side or the other, although we must take into account that, by stretching the formula to one side, the formula references will also move horizontally, and we will have to see if this makes sense with the table that we are making, and if the formula when pasting it in the new cell takes the correct references to perform the calculation that we really want to do.
How to edit a formula in Excel: Change reference in formulas
We remind you that the references of a formula can be changed at any time by double-clicking on the cell containing the formula, or by pressing the F2 key. When the cells referenced in the formula are marked in color, we can drag them to other cells by clicking without releasing one of the borders that we will see in color and drag it to another cell.
To modify a formula in Excel, we can also place ourselves in the cell that contains the formula, and change the cell reference directly in the formula bar using our keyboard.
From the formula bar, we can change any part of our formula: a formula reference, an operator (or operation sign), a fixed value, or a function.
Steps to drag a formula in Excel
To explain how to drag a formula, we're going to use the same example as before, the example we used to copy and paste a formula. In this way, we will be able to see how dragging or stretching a formula in practice works similarly to copying and pasting formulas.
Differences between copy and paste vs. drag formulas in Excel
The difference between dragging formulas and copy and paste lies in the freedom we have when it comes to being able to paste the formula. Whereas when we copy a cell, we can paste it anywhere in the document, when we drag a formula we are applying the formula to neighboring cells.
To drag a formula in Excel, the first thing we do is type the formula in a cell. The formula that we are going to carry out is the same as that we have done before, in order to know the income of a company, we are going to multiply the number of sales made by the price of each product. As you can see in the image, we work on a table perfectly organized and structured in columns.
After performing the formula, and as we already know, we press the "Enter" key on our keyboard to see the result of the formula we just performed.
We have made the formula for one of the rows, specifically for the first row. Now what we want to do is stretch the formula down so that the same formula logic is performed on all rows.
Now instead of copying the formula, we are going to place ourselves with the keyboard or the mouse in the cell where the formula is that we are going to drag. When we place ourselves in the cell that contains the formula we will see that it is highlighted, and we can see a small square in the lower right corner of the cell.
Now to drag the formula you have to click and hold the left mouse button on that small square located in the lower right corner of the cell, and drag it down to the last cell where we want to apply the formula, releasing the mouse button. mouse once you have selected the range of cells to which you want to apply the formula. We will then see that the formula has been applied to all those cells.
Now, we check how we already know that the formula has been dragged and applied correctly, selecting one of the cells where we have dragged the formula by double-clicking, or by pressing the “F2” key on our keyboard.
As we have seen in the examples, copying and pasting in Excel, and dragging formulas are two Excel features that allow us to move much faster and safely. In practice and in the use of Excel, they are functionalities that we will surely use to be able to work productively, and consistently.
Excel, as we have seen, has a logic that applies when we use some of its features, as is the case. Thus, when we use these features by pasting formulas in other cells, or by dragging the formulas, we can see how in each cell what Excel has really copied and pasted is the structure of the formula. In this way the formula when pasted or dragged, adapts in each cell to perform the calculation.
When we use these functionalities, and especially at the beginning, when we are learning to use Excel, it is equally important to always have a culture of checking the result obtained, in this case to be able to confirm that the formula is in other cells by copying and pasting it or by dragging it, it is being applied correctly.
In this sense, we already know that at any time we can press the “F2” key on our keyboard, or double-click to see the formula we are carrying out, in the operations bar, and to see what cell reference is being used.
The importance of keeping an order and starting to build tables in Excel is evident in this case where, thanks to the structure of the table, and the order of the information that is collected in columns, it allows us to be able to stretch a formula downwards. instead of having to copy and paste the formula into each cell.
