Excel for SEO & Analytics – Powerful Cheat Sheet

Last Updated: December 9, 2021

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

SNOActionSolution
1Add or edit a cell commentSHIFT+F2
2Copy the selected Text/CellCTRL+C
3Cut the selected Text/CellCTRL+X
4Display the Find and Replace dialog boxCTRL+F
5Display the Insert/Edit Hyperlink dialog boxCTRL+K
6Displays the Spelling dialog box to check spellingF7
7Edit the active cell & move the cursor at the end of the cellF2
8Paste the selected Text/CellCTRL+V
9Undo the last actionCTRL+Z
10Redo the last actionCTRL+Y
11Display the ‘Save As’ dialog boxF12
12Separate the contents of one cell into separate columns‘Text to Columns’ button under the ‘Data’ Tab
13Remove duplicate rows from a worksheet‘Remove Duplicates’ button under the ‘Data’ Tab
14Sort data based on several criteria‘Sort’ button under the ‘Data’ tab
15Enable filtering of the selected cellsCTRL+SHIFT+L
16Group range of cells together so that they can be collapsed or expandedSHIFT+ALT+RIGHT-ARROW-KEY
17Ungroup range of cells together so that they can be collapsed or expandedSHIFT+ALT+LEFT-ARROW-KEY

Text Manipulation in Excel

SNOActionSolution
1Returns 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)
2Returns 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)
3Check whether two text strings are exactly the same.EXACT(text1,text2)
4Remove all extra spaces from a text stringTRIM(text)
5Return a specific number of characters from the start of a text string.LEFT(text,number-of-characters)
6Return a specific number of characters from the end of a text string.RIGHT(text,number-of-characters)
7Return a specific number of characters from the middle of a text string.MID(text,starting-position,number-of-characters)
8Return number of characters in a text string (including white spaces)LEN(text)
9Convert all letters in a text string to lower caseLOWER(text)
10Convert all letters in a text string to upper caseUPPER(text)
11Convert 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
12Remove all non-printable characters from a text=CLEAN(text)
13Concatenate two or more strings=CONCATENATE(text1, text2, …)

Data Validation in Excel

SNOActionSolution
1Returns TRUE if the value is blankISBLANK(value)
2Returns TRUE if the value is any error valueISERROR(value)
3Returns TRUE if the number is evenISEVEN(value)
4Returns TRUE if the number is oddISODD(value)
5Returns TRUE if the value is a logical value (True or False)ISLOGICAL(value)
6Returns TRUE if the value is not textISNONTEXT(value)
7Returns TRUE if the value is a numberISNUMBER(value)
8Returns TRUE if the value is textISTEXT(value)
9Returns a number indicating the data type of a valueTYPE(value)
10Returns TRUE if the value is a referenceISREF(value)
11Check whether all arguments are true and return true if all arguments are trueAND(logic1,logic2…)
12Check 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)
13Returns the value you specify if a formula evaluates to an error, otherwise returns the result of the formulaIFERROR(value,value-if-error)
14Change logical value False to True or Vice VersaNOT(logical)
15Check whether any of the arguments are true and returns false only if all arguments are FalseOR(logic1,logic2,….)
16Counts the number of cells within a range that meet the given conditionCOUNTIF(range,criteria)
17Prevent invalid data from being entered into a cell‘Data Validation’ button under the ‘Data’ Tab

Working with Formulas in Excel

SNOActionSolution
1Expand/collapse the formula barCTRL+SHIFT+U
2Move in the cursor in the formula bar to the end of the textCTRL+END
3Select all text in the formula bar from the cursor position to the endCTRL+SHIFT+END
4Substitute cell references used in a formula with a nameCTRl+F3
5List of all available formulas in excel‘Formula’ Tab

Selection in Excel

SNOActionSolution
1Select an entire row in a worksheetSHIFT+SPACEBAR
2Select an entire column in a worksheetCTRL+SPACEBAR
3Select the entire worksheetCTRL+SHIFT+SPACEBAR
4Extend the selection of cells to the first cell of the worksheetCTRL+SHIFT+HOME
5Turn extended selection mode on/off F8

Navigation in Excel

SNOActionSolution
1Move to the first cell of a worksheet –CTRL+HOME
2Display the Go To dialog boxF5
3Display Excel Help windowF1
4Switches between different tabs in a worksheetF6
5Move between worksheetsCTRL+PageUp/Page Down
6Display the print preview windowCTRL+F2

Charts and Tables in Excel

SNOActionSolution
1Create a TableCTRL+L
2Create a chart of the data in the selected rangeF11
3Extract data stored in a PivotTableGETPIVOTDATA (data_field,pivot_table,field1,item1,field2,item2,…)
4Quickly 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

SNOActionSolution
1Display the Macro dialog box to run, edit, create or delete a macroALT+F8 or ‘View’ Tab >Macros
2Create a new Macro SheetCTRL+F11
3Display Visual Basic (VB) EditorALT+ F11
4Switch to VB EditorALT+F6
5Create a module in ExcelOpen VB Editor > Insert Menu > Module
6Close VB Editor and return back to ExcelALT+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

SNOActionSolution
1Search 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])
2Lookup 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)
3Returns a value or reference of the cell at the intersection of a particular row and column, in a given rangeINDEX(array,row_num,column_num)
4Returns 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

Register for the FREE TRAINING...

"How to use Digital Analytics to generate floods of new Sales and Customers without spending years figuring everything out on your own."



Here’s what we’re going to cover in this training…

#1 Why digital analytics is the key to online business success.

​#2 The number 1 reason why most marketers are not able to scale their advertising and maximize sales.

#3 Why Google and Facebook ads don’t work for most businesses & how to make them work.

#4 ​Why you won’t get any competitive advantage in the marketplace just by knowing Google Analytics.

#5 The number 1 reason why conversion optimization is not working for your business.

#6 How to advertise on any marketing platform for FREE with an unlimited budget.

​#7 How to learn and master digital analytics and conversion optimization in record time.



   

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

  • Founder, OptimizeSmart.com
  • Over 15 years of experience in digital analytics and marketing
  • Author of four best-selling books on digital analytics and conversion optimization
  • Nominated for Digital Analytics Association Awards for Excellence
  • Runs one of the most popular blogs in the world on digital analytics
  • Consultant to countless small and big businesses over the decade
error: Alert: Content is protected !!