Microsoft Excel for Web Analytics and Big Data

 

No one has time to spend ages mining for Excel answers from mountains of text to quickly find and fix 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.

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, Excel 2010 and Excel 2013.

 

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 which 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 which 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 specific number of characters from the start of a text string.LEFT(text,number-of-characters)
6Return specific number of characters from the end of a text string.RIGHT(text,number-of-characters)
7Return 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 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 returns 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

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

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])
2Look 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)
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 specificied value in a specified order.MATCH(lookup_value, lookup_array, [match_type])

 

Big Data Analytics with Microsoft Excel

The video below shows how to create big data solution by using Microsoft Azure HDInsight and excel 2013:

 

Other articles you will find useful

 


My new Book is now available both in Paperback and kindle format: Maths and Stats for Web Analytics and Conversion Optimization

 

 

Himanshu Sharma

Certified web analyst and founder of OptimizeSmart.com

himanshu sharma optimizesmart

My name is Himanshu Sharma and I help businesses in finding and fixing their Google Analytics and conversion issues.

  • More than ten years' experience in SEO, PPC and web analytics
  • Certified web analyst (master level) from MarketMotive.com
  • Google Analytics certified
  • Google AdWords certified
  • Nominated for Digital Analytics Association Award for Excellence
  • Bachelors degree in Internet Science
  • Founder of OptimizeSmart.com and EventEducation.com

I am also the author of the book Maths and Stats for Web Analytics and Conversion Optimization

If you have any questions or comments please contact me