My frequent go-to formulas and their respectives uses. To clarify, some formulas can be applied in both systems (excel/google sheets) but might require some changes to make it work.
Let's say that you have a range of cells where some of them are not entirely empty but are completed with an empty space. This might impact you if you are using these cells for other formulas/purposes, as their data type are not the same (Read: difference between null and an empty space string). There are several ways to check if a cell is actually empty, but this is the one that i would use to check that:
=IF(COUNTBLANK(A1),"is empty","has a blank space")If by any chance you would need the IF statement to tell you which is the cell that was checked, you could also use this formula below, modifying only the "A1" part of it to the cell that you need to check.
=IF(COUNTBLANK(A1),SUBSTITUTE(CELL("address",A1),"$","")&" is empty",SUBSTITUTE(CELL("address",A1),"$","")& " has a blank space")Expected output: A1 is empty, A1 has a blank space
There are some situations where you might need to search for a value using more than one column as a reference. In this case, I would use an Array Index-Match formula (Read: Create an Array Formula in Excel) which allows us to include more than one range of cells as a reference, but before doing this, is important to understand the sintaxis of a normal Index Match formula, which is explained here. The main advantage of using an Index-Match formula instead of a vlookup is the flexibility that it provides (as VLOOKUP can only be used when the lookup value is to the left of the desired attribute to return).
Let's use this hypothetical table as an example:
| A | B | C | |
|---|---|---|---|
| 1 | Animal | Age | Name |
| 2 | Cat | 2 | Missy |
| 3 | Dog | 5 | Confetti |
| 4 | Dog | 4 | Coco |
| 5 | Cat | 4 | Simba |
| 6 | Cat | 3 | Kyra |
If we would use an index match formula to return the name of a dog, we would use
=INDEX($A$1:$C$6, MATCH("dog",$A$1:$A$6,0),2)Expected output: Confetti, since its the first dog name that appears on the table.
Now if we wanted to know which is the name of a 4 y/o Cat, we would need to use an array index match formula:
=INDEX($A$1:$C$6, MATCH("cat"&"4",$A$1:$A$6&$B$1:$B$6,0),2)Expected output: Simba
Note: this formula will only work in excel if you press CTRL + SHIFT + ENTER, otherwise i'll throw a #VALUE error.
Example: The table is over the cat
=SUBSTITUTE("the table is over the cat"," ","")Expected output: Thetableisoverthecat
If for some reason you would need to include nonbreaking space characters, you could also use:
=SUBSTITUTE(A1,CHAR(160),"")There are several ways to create a 'Category' column. In this example I'll show one that helped me on several occassions.
Let's use this table as an example:
| A | B | |
|---|---|---|
| 1 | Category | Product |
| 2 | Cellphone | |
| 3 | Computer | |
| 4 | Computer Desk | |
| 5 | Chair | |
| 6 | Table |
Let's imagine that we have a list of items and we would like to categorize these as 'Technology', 'Furniture' . We could use a nested IF formula similar to this one to do it:
=IF(OR(B4="Cellphone",B4="Computer"), "Technology", IF(OR(B4="Desk",B4="Chair",B4="Table"), "Furniture", "Uncategorized"))However, that formula would bring us issues to categorize "Desk", as the value in the cell B4 says "Computer Desk" and not only "Desk". To fix this, we could rearrange the formula a bit and use this one instead
=IF(OR(B4="Cellphone",B4="Computer"), "Technology", IF(OR(ISNUMBER(SEARCH("Desk",B4)),B4="Chair",B4="Table"), "Furniture", "Uncategorized"))Expected output:
| A | B | |
|---|---|---|
| 1 | Category | Product |
| 2 | Technology | Cellphone |
| 3 | Technology | Computer |
| 4 | Furniture | Computer Desk |
| 5 | Furniture | Chair |
| 6 | Furniture | Table |
What the =ISNUMBER(SEARCH("Desk",B4)) formula does is to search for the position of the word "Desk" inside the string "Computer Desk", and if that formula throws a number (which it would do, if the word is in the string of B4) the =ISNUMBER() would return a TRUE boolean result, and since this is all inside an =OR() formula (which basically executes the TRUE condition of the IF formula, if any of the checks inside the =OR() throw a TRUE result) it ends up returning the category that we wanted, which is 'Furniture'.
Again, this is one of many ways to label a keyword. in Google Sheets you could even use =IFS() to perform a similar function to a nested IF but more legible.
A generic way to replace an #ERROR with a specific text would be to use the =IFERROR formula at the start of your original formula. For Example:
=IFERROR(INDEX($A$1:$C$6, MATCH("dog",$A$1:$A$6,2)),"This is giving me an error")However you can replace some specific errors instead if you need to (e.g: replacing a #N/A error)
=IF(ISNA(INDEX($A$1:$C$6, MATCH("dog",$A$1:$A$6,0),2)),"This is a #NA error", INDEX($A$1:$C$6, MATCH("dog",$A$1:$A$6,0),2))| A | B | C | |
|---|---|---|---|
| 1 | Current Format | Wanted Format | Formula |
| 2 | 10/31/2021 | October 31, 2021 | =TEXT(A2,"mmmm dd, yyyy") |
| 3 | 10/31/2021 | Sunday 31 October, 2021 | =TEXT(A3, "dddd d mmmm, yyyy") |
| 4 | 10/31/2021 | 31/10/2021 | =TEXT(A3, "dd/mm/yyyy") |
You might noticed that I use a =TEXT formula to change these formats, keep in mind that using that formula you are converting the data from the cell to a string, so, for example, if you are suming two cells with a date inside (which you can do), you won't be able to do so with the output of the =TEXT formula, as these values won't be considered numbers but will be considered text.
To do this you need to apply a custom number format in the selected cells. If you need to change the format from 1,000 to 1K, use the following custom number format:
0.0, “K”If instead, for example, you need to change 1,000,000 to 1M, use the following custon number format:
0.0,, “M”Note that these format modifications are only changing the way that the numbers looks in the sheet, but the numbers stored in the cell are the same. If you want to change the numbers from the cell to a different format (e.g: from number format to text format) you can use the =TEXT formula mentioned before. example:
=TEXT(1000,"#,##0,K")Expected output: 1K
Have you ever had a situation where you need to extend the range of your formulas to adapt them to the range of your current data set? Did you ever think about how annoying is to do this manually, specially if you need to update your data set frequently? well, you could use an =INDIRECT formula and if you are creative with it you can readapt ranges in a creative way. for example:
=SUM(INDIRECT("H26:H")&ROW(A296)-1))Would basically translate the formula to this
Expected output: =SUM(H26:H295)
But since it might be complex to use the INDIRECT formula to refer to ranges, there is another way to do this with the use of =ARRAYFORMULA(). Note: using a lot of arrayformulas in a file can make it work slowly or even make it more prone to crash, so keep that in mind in case that you want to implement it for this use.
=ARRAYFORMULA() is, basically, a single formula in a cell with an output that can be multiples rows/columns. For example purposes, let's check the following table:
| A | B | C | |
|---|---|---|---|
| 1 | Name | Surname | Complete Name |
| 2 | Geralt | Of Rivia | |
| 3 | Yennefer | Vengerberg | |
| 4 | Cirilla Fiona | Elen Riannon |
If we needed to complete column "C" with a formula, we could simply concatenate cells in column A and B to do this. for example, we could use =A2&" "&B2 and the output would be this:
| A | B | C | |
|---|---|---|---|
| 1 | Name | Surname | Complete Name |
| 2 | Geralt | Of Rivia | =A2&" "&B2 |
| 3 | Yennefer | Vengerberg | |
| 4 | Cirilla Fiona | Elen Riannon |
Expected output for C2: Geralt of Rivia
However we would need to drag down the formula in C3 and C4 to do the same in those fields. In this case that's easy to do, but as mentioned before, if your table changes frequenly and you don't want to manually drag that down, you could use in the cell C2 of this example =ARRAYFORMULA(A2:A&" "&B2:B) and that would automatically fill the rest!
| A | B | C | |
|---|---|---|---|
| 1 | Name | Surname | Complete Name |
| 2 | Geralt | Of Rivia | =ARRAYFORMULA(A2:A&" "&B2:B) |
| 3 | Yennefer | Vengerberg | |
| 4 | Cirilla Fiona | Elen Riannon |
Expected output:
| A | B | C | |
|---|---|---|---|
| 1 | Name | Surname | Complete Name |
| 2 | Geralt | Of Rivia | Geralt of Rivia |
| 3 | Yennefer | Vengerberg | Yennefer Vergerberg |
| 4 | Cirilla Fiona | Elen Riannon | Cirilla Fiona Elen Riannon |
Here is a recommended video, if you need more examples to understand how to use =ARRAYFORMULA
There is an easy and fantastic formula to import data from one google sheet to another one, and that is =IMPORTRANGE("spreadsheet_url", "range_string"). As a reference, here is the link to the documentation page in case that you need it.
There is also another fantastic formula called =QUERY("data", query, [headers]) that works similar to a query in SQL. I'll also leave here the link to the documentation page for reference.
Now you know what's the best part? you can use both: a =QUERY(=IMPORTRANGE(), query, [headers])) formula to bring information from one tab (protip: you can even use more than one importrange formula to bring information from more than one tab) filtered with the query needed. It's hard to showcase this with Github tables, so I recommend to check this video instead if you are interested in learning how to do this.
This works for both Excel and Google Sheets, but i would recommend to test it in Excel as it's more evident.
Let's use this table as an example:
| A | |
|---|---|
| 1 | 5 |
| 2 | 10 |
| 3 | 3.5 |
| 4 | 9 |
Imagine that you write this formula in another cell: =SUM(A1:A4). If you highlight the A1:A4 part in the formula bar and then you press F9 you'll see the actual values of the range that you selected (In this example, you would see =SUM({5,10,3.5,9}) . This is super useful for formulas that are extremely large, since you might have an error and this trick allows you to debug each part of the formula to see what's really returning.
If you write sheet.new, docs.new or slide.new in your favorite web browser you'll automatically create a new google sheet/doc/slide!
Most frequent shortcut to find things in general is CTRL + F, but you can use CTRL + H in Google Sheet to use directly the Find and Replace tool.
Estas son las formulas y tips que más frecuentemente uso, junto a una descripción de como usarlo. Para aclarar, algunas formulas pueden ser usadas en ambos aplicativos (excel/google sheets) pero puede que requiera algunas modificaciones para que funcionen correctamente.
Let's say that you have a range of cells where some of them are not entirely empty but are completed with an empty space. This might impact you if you are using these cells for other formulas/purposes, as their data type are not the same (Read: difference between null and an empty space string). There are several ways to check if a cell is actually empty, but this is the one that i would use to check that:
=IF(COUNTBLANK(A1),"is empty","has a blank space")If by any chance you would need the IF statement to tell you which is the cell that was checked, you could also use this formula below, modifying only the "A1" part of it to the cell that you need to check.
=IF(COUNTBLANK(A1),SUBSTITUTE(CELL("address",A1),"$","")&" is empty",SUBSTITUTE(CELL("address",A1),"$","")& " has a blank space")Expected output: A1 is empty, A1 has a blank space
There are some situations where you might need to search for a value using more than one column as a reference. In this case, I would use an Array Index-Match formula (Read: Create an Array Formula in Excel) which allows us to include more than one range of cells as a reference, but before doing this, is important to understand the sintaxis of a normal Index Match formula, which is explained here. The main advantage of using an Index-Match formula instead of a vlookup is the flexibility that it provides (as VLOOKUP can only be used when the lookup value is to the left of the desired attribute to return).
Let's use this hypothetical table as an example:
| A | B | C | |
|---|---|---|---|
| 1 | Animal | Age | Name |
| 2 | Cat | 2 | Missy |
| 3 | Dog | 5 | Confetti |
| 4 | Dog | 4 | Coco |
| 5 | Cat | 4 | Simba |
| 6 | Cat | 3 | Kyra |
If we would use an index match formula to return the name of a dog, we would use
=INDEX($A$1:$C$6, MATCH("dog",$A$1:$A$6,0),2)Expected output: Confetti, since its the first dog name that appears on the table.
Now if we wanted to know which is the name of a 4 y/o Cat, we would need to use an array index match formula:
=INDEX($A$1:$C$6, MATCH("cat"&"4",$A$1:$A$6&$B$1:$B$6,0),2)Expected output: Simba
Note: this formula will only work in excel if you press CTRL + SHIFT + ENTER, otherwise i'll throw a #VALUE error.
Example: The table is over the cat
=SUBSTITUTE("the table is over the cat"," ","")Expected output: Thetableisoverthecat
If for some reason you would need to include nonbreaking space characters, you could also use:
=SUBSTITUTE(A1,CHAR(160),"")There are several ways to create a 'Category' column. In this example I'll show one that helped me on several occassions.
Let's use this table as an example:
| A | B | |
|---|---|---|
| 1 | Category | Product |
| 2 | Cellphone | |
| 3 | Computer | |
| 4 | Computer Desk | |
| 5 | Chair | |
| 6 | Table |
Let's imagine that we have a list of items and we would like to categorize these as 'Technology', 'Furniture' . We could use a nested IF formula similar to this one to do it:
=IF(OR(B4="Cellphone",B4="Computer"), "Technology", IF(OR(B4="Desk",B4="Chair",B4="Table"), "Furniture", "Uncategorized"))However, that formula would bring us issues to categorize "Desk", as the value in the cell B4 says "Computer Desk" and not only "Desk". To fix this, we could rearrange the formula a bit and use this one instead
=IF(OR(B4="Cellphone",B4="Computer"), "Technology", IF(OR(ISNUMBER(SEARCH("Desk",B4)),B4="Chair",B4="Table"), "Furniture", "Uncategorized"))Expected output:
| A | B | |
|---|---|---|
| 1 | Category | Product |
| 2 | Technology | Cellphone |
| 3 | Technology | Computer |
| 4 | Furniture | Computer Desk |
| 5 | Furniture | Chair |
| 6 | Furniture | Table |
What the =ISNUMBER(SEARCH("Desk",B4)) formula does is to search for the position of the word "Desk" inside the string "Computer Desk", and if that formula throws a number (which it would do, if the word is in the string of B4) the =ISNUMBER() would return a TRUE boolean result, and since this is all inside an =OR() formula (which basically executes the TRUE condition of the IF formula, if any of the checks inside the =OR() throw a TRUE result) it ends up returning the category that we wanted, which is 'Furniture'.
Again, this is one of many ways to label a keyword. in Google Sheets you could even use =IFS() to perform a similar function to a nested IF but more legible.
A generic way to replace an #ERROR with a specific text would be to use the =IFERROR formula at the start of your original formula. For Example:
=IFERROR(INDEX($A$1:$C$6, MATCH("dog",$A$1:$A$6,2)),"This is giving me an error")However you can replace some specific errors instead if you need to (e.g: replacing a #N/A error)
=IF(ISNA(INDEX($A$1:$C$6, MATCH("dog",$A$1:$A$6,0),2)),"This is a #NA error", INDEX($A$1:$C$6, MATCH("dog",$A$1:$A$6,0),2))| A | B | C | |
|---|---|---|---|
| 1 | Formato Actual | Formato Deseado | Formula |
| 2 | 10/31/2021 | Octubre 31, 2021 | =TEXTO(A2,"mmmm dd, yyyy") |
| 3 | 10/31/2021 | Domingo 31 Octubre, 2021 | =TEXTO(A3, "dddd d mmmm, yyyy") |
| 4 | 10/31/2021 | 31/10/2021 | =TEXTO(A3, "dd/mm/yyyy") |
Habrás notado que usé la formula =TEXTO para cambiar esos formatos. Ten presente que usando esa formula estás efectivamente convirtiendo la data de la celda a un string, por lo que, si por ejemplo, quieres sumar dos fechas de dos celdas distintas (algo posible de hacer), no podrás hacer la sumaa si las celdas están completadas por la formula =TEXTO, ya que esas celdas por más que tengan fechas van a ser consideradas como texto.
Para realizar esto tendrás que aplicar un formato de numero personalizado en las celdas seleccionadas. Si necesitás cambiar el formato de 1,000 a 1K, tendrás que usar el siguiente formato de numero personalizado:
0.0, “K”Si en cambio, por ejemplo, necesitás cambiar el formato de 1,000,000 a 1M, tendrás que usar el siguiente formato:
0.0,, “M”Tené en cuenta que estas modificaciones de formato estan simplemente cambiando la manera en que los numeros se ven en las celdas, pero dentro de ellas el valor sigue siendo el mismo. Si quisieses cambiar el valor DENTRO de la celda, tendrías que usar la formula de =TEXTO combinada con los ejemplos de formato mencionados arriba. Por ejemplo:
=TEXTO(1000,"#,##0,K")Resultado esperado: 1K
Have you ever had a situation where you need to extend the range of your formulas to adapt them to the range of your current data set? Did you ever think about how annoying is to do this manually, specially if you need to update your data set frequently? well, you could use an =INDIRECT formula and if you are creative with it you can readapt ranges in a creative way. for example:
=SUM(INDIRECT("H26:H")&ROW(A296)-1))Would basically translate the formula to this
Expected output: =SUM(H26:H295)
But since it might be complex to use the INDIRECT formula to refer to ranges, there is another way to do this with the use of =ARRAYFORMULA(). Note: using a lot of arrayformulas in a file can make it work slowly or even make it more prone to crash, so keep that in mind in case that you want to implement it for this use.
=ARRAYFORMULA() is, basically, a single formula in a cell with an output that can be multiples rows/columns. For example purposes, let's check the following table:
| A | B | C | |
|---|---|---|---|
| 1 | Name | Surname | Complete Name |
| 2 | Geralt | Of Rivia | |
| 3 | Yennefer | Vengerberg | |
| 4 | Cirilla Fiona | Elen Riannon |
If we needed to complete column "C" with a formula, we could simply concatenate cells in column A and B to do this. for example, we could use =A2&" "&B2 and the output would be this:
| A | B | C | |
|---|---|---|---|
| 1 | Name | Surname | Complete Name |
| 2 | Geralt | Of Rivia | =A2&" "&B2 |
| 3 | Yennefer | Vengerberg | |
| 4 | Cirilla Fiona | Elen Riannon |
Expected output for C2: Geralt of Rivia
However we would need to drag down the formula in C3 and C4 to do the same in those fields. In this case that's easy to do, but as mentioned before, if your table changes frequenly and you don't want to manually drag that down, you could use in the cell C2 of this example =ARRAYFORMULA(A2:A&" "&B2:B) and that would automatically fill the rest!
| A | B | C | |
|---|---|---|---|
| 1 | Name | Surname | Complete Name |
| 2 | Geralt | Of Rivia | =ARRAYFORMULA(A2:A&" "&B2:B) |
| 3 | Yennefer | Vengerberg | |
| 4 | Cirilla Fiona | Elen Riannon |
Expected output:
| A | B | C | |
|---|---|---|---|
| 1 | Name | Surname | Complete Name |
| 2 | Geralt | Of Rivia | Geralt of Rivia |
| 3 | Yennefer | Vengerberg | Yennefer Vergerberg |
| 4 | Cirilla Fiona | Elen Riannon | Cirilla Fiona Elen Riannon |
Here is a recommended video, if you need more examples to understand how to use =ARRAYFORMULA
There is an easy and fantastic formula to import data from one google sheet to another one, and that is =IMPORTRANGE("spreadsheet_url", "range_string"). As a reference, here is the link to the documentation page in case that you need it.
There is also another fantastic formula called =QUERY("data", query, [headers]) that works similar to a query in SQL. I'll also leave here the link to the documentation page for reference.
Now you know what's the best part? you can use both: a =QUERY(=IMPORTRANGE(), query, [headers])) formula to bring information from one tab (protip: you can even use more than one importrange formula to bring information from more than one tab) filtered with the query needed. It's hard to showcase this with Github tables, so I recommend to check this video instead if you are interested in learning how to do this.
Funciona tanto para excel como para google sheets, aunque es recomendado que a fines de aprendizaje se testee esto dentro de un excel, ya que es más evidente.
Pensemos en la siguiente tabla como un ejemplo:
| A | |
|---|---|
| 1 | 5 |
| 2 | 10 |
| 3 | 3.5 |
| 4 | 9 |
Imaginemos que escribes ésta formula en una celda: =SUMAR(A1:A4). Si resaltas la parte de A1:A4 en la barra de formula y después aprietas F9 vas a ver el valor actual de las celdas que seleccionaste (En este ejemplo, si seleccionas solamente A1:A4 y aprietas F9 veerías en la barra de formula =SUMAR({5,10,3.5,9}). Esto es super útil para debugguear formulas muy extensas, ya que puede que tengas un error en alguna parte y haciendo esto parte por parte puedes chequear donde está el error.
Si escribís sheet.new, docs.new o slide.new en el buscador de tu browser favorito (chrome/firefox/brave/etc)y apretás enter vas a crear automáticamente un nuevo google sheet/doc/slide!
Es sabido que el atajo más conocido para buscar algo tanto en Google Sheets como en otros aplicativos es CTRL + F, pero en Google Sheets también podes usar CTRL + H para directamente usar la herramienta de "Buscar y Reemplazar".