6 Excel Tips to Make Online Marketing and SEO Easier

Excel is one of those programs you either love or hate. I happen to be a huge fan.

In fact, I have a hard time understanding what’s not to love about Excel. It’s designed to make your life easier, organizing data, facts, and figures any way you like. This is particularly beneficial for online marketing and SEO work, which often involves sorting through lots of data.

But you have to know how to work with Excel. If you’re using some basic functions, like Sums and Averages, you’re on the right path, but most people are barely skimming the surface of the program’s capabilities.

Here are a few Excel tips geared towards helping those in online marketing and SEO make the most of the program.

When sorting through lots of figures, often you’re really looking for outliers. For example, you may want to spot AdWords Campaigns that are performing poorly or keywords that are particularly popular.

You can go through manually and look for the data.You can print it out and highlight the ones you’re looking for. Or you can have Excel do the work for you.

Select the column of data that you’re looking at and select “Conditional Formatting” under the “Home” tab. For here, you can assign rules for Excel to look for, and it will adjust the formatting of that cell. For example, if you ask Excel to look for values higher than “10,000” searches with “Low” competition in a report from Google’s keyword tool to more easily spot prime keywords to target with a campaign.

This Excel tip also helps your reports to look more professional. In a matter of seconds, you can produce a more attractive final product than you could highlighting each piece of data.

Even better, if you go back and edit the data, Excel will change the formatting if need be. This means that if you update a spreadsheet regularly, it will help pinpoint the data you need each time.

Whether you’re writing for Twitter or trying to keep a Title Tag under a certain limit for SEO purposes, counting characters can be a nuisance. Excel allows you to set a limit, so that you simply can’t input data over a certain amount of characters. It’s very convenient for writing many Tweets in advance. Here’s how you do it:

Under the “Data” tab, select “Data Validation”. Under allow, select “Text Length”, and then set the parameters you want. Now if you input text that’s too long, Excel will throw an error and tell you to correct it before it will accept the information.

So someone gives you an email list like this:

bob@gmail.com, jen@hotmail.com, john@outlook.com, kelly@yahoo.com

…but unfortunately, your mass email provider requires that you have it in a column form like this:

bob@gmail.com

jen@hotmail.com

john@outlook.com

kelly@yahoo.com

Not a big deal for a handful of emails, but a real headache if you’re dealing with dozens or hundreds.

Often, we aren’t thinking about how we’re ultimately going to organize data when we receive it, so it can become messy when we’re trying to input it into a pretty Excel document. Luckily, Microsoft is aware of this problem. With just two quick steps, you can transform the email list above into that column format.

1.) Copy/paste the comma separated list into a cell in your document. Then under the “Data” tab, find the “Text to Columns” button. This will put each email address in a separate cell using the specifications you provide. In this case, you want to select “Delimited” by a “Comma” and “Space”. When you’re done, they’ll be laid out in separate cells horizontally.

2.) To switch it from a horizontal list into a vertical one, copy all the cells. Then select “Past Special” under the “Paste” menu on the “Home” tab. Check the box for “Transpose”. Now your data is organized how you want it!

When creating a report or presentation, there’s often a lot of “messy” calculations and data that you needed to help put it all together, but gets in the way of the data you’re trying to highlight. Many people simply delete this “mess,” but this can cause problems if you need that data later to back-up claims or if you end up wanting to refer to it during your presentation.

Instead of creating a new file, you can simply use Excel’s “hide” feature, which allows you to hide rows, columns, or even tabs. Just right click on the row, column, or tab and select “Hide.” Then you can print it out or use it for your presentation. Need it again? Just right click again and select “Unhide.”

Be aware, though, that if you send this file to someone, they can also unhide data. So if it’s important that the information remain private, then be sure to actually delete it before emailing the file over.

If you don’t already know it, this is one of those Excel tips that can save you a lot of hassles. One of the most frustrating things is when you delete some figures or copy/paste data from one place to another, only to find a bunch of broken formulas. This is another job for Excel’s Paste Special command.

Select “Values” and now Excel will grab the final numbers – not the formulas it used to calculate that number – and paste it wherever you like!

A fancy feature if you’re asking someone to input data in a sheet, but it also keeps things standardized if you (or someone else) is inputting data that needs to be sorted later. And of course, it can be a timesaver.

1.) Create the list of items that you want to appear in the drop-down. Each item should be in a separate cell.

2.) Then select the cell (or cells) that you want to contain the drop-down list. Under  the “Data” tab, select “Validation”. In the Allow field, select “List” and then specify the range with the drop-down items.

• To insert today’s date, hold down Ctrl and press the semicolon ( 😉 key.

• To insert the current time (without a date), hold down Ctrl, Shift, and Semicolon.

• If you select a range of data, Excel automatically calculates the average, count, and sum in the lower right corner of the program.

• To insert the sum of a range of data directly beneath it, select it and then press “Alt” and “=”.

• To add multiple lines in one cell, press “Alt” and “Enter”.

• Select all the data in a spreadsheet by clicking on the grey box above the “1” and to the left of the “A”.

• Click the “Filter” button under the “Data” tab to automatically add drop-down arrows that allow you to easily filter out data you need.