All the formulas, functions, shortcuts, and tips you need to master Excel.
These days, knowing how to use Microsoft Excel is so expected that it hardly warrants a line on our resumes. But, let's be honest here: How well do you really know how to use it?
You may know how to plug in numbers and add up cells in a column, but that's not going to get you far when it comes to reporting on your metrics.
Gone are the days when marketers could rely on their gut for important business decisions. More than likely, you've already been tasked with identifying trends within customer survey results, performing content topic analyses, or pulling in sales data to calculate return on investment. What do all these things have in common? They require a bit more Excel knowledge than what we learned in grade school.
Whether you’d like to create more beautiful charts, finally understand what pivot tables are, or complete your first VLOOKUP (I promise it’s not as scary as it sounds), this guide will teach you everything you need to make more informed marketing decisions, in less time.
Aspiring Excel wizard, read on. Feel free to email, bookmark, or jump to the section that interests you most. And to all the Harry Potter fans out there … you’re welcome in advance.
Sometimes, Excel seems too good to be true. Need to combine information in multiple cells? Excel can do it. Need to copy formatting across an array of cells? Excel can do that, too.
In fact, if you ever encounter a situation where you need to manually update or calculate your data, you’re probably missing out on a shortcut.
Before spending hours and hours counting cells or copying and pasting data, look for a quick fix in Excel — you’ll likely find one.
In the spirit of working more efficiently and avoiding tedious, manual work, let’s start this Excel deep dive with the basics. Once you have these functions down, you’ll be ready to tackle the advanced Excel lessons head on.
As you play around with your data, you might find you're constantly needing to add more rows and columns. Sometimes, you may even need to add hundreds of rows. Doing this one-by-one would be super tedious. Luckily, there's always an easier way.
To add multiple rows or columns in a spreadsheet, highlight the same number of pre-existing rows or columns that you want to add. Then, right click and select "Insert."
In the example below, I want to add an additional three rows. By highlighting three rows and then clicking insert, I'm able to add an additional three blank rows into my spreadsheet quickly and easily.
If you have any basic Excel knowledge, it’s likely you already know this quick trick. But to cover our bases, allow me to show you the glory of Autofill. This lets you quickly fill adjacent cells with several types of data, including values, series, and formulas.
There are multiple ways to deploy this feature, but the fill handle is among the easiest. Select the cells you want to be the source, locate the fill handle in the lower-right corner of the cell, and either drag the fill handle to cover cells you want to fill or just double click.
When you're looking at very large data sets (as marketers often do), you don't usually need to be looking at every single row at the same time. Sometimes, you only want to look at data that fit
Filters allow you to pare down your data to only look at certain rows at one time. In Excel, a filter can be added to each column in your data. From there, you can then choose which cells you want to view at once.
Let's take a look at the example below. Add a filter by clicking the Data tab and selecting "Filter." Click the arrow next to the column headers and you'll be able to choose whether you want your data to be organized in ascending or descending order, as well as which rows you want to show.
In my Harry Potter example, let's say I only want to see the students in Gryffindor. By selecting the Gryffindor filter, the other rows disappear.
Pro Tip: Copy and paste the values
Sometimes you may have a list of data that has no organization whatsoever. Maybe you exported a list of your marketing contacts or blog posts. Whatever the case may be, Excel’s sort feature will help you alphabetize any list.
Click on the data in the column you want to sort. Then click on the "Data" tab in your toolbar and look for the "Sort" option on the left. If the "A" is on top of the "Z," you can just click on that button once. If the "Z" is on top of the "A," click on the button twice. When the "A" is on top of the "Z," that means your list will be sorted in alphabetical order. However, when the "Z" is on top of the "A," that means your list will be sorted in reverse alphabetical order.
Larger datasets tend to have duplicate content. You may have a list of multiple contacts in a company and only want to see the number of companies you have. In situations like this, removing the duplicates comes in handy.
To remove your duplicates, highlight the row or column that you want to remove duplicates of. Then, go to the Data tab, and select "Remove Duplicates" (under Tools). A pop-up will appear to confirm which data you want to work with. Select "Remove Duplicates," and you're good to go.
You can also use this feature to remove an entire row based on a duplicate column value. So if you have three rows
Often, you’ll want to transform the items in a row of data into a column (or vice versa). It would take a lot of time to copy and paste each individual header. Not to mention, you may easily fall into one of the biggest, most unfortunate Excel
Instead, let Excel do the work for you. Go ahead and highlight the column or row you want to transpose.
Paste Special is one function I find myself coming back to time and time again. In the module, you can also choose between copying formulas, values, formats, or even column widths. This is especially helpful when it comes to copying the results of your pivot table (we’ll get there…) into a chart you can format and graph.
What if you want to split out information that's in one cell into two different cells? For example, maybe you want to pull out someone's company name through their email address. Or perhaps you want to separate someone's full name into a first and last name for your email marketing templates.
Thanks to Excel, both are possible. First, highlight the column that you want to split up. Next, go to the Data tab and select "Text to Columns." A module will appear with
In the example case below, let's select "Delimited" so we can separate the full name into first name and last name.
Then, it's time to choose the delimiters. This could be a tab, semicolon, comma, space, or something else. ("Something else" could be the "@" sign used in an email address, for example.) In our example, let's choose the space. Excel will then show you a preview of what your new columns will look like.
When you're happy with the preview, press "Next." This page will allow you to select Advanced Formats if you choose to. When you're done, click "Finish."
As you’ve probably noticed, Excel has a lot of features to make crunching numbers and analyzing your data quick and easy. But if you ever spent some time formatting a sheet to your liking, you know it can get a bit tedious.
Don’t waste time repeating the same formatting commands over and over again. Use the format painter to easily copy the formatting from one area of the worksheet to another. To do so, choose the cell you’d like to replicate, then select the format painter option (paintbrush icon) from the top toolbar.
Creating reports in Excel is time-consuming enough. How can we spend less time navigating, formatting, and selecting items in our spreadsheet? Glad you asked. There are a ton of Excel shortcuts out there, including some of our favorites listed below.
PC: Ctrl-N | Mac: Command-N
PC: Shift-Space | Mac: Shift-Space
PC: Ctrl-Space | Mac: Control-Space
PC: Ctrl-Shift-Down/Up | Mac: Command-Shift-Down/Up
PC: Ctrl-Shift-Right/Left | Mac: Command-Shift-Right/Left
PC: Ctrl-K | Mac: Command-K
PC: Ctrl-1 | Mac: Command-1
PC: Alt-= | Mac: Command-Shift-T
At this point, you’ve familiarized yourself with Excel’s interface and are already flying around your spreadsheets faster than Harry Potter on his Nimbus 2000. Let’s dive into the core use case for the software: Excel formulas. Excel can help you do simple arithmetic like adding, subtracting, multiplying, or dividing any of your data.
Remember, all formulas in Excel must begin with an equal sign (=). Use parentheses to ensure certain calculations are done first. For example, consider how =10+10*10 is different than =(10+10)*10.
In addition to manually typing in simple calculations, you can refer to Excel’s built-in formulas. Some of the most common include:
Also note that series of specific cells are separated by a comma (,), while cell ranges are notated with a colon (:). For example, you could have =SUM(4,4) or =SUM(A4,B4) or =SUM(A4:B4).
Conditional formatting allows you to change a cell's color based on the information within the cell. For instance, if you want to flag certain numbers that are above average or in the top 10% of the data in your spreadsheet.
To get started, highlight the group of cells you want to use conditional formatting on. Then, choose "Conditional Formatting" from the Home menu and select your logic from the dropdown. (You can also create your own rule if you want something different.) A window will pop up that prompts you to provide more information about your formatting rule. Select "OK" when you're done, and you should see your results automatically appear.
Have you ever seen a dollar sign in an Excel formula? When used in a formula, it isn't representing an American dollar; instead, it makes sure that the exact column and row are held the same even if you copy the same formula in adjacent rows.
You see, a cell reference — when you refer to cell A5 from cell C5, for example — is relative by default. In that case, you're actually referring to a cell that's five columns to the left (C minus A) and in the same row (5). This is called a relative formula. When you copy a relative formula from one cell to another, it'll adjust the values in the formula based on where it's moved. But sometimes, we want those values to stay the same no matter whether they're moved around or not. We can do that by making the formula in the cell into what's called an absolute formula.
To change the relative formula (=A5+C5) into an absolute formula, we'd precede the row and column values by dollar signs, like this: (=$A$5+$C$5).
Databases tend to split out data to make it as exact as possible. For example, instead of having a data that shows a person's full name, a database might have the data as a first name and then a last name in separate columns. Or, it may have a person's location separated by city, state, and zip code. In Excel, you can combine cells with different data into one cell by using the "&" sign in your function.
Our example below uses this formula: =A2&" "&B2
Let's go through the formula together using an example. Pretend we want to combine first names and last names into full names in a single column. To do this, we'd first put our cursor in the blank cell where we want the full name to appear. Next, we'd highlight one cell that contains a first name, type in an "&" sign, and then highlight a cell with the corresponding last name.
But you're not finished. If all you type in is =A2&B2, then there will not be a space between the person's first name and last name. To add that necessary space, use the function =A2&" "&B2. The quotation marks around the space tell Excel to put a space in between the first and last name.
To make this true for multiple rows, simply drag the corner of that first cell downward as shown in the example.
Pivot tables are used to reorganize data in a spreadsheet. They won't change the data that you have, but they can sum up values and compare different information in your spreadsheet depending on what you'd like them to do.
Let's take a look at an example. Let's say I want to take a look at how many people are in each house at Hogwarts. You may be thinking that I don't have too much data, but for longer datasets, this will come in handy.
To create the Pivot Table, go to Data > Pivot Table. Excel will automatically populate your Pivot Table, but you can always change the order of the data. Then, you have four options to choose from.
Since I want to count the number of students in each house, I'll go to the pivot table and drag the House column to both the Row Labels and the Values. This will sum up the number of students associated with each house.
At its most basic level, Excel’s IF function lets you see if a condition you set is true or false for a given value. If the condition is true, you get one result. If the condition is false, you get another result.
Before we dive in, let’s take a look at the this function’s syntax:
=IF(logical_test, value_if_true, [value_if_false])
With values, this could be:
=IF(A2>B2, “Over Budget”, “OK”)
In other words, if your spending (what’s in A2) is greater than your budget (what’s in B2), this IF function will make it easy to see. You can then filter the data and only see the line items where you’re going over budget.
The real power of the IF function, however, comes when you string multiple IF statements
Ranges are one way to segment your data for better analysis. For example, you can categorize data into values that are less than 10, 11 to 50, or 51 to 100.
=IF(B3<11,“10 or less”,IF(B3<51,“11 to 50”,IF(B3<100,“51 to 100”)))
The power of IF functions
For example, let's say I want to count the number of times the word "Gryffindor" appears in my data set.
The formula: =COUNTIF(range, criteria)
The formula with variables from our example below: =COUNTIF(D:D,"Gryffindor")
In this formula, there are several variables:
Simply typing in the COUNTIF formula in any cell and pressing "Enter" will show me how many times the word "Gryffindor" appears in the dataset.
Ready to make IF function a bit more complex? Let’s say you want to analyze the number of leads your blog has generated, but you only want to count leads from blog posts you wrote, not your entire team. With the SUMIFS function, you can add up cells that meet certain criteria.
Here’s your formula:
=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria 2],...)
That’s a lot of criteria. Let’s dive deeper into each part:
As you can see in the formula syntax, you can add as many different criteria to the formula as you like.
The final member of the IF function family are the OR and AND functions. As their name gives away, these functions check multiple arguments and returns either TRUE or FALSE depending on if at least one of the arguments is true (this is the OR function), or if all of them are true (this is the AND function).
Lost in a sea of “and’s” and “or’s”? Don’t check out yet. In practice, OR and AND functions will never be used on their own. They need to be nested inside of another IF function. Recall the syntax of a basic IF function:
=IF(logical_test, value_if_true, [value_if_false])
Now, let’s fit an OR function inside of the logical_test:
=IF(OR(logical1, logical2), value_if_true, [value_if_false])
In plain English, this combined formula allows you to return a value if one of two conditions are true, as opposed to just one. Ultimately with AND/OR functions, your formulas can be as simple or complex as you want them to be, as long as you understand the basics of the IF function.
Have you ever had two sets of data on two different spreadsheets that you want to combine into a single spreadsheet?
For example, you might have a list of people's names next to their email addresses in one spreadsheet, and a list of those same people's email addresses next to their company names in the other — but you want the names, email addresses, and company names of those people to appear in one place.
I have to combine data sets like this a lot, and when I do, the VLOOKUP is my go-to formula. Before you use the formula, though, be absolutely sure that you have at least one column that appears identically in both places. Scour your data sets to make sure the column of data you're using to combine your information is exactly the same, including no extra spaces.
The formula: =VLOOKUP(lookup value, table array, column number, [range lookup])
The formula with variables from our example below: =VLOOKUP(C2
In this formula, there are several variables. The following is true when you want to combine information in Sheet 1 and Sheet 2 onto Sheet 1.
In the example below, Sheet 1 and Sheet 2 contain lists describing different information about the same people, and the common thread between the two is their email addresses. Let's say we want to combine both datasets so that all the house information from Sheet 2 translates over to Sheet 1.
So when we type in the formula =VLOOKUP(C2
Keep in mind that VLOOKUP will only pull back values from the second sheet that are to the right of the column containing your identical data. This can lead to some limitations, which is why some people prefer to use the INDEX and MATCH functions instead.
Like VLOOKUP, the INDEX and MATCH functions pull in data from another dataset into one central location. Here are the main differences:
So if I want to combine information in Sheet 1 and Sheet 2 onto Sheet 1, but the column values in Sheets 1 and 2 aren't the same, then to do a VLOOKUP, I would need to switch around my columns. In this case, I'd choose to do an INDEX MATCH instead.
Let's look at an example. Let's say Sheet 1 contains a list of people's names and their Hogwarts email addresses, and Sheet 2 contains a list of people's email addresses and the Patronus that each student has. (For the non-Harry Potter fans out there, every witch or wizard has an animal guardian called a "Patronus" associated with him or her.) The information that lives in both sheets is the column containing email addresses, but this email address column is in different column numbers on each sheet. I'd use the INDEX MATCH formula instead of VLOOKUP so I wouldn't have to switch any columns around.
So what's the formula, then? The INDEX MATCH formula is actually the MATCH formula nested inside the INDEX formula.
=INDEX(table array, MATCH formula)
=INDEX(table array, MATCH (lookup_value, lookup_array))
The formula with variables from our example below:
Here are the variables:
Once you have your variables straight, type in the INDEX MATCH formula in the top-most cell of the blank Patronus column on Sheet 1, where you want the combined information to live.
Now that you’ve mastered all of those formulas and functions, let’s conclude by making a visual representation of your analysis. With the help of a beautiful graph, your audience (whether it’s a potential customer or your boss) will be able to synthesize and retain the content more effectively. Who knows? You might find just the edge to convince your boss to adopt inbound marketing or give you an extra sliver of
First, you’ll have to decide what type of graph to use. Bar charts and pie charts help you compare categories. Pie charts compare part of a whole and are often best when one of the categories is way larger than the others. Bar charts highlight incremental differences between categories. Finally, line charts are used to display trends over time.
To begin, highlight the data you want to morph into a chart, then choose “Charts” in the top navigation (or Insert > Chart if you have an older version of Excel). Then choose the graph most appropriate for your data.