Idea Transcript
How to Use
EXCEL Essential Training for Data-Driven Marketing
................................................................. 1
TABLE OF CONTENTS Introduction . . . . . . . . . . . . . . . . . . 3 Basic Functions . . . . . . . . . . . . . . . 4 VLOOKUP . . . . . . . . . . . . . . . . . . . 10 IF Functions . . . . . . . . . . . . . . . . . . 14 Pivot Tables . . . . . . . . . . . . . . . . . . 18 Data Visualization. . . . . . . . . . . . 20 Final Touches . . . . . . . . . . . . . . . . 23 Additional Resources. . . . . . . . . . 27
................................................................. 2
INTRODUCTION Ever find yourself elbows deep in an Excel worksheet with seemingly no end in sight? You’re manually replicating columns and scribbling down long-form math on a scrap of paper, all while thinking to yourself, “There has to be a better way to do this.” Truth be told, there probably is … you just don’t know it yet. In a world where being proficient in Excel is often regarded as no more impressive than being proficient at breathing, there are still plenty of tips and tricks that remain unknown to the majority of office workers. Mastering Excel specifically for marketing is another beast in its own right. More than likely, you’ve already been tasked with analyzing data from an NPS survey, performing a content topic analysis, or pulling in sales data to calculate return on marketing investment -- all of which require a bit more Excel knowledge than a simple SUM formula. Here’s where this guide comes in. Whether you’d like to speed up your chart formatting, finally understand pivot tables, or complete a VLOOKUP (I promise it’s not as scary as it sounds), we’ll teach you everything you need to know to call yourself a master of Excel -- and truly mean it. Since we all know that reading about Excel may not be the most captivating topic, we’ve tried to cater the training to your unique learning style. At the start of each advanced topic, you’ll find a short video to dip your toe in the water -- a perfect solution for those pressed for time and in search of a quick answer. Next, the deep dive. Read along for a few extra functions and reporting insight. Finally, for those who learn best by doing, we’ve included Test Your Skills questions at the close of each chapter for you to complete with our Excel practice document, included in the zip file download of this offer.
One final note: All of the screenshots included in this guide were taken in Microsoft Excel for Mac 2011. There are many versions of Excel, particularly differing between Macs and PCs. Nevertheless, all of the functions explained can be used with either version.
................................................................. 3
BASIC FUNCTIONS 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 formula that can do it for you. 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 formulas and key commands ingrained in your fingertips, you’ll be ready to tackle each of the advanced Excel lessons head on.
THE ESSENTIAL FUNCTIONS ADDITION
+
SUBTRACTION
-
MULTIPLICATION
*
DIVISION
/
EXPONENTS
^
NOTE: Remember, all formulas in Excel must begin with an equals sign (=). Use parentheses to ensure certain calculations are done first. For example, consider how =10+10*10 is different than =(10+10)*10 AVERAGE
=AVERAGE(cell range)
SUM
=SUM(cell range)
COUNT
=COUNT(cell range)
NOTE: Series of specific cells are separated with a comma (,). Cell ranges are notated with a colon (:). For example, you could have =SUM(4,4) or =SUM(A4,B4) or =SUM(A4:B4).
................................................................. 4
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 incredibly laborious. 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.”
Concatenate The concatenate function, as with most features of Excel, is all about saving time. Use this function to join multiple strings of text into a single cell. The formula looks like this: =CONCATENATE(text1,text2) Check out how we’ve used this function to combine the root domain and subdomain into a single column. You can also add additional text before, after, or between the cells you’re combining. Here we’ve added “http://” into the URLs. For the formula syntax, just remember to add quotations around any additional text.
................................................................. 5
Text to Columns If concatenate combines cells, Text to Columns does the opposite. Let’s look at another URL example. This function makes it incredibly easy to divide subdomains, subdirectories, and UTM parameters. Instead of using a formula, you’ll need to locate the Text to Columns option under the Data menu. Next, a Wizard dialog box will appear. Be sure “delimited” is selected. This tells Excel you want to separate the text into a new column where there is a comma or a tab. Since a forward slash (/) separates the different parts of a domain, you’ll need to add this to the “other” option in Step 2. Lastly, choose the destination of your returned data in Step 3. And you’re done!
Autofill If you have any basic Excel knowledge, it’s likely you already know this quick trick. But just 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.
................................................................. 6
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.
KEYBOARD SHORTCUTS PC
MAC
Ctrl-N
Command-N
SELECT ENTIRE ROW
Shift-Space
Shift-Space
SELECT ENTIRE COLUMN
Ctrl-Space
Control-Space
SELECT REST OF ROW
Ctrl-Shift-Right/Left
Command-Shift-Right/Left
SELECT REST OF COLUMN
Ctrl-Shift-Up/Down
Command-Shift-Up/Down
ADD HYPERLINK
Ctrl-K
Command-K
OPEN FORMAT CELLS
Ctrl-1
Command-1
AUTOSUM SELECTED CELLS
Alt-=
Command-Shift-T
CREATE A NEW WORKBOOK
................................................................. 7
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 on 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.
................................................................. 8
Dollar Signs Have you ever seen a dollar sign in an Excel formula? It isn’t formatting numbers into currency. Instead, it makes sure that the exact column and row are kept consistent even if you copy the same formula in adjacent rows. Excel is smart. When you refer to cell A5 from cell C5, for example, it defaults by notating its relative location. So in this case, you’re actually referring to a cell that’s two columns to the left (C minus A) and in the same row (5). When you copy this relative formula from one cell to another, it’ll adjust the values in the formula based on where it’s moved. But what if you want that reference to stay the same, no matter where you copy the formula? Change the relative formula (=A5+C5) into an absolute formula by preceding the row and column values with dollar signs (=$A$5+$C$5).
TEST YOUR SKILLS: If you haven’t already, download our Excel Practice Document to test your skills of the Basic Functions chapter. Insert a column after “Last Name.” Then combine the “First Name” and “Last Name” column using the concatenate function. Call your new column “Author” and delete “First Name” and “Last Name.”
................................................................. 9
VLOOKUP Have you ever had two sets of data on two different spreadsheets that you want to combine into a single spreadsheet? Yes, you could always open the two Excel documents and copy and paste cell by cell. But what happens when your spreadsheet contains hundreds of rows? VLOOKUP is your answer. In short, this function uses a unique identifier -- like an email address or SKU number -- to match data from two different sources. For example, you might have some data from HubSpot and some from Salesforce that you want to combine together. Excel looks for a unique value in the leftmost column of a spreadsheet and fills a value in the same row from a column you specify in your other spreadsheet. Before you use the formula, copy and paste your data so they are in two different sheets in the same Excel document. We’ll refer to Sheet 1 as the location where you want the final combined data to end up. Sheet 2 is the location of the data you want to transpose into Sheet 1. Then, you’ll have to be absolutely sure that you have at least one column that appears identically in both Sheet 1 and Sheet 2. Sort your data (Data > Sort) in ascending order by this column and scour your data sets for discrepancies and extra spaces. Here’s what the formula looks like: =VLOOKUP(lookup value, table array, column number, [range lookup]) With values included, it becomes something like this: =VLOOKUP(C2,Sheet2!A:B,2,FALSE) If you think that looks like a mix of random numbers and letters, you’re not alone. Let’s break down each component. • Lookup Value: This is the identical value you have in both
spreadsheets. Choose the first value in Sheet 1 that you’re trying to find a match for.
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10
• Table Array: This is the range of columns in Sheet 2 you want Excel
to pull the data from. Be sure you’re highlighting both the column of data identical to your lookup value in Sheet 1 and the data that’s only available in Sheet 2 that you want to transpose. When you highlight this selection, Excel will enter a value like this into the formula: “Sheet2!A:B.” • Column Index Number: This value tells Excel which column in Sheet 2 holds the new data you want to transpose into Sheet 1. Note, this is notated as a number, not by a letter. So the data in Column B would be referred to as “2” for the column number because it’s second from the left. • Range Lookup: Enter FALSE to ensure you pull in only exact value matches. There’s no need to memorize this formula. You have easy access with the Function button to the left of the Formula Bar. Locate VLOOKUP and enter the values into the dialog box. With your formula complete and working properly, use Autofill to copy the VLOOKUP down the rest of the column. Keep in mind, 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. But never fear, the INDEX and MATCH functions in the next section provide a handy workaround.
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11
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 data
sets that would require thousands of lookups, then 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 your lookup column is 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 lead to errors. Let’s take a look at the formula. You might notice the INDEX MATCH formula is actually the MATCH formula nested inside the INDEX formula. =INDEX(table array, MATCH(lookup_value, lookup_array, match_type)) With actual values this becomes: =INDEX(Sheet2!A:A, (MATCH(Sheet1!C:C,Sheet2!C:C,0))) Let’s do another breakdown of these variables. • Table Array: The range of columns on Sheet 2 containing the new
data you want to bring over into Sheet 1. In the example above, this is just column A of Sheet 2. • Lookup Value: This is the column in Sheet 1 that contains the identical values in both spreadsheets that you are trying to match. In the example, this is Column C of Sheet 1. • Lookup Array: This is the column in Sheet 2 that contains identical values in both spreadsheets that Excel is searching. In the example, this is Column C of Sheet 2. • Match Type: This tells Excel whether you want to return an exact match or the nearest match. To avoid unnecessary complexities, just remember to always use “0” here to get exact matches. Enter this formula (or locate the INDEX and MATCH formulas with the Function button) into the first cell of the column where you want the combined information to live in Sheet 1. Autofill down.
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12
TEST YOUR SKILLS: Let’s look back to our Excel Practice Document. In the first exercise, you were introduced to a spreadsheet of blog post data, including views and author. You’ll notice we’ve included a second sheet with lead counts for each blog post. Choose either the VLOOKUP function or INDEX MATCH to add lead numbers to each blog post. Insert this new “Lead” column to the right of “Views” in Sheet 1.
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13
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