Our San Francisco digital marketing agency depends on a number of tools to get the job done, but the most versatile of these, by far, is Excel Spreadsheet. Yep, Microsoft’s surprisingly robust spreadsheet program, now in its fourth decade, is our go-to for just about everything from keyword research to traffic flow analysis. For any of our readers who work with data professionally, this surely comes as no surprise. Excel has always been a reliable workhorse, and it’s got a curious gift for always having the feature you need.
It’s so robust, indeed, that most of its deeper functionality isn’t apparent to the casual user. Even if you’ve mastered the basics of working with Excel formulas with so many options, and so many different types of spreadsheet workflows, you might go for years doing something manually that might have been automated, or repeating steps that you might have avoided.
We wanted to share some of our favorite Excel spreadsheet tips and tricks for digital marketing. In a few cases, it took us longer than we’d care to admit to figure out that there was a better way of doing things, but part of being a B Corp-certified digital marketing agency is sharing our hard-won experience to save some of you the same hassles.
And if you do get some mileage out of these tips, why not send one our way? Find us on Facebook or Twitter, and let us know what tips and tricks you’ve come to depend on!
#1. Do Your Color Coding before Pasting from Sheet to Sheet
If you’re anything like us, you find it much easier to parse a huge dataset if it’s color-coded. For instance, when we’re research keywords, we’ll collate data from Google Analytics, Moz, and other sources. Each tool might provide multiple columns, so we’ll highlight all the data from a certain tool a consistent color. The problem arises when we’re pasting from a report.
For instance, maybe we’re adding values for ten new keywords to an existing list. We output those values from [Moz] to a .csv file, then copy and paste them into our spreadsheet. Sounds simple enough, but they’ll paste with their existing formatting. For every new set, we have to highlight the cells all over again.
Simple fix: format the cells appropriately before copying, so they will paste seamlessly into your dataset.
#2. Automate Your Character Counter
This simple little tip is one of the simplest, but often one of the most underutilized. Excel will calculate how many characters are in a given cell, saving you the trial-and-error frustration of getting your title tags and meta descriptions just right.
Observe:
The formula is as follows:
=LEN(cell)
Breaking that down, LEN (Length) outputs the length of the string in the named cell (A1, D20, whatever). Like most excel formulas, it can be expanded instantly, by dragging that little red pull-down in the lower left corner
#3. Color Code with Conditional Formatting
Let’s stick with our title tags, there. Google recommends a title tag be no longer than 55 characters (to ensure that nothing gets cut off in the SERPs). Further, if your title tag is too short, odds are you could be making better use of the space you’re given. So, we’ll add some conditional formatting to give us an easy visual reference.
We’ll highlight the appropriate column (to be sure we don’t mess things up elsewhere in our sheet!), and pull up our “Conditional Formatting” tab. You can do all this with a typed formula, but it’s easier to get the hang of if you let yourself play around with the formatting options manually, first.
We’ll start simple: anything over 55 characters will show up in red:
Next, we’ll give a range for our sweet spot, and lastly we’ll warn ourselves if our title tag seems like it might have room for growth. Our rules now look like this…
…and our title tag list’s functionality has been considerably improved!
#4. Convert Google’s Data to Numerals
Google distinguishes itself in the world of data analysis for its habit of proprietarily exporting values over three digits with a k-suffix (e.g. 1k = 1000). This isn’t necessarily a problem so long as you’re staying inside Google’s digital marketing ecosystem, but as soon as you try to compare figures with, say, Moz, you’re faced with an incompatibility. Similarly, if you want to sort your data, your spreadsheet will treat your figures alphabetically, rather than by numerical value.
As soon as you export data from Google (most likely from Keyword Planner), take a moment and do a full find-and-replace for 1k, 10k, 100k, and 1m respectively, to sub in their proper numerical values.
(Sidebar: this once got me into a bind when my find command caught “401k” and replaced it with “401000”, so don’t just use the replace function unthinkingly!)
#5. Sort Your Keyword Lists Alphabetically when Working between Tools
It’s natural to want to sort your keyword lists by their metrics, usually their difficulty score or their estimated monthly volume. After all, why wouldn’t you want to want to rank your keywords by their utility? But be sure you aren’t doing that too soon in your keyword research workflow.
If you’re going to be bringing in data from more than one source (again, our common examples have been AdWords and Moz, but that’s hardly an exclusive list), those lists may not rank your keywords quite the same way. Data analysis rookies copy the fields for each keyword individually, but there’s a better way.
Sorting your keyword lists alphabetically, at least until you’ve input all the data, to ensure that you can paste in batches.
(Another sidebar: some tools will condense very similar keywords into a single entry, so avoid surprises down the line by double-checking your row counts before you paste. If something’s missing, it will be significantly easier to sort out at the start!)
#6. Never Add Individual Cells
A good spreadsheet will be full of formulas (like the ones we created above), and linked references between cells and sheets. If you add a single cell, you’ll be shifting the adjoining cells, potentially ruining all your careful planning.
By adding a single cell, to A3, I’ve made my whole spreadsheet untrustworthy:
You’ll see that B3’s formula now refers to A4, so the sheet is misleading. A few new cells, and you’ll have to restore a previous version of your sheet just to sort out the knotted mess.
Instead, if you need a new cell, add an entire row or column (as appropriate) to maintain the alignment of the rest of your sheet.
#7. Try the Transpose Feature, before Re-Doing Work
It may not be clear at the start what the perfect layout for your data will be. It’s happened to us more than once, that we’ve needed to switch our rows and columns to allow for a more natural flow. A little-known feature in Excel is the “Transpose” function.
Copy your data, then highlight the uppermost, leftmost cell (top left) for your new set. Rather than pasting, right-click, and under the “Paste Special…” menu, you’ll find a subtle toggle box for “Transpose.”
It transposes rows with columns, but it maintains agreement between formulas.
#8. Reference Cells across Sheets
Excel doesn’t really care where your cells are, when your formulas reference them. It is perfectly willing to reference cells on other sheets, even multiple sheets at once. This is extremely useful for creating a “Summary” or “Totals” tab. You might use it to chart site traffic across multiple months, or to see at a glance how many pages need new title tags.
To reference a different sheet’s cell in your formula, you’ll need to name it directly. For instance, imagine we’re calculating total site traffic, over the first quarter. Your spreadsheet has sheets for each month, and the total site traffic for that month is given in cell A1, on each sheet. Your formula might read:
=Jan!A1+Feb!A1+Mar!A1+Apr!A1
That exclamation point is used to identify the exact name of the sheet to be referenced, before its particular cell.
#9. Keyboard Shortcut Lightning Round!
Alright – it wouldn’t be a complete list of Excel workflow tips if we didn’t make room for some of the more useful Excel keyboard shortcuts we’ve come to depend on over the years.
a) Crtl + Shift + * [Asterisk]
This highlights the whole data set, depending on which cell you have highlighted, but it treats empty cells as borders. It’s much more efficient than using a mouse, highlighting each cell individually, or wasting your time with the much less accurate Ctrl + a.
b) F4
This repeats your previous action, and applies it to whichever cell(s) you have selected. I find myself using it most frequently for adding fill so I don’t have to keep selecting the same color over and over, but it very quickly becomes a habit for all sorts of contexts. (NB. Ctrl+Y does the same thing, if you’ve mapped your F Keys elsewhere.)
c) Ctrl + Shift + ; [Semicolon]
This inserts the current time. (Omit the Shift, and Ctrl + ; will insert the date). It’s great for real-time data tracking. For many digital marketers, the precise timing and precise result of an action (like making a bid adjustment in AdWords) can make or break a campaign.
d) F5
This pulls up the Go To menu. Rather than scrolling endlessly with your trackpad, which is an inexact science at best, this lets you jump, instantly, to any cell.
e) Ctrl + F1
(NB, Macs prefer Cmd+Opt+R for this particular shortcut) This one hides and shows the ribbon, the toolbar across the top of the window. Most of the time, the ribbon will just be taking up valuable screen real estate (upwards of a full four rows, depending), and in Excel, screen space is always at a premium. Reclaim some of that waste, with a simple keystroke? Count me in!
And there you have it – those are our top Excel tips for digital marketing. We hope you’ll put some of these to use in your own workflows! Like we said at the top of this post, if we’ve left out some of your personal favorites, don’t hesitate to drop us a line on Facebook or Twitter!
Colibri Digital Marketing
Ready to work with the first and only full service B Corp-certified digital marketing agency in San Francisco? Drop us a line or schedule a free digital marketing strategy session so we can make beautiful spreadsheets for you! spreadsheet