How to Make Your SEO & PPC Spreadsheets Rock
As a marketing professional, you no doubt utilize Microsoft Excel in your regular daily work, especially for tracking analytics and campaign activities. Depending upon how long you’ve been using Excel or who taught you how to do things, you may or may not be making the most out of this powerful tool. Whether you are frustrated because certain tasks are too cumbersome or time consuming or you don’t even think about it because you just do what you’ve always done, I’ll bet you can be more efficient.
So, if you’re interested in doing all of the things you’re doing in Excel today but in less time and with less opportunity for manual error, let these 10 tips be your guide. Note that in each example, cell values such as A1 are used as samples only. You should enter the cell identifiers specific to your situation as appropriate.
- Find/Replace
The problem? While the basic Microsoft Office Find and Replace features work, they take more time than you would like.
The solution? The SUBSTITUTE formula. By inserting this easy formula, you can speed up the time it takes you (or Excel) to find and replace select values. As an added bonus, you can do this for multiple values at the same time and even designate the order they should be done in. This formula is:
SUBSTITUTE(A1,”originalvalue”,”replacementvalue”)
- Capitalization
The problem? When keywords are imported from tools or titles are created via concatenation, every word can end up in all lowercase letters but you know that you need title case.
The solution? The PROPER formula. With just a few keystrokes, you can have all of your data display in title case. This formula is:
=PROPER(A1)
- Extra Spaces
The problem? Extra spaces can be added to keywords or other data when imported into your spreadsheet.
The solution? The TRIM formula. This function lets you identify which cells for Excel to scan and eliminate all unnecessary spaces, before or after phrases or in between words. This formula is:
=TRIM(A1)
- Combining Values into One Cell
The problem? Keyword research often finds you wanting to combine values from multiple cells into one cell and you don’t want to have to do that manually.
The solution? The CONCATENATE formula—short version. You may have seen the standard formula that lets you concatenate (which simply means to combine) information from two cells into one but there’s a shorter, more concise version that makes this function even better. This formula is:
=A1&”text”&B1
- Match Type
The problem? Being able to manipulate your match type value can be time consuming.
The solution? Multiple match type formulas. Excel gives you the power to search by exact match, phrase match or broad match modifier. Each of these functions leverages the power of multiple formulas together, including TRIM, SUBSTITUTE and CONCATENATE. These formulas are:
For exact match: =”[“&TRIM(A1)&”]”
For phrase match: =””””&TRIM(A1)&””””
For broad match modifier: =”+”&SUBSTITUTE(TRIM(A1),””,”+)
- Sums
The problem? Excel’s SUM function is great but you find it tedious to manually enter =SUM(A1:A9).
The solution? The Quick SUM formula. With only two key clicks, you can create the sum formula seen above that would take you 11 key clicks. This lets you quickly add up all values in a column in the cells above the one in which you enter the function. This function is accessed by:
ALT =
Step 1: place the cursor below the column of numbers you want to sum (or to the left of the row of numbers you want to sum).
Step 2: hold down the Alt key and then press the equals = sign while still holding Alt.
Step 3: press Enter.
- Scrolling Up and Down
The problem? Your spreadsheets are likely thousands of rows long. If you are on row 2034 and want to view something on row 8, you need an easy way to get there.
The solution? The Quick Scroll keyboard function. This is not so much of a formula as it is a handy function in Excel. If you want to select cells from your starting and ending place, you can use the same keystrokes but also add SHIFT. These shortcuts are:
To go up: CTRL + ↑
To go down: CTRL + ↓
- Designating Percentages
The problem? Entering numbers or importing them is enough, you don’t want to go through multiple steps and dialog boxes just to add a % sign, too.
The solution? The Number Formatting shortcut. When you consider that the % sign is found on the 5 key, this makes a lot of sense. This shortcut is:
CTRL SHFT 5
- Designating Currency
The problem? Entering numbers or importing them is enough, you don’t want to go through multiple steps and dialog boxes just to add a $ sign, too.
The solution? The Number Formatting shortcut. When you consider that the $ sign is found on the 4 key, this makes a lot of sense. This shortcut is:
CTRL SHFT 4
- Applying a Formula to Multiple Cells
The problem? In a great many cases, the formula you assign to a particular cell is the one you will want assigned to all cells in that column—and you don’t want to do this manually.
The solution? The Repeat Formula shortcut. You only need to enter your formula in the first cell of a column and then you can copy it out from there. To do this:
Hover cursor in lower right of first cell, wait for + sign to appear
Double click to copy
These are by no means all of the functions you can take advantage of in Excel to make your day easier but they will no doubt get you off to a great start. Your time is valuable and the need for your data to be accurate is great. Take advantage of all the help that your tools offer to simplify, save time and improve the quality of your work.
