Excel for SEO & Analytics – Powerful Cheat Sheet

Learn the most important Excel tricks and tips to effectively manage and speed up your SEO and Analytics campaigns. 

These are the tricks which every internet marketer should know. 

No one has time to spend ages mining for Excel answers from mountains of text to quickly find and solve SEO and Analytics issues. These issues can be something like:

  • Finding and removing duplicate URLs
  • Extracting domain names from the URLs
  • Converting upper case URLs into lower case URLs
  • Scraping on-page elements like title tags, meta description tags, keywords etc.
  • Finding and removing duplicate words for keyword research
  • Creating the right charts for reporting and analysis.

What I am going to present to you is actually a worksheet (we use internally for training purpose and quick reference) in the form of a blog post. This cheatsheet works well for Excel 2007 and Excel 2010.

Menu

  1. Editing in Excel
  2. Text Manipulation in Excel
  3. Data Validation in Excel
  4. Working with Formulas in Excel
  5. Selection in Excel
  6. Navigation in Excel
  7. Charts and Tables in Excel
  8. Macros and VB Editor in Excel
  9. Lookup and Reference in Excel
  10. Excel Plugins for SEO & Analytics

 

Editing in Excel

SNO

Action

Solution

1

Add or edit a cell comment

SHIFT+F2

2

Copy the selected Text/Cell

CTRL+C

3

Cut the selected Text/Cell

CTRL+X

4

Display the Find and Replace dialog box

CTRL+F

5

Display the Insert/Edit Hyperlink dialog box

CTRL+K

6

Displays the Spelling dialog box to check spelling

F7

7

Edit the active cell & move the cursor at the end of the cell

F2

8

Paste the selected Text/Cell

CTRL+V

9

Undo the last action

CTRL+Z

10

Redo the last action

CTRL+Y

11

Display the ‘Save As’ dialog box

F12

12

Separate the contents of one cell into separate columns

‘Text to Columns’ button under the ‘Data’ Tab

13

Remove duplicate rows from a worksheet

‘Remove Duplicates’ button under the ‘Data’ Tab

14

Sort data based on several criteria

‘Sort’ button under the ‘Data’ tab

15

Enable filtering of the selected cells

CTRL+SHIFT+L

16

Group range of cells together so that they can be collapsed or expanded

SHIFT+ALT+RIGHT-ARROW-KEY

17

Ungroup range of cells together so that they can be collapsed or expanded

SHIFT+ALT+LEFT-ARROW-KEY

 

 Text Manipulation in Excel

SNO

Action

Solution

1

Returns the number of characters at which a specific character or text string is first found. Read from left to right while searching. Allow the use of wild cards and do a search which is not case sensitive.

SEARCH(find_text, within_text, start_num)

2

Returns the number of characters at which a specific character or text string is first found. Read from left to right while searching.  Do a search which is case sensitive.

FIND(find_text,within_text,start_num)

3

Check whether two text strings are exactly the same.

EXACT(text1,text2)

4

Remove all extra spaces from a text string

TRIM(text)

5

Return specific number of characters from the start of a text string.

LEFT(text,number-of-characters)

6

Return specific number of characters from the end of a text string.

RIGHT(text,number-of-characters)

7

Return specific number of characters from the middle of a text string.

MID(text,starting-position,number-of-characters)

8

Return number of characters in a text string (including white spaces)

LEN(text)

9

Convert all letters in a text string to lower case

LOWER(text)

10

Convert all letters in a text string to upper case

UPPER(text)

11

Convert all letters in a text string to upper case (i.e. first letter in each word is uppercase and all other letters are lowercase).

PROPER

12

Remove all non-printable characters from text

=CLEAN(text)

13

Concatenate two or more strings

=CONCATENATE(text1, text2, …)

 

 Data Validation in Excel

SNO

Action

Solution

1

Returns TRUE if the value is blank

ISBLANK(value)

2

Returns TRUE if the value is any error value

ISERROR(value)

3

Returns TRUE if the number is even

ISEVEN(value)

4

Returns TRUE if the number is odd

ISODD(value)

5

Returns TRUE if the value is a logical value (True or False)

ISLOGICAL(value)

6

Returns TRUE if the value is not text

ISNONTEXT(value)

7

Returns TRUE if the value is a number

ISNUMBER(value)

8

Returns TRUE if the value is text

ISTEXT(value)

9

Returns a number indicating the data type of a value

TYPE(value)

10

Returns TRUE if the value is a reference

ISREF(value)

11

Check whether all arguments are true and returns true if all arguments are true

AND(logic1,logic2…)

12

Check whether a condition is met and return one value if true and another value if false.

IF(logical-test,value-if-true,value-if-false)

13

Returns the value you specify if a formula evaluates to an error, otherwise returns the result of the formula

IFERROR(value,value-if-error)

