Excel

Front 1

GO back to A1 cell

Back 1

Ctrl + Home

Front 2

Go to last data cell (Far right,Down)

Back 2

Ctrl + End

Front 3

Select all table cells

Back 3

Shift + Ctrl + End

Front 4

Move to next data sheet

Back 4

Ctrl + PgUp

Front 5

Move to previous data sheet

Back 5

Ctrl + PgDn

Front 6

Move horizontally (Right)

Back 6

Alt + PgDn

Front 7

Move horizontally (Left)

Back 7

Alt + PgUp

Front 8

Move Vertically (Down)

Back 8

PgDn

Front 9

Move Vertically (Up)

Back 9

PgUp

Front 10

Create a new worksheet

Back 10

Shift + F11

Front 11

Enter today's date

Back 11

Ctrl + ;

OR

=today()

Front 12

Function to get date difference by year. (e.g. DOB - Today's date)

Back 12

=datedif(DOB, Today(),"y")

Front 13

How to concatenate several cells into a cell?

What are the two different ways of doing it?

Back 13

=concatenate(A2, " ", C2, " ", D2)

OR

=A2 & " " & C2 & " " & D2

Front 14

Give an example of AND

Back 14

=IF(AND(B2>0.55,C2>0.55,D2>.55),"Pass","Fail")

Front 15

What is the difference between using "IF" only vs "IF(AND"

Back 15

In "IF" we can check for only one condition to be true but in "IF(AND" we can check for multiple conditions to be either True or False

Front 16

Function to check if a cell has a formula

Back 16

=if(isformula(D8),formulatext(D8),"No Formula")

Front 17

How Does "VLOOKUP" work?

Back 17

=VLOOKUP(equals value we look for, table, the number of column starting from 1st column in the table, False --> exact match/True --> similar)

Front 18

Under which tab we can find the "Name Manager"?

Back 18

"FORMULAS"

Front 19

How does VLOOKUP and HLOOKUP "True" work?

Back 19

It looks at a table and sees the cut-off points. for example if our value is 40% and the table is 0%,50%,70%,90%. It will realize that 40% is before 50% so it will be equal to 0%.

Front 20

How to copy a sheet from one workbook to another?

Back 20

click on one one of the cells in (the sheet you want to copy)

Then go to the other workbook and select where you want to paste the data and press "Enter"

Front 21

How to add new data to an already existing name range table?

Back 21

The solution is to include several empty rows and the bottom of the name range we want to set so when we insert new data at the end of the table the range will then extend to include the last empty rows as well.

Front 22

What function do we use to extract ONLY the seconds of a time?

Back 22

=second(A2)

Front 23

What function do we use to extract ONLY the minutes of a time?

Back 23

=minute(A2)

Front 24

What function do we use to extract ONLY the hours of a time?

Back 24

=hour(A2)

Front 25

What function do we use to combine minutes ,seconds and hours into a single date?

Back 25

=time(A2,B2,C2)

Front 26

How do we calculate the time between 11:00 PM and 2:00 AM? (Assuming that 11:00 AM is A2 and 2:00 PM is B2)

Back 26

We add value of 1 to A2. in excel 1 means one day or 24hours. We could solve this with an if statement---> =IF(B2<A2,(B2+1)-A2,B2-A2)

Front 27

What function do we use to extract ONLY the day of a date?

Back 27

=day(A2)

Front 28

What function do we use to extract ONLY the month of a date?

Back 28

=month(A2)

Front 29

What function do we use to extract ONLY the year of a date?

Back 29

=year(A2)

Front 30

How to convert several cells containing day,month and year into a single date?

Back 30

=date(A2,B2,C2)

Front 31

What is the function to round a number by for example 4 decimal places?

Back 31

=round(A2,4)

Front 32

How to round down a number by 3 decimal places?

Back 32

=rounddown(A2,3)

Front 33

How to round up a number by 5 decimal places?

Back 33

=roundup(A2,5)

Front 34

What is the function to get the integer (Without decimal places?

Back 34

=int(A2)

Front 35

How to get only the decimal place of a division?

(A2 is the main number and B2 is the divisor)

Back 35

=mod(A2,B2)

Front 36

How to check if a cell value is even or odd?

Back 36

=if(mod(A2,2)=0, "The number is even","The number is odd")

Front 37

Function to generate a random number?

Back 37

=rand()

Front 38

Function to generate a number withing a specific range?(e.g. 1 and 100)

Back 38

=randbetween(1, 100)

Front 39

The function to calculate loan payment per month

A2= annual rate %

B2= total number of payments

C2= total amount borrowed

Back 39

=PMT(A2/12,B2,C2)

Front 40

The function to calculate saving needs to be made each month to reach a target in a year

A2= Annual Interest Rate

B2= Number of monthly payments

C2= Target sum

Back 40

=PMT(A2/12,B2,0,C2,0)

Front 41

Function to remove spaces

Back 41

=trim(A2)

Front 42

Measure the characters of a cell

Back 42

=len(A2)