Excel for SEO & Analytics – Powerful Cheat Sheet
In this article, I share the cheatsheet on excel for SEO and analytics. We use this cheatsheet internally for training purpose and quick reference. This cheat sheet works well for Excel 2007 and above.
No one has time to spend ages mining for Excel answers from mountains of text to quickly find and fix 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.
- Creating the right charts for reporting and analysis.
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 that 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 that 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 a specific number of characters from the start of a text string. LEFT(text,number-of-characters) 6 Return a specific number of characters from the end of a text string. RIGHT(text,number-of-characters) 7 Return a 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 a 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 return 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
Sub ExtractLinks()
Dim Link As Hyperlink
For Each Link In ActiveSheet.Hyperlinks
Link.Range.Offset(0, 1).Value = Link.Address
Next
End Sub
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
Sub Activatelink()
Dim Link As Range
For Each Link In Selection
Link.Hyperlinks.Add Anchor:=Link, Address:=Link.Text
Next Link
End Sub
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 Lookup 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 specified value in a specified order. MATCH(lookup_value, lookup_array, [match_type])
.
Other Web Analytics Tools
- How to use Keyword Hero to reveal Not Provided keywords in Google Analytics
- Regular Expressions Guide for Google Analytics and Google Tag Manager
- Beginners Guide to Coding for SEO & Web Analytics
- Google Analytics Usage Trends Tool
- Why you may no longer need Google Tag Manager
- Optimize Smart Web Analytics Tool Box
- Best Google Analytics Shortcuts: Tricks, Tools & APIs to save Time
- How to use Google Analytics API without any coding
In this article, I share the cheatsheet on excel for SEO and analytics. We use this cheatsheet internally for training purpose and quick reference. This cheat sheet works well for Excel 2007 and above.
No one has time to spend ages mining for Excel answers from mountains of text to quickly find and fix 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.
- Creating the right charts for reporting and analysis.
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 that 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 that 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 a specific number of characters from the start of a text string. | LEFT(text,number-of-characters) |
| 6 | Return a specific number of characters from the end of a text string. | RIGHT(text,number-of-characters) |
| 7 | Return a 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 a 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 return 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
Sub ExtractLinks() Dim Link As Hyperlink For Each Link In ActiveSheet.Hyperlinks Link.Range.Offset(0, 1).Value = Link.Address Next End Sub
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
Sub Activatelink() Dim Link As Range For Each Link In Selection Link.Hyperlinks.Add Anchor:=Link, Address:=Link.Text Next Link End Sub
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 | Lookup 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 specified value in a specified order. | MATCH(lookup_value, lookup_array, [match_type]) |
.
Other Web Analytics Tools
- How to use Keyword Hero to reveal Not Provided keywords in Google Analytics
- Regular Expressions Guide for Google Analytics and Google Tag Manager
- Beginners Guide to Coding for SEO & Web Analytics
- Google Analytics Usage Trends Tool
- Why you may no longer need Google Tag Manager
- Optimize Smart Web Analytics Tool Box
- Best Google Analytics Shortcuts: Tricks, Tools & APIs to save Time
- How to use Google Analytics API without any coding
My best selling books on Digital Analytics and Conversion Optimization
Maths and Stats for Web Analytics and Conversion Optimization
This expert guide will teach you how to leverage the knowledge of maths and statistics in order to accurately interpret data and take actions, which can quickly improve the bottom-line of your online business.
Master the Essentials of Email Marketing Analytics
This book focuses solely on the ‘analytics’ that power your email marketing optimization program and will help you dramatically reduce your cost per acquisition and increase marketing ROI by tracking the performance of the various KPIs and metrics used for email marketing.
Attribution Modelling in Google Analytics and BeyondSECOND EDITION OUT NOW!
Attribution modelling is the process of determining the most effective marketing channels for investment. This book has been written to help you implement attribution modelling. It will teach you how to leverage the knowledge of attribution modelling in order to allocate marketing budget and understand buying behaviour.
Attribution Modelling in Google Ads and Facebook
This book has been written to help you implement attribution modelling in Google Ads (Google AdWords) and Facebook. It will teach you, how to leverage the knowledge of attribution modelling in order to understand the customer purchasing journey and determine the most effective marketing channels for investment.
About the Author
Himanshu Sharma
