Excel for Data Analytics - Powerful Cheat Sheet

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

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, …)

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

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

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

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

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

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

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