## The Ultimate Guide to Using Microsoft Excel

The Ultimate Guide to Using Microsoft Excel.

All the formulas, functions, shortcuts, and tips you need to master Excel.

Free Download: 9 Excel Marketing Templates.

Don’t let Excel intimidate you.

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.

Microsoft Excel Basics.

Excel Formulas.

Pivot Tables.

IF Functions.

VLOOKUP.

INDEX MATCH.

Microsoft Excel Basics.

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.

Inserting Rows or Columns.

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.

Autofill.

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.

Tired of struggling with spreadsheets? These free Microsoft Excel templates can help.

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.

Filters.

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 into certain criteria. That’s where filters come in.

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 in the spreadsheet when a filter is on to do additional analysis in another spreadsheet.

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.

Remove Duplicates.

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 with Harry Potter’s information and you only need to see one, then you can select the whole dataset and then remove duplicates based on email. Your resulting list will have only unique names without any duplicates.

Paste Special.

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 traps: human error.

Instead, let Excel do the work for you. Go ahead and highlight the column or row you want to transpose. Right click and select “Copy.” Next, select the cells in your spreadsheet where you want your first row or column to begin. Right click on the cell, and then select “Paste Special.” When the module appears, choose the option 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.

Text to Columns.

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 additional information. First, you need to select either «Delimited» or «Fixed Width.»

«Delimited» means you want to break up the column based on characters such as commas, spaces, or tabs. «Fixed Width» means you want to select the exact location in all the columns that you want the split to occur.

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

Format Painter.

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.

Keyboard Shortcuts.

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.

Create a New Workbook.

PC: Ctrl-N | Mac: Command-N.

Select Entire Row.

PC: Shift-Space | Mac: Shift-Space.

Select Entire Column.

PC: Ctrl-Space | Mac: Control-Space.

Select Rest of Column.

PC: Ctrl-Shift-Down/Up | Mac: Command-Shift-Down/Up.

Select Rest of Row.

PC: Ctrl-Shift-Right/Left | Mac: Command-Shift-Right/Left.

Add Hyperlink.

PC: Ctrl-K | Mac: Command-K.

Open Format Cells Window.

PC: Ctrl-1 | Mac: Command-1.

Autosum Selected Cells.

PC: Alt-= | Mac: Command-Shift-T.

Excel Formulas.

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.

To add, use the + sign. To subtract, use the — sign. To multiply, use the * sign. To divide, use the / sign. To use exponents, use the ^ sign.

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:

Average: =AVERAGE(cell range) Sum: =SUM(cell range) Count: =COUNT(cell range)

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.

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.

Dollar Signs.

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

Combine Cells Using “&”

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.

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.

Report Filter: This allows you to only look at certain rows in your dataset. For example, if I wanted to create a filter by house , I could choose to only include students in Gryffindor instead of all students. Column Labels: These could be your headers in the dataset. Row Labels: These could be your rows in the dataset. Both Row and Column labels can contain data from your columns (e.g. First Name can be dragged to either the Row or Column label — it just depends on how you want to see the data.) Value: This section allows you to look at your data differently. Instead of just pulling in any numeric value, you can sum, count, average, max, min, count numbers, or do a few other manipulations with your data. In fact, by default, when you drag a field to Value, it always does a count.

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.

IF Functions.

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 together, or nest them. This allows you to set multiple conditions, get more specific results, and ultimately organize your data into more manageable chunks.

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 expands beyond simple true and false statements. With the COUNTIF function, Excel can count the number of times a word or number appears in any range of cells.

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:

Range: The range that we want the formula to cover. In this case, since we’re only focusing on one column, we use «D:D» to indicate that the first and last column are both D. If I were looking at columns C and D, I would use «C:D.» Criteria: Whatever number or piece of text you want Excel to count. Only use quotation marks if you want the result to be text instead of a number. In our example, the criteria is «Gryffindor.»

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.

SUMIF Function.

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:

Sum_range: This is the range of cells you’re going to add up. Criteria_range1: This is the range that is being searched for your first value, also known as. Criteria1: This is the specific value that determines which cells in Criteria_range1 will be added together. Remember to use quotation marks if you’re searching for text.

As you can see in the formula syntax, you can add as many different criteria to the formula as you like.

If AND/OR.

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.

VLOOKUP.

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 ,Sheet2!A :B ,2 ,FALSE )

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.

Lookup Value: This is the identical value you have in both spreadsheets. Choose the first value in your first spreadsheet. In the example that follows, this means the first email address on the list, or cell 2 (C2). Table Array: The range of columns on Sheet 2 you’re going to pull your data from, including the column of data identical to your lookup value (in our example, email addresses) in Sheet 1 as well as the column of data you’re trying to copy to Sheet 1. In our example, this is «Sheet2!A :B .» «A» means Column A in Sheet 2, which is the column in Sheet 2 where the data identical to our lookup value (email) in Sheet 1 is listed. The «B» means Column B, which contains the information that’s only available in Sheet 2 that you want to translate to Sheet 1. Column Number: If the table array (the range of columns you just indicated) this tells Excel which column the new data you want to copy to Sheet 1 is located in. In our example, this would be the column that «House» is located in. «House» is the second column in our range of columns (table array), so our column number is 2. [Note: Your range can be more than two columns. For example, if there are three columns on Sheet 2 — Email, Age, and House — and you still want to bring House onto Sheet 1, you can still use a VLOOKUP. You just need to change the «2» to a «3» so it pulls back the value in the third column: =VLOOKUP(C2:Sheet2!A:C,3,false).] Range Lookup: Use FALSE to ensure you pull in only exact value matches.

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 ,Sheet2!A :B ,2 ,FALSE ), we bring all the house data into Sheet 1.

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.

INDEX MATCH.

Like VLOOKUP, the INDEX and MATCH functions pull in data from another dataset into one central location. Here are the main differences:

VLOOKUP is a much simpler formula. If you’re working with large datasets that would require thousands of lookups, using the INDEX MATCH function will significantly decrease load time in Excel. INDEX MATCH formulas work right-to-left, whereas VLOOKUP formulas only work as a left-to-right lookup. In other words, if you need to do a lookup that has a lookup column to the right of the results column, then you’d have to rearrange those columns in order to do a VLOOKUP. This can be tedious with large datasets and/or lead to errors.

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:

=INDEX(Sheet2!A:A,(MATCH(Sheet1!C:C,Sheet2!C:C,0)))

Here are the variables:

Table Array: The range of columns on Sheet 2 containing the new data you want to bring over to Sheet 1. In our example, «A» means Column A, which contains the «Patronus» information for each person. Lookup Value: This is the column in Sheet 1 that contains identical values in both spreadsheets. In the example that follows, this means the «email» column on Sheet 1, which is Column C. So: Sheet1!C:C. Lookup Array: This is the column in Sheet 2 that contains identical values in both spreadsheets. In the example that follows, this refers to the «email» column on Sheet 2, which happens to also be Column C. So: Sheet2!C:C.

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.

Data Visualization.

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

Create a Basic Graph.

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.

## Добавить комментарий