Excel and marketing go together like peanut butter and chocolate. There's just one problem -- for many of us, trying to organize and analyze Excel worksheets can feel like walking into a brick wall over and over again. 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.
Excel can be tricky that way. On one hand, it's an exceptionally powerful tool for reporting and analyzing marketing data. On the other, without the proper training, it's easy to feel like it's working against you.
To help you use Excel more effectively (and save a ton of time), we've compiled a list of essential functions, keyboard shortcuts, and other small tricks you should know.
15 Excel Formulas, Keyboard Shortcuts & Tricks That'll Save You Lots of Time
Excel Formulas
1) SUMIF
Let's say you want to determine the profit you generated from a list of leads who are associated with specific area codes, or calculate the sum of certain employees' salaries -- but only if they fall above the a particular amount. Doing that manually sounds a bit time-consuming, to say the least.
With the SUMIF function, it doesn't have to be -- you can easily add up the sum of cells that meet a certain criteria, like in the salary example above.
- The formula: =SUMIF(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
- Sum_range: The range of cells you're going to add up.
- Criteria_range1: The range that is being tested using Criteria1.
- Criteria1: The criteria that determine which cells in Criteria_range1 will be added together.
In the example below, we wanted to calculate the sum of the salaries that were greater than $70,000. The SUMIF function added up the dollar amounts that exceeded that number in the cells C3 through C12, with the formula =SUMIF(C3:C12,">70,000").
2) TRIM
Email and file sharing are wonderful tools in today's workplace. That is, until one of your colleagues sends you a worksheet with some really funky spacing. Not only can those rogue spaces make it difficult to search for data, but they also affect the results when you try to add up columns of numbers.
Rather than painstakingly removing and adding spaces as needed, you can clean up any irregular spacing using the TRIM function, which is used to remove extra spaces from data (except for single spaces between words).
- The formula: =TRIM("Text")
- Text: The text from which you want to remove spaces.
Here's an example of how we used the TRIM function to remove extra spaces after a name on our list. To do so, we entered =TRIM("Steve Peterson") into the Formula Bar.
3) LEFT, MID, and RIGHT
Let's say you have a line of text within a cell that you want to break down into a few different segments. Rather than manually retyping each piece of the code into its respective column, users can leverage a series of string functions to deconstruct the sequence as needed: LEFT, MID, or RIGHT.
LEFT:
- Purpose: Used to extract the first X numbers or characters in a cell.
- The formula: =LEFT(text, number_of_characters)
- Text: The string that you wish to extract from.
- Number_of_characters: The number of characters that you wish to extract starting from the left-most character.
In the example below, we entered =LEFT(A2,4) into cell B2, and copied it into B3:B6. That allowed us to extract the first 4 characters of the code.
MID:
- Purpose: Used to extract characters or numbers in the middle based on position.
- The formula: =MID(text, start_position, number_of_characters)
- Text: The string that you wish to extract from.
- Start_position: The position in the string that you want to begin extracting from. For example, the first position in the string is 1.
- Number_of_characters: The number of characters that you wish to extract.
In this example, we entered =MID(A2,5,2) into cell B2, and copied it into B3:B6. That allowed us to extract the two numbers starting in the fifth position of the code.
RIGHT:
- Purpose: Used to extract the last X numbers or characters in a cell.
- The formula: =RIGHT(text, number_of_characters)
- Text: The string that you wish to extract from.
- Number_of_characters: The number of characters that you want to extract starting from the right-most character.
For the sake of this example, we entered =RIGHT(A2,2) into cell B2, and copied it into B3:B6. That allowed us to extract the last two numbers of the code.
4) VLOOKUP
This one is an oldie, but a goodie -- and it's a bit more in depth than some of the other formulas we've listed here. But it's especially helpful for those times when you have two sets of data on two different spreadsheets, and want to combine them into a single spreadsheet.
My colleague, Rachel Sprung -- whose "How to Use Excel" tutorial is a must-read for anyone who wants to learn -- uses a list of names, email addresses, and companies as an example. If you 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 -- that's where VLOOKUP comes in.
Note: When using this formula, you must be certain that at least one column appears identically in both spreadsheets. 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])
- Lookup Value: The identical value you have in both spreadsheets. Choose the first value in your first spreadsheet. In Sprung's 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: The table array tells Excel where (which column) the new data you want to copy to Sheet 1 is located. In our example, this would be the "House" column, the second one in our table array, making it column number 2.
- Range Lookup: Use FALSE to ensure you pull in only exact value matches.
- The formula with variables from Sprung's example below: =VLOOKUP(C2,Sheet2!A:B,2,FALSE)
In this example, 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. Here's how that would work:
5) IF
There are times when we want to know how many times a value appears in our spreadsheets. But there are also those times when we want to find the cells that contain those values, and input specific data next to it.
We'll go back to Sprung's example for this one. If we want to award 10 points to everyone who belongs in the Gryffindor house, instead of manually typing in 10's next to each Gryffindor student's name, we'll use the IF-THEN formula to say: If the student is in Gryffindor, then he or she should get ten points.
- The formula: IF(logical_test, value_if_true, value of false)
- Logical_Test: The logical test is the "IF" part of the statement. In this case, the logic is D2="Gryffindor." Make sure your Logical_Test value is in quotation marks.
- Value_if_True: If the value is true -- that is, if the student lives in Gryffindor -- this value is the one that we want to be displayed. In this case, we want it to be the number 10, to indicate that the student was awarded the 10 points. Note: Only use quotation marks if you want the result to be text instead of a number.
- Value_if_False: If the value is false -- and the student does not live in Gryffindor -- we want the cell to show "0," for 0 points.
- Formula in below example: =IF(D2="Gryffindor","10","0")
6) RANDOMIZE
There's a great article that likens Excel's RANDOMIZE formula to shuffling a deck of cards. The entire deck is a column, and each card -- 52 in a deck -- is a row. "To shuffle the deck," writes Steve McDonnell, "you can compute a new column of data, populate each cell in the column with a random number, and sort the workbook based on the random number field."
In marketing, you might use this feature when you want to assign a random number to a list of contacts -- like if you wanted to experiment with a new email campaign and had to use blind criteria to select who would receive it. By assigning numbers to said contacts, you could apply the rule, “Any contact with a figure of 6 or above will be added to the new campaign.”
- The formula: RAND()
- Start with a single column of contacts. Then, in the column adjacent to it, type “RAND()” -- without the quotation marks -- starting with the top contact’s row.
- For the example below: RANDBETWEEN(bottom,top)
- RANDBETWEEN allows you to dictate the range of numbers that you want to be assigned. In the case of this example, I wanted to use one through 10.
- bottom: The lowest number in the range.
- top: The highest number in the range,
- Formula in below example: =RANDBETWEEN(1,10)
Excel Keyboard Shortcuts
7) Quickly select rows, columns, or the whole spreadsheet.
Perhaps you're crunched for time. I mean, who isn't? No time, no problem. You can select your entire spreadsheet in just one click. All you have to do is simply click the tab in the top-left corner of your sheet to highlight everything all at once.
Just want to select everything in a particular column of row? That's just as easy with these shortcuts:
Mac
- Select Column = Command + Shift + Down/Up
- Select Row = Command + Shift + Right/Left
PC
- Select Column = Control + Shift + Down/Up
- Select Row = Control + Shift + Right/Left
This shortcut is especially helpful when you're working with larger data sets, but only need to select a specific piece of it.
8) Quickly open, close, or create a workbook.
Need to open, close, or create a workbook on the fly? The following keyboard shortcuts will enable you to complete any of the above actions in less than a minute's time.
For Mac:
- Open = Command + O
- Close = Command + W
- Create New = Command + N
For PC:
- Open = Control + O
- Close = Control + F4
- Create New = Control + N
9) Format numbers into currency.
Have raw data that you want to turn into currency? Whether it be salary figures, marketing budgets, or ticket sales for an event, the solution is simple. Just highlight the cells you wish to reformat, and select Control + Shift + $.
The numbers will automatically translate into dollar amounts -- complete with dollar signs, commas, and decimal points.
Note: This shortcut also works with percentages. If you want to label a column of numerical values as "percent" figures, replace "$" with "%".
10) Insert current date and time into a cell.
Whether you're logging social media posts, or keeping track of tasks you're checking off your to-do list, you might want to add a date and time stamp to your worksheet. Start by selecting the cell to which you want to add this information.
Then, depending on what you want to insert, do one of the following:
- Insert current date = Control + ; (semi-colon)
- Insert current time = Control + Shift + ; (semi-colon)
- Insert current date and time = Control + ; (semi-colon), SPACE, and then Control + Shift + ; (semi-colon).
Other Excel Tricks
11) Automatically fill columns or rows with data.
Tired of manually entering data that follows a pattern across a bunch of cells? Excel's Auto Fill feature is designed to minimize the work required on your end, by making it easy to repeat values you've already input.
To do so, click and hold the lower right corner of a cell, and then drag it down or across into adjacent cells. When you release, Excel will fill in the adjacent cells with the data from the cell you first selected.
12) Customize the color of your tabs.
If you've got a ton of different sheets in one workbook -- which happens to the best of us -- make it easier to identify where you need to go by color-coding the tabs. For example, you might label last month's marketing reports with red, and this month's with orange.
Simply right click a tab and select "Tab Color." A popup will appear that allows you to choose a color from an existing theme, or customize one to meet your needs.
13) Add a comment to a cell.
When you want to make a note or add a comment to a specific cell within a worksheet, simply right-click the cell you want to comment on, then click Insert Comment. Type your comment into the text box, and click outside the comment box to save it.
Cells that contain comments display a small, red triangle in the corner. To view the comment, hover over it.
14) Copy and duplicate formatting.
If you've ever spent some time formatting a sheet to your liking, you probably agree that it's not exactly the most enjoyable activity. In fact, it's pretty tedious.
For that reason, it's likely that you don't want to repeat the process next time -- nor do you have to. Thanks to Excel's Format Painter, you can easily copy the formatting from one area of a worksheet to another.
Select what you'd like to replicate, then select the Format Painter option -- the paintbrush icon -- from the dashboard. The pointer will then display a paintbrush, prompting you to select the cell, text, or entire worksheet to which you want to apply that formatting, as shown below:
15) Identify duplicate values.
In many instances, duplicate values -- like duplicate content when managing SEO -- can be troublesome if gone uncorrected. In some cases, though, you simply need to be aware of it.
Whatever the situation may be, it's easy to surface any existing duplicate values within your worksheet in just a few quick steps. To do so, click into the Conditional Formatting option, and select Highlight Cell Rules > Duplicate Values
Using the popup, create the desired formatting rule to specify which type of duplicate content you wish to bring forward.
In the example above, we were looking to identify any duplicate salaries within the selected range, and formatted the duplicate cells in yellow.
Ready to Get Started?
In marketing, the use of Excel is pretty inevitable -- but with these tricks, it doesn't have to be so daunting. As they say, practice makes perfect. The more you use these formulas, shortcuts, and tricks, the more they'll become second nature.
Want to dig a little deeper? Check out a few of our favorite resources for learning Excel.
What are your favorite Excel tips and tricks for saving time? Let us know in the comments section below.
Editor's Note: This post was originally published in October 2015 and has been updated for accuracy and comprehensiveness.
No comments:
Post a Comment