27 Excel Formulas to Turn into Shortcuts

27 Excel Formulas to Turn into Shortcuts

0 Mins Read

27 Excel Formulas to Turn into Shortcuts

Share

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

  • Explainer: =LEFT([[URL]],FIND("/",[[URL]],9))

  • Example: =LEFT(a1,FIND("/",a1,9))

  • 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.

Make tasks disappear.
Like magic.

Slash through repetitive tasks in seconds by teleporting data between your tabs.

Chrome Store · 4.6 stars · 3,200+ reviews