ms.excel-big dataNo 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. Here is the excel guide and this cheatsheet works well for Excel 2007, Excel 2010 and Excel 2013.

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])

Big Data Analytics with Microsoft Excel

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

Source