DATA ANALYSIS AND

VISULIZATION
DAY 1
DATA TYPE

1 NUMBER
2 TEXT

HOW TO KNOW ANY NO IS NO OR TEXT(3 TYPES)

1 ALLIGMENT = TEXT ALWAYS LEFT

NUMBER ALWAYS RIGHT

2 GREEN TRIANGLE = IF NOT APPEARING GO TO FILE -GO TO

OPTION – GO TO FORMULA AND LAST 2 IS NOT TICKED THEN TIKED
THE LAST 2 BLOCK

3 FORMAT CELL = FORMAT CELL ALWAYS WORK ON NUMBER

NEVER ON TEXT

PRESENT THE DATA = 18 TYPES TO PRESENT DATA

IF 1234 = NUMBER

ABCD = TEXT

ABCD1234 = TEXT

!@#$%^ = TEXT

WE HAVE A NO SUPPOSE 12-11-2003

I WANT TO EDIT IT

I WILL GO TO FORMAT CELL AND THERE WAS A LOT OPTIONS BUT

IF YOU WANT TO CUSTOMISE -GO TO COUSTMISE

HERE IS THE POINT WHERE I WANT TELL SOMETHING

IF WE WANT M= MONTH NO MM=MONTH NO MMM=MONTH SHORT
NAME MMMM=MONTH FULL NAME

IF WE WANT D=DAY NO DD=DAY NO DDD=DAY SHORT NAME

DDDD=DAY FULL NAME

FORMULA INPUT TYPE

1 NO = 1234

2 TEXT = ABCD

3 CELL REFFRENCE = A1,B2,C3,D4

Day 2

Formulas

1 concatenate = 2 or value in 1 cell

=concantenate(text1,text2,----)

If we need space in text we use “space”

2 upper = make every text in capital

=upper(text,cell)

3 lower = make every text in lower

=lower(text)

4 proper = make proper first text incap other lower

=proper(cell)

5 substitute = use for replace any word

=subsisitute(cell,”what to change”,”change word”)

6 len = count total number

=len(cell)

7 repeat = repeat the text

=rept(cell)

8left = left use to out character from left, but here we not able to
extract same from every row, so we need to use find method left
use for particular no character, if we want to out from every
row ,but if we have diff no of character to out we need to use find
formula

Jay Is a good boy Jay

Harpreet Is a good boy Harpreet
Gaurav
Gaurav Is a good boy
Jayprakashanan is a good boy Jayprakashanan

When we have that type of condition we use left+find

8 find = use when we want to do more than 1 task in a part