14

Change logical value False to True or Vice Versa

NOT(logical)

15

Check whether any of the arguments are true and returns False only if all arguments are False

OR(logic1,logic2,….)

16

Counts the number of cells within a range that meet the given condition

COUNTIF(range,criteria)

17

Prevent invalid data from being entered into a cell

‘Data Validation’ button under the ‘Data’ Tab

 

 Working with Formulas in Excel

SNO

Action

Solution

1

Expand/collapse the formula bar

CTRL+SHIFT+U

2

Move in the cursor in the formula bar to the end of the text

CTRL+END

3

Select all text in the formula bar from the cursor position to the end

CTRL+SHIFT+END

4

Substitute cell references used in a formula with a name

CTRl+F3

5

List of all available formulas in excel

‘Formula’ Tab

 

Selection in Excel

SNO

Action

Solution

1

Select an entire row in a worksheet

SHIFT+SPACEBAR

2

Select an entire column in a worksheet

CTRL+SPACEBAR

3

Select the entire worksheet

CTRL+SHIFT+SPACEBAR

4

Extend the selection of cells to the first cell of the worksheet

CTRL+SHIFT+HOME

5

Turn extended selection mode on/off

 F8

 

Navigation in Excel

SNO

Action

Solution

1

Move to the first cell of a worksheet -

CTRL+HOME

2

Display the Go To dialog box

F5

3

Display Excel Help window

F1

4

Switches between different tabs in a worksheet

F6

5

Move between worksheets

CTRL+PageUp/Page Down

6

Display the print preview window

CTRL+F2

 

Charts and Tables in Excel

SNO

Action

Solution

1

Create a Table

CTRL+L

2

Create a chart of the data in the selected range

F11

3

Extract data stored in a PivotTable

GETPIVOTDATA (data_field,pivot_table,field1,item1,field2,item2,…)

4

Quickly format a range of cells and convert it into a table by selecting a pre defined format.

‘Format as Table’ Button under the ‘Home’ Tab

 

 Macros and VB Editor in Excel

SNO

Action

Solution

1

Display the Macro dialog box to run, edit, create or delete a macro

ALT+F8 or ‘View’ Tab >Macros

2

Create a new Macro Sheet

CTRL+F11

3

Display Visual Basic (VB) Editor

ALT+ F11

4

Switch to VB Editor

ALT+F6

5

Create a module in Excel

Open VB Editor > Insert Menu > Module

6

Close VB Editor and return back to Excel

ALT+Q

7. Extract link from a hypertext or a long list of hypertext

1.Open Visual Basic Editor (ALT + F11)
2.Go to Insert Menu > Module (to add a module)
3. Paste the following code

4. Close the Visual Basic Editor (use ALT + Q)
5. Select the hypetexts and then run the macro ‘ExtractLinks’ (use ALT+F8)

 

8. Make a URL or list of URLs active

1.Open Visual Basic Editor (ALT + F11)
2.Go to Insert Menu -> Module (to add a module)
3. Paste the following code

4. Close the Visual Basic Editor (use ALT + Q)
5. Select the links you want to activate and then run the macro ‘Activatelink’ (use ALT+F8)

 

 Lookup and Reference in Excel

SNO

Action

Solution

1

Search the first column of a range of cells, and then return a value from any cell on the same row of the range.

VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

2

Look up a value horizontally across a row. When the value is found, return a value in another row that corresponds to the column of that value.

HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)

3

Returns a value or reference of the cell at the intersection of a particular row and column, in a given range

INDEX(array,row_num,column_num)

4

Returns the relative position of an item in an array that matches a specificied value in a specified order.

MATCH(lookup_value, lookup_array, [match_type])

 

 Excel Plugins for SEO & Analytics

SNO

Action

Solution

1

Fetch several on-page elements from a URL or list of URLs like: Title tag, Meta description tag, Meta keywords tag, Meta robots tag, H1 tag, H2 tag, HTTP Header, Backlinks, Facebook likes etc.

Niels Bosma’s Excel Plugin for SEO

2

Scrape keywords data (average search volume, local search volume, keyword suggestions) from Google Adwords API

SEO Gadget Google Adwords API Extension for Excel

3

Fetch keywords from the contents of a web page(s)

Excel Macro Spreadsheet from SEO Gadget

Note:you need an Alchemy API key to use this macro

4

Import web analytics data from Google Analytics into a spreadsheet

Excellent Analytics

5

keyword research and optimization tool that operates in Excel

Microsoft Advertising Intelligence

 Other Posts you may find Interesting

 

 

About the Author:

My business thrives on referrals, so I really appreciate recommendations to people who would benefit from my help.Please feel free to endorse/forward my LinkedIn Profile to your clients, colleagues, friends and others you feel would benefit from SEO, PPC or Web Analytics.