top of page
Writer's pictureNeha Chandekar

Cheat Sheet For EXCEL


Microsoft Excel is a spreadsheet editor that helps to format, organize and analyze data in a spreadsheet. In this blog, I am trying to list a few tricks and hacks for Excel that will help beginners to efficiently analyze data.


Keyboard Shortcuts for excel

--------------------------------------------------------------------------------------------------------------------------------

To open the ‘Open’ dialog box – CTRL + O

Open a new Sheet - CTRL+ N / Shift + F11

Save sheet - Shift + F12

Save as - F12

Open an existing sheet – CTRL + F12

Close Workook – CTRL + W

Find – CTRL + F

Replace – CTRL + R

To move to the next non-empty cell – CTRL + Arrow key

To move to the next non-empty cell

and select it - CTRL+ SHIFT+ ARROW

To select the next cell – SHIFT+ Arrow key

Move to the cell to the right – TAB

Move cell to the left – SHIFT +TAB

Auto sum all Selected cells – ALT =

Selecting all cells at once – Click on the light gray triangle in the upper left corner

Move to the new sheet - CTRL + Pg Dn

Move to Previous sheet – CTRL + Pg Up

Select Entire Row – SHIFT +Spacebar

Select entire Column – CTRL +Spacebar

Beginning of the row for

current active cell – HOME

First Cell in the sheet – CTRL +Home


--------------------------------------------------------------------------------------------------------------------------------

Editing The data in cells




Edit the cell - Double Click on the cell

Edit the formula in the cell – Click on the cell and then click on the formula bar

Undo – CTRL +Z

Redo – CTRL + Y

Replace the content of cell – CTRL +H

Italics font – CTRL +I , CTRL + 3

Bold – CTRL +B , CTRL + 2

Underline – CTRL +U, CTRL + 4

Strikethrough the value in the cell – CTRL + 5

Insert Hyperlink – CTRL +K

Create Table – CTRL +L

Display Formula Ribbon Shortcuts – ALT +M

Display Home Ribbon Shortcuts – ALT +H

Display Insert Ribbon Shortcuts – ALT + N

Page layout Ribbon Strikethrough

the value in the cell – CTRL + 5

Insert Hyperlink – CTRL +K

Create Table – CTRL +L

Display Formula Ribbon Shortcuts – ALT +M

Display Home Ribbon Shortcuts – ALT +H

Display Insert Ribbon Shortcuts – ALT + N

Page layout Ribbon Shortcuts – ALT +P

Hide Columns – CTRL +O

Format cells – CTRL + 1

To check how the cell depends

on other calculations – CTRL +SHIFT + Q

To Check what cells are used

for this calculation – CTRL +Q

To fill Right – CTRL + R

To fill Down – CTRL + D

To delete Row – CTRL + Minus

To Group Rows together – ALT + SHIFT + Right arrow key

To ungroup Rows – ALT + SHIFT + Left arrow Key

Add Outline to cell – CTRL +SHIFT + &

Remove Outline – CTRL + SHIFT + Minus

Copy above cell - CTRL + SHIFT + “

Apply Currency format to cell – CTRL +SHIFT + $

Apply Percentage format to cell – CTRL + SHIFT + %

Apply Time Format - CTRL + SHIFT + @

To enter the current time - CTRL + SHIFT + :

To enter the Current Date – CTRL + ;

Apply Date format to cell – CTRL + SHIFT + #

To select all the adjacent cells - CTRL + SHIFT + *

To insert cells to the right or down, or

to insert an entire row or column – CTRL + SHIFT + Plus

Copy Formula of Above cell – CTRL + ‘

Hide a row – CTRL + 9

Hide a column – Ctrl + 0

Flashfill – CTRL + E

Go to Dialog box – CTRL + G– ALT +P

Hide Columns – CTRL +O

Format cells – CTRL + 1

To check how the cell depends

on other calculations – CTRL +SHIFT + Q

To Check what cells are used

for this calculation – CTRL +Q

To fill Right – CTRL + R

To fill Down – CTRL + D

To delete Row – CTRL + Minus

To Group Rows together – ALT + SHIFT + Right arrow key

To ungroup Rows – ALT + SHIFT + Left arrow Key

Add Outline to cell – CTRL +SHIFT + &

Remove Outline – CTRL + SHIFT + Minus

Copy above cell - CTRL + SHIFT + “

Apply Currency format to cell – CTRL +SHIFT + $

Apply Percentage format to cell – CTRL + SHIFT + %

Apply Time Format - CTRL + SHIFT + @

To enter the current time - CTRL + SHIFT + :

To enter the Current Date – CTRL + ;

Apply Date format to cell – CTRL + SHIFT + #

To select all the adjacent cells - CTRL + SHIFT + *

To insert cells to the right or down, or

to insert an entire row or column – CTRL + SHIFT + Plus

Copy Formula of Above cell – CTRL + ‘

Hide a row – CTRL + 9

Hide a column – Ctrl + 0

Flashfill – CTRL + E

Go to Dialog box – CTRL + G


--------------------------------------------------------------------------------------------------------------------------------

Look up Formulas

Index function – searches horizontally and vertically in a table.

HLOOKUP – This function searches horizontally in a table.

VLOOKUP – this functions searches vertically in a Table.

MATCH – looks for a specific item in a range of cells in a row or a column, and then returns its relative position.

For example : if for range B3:B7,values are 8,4,68,98,67, then for

Match(98, B3:B7,0) formula the value comes to be 4 because for 98 the relative position in the series is 4.

OFFSET – Returns the range of cells, from a particular reference point.

For example, Column B has values 9,8, 7, and 5 from B4:B7

Column C has values 10, 8, 21, and 55 from C4: C7

Then the formula OFFSET(B5,2,1), returns 55. It begins to count from cell number B5, and moves two places down, where it reaches B7. Then it moves 1 cell to the right where it reads C7. Thus it gives 55.

--------------------------------------------------------------------------------------------------------------------------------

Calculations

· Insert Function –

Click on the insert function button under the formula tab to select a function.

-The count () function counts numbers in a range and it ignores any blank or empty cells.

- CountA() function counts a number of characters in a range and ignores any empty or blank cells.

· Auto functions under the auto sum button help to reduce time in calculations.


-Sum Function


Firstly, we click on the auto sum button, then select the range of cells in a column, then is displays the sum formula automatically in the last empty cell in the column. When we click on that cell, it displays the Auto sum result.

Image by Author



-Average function calculates the average of values in the range of cells.

When we select a range of cells in a column and then click on average, it displays the average of the values in the cells.


Image by Author


Image by Author




- Max Function tells the max value in a range of cells in a column.

To find the max value, select the range of cells in a column and then click on the Max function. This displays the max value in the bottom-most empty cell.


- Min Function tells the min value in a range of cells in a column.

To find the min value, select the range of cells in a column and then click on the Min function. This displays the min value in the bottom-most empty cell.


--------------------------------------------------------------------------------------------------------------------------------

Miscellaneous Formulas

· =Trim -Removes extra spaces from the test. It removes leading, trailing blanks.

· =Days – Calculate the number of days between two dates.

· =Networkdays- calculate the number of working days between two dates excluding weekends and any optional holidays.


This concludes my blog on tricks in Excel. Hope you find it useful and helps to simplify Excel for you. While Excel is vast to learn but learning these basics would help you to speed up the work.




179 views
bottom of page