= left(cell,find(“ “ cell)-1or+1 about all

9 mid = text out from middle one

=mid(cell,find(“ “,cell)+1,no of word want to out)

10 replace = replace anything

=replace(cell)

DAY 2

LOGICAL FUNCTION

1 IF CONDITION = MADE CONDITIONS LIKE WE WANT 1 PERSON

GET PASS OR FAIL WE USE IF CONDITON TO WORK ON IT

EG = IF (LOGICAL TEXT (,VALUE WE

WANT,VALUE FALL)

Mark
Name s STATUS
AA 55 pass
BB 63 pass
CC 24 fail
DD 81 pass
EE 34 fail

IN THIS DATA WE HAVE TO USE IF FORMULA FOR SURE

NOW WE WANT TO WORK ONA DATA OR MORE THAN 1 CELL OR A

PROPER TABLE AT THAT TIME WE HAVE TO WORK ON THE IF WITH
A ADDITIONAL FORMULA OF AND

THE ADDITIONAL FORMULA IS AND WITH IF

AA 55 52 52
BB 60 70 89
CC 55 42 49
DD 48 99 51
EE 55 89 50
LIKE WE HAVE DATA AS YOU CAN SEE

IN THIS DATA WE WILL DO IF+AND COMBINATION TO WORK ON M

ORE THAN 1 DATA S WELL AS FOR THE MORE ANSWER

=IF(AND(ALL MARKS FROM WHERE WE WANT TO CHOSE ,MORE

OR LESS,PASS OR FAIL )

=IF(AND(L1,L2,L3)=50),”PASS”,”FAIL”)

THERE IS 2 OPTION

1 AND = WHICH SATISFY ALL

2 OR = WHICH SATISY 1 FROM THEM

AGGERAGRATE

=IF(AND(CELL>=50,CELL>=50,CELL>=50,SUM(CELL-
CELL)>=170),”PASS”,”FAIL)

GRADES
WE DO SAME IF TO DO GRADES IN IT

LIKE WE HAVE

Mark
Name s GRADE
AA 51
BB 63
CC 96
DD 81
EE 34

WE SHOULD START GRADING IT

THIS IS >=

=IF(CELL>=90,”A”,IF(CELL>=70,”B”,IF(CELL>=60,”C”,IF(CELL>=
50,”D”,”FAIL))))

WE WILL USE ) UNTILL IT NOT COME TO BLACK

NOW GRADE IN <=

=IF(CELL<=50,”FAIL”.IF(CELL<=60,”C”,IF(CELL<=70,”B”,IF(CELL<
=90,”A”))))

Worst 10
Bad 8
Poor 6
Average 4
Good 2
Excellent 0

NOW WE HAVE THAT DATA VERTICAL WE HAVE TO ANSWER

>=

=IF(CELL>=10,”WORST”,IF(CELL>=8,”BAD”,IF(CELL>=6,”POOR”,I
F(CELL>=4,”AVERAGE”,IF(CELL>=2,”GOOD”,”EXCELLENT”))))
 DAY 3

MATH &TRIGNOMATRY AND STATICAL FUNCTIONS

1 ABS = NEGATIVE TO POSITIVE

=ABC(CELL)

2POWER = MULTIPLE IN POWERS

=POWER(10,3) =1000

3 PRODUCT = MULTIPLE THE DIGITS

(10,3) = 30

4 COUNT = COUNT ONLY NO

=COUNT(CELL)

5 COUNTA = COUNT NUMBER + TEXT

=COUNTA(CELL)

6 COUNT BLANK = COUNT THE BLANK CELL

+COUNT(CELL-CELL)

7 COUNT TEXT =COUNT TEXT

=COUNTEXT(CELL-CELL)

8 MAX = MAXIMUM VALUE

=MAX(CELL-CELL)

9 MIN = MINIMUM VALUE

=MIN(CELL-CELL)

10 LAVY =COUSTMISE THE LARGEST

=LAVY(CELL-CELL,3)

11 SMALL = CUSTOMISE THE SMALLEST

= SMALL(CELL-CELL,3) (3) WHICH SMALLEST YOU

WANT

12 SUM IF = USE TO SUM OF CELLS

=SUMIF(RANGE,CRYTERIA,SUM RANGE)
NOW IN THIS ONE WE HAVE TO ADD ONE MORE POINT THAT IS
FREEZING BECAUSE ALWYS WHEN FOMULA WE USE EXCEL ALSO
MOVE IN SAME DIRECTION SO FOR THAT WE USE FREZZEING IN IT

FREEZING FORMULA = FN+F4

13 COUNT IF = COUNT THE PRODUCT IN NUMBER

=COUNTIF(RANGE,CRYTARIA)

NO NEED OF SALE OR AMOUNT BECAUSE IT JUST GOING TO COUNT

THE NUMBERS ONLY SO WE JUST PUTT ALL AND COUNT WHICH
THNG THAT’S IT

14 AVERAGE IF = USE TO CALCULATE THE AVERAGE OF A

PARTICUAL GIVEN CONDITION

-AVERAGEIF(RANGE,CRITIRIA,AVERAGE
RANGE)

15 SUM IFS = SAME AS SUMIF BUT INCLUDE MORE THAN 1

CRITIREA OR CONDITION

SUMIF(SUMRANGE,CRITIREA RANGE 1, CRITIRIEA

1 ,CRITIREA RANGE 2 ,CRITIREA 2 ,CRITIREA RANGE 3 , CRITIREA
3)

16 COUNTIFS = SAME AS COUNT IF BUT USE WHEN MORE THAN 1

CONDITION OR CRITERIA

=COUNTIF(CRITIREA RANGE 1,CRITIREA

1 ,CRITIREARANGE 2 ,CRITIREA 2 ,CRITIREA RANGE 3 ,CRITIREA 3 )

17 AVERAGE IFS = SAME AS AVERAGE IF BUT USE WHEN MORE

THAN 1 CONDITION AND MORE THAN 1 CRITIREA
 =AVERAGEIFS(AVERAGE RANGE, CRITIREA
RANGE 1,CRITIREA 1 ,CRITIREARANGE 2 ,CRITIREA 2 ,CRITIREA
RANGE 3 ,CRITIREA 3)

18 NAME MANAGER = NAME COLUM (EDIT NAME )

WHEN WE HAVE DATA LIKE FROM CELL A1 TO A 100 WE SHOUD GO

TO NAME MANAGER AND EDIT THE NAME WRITTEN A1;A100 TO
ANY NAME THAT WILL HELP US TO SELECT DATA WITH THE ROW
NAME

19 ASTRISK (*) = THIS SINE USE WHEN WE WANT TO REPLACE N

NUMBER OF CHARACTER THIS SINGLE SYMBOL CAN HAVE A
PLACE OF A PROPER WORD PLACE JUST WITH STARTING OR
ENDING WORD

20 QUESTION MARK {?} = THIS SINGLE SYMBOL REPLACE A

SINGLE WORD AT ONE TIME

DATE FORMULAS

1 DATE = THIS FORMULA COMBINE THE DATE IN ONE CELL USE OF

INFO . =DATE
(YEAR,MONTH,DATE)

2 DAY = EXTRACT DATE FROM FULL DATE

=DAY(DATE CELL)

3 MONTH = EXTRACT MONTH FROM DATE

=MONTH(YEAR CELL)

4 YEAR = EXTRACT YEAR FROM DATE

=YEAR( YEAR CELL)

NOW HERE ALSO A THING WHEN

WE TYPE DD ITS DATE IN 1234

WE TYPE DDDD ITS MONDAY,TUSEDAY

5 DATED IF = WHEN WE HAVE TO KNOW DIFFRENCE IN 2 DATES

=DATEDIF(STARTING DATE,ENDINGDATE)

NOW IF WE NEED TO USE YEAR FORMULA WE USE Y ,

USE MONTH FORMULA WE USE YM

USE DAY FORMULA WE USE MD

IF WE NEED EXACT HOW MANY DAYS , HOW MANY MONTH AND

HOW MANY YEARS

WE USE CONCEDINATE

=CONCETENATE(DATEDIF(SD,ED,”Y”).”YEAR”,DATEDIF(SD,ED,”VM
”),”MONTH” ,DATEDIF(SD,ED,”MD”),”DAY”

6 NET WORKING DAYS = CALCULATE NET WORKING DAY

=NETWORKING(SD,ED,LIST OF
HOLIDAYS)

IN THIS WEEKEND IS CALCULATED SATURDAY,SUNDAY

IF WE WANT TO CUSTOMISE WEKEND WE WILL GO AND USE

7 NET WORKING INT = CALCULATE NET WORKING WITH OWN

CUSTOMIZATION OF WEEKEND

=
NETWORKINGINT(SD,ED,WEEKEND,LIST OF HOLIDAYS)

8 WORK DAY = DATE AFTHER WORK DONE

LET US SUPPOSE 100 WORKING DAYS

=WORKDAYS(SD,DAY,HOLIDAYS)

NOW IN THIS SAME SITUATION SUNDAY AND SATURDAY NOW

AGAIN WE USE

9 WORK DAY INT = CUSTOMISE WEEKEND DAYS

 =WORKDAYINT(SD,WORKING
DAYS,WEKEND ,LIST OF HOLIDAY)

10 NOW = CUREENT TIME DATE

=NOW()

11 HOUR = HOUR(CELL)

12 MINUTES = (MINUTES)

13 SECOND =SECOND(CELL)

14 TIME = TIME(HOUR,MINUTES,SECONDS)

15 TODAY = TODAY ()

NEXT TOPIC IS PIVOT TABLE IN PIVOT TABLE WE CAN SAY WE CAN

SUMMARIES DATA IN JUST SECONDS AS PER OUR REQUIREMENT
PIVERT IS ONE OF THE BEST TOLL

JUST SELECT ALL DATA GO INSRECT AND CLICK PIVERT

2 COLUM

3 ROW

4 VALUE

TABLE

IN PIVERT TABLE WE HAVE 4 THINGS

1 FILTER

JUST DRAG DATA FROM LIST WHERE WE WANT TO EDIT OR MAKE

IT

AFTHER THAT IF WE ADD OR CHANGE DATA

THERE 2 OPTION

1 REFRESH = IT REFRESH THE DATA FROM OLD TO NEW

2 DATA ADD OR INSERT = IT ADD DATA FROM THE SHEET WHAT

YOU NEW DONE IN SHEET

AFTER THAT WE HAVE CONDITION FORMATION WHERE WE CAN

CHANGE COLOURS OR MANY MORE

MOST IMPORTANT TOOLS

1 VLOOKUP = IT USE TO FIND CORRESPONDING VALUES FROM

DATA

IT ALWAYS MOVE LEFT TO RIGHT

=VLOOKUP(LOOKUP VALUE,TABLE ARRAY,CLOM

INDEX,0)

WHEN WE USE WE ALSO HAVE TO USE FREZZING SO WE CAN COPY

OR USE FOR OTHER THINGS LIKE TO DRAG FORMULA

2 HLOOKUP = USE TO FIND OUT THE VALUE FROM DATA

= ALWAYS MOVE TOP TO BOTTOM

=HLOOKUP(LOOKUPVALUE,TABLE ARAY , ROW

INDEX NUMBER)

NOW IN THIS FORMULAS WE HAVE TO GIVE TIME AND DO IT

MANUALLY LIKE SINGLE NO CHANGE TO MAKE BIG DATA BUT
WHEN WE HAVE THOUSANDS OD DATA THEN? SO HERE THE
SOLUTION

MATCH = IT USE TO FING THE MATCH WITH MORE EASSY WAY

WE USE IT WITH LOOKUP FIRST

=VLOOKUP(LOOKUP VALUE,TABEL
ARRAY,MATCH(LOOKUPVALUE,LOOKUP ARRAY ,0)
FREZING IS ALSO IMPORTANT

NEXT WE HAVE

=HLOOKUP(LOOKUPVALUE,VALUE
ARRAY,MATCH(LOOKUP VALUE,LOOKUPARRAY,0),)
NOW WE INTRODUSE INDEX

INDEX = IT SELECT FROM INTER SECTION POINT

= INDAY(ARRAY,ROW,COLUM)

IN THIS WE ALSO HAVE MATCH

INDEX(ARRAY,MATCH(ARRAY,ROW,0),MATCH(ARRAY,COLUM,0))

FREEZING ALSO USE IN IT

NOW WE HAVE 2 FUNCTION OF WHAT IT

1 GOAK SEEK = HOW MUCH PRICE OR UNIT INCRE TO GET DESIRE

PROFIT

3 STEPS IN IT

1 SET SALE

2 DESIRED VALE

3CHANGING OF (UNIT,PRICE,COST)

2 IS DATA TABLE

DATA TABLE IS MADE

2 OPTIONS

1 ROW

2 COLUMS