Even an Excel wiz can be forgetful. How many times have you stared at a spreadsheet and thought, "I know there's a way to do this, but..."
Thanks to the power of Magical's Shortcuts, you can now create super-helpful reminder lists for all your favorite formulas. For example, you could create a list that you summon using a "//formulas" command that contains the following:
Vlookup: Use me to find related data for a value
Concatenate: Use me to combine cells
Trim: Use me to get rid of spaces
Iferror: Use me to blank out results when a formula doesn't work
However, if you want a bit more hand-holding, or you want to drop a sample version of a formula into your online spreadsheet instead of looking up a helpful description, you can do that, too! Create a snippet, give it any name you want, insert the formula you usually use, and create a mental reminder that you have to change the variables to get it to work with new spreadsheets you create (unless you keep the same template for each one).
To help you get started, here are a bunch of our favorite formulas, ranging from the simple to the complex, that you can add as individual snippets. We've even given you a sample snippet name you can use, as well as two versions of each formula: One with variables you need to change explained within "[[" and "]]" brackets, and another that's a more plain version with a text explanation of what it's doing.
(And, yes, you could even use the aforementioned example—//formuas—to create a quick reference list for all your "//" shortcuts, in case you start amassing a lot.)
Vlookup
Snippet name: //vl
Explainer: =VLOOKUP([[What You're Looking For]],[[Table size]],[[The data you want is # of columns to the right]],false)
Example: =VLOOKUP(e1,a1:d10,4,false)
This looks up the value in cell E1 within the A column of the table that spans from A1 to D10. It then returns the value in the fourth column, or D
Combine text from different cells
Snippet name: //combine
Explainer: =CONCAT([[first value]],[[second value]],[[...]])
Example: =CONCAT(A1,” “,C3)
This combines the values in A1 and C3 into a single cell (and drops a space in between).
What’s the first word?
Snippet name: //fw
Explainer: =LEFT([[CELL]],FIND(" ",[[CELL]])-1)
Example: =LEFT(A1,FIND(" ",A1)-1)
This returns the very first word (before the first space) in cell A1.
What’s the last word?
Snippet name: //lw
Explainer: =TRIM(RIGHT(SUBSTITUTE([[CELL]]," ",REPT(" ",100)),100))
Example: =TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",100)),100))
This returns the very last word (after the last space) in cell A1.
Trim unnecessary spaces
Snippet name: //spaces
Explainer: =trim([[cell]])
Example: =trim(a1)
This removes any unnecessary spaces found in cell A1.
Verify email addresses
Snippet name: //email
Explainer: =isemail([[cell]])
Example: =isemail(a1)
Tells you if the value in cell A1 is a properly formatted email address
Figure out the average of a series of numbers
Snippet name: //avg
Explainer: =average([[cell range]])
Example: =average(a:a)
Gives you the average of every number in the first column
Count how many cells with numbers are in a range
Snippet name: //ct
Explainer: =count([[cell range]])
Example: =count(a:a)
Counts up every single number (not text; number) in the first column
Add all the numbers in a range
Snippet name: //sum
Explainer: =sum([[cell range]])
Example: =sum(a:a)
Adds together every number in the first column and gives you the total
Count how many cells in a range match a particular value
Snippet name: //ctif
Explainer: =countif([[range of cells]],[[what you’re looking for]])
Example: =countif(a:a,b1)
This counts how many cells in the first column match whatever you’ve written in cell B1
Capitalize text automatically
Snippet name: //cap
Explainer: =proper([[cell]])
Example: =proper(a1)
This automatically fixes the capitalization of any text in cell A1 by making each word start with a capital letter. (Use =lower to do the opposite.)
Look up an exact value based on two separate variables
Snippet name: //inmat
Explainer: =index([[size of your table]],match([[first variable]],[[column where you’ll find it]],0),match([[column name]],[[row where you’ll find it]],0))
Example: =index(A1:D10,match("Frank",A1:A10,0),match("march",A1:D1,0))
This one’s a little complicated, but it’s incredibly useful. Suppose you have a bunch of names in Column A and a bunch of months in Row 1. You want to look up how many sales a certain person got in a particular month. With this formula, you’ll use MATCH functions to find the row where that person appears and the column of the month you’re looking for, and pipe that into an INDEX formula that will return the desired result. You’re basically creating your own little spreadsheet search tool.
Find how many business days are between two dates
Snippet name: //workdays
Explainer: =NETWORKDAYS([[first date]],[[second date]])
Example: =NETWORKDAYS(A1,A2)
This counts the number of workdays between dates in cells A1 and A2.
Add up all the values that fit a particular criteria
Snippet name: //sumif
Explainer: =SUMIF([[range of cells you’re adding up]],[[What’s the criteria?]])
Example: =SUMIF(A:A,">20")
This counts all the values in column A that are greater than 20.
Average all the values that fit a particular criteria
Snippet name: //avgif
Explainer: =AVERAGEIF([[range of cells you’re adding up]],[[What’s the criteria?]])
Example: =AVERAGEIF(A:A,">20")
This averages all the values in column A that are greater than 20.
Rank the values in a column
Snippet name: //rank
Explainer: =RANK([[Cell to rank]],[[range of cells]],[[ascending =”1”]])
Example: =RANK(A1,A:A,1)
This ranks the value in cell A1 against all the other values in column A, and said rank goes in ascending order (smallest number = “1”)
Find the lowest value of an item in a list
Snippet name: //min
Explainer: =MINIFS([[all the values you’re searching]],[[all the items you’re searching]],[[exact item you want to find]])
Example: =MINIFS(B:B,A:A,”dog”)
If we have a large list of pet types (column A) and their weights every time they’ve been weighed (column B), this returns the lowest weight for “dog” that it can find.
Find the highest value of an item in a list
Snippet name: //max
Explainer: =MAXIFS([[all the values you’re searching]],[[all the items you’re searching]],[[exact item you want to find]])
Example: =MAXIFS(B:B,A:A,”dog”)
If we have a large list of pet types (column A) and their weights every time they’ve been weighed (column B), this returns the highest weight for “dog” that it can find.
How many cells are not empty?
Snippet name: //empty
Explainer: =COUNTIF([[range of cells]],"<>")
Example: =COUNTIF(A:A,"<>")
This formula counts how many cells in column A are not empty.
Add up all the values of an item in a list
Snippet name: //sumlist
Explainer: =SUMPRODUCT(A:A="dogs",B:B)
Example: =SUMPRODUCT(A:A="dogs",B:B)
TKTKTK
List all the unique values in a row or column
Snippet name: //unique
Explainer: =UNIQUE([[data range]])
Example: =UNIQUE(a:a)
This formula returns all the unique vales within a range of data (column A, in this example).
Combine and separate cells by comma (ignoring all blank cells)
Snippet name: //join
Explainer: =TEXTJOIN("[[how you’ll separate the text]]",[[“true” to ignore spaces]],[[data range]])
Example: =TEXTJOIN(", ",true,1:1)
This links together the text in every cell in row 1, with each individual cell’s contents being separated by a comma and a space.
Rip a domain name from an email address
Snippet name: //ripemail
Explainer: =RIGHT([[email address]],LEN([[email address]])-FIND("@",[[email address]]))
Example: =RIGHT(A1,LEN(A1)-FIND("@",A1))
This formula looks up an email address in cell A1 and returns its domain name
Rip a domain name from a URL
Snippet name: //ripweb
This formula looks up an URL in cell A1 and returns the domain name. If you want to get really fancy, you can use this complicated formula to remove the trailing slash: =LEFT(LEFT(A1,FIND("/",A1,9)),LEN(LEFT(A1,FIND("/",A1,9)))-(RIGHT(LEFT(A1,FIND("/",A1,9)))="/"))
Create a simple histogram of your data
Snippet name: //hist
Explainer: =REPT("*",[[Cell with data]]/[[scale factor]])
Example: =REPT("*",A1/100)
In this formula, we’re taking whatever value is in A1, dividing it by 100, and representing each of these “chunks” as an asterisk. You can change the scale (what you’re dividing by) to whatever you want depending on how big or small your values go. And, of course, you can change the asterisk to any symbol you want, too.
Create a quick filter for your data
Snippet name: //filter
Explainer: =FILTER([[data you want to extract]],[[qualifier for the filter]],[[what to do if the filter matches nothing]])
Example: =FILTER(A:C,A:A=1)
In this case, we have a data table with data in columns A, B, and C. We want to only show rows where the value in column A matches the number “1.”
Wrap formulas in “iferror” to hide broken results
Snippet name: //error
Explainer: =IFERROR([[your normal formula]],[[what to show if an error is present]])
Example: =IFERROR(VLOOKUP("dogs",A:C,3),"0")
Wrap your formulas in an “IFERROR,” and you’ll be able to return any value you want, even a blank cell (“”), if it messes up. In this case, if our VLOOKUP doesn’t find the value “dogs” in our data, it simply returns a “0” in the cell, rather than an ugly error.
For a quicker shortcut, simply use =IFERROR( as your snippet. That way, you can easily fill in the rest of your formula and close it with a parentheses.
Convert one unit of measurement to another
Snippet name: // followed by whatever measurement you want
Explainer: =CONVERT([[cell with data]],[[original measurement]],[[new measurement]]
Example: =CONVERT(A1,"min","hr")
This formulas takes the value in A1 and converts it from minutes into hours.
