Spreadsheets represent an incredible revolution that helped systematize, order and process large amounts of data from various sectors in any of the services that we use in our daily activities.
Although the system of use par excellence for this work is usually Microsoft Excel, currently we can access other free alternatives with equally interesting functions.
If we need to make use of the basic tools of Excel and we do not want to acquire it, we can have access to options such as Google Sheets, which works like a spreadsheet with the gadgets that you might need , in which are its wide set of formulas.
To fully exploit these features, we must know well how these formulas work and how to apply them correctly, starting with the basic formulas that give us access to functions that save us a lot of work, such is the case of the “ARRAYFORMULA “ formula , which will allow you to drag formulas to other cells automatically.
In this article we will be in charge of explaining how to correctly apply “ARRAYFORMULA” and the most common cases in which its use will be presented.
- How to do to apply a formula to a whole column?
- How to copy and paste formulas in Google Sheets?
- For a single grid
- For a whole column
- Learn how to import data from other documents to Google Sheets
How to do to apply a formula to a whole column?
When we are working with any type of calculations or formulas in spreadsheets, we often find that we have to organize data distributed in long columns, making the calculation for each cell a tedious work .
The common method to avoid copying the formula cell by cell is to drag the formula to the end of the column , but this process is also slow so the fastest option is to use the ARRAYFORMULA formula , than with the simple use of a command will allow us to drag the entire formula through the column automatically.
How to copy and paste formulas in Google Sheets?
ARRAYFORMULA is an array or matrix type formula , which is written in one cell, but can express results in others, being possible to apply it for the following cases:
For a single grid
For the case in which we only need to copy a single formula from one cell to another, we use the following command: “= ArrayFormula (A1)” . Where A1 stands out as the cell from which we want to extract the formula, which can be located anywhere in the spreadsheet.
It is important to note that this function is not limited only to copying and pasting a series of formulas from one place to another, but it will also give us the possibility of making changes within the argument of the formula, such as inserting other functions such as VLOOKUP as in the following example: “= ArrayFormula (VLOOKUP (A1))”
In this way we can greatly simplify the procedure to perform various calculations, avoiding the hassle of having to copy the entire cell again, it would only be necessary to add the necessary function and Google Sheets will take care of the rest.
In addition, it should be noted that, within its argument, ARRAYFORMULA also allows us to perform mathematical calculations in its argument, facilitating our work.
For a whole column
Although it is useful to know this process to replicate cells, its true utility is highlighted when handling large volumes of data in columns, for which we will only have to apply the formula in the first cell as follows: “= ArrayFormula (A1 : Ax) ”
Where A1 represents the first cell of the column and Ax represents the last cell , in this way it will only be necessary to press enter and we can see how the calculation extends throughout the column. In addition, we can also perform operations or function changes as shown in the following image.
Learn how to import data from other documents to Google Sheets
Another aspect that is usually important to know is how to take or share information from other Google Sheets documents , since many times these are composed of complex calculations that take a long time to perform. In order to import them all into a new document, we will use the following formula: “= IMPORTRANGE (« »,« »)”
Where we have to place in “URL>” the address where the spreadsheet to be imported is located . Later, we will place the range of cells that we are going to import in «» , so that it is expressed as follows: “= IMPORTRANGE (« http://docs.google.com/spreadsheets/d/URL/to/spreadsheet/ edit »,« A2: A27 »)”
Finally, we will press accept and we will see how an error is displayed in the cells, this error appears because a prior approval is needed to access the document we want to import, we just have to right click on the cells with error and select “Allow access” .