_edited_edited.png)
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.