Shortcuts

ctrl + A=select all

ctrl+9 = hide a row

ctrl + shift + 9 = unhide a row

ctrl + o = hide a column

Editing & Customizing data

f2 = editing any cell

Freezing

view --> window --> Freeze Panes

Splitting

view --> window --> new window

Copy & Paste

Clipboard - select whatever part you want to copy and paste whichever MS tools you want.

Paste Special

ctrl+alt+v

copy any cell then then select another sell and ctrl+alt+v then a dialog box will open and you can do whatever you want from the dialog box.

New Comment

Worksheets

Delete a worksheet - right click and delete

Move and copy

select (ctrl + click)
right click and select move and copy
select file name or new book
select where you want to paste
click ok

Color of the sheet

right click and tab color

Hide & Unhide

Hide - right click and choose hide

Unhide - click any sheet and right click then unhide

Protect sheet

right click, choose password and ok

Basic Formulas & Functions

Formulas

Functions - predefined formulas

Functions

count - numbers only
counta - cell which contains anything
countblank - cell which are blank

Find, Select, Replace, Go to

Find and Select

select cells and go to Find & Select (Home->Find & Select)

Conditional Formatting

Constants

Data validation

Go To (ctrl + 9), Go To Special (f5)

Precedents
Descendants

Find (ctrl + f) and Replace (ctrl + h)

Filling (Home -> Fill)

Fill down - ctrl + d

Fill right - ctrl + r

Flash Fill - automatically detects a pattern - ctrl + e

Copying Formula

copy formula from formula bar -> same answer will come from where it was copied

Cell referencing - copy result and paste it where you want to use the formula only ->direct copy the result and paste

Cell Referencing

Relative CR - results get updated

Absolute CR

Constant

Add $ sign either before the column or row no to make column and row constant

$ (f4) - shortcut

Absolute Reference - $E$13
Mixed Reference - $E13, E$13

Subtotal

Total - complete sum of some numbers

Subtotal - total category-wise

For subtotal, data of at least one column should be in ascending order -> choose filter to choose category
select any one cell of a column you have to use
Data -> Outline -> Subtotal
\=SUBTOTAL(Any_Operation, range)

Text Functions (Formulas -> Text)

Upper
Lower
Proper/Title
Left
Right
Mid
Trim (remove unwanted spaces)
Length
Find
- it is case sensitive.
- \=FIND("find_text", text, start_num)
Search - it isn't case sensitive.
Substitute
- it is case sensitive.
Replace

Round Functions

ROUND - =round(number, decimal_place)
ROUNDUP - =roundup(number, decimal_place)
ROUNDDOWN - - =rounddown(number, decimal_place)
MROUND - =mround(number, decimal_place)
- M means multiple

Logical or Comparative Operators

\>, <, =, >=, <=, <>

Text Operations

Text To Column (alt + v)

Data -> Text to Columns
Date Format
- Data -> Text to Columns
- select the data
- Next
- choose date pattern and destination
- Finish

Textsplit

Column-wise

For Single delimiter
- \=TEXTSPLIT(B3,";", ,TRUE)
For Multiple-delimiter
- \=TEXTSPLIT(B3,{";,*"}, ,TRUE)

Row-wise

For Multiple delimiter

\= TEXTSPLIT(D4, "," , ";" , "-" ,TRUE)

\= TEXTSPLIT(F7,{"," , ";" , "-"},",",TRUE)

NOTE - {} is for column delimiter, outside the {} row delimiter.

Textbefore

Choose delimiter which is present just after the text if you want to show the text only.

\=TEXTBEFORE(J9,"=")
\=TEXTBEFORE(J9,"=",2) (Instance Number - if there is two equal to then you can select which one)

Textafter

\=TEXTAFTER(J9,"=")

Logical Functions

OR, AND, NOT, XOR

\=or(G9<68)
\=if(OR(C6>52,F7<96),"Pass", "Fail")

AND

\=and(E7>40)

NOT

\=not(FALSE)--->TRUE
\=not(TRUE)--->FALSE
if(C5=true,"Pass","Fail")

XOR

\=xor(C6>52,F7<96)

XOR result - TRUE when the number of valid/correct inputs is ODD

XOR result - FALSE when the number of valid/correct inputs is EVEN

Show Formulas

ctrl + ~(tilde) --> show formulas, for undo again ctrl + ~

Speak cells

File->More->Options->Quick Access Toolbar->Popular commands->All commands->Speak cells->Add

It will show on the top of the screen as icon.

Basic unconditional formatting in Excel

Date Format

Home->Number section -> Date -> custom

DD - 08

MM - 02

YY - 24

DDD - Wed

MMM - Feb

DDDD - Wednesday

MMMM - February

YYYY - 2020
dd/mm/yy - 06/01/02

mm/dd/yy - 01/17/02

dddd/mm/yy - Thursday/01/02

dddd/mmmm/yyyy - Saturday/January/2002

dd-mm-yyyy-dddd - 06-01-2002,Sunday

Number Format

Home->Number section -> Number -> custom

It will give result in decimal format.

Format Painter

Single click for one use - copy the format painter and select the place where you want to use and leave the cursor.

Double click for multiple use - copy the format painter and select the place wherever you want to use and leave the cursor.

Protection in Excel

cell -> right click -> Format cells -> Protection -> tick locked -> ok

For undo - cell -> right click -> Protect sheet -> password -> ok

2nd way - Review -> Protect sheet
3rd way - File -> Info -> Protect Workbook drop down -> Protect current sheet

Protecting Files and Workbooks

Locking the workbook

File -> Info -> Protect Workbook drop down -> Encrypt your password -> write password

Unlock the workbook

File -> Info -> Protect Workbook drop down -> Encrypt your password -> Erase the password -> ok

Formula Auditing

Formulas -> Formula Auditing section -> Evaluate Formula

Trace Precedents
Trace Dependents
Evaluate Formula
Show Formulas - to show all the formulas and clicking again will undone.

How to hide the formulas?

\=formulatext(select_any_cell_which_has_formula)
Now, it will be uneditable.

2nd way to hide and unhide the formula is ctrl + ~

3rd way to click on Show Formula in Formula Auditing section.

Error Checking
- Error checking
- Trace Error - Before Trace Error do Error checking. Then, click the error and then click on Trace Error.
Watch Window
- Click Watch Window to see the changes you are going to make in the specific Dependent cell due to changing the Preceding cell.
- Next, Click on Add watch to add the Dependent cell which has formula and which will change a/c to preceding cell.

Microsoft Excel Basics

Table of contents