HYPERLINK

Power Query: 5:55:23

 DAX (Data Analysis Expressions): 1:03:15

 Excel Dashboards: 7:37:00

 Time Series Analysis: 6:45:02

 Power Pivot: 5:33:59

 Advanced Data Validation and Conditional Formatting: 3:41:56

 Embedding Excel in PowerPoint / PDFs in Excel: 1:12:31, 1:14:40

 Excel Copilot – 00:27:41

 Data Analytics using AI – 00:29:20

 Descriptive Statistics Using Excel – 00:36:19

 Using ChatGPT with Excel for Data Analytics – 06:33:23

 Top 10 Data Analyst Projects in 2025 – 07:11:13

RED COLOUR IS QUESTION

BLUE COLOUR IS CHOOSING THE TEXT INSIDE THE CELL

GREEN COLOUR IS FOR CAPITAL LETTERS

YELLOW COLOUR IS ANYTHING YOU CAN GIVE

CONVERT NUMBER TO WORD(WITH DOLLAR)

Function Convert_Number_into_word_with_currency(ByVal whole_number) Dim

converted_into_dollar, converted_into_cent my_ary = Array("", "", " Thousand ", " Million ", "
Billion ", " Trillion ") whole_number = Trim(Str(whole_number)) x_decimal = InStr(whole_number,
".") If x_decimal > 0 Then converted_into_cent = get_ten(Left(Mid(whole_number, x_decimal + 1)
& "00", 2)) whole_number = Trim(Left(whole_number, x_decimal - 1)) End If xIndex = 1 Do While
whole_number <> "" xHundred = "" xValue = Right(whole_number, 3) If Val(xValue) <> 0 Then
xValue = Right("000" & xValue, 3) If Mid(xValue, 1, 1) <> "0" Then xHundred =
get_digit(Mid(xValue, 1, 1)) & " Hundred " End If If Mid(xValue, 2, 1) <> "0" Then xHundred =
xHundred & get_ten(Mid(xValue, 2)) Else xHundred = xHundred & get_digit(Mid(xValue, 3)) End If
End If If xHundred <> "" Then converted_into_dollar = xHundred & my_ary(xIndex) & Dollar End If
If Len(whole_number) > 3 Then whole_number = Left(whole_number, Len(whole_number) - 3) Else
whole_number = "" End If xIndex = xIndex + 1 Loop Select Case converted_into_dollar Case ""
converted_into_dollar = " Zero Dollar" Case "One" converted_into_dollar = " One Dollar" Case Else
converted_into_dollar = converted_into_dollar & "Dollars" End Select Select Case
converted_into_cent Case "" converted_into_cent = " and Zero Cent" Case "One"
converted_into_cent = " and One Cent" Case Else converted_into_cent = " and " &
converted_into_cent & "Cents" End Select Convert_Number_into_word_with_currency =
converted_into_dollar & converted_into_cent End Function Function get_ten(pTens) Dim
my_output As String my_output = "" If Val(Left(pTens, 1)) = 1 Then Select Case Val(pTens) Case 10:
my_output = "Ten" Case 11: my_output = "Eleven" Case 12: my_output = "Twelve" Case 13:
my_output = "Thirteen" Case 14: my_output = "Fourteen" Case 15: my_output = "Fifteen" Case 16:
my_output = "Sixteen" Case 17: my_output = "Seventeen" Case 18: my_output = "Eighteen" Case
19: my_output = "Nineteen" Case Else End Select Else Select Case Val(Left(pTens, 1)) Case 2:
my_output = "Twenty " Case 3: my_output = "Thirty " Case 4: my_output = "Forty " Case 5:
my_output = "Fifty " Case 6: my_output = "Sixty " Case 7: my_output = "Seventy " Case 8:
my_output = "Eighty " Case 9: my_output = "Ninety " Case Else End Select my_output = my_output
& get_digit(Right(pTens, 1)) End If get_ten = my_output End Function Function get_digit(pDigit)
Select Case Val(pDigit) Case 1: get_digit = "One" Case 2: get_digit = "Two" Case 3: get_digit =
"Three" Case 4: get_digit = "Four" Case 5: get_digit = "Five" Case 6: get_digit = "Six" Case 7:
get_digit = "Seven" Case 8: get_digit = "Eight" Case 9: get_digit = "Nine" Case Else: get_digit = ""
End Select End Function

NUMBER TO WORD

Function number_converting_into_words(ByVal MyNumber)

Dim x_string As String

Dim whole_num As Integer

Dim x_string_pnt

Dim x_string_Num

Dim x_pnt As String

Dim x_numb As String

Dim x_P() As Variant

Dim x_DP

Dim x_cnt As Integer

Dim x_output, x_T As String

Dim x_my_len As Integer

On Error Resume Next

x_P = Array("", "Thousand ", "Million ", "Billion ", "Trillion ", " ", " ", " ", " ")

x_numb = Trim(Str(MyNumber))

x_DP = InStr(x_numb, ".")

x_pnt = ""

x_string_Num = ""
If x_DP > 0 Then

x_pnt = " point "

x_string = Mid(x_numb, x_DP + 1)

x_string_pnt = Left(x_string, Len(x_numb) - x_DP)

For whole_num = 1 To Len(x_string_pnt)

x_string = Mid(x_string_pnt, whole_num, 1)

x_pnt = x_pnt & get_digit(x_string) & " "

Next whole_num

x_numb = Trim(Left(x_numb, x_DP - 1))

End If

x_cnt = 0

x_output = ""

x_T = ""

x_my_len = 0

x_my_len = Int(Len(Str(x_numb)) / 3)

If (Len(Str(x_numb)) Mod 3) = 0 Then x_my_len = x_my_len - 1

Do While x_numb <> ""

If x_my_len = x_cnt Then

x_T = get_hundred_digit(Right(x_numb, 3), False)

Else

If x_cnt = 0 Then

x_T = get_hundred_digit(Right(x_numb, 3), True)

Else

x_T = get_hundred_digit(Right(x_numb, 3), False)

End If

End If

If x_T <> "" Then

x_output = x_T & x_P(x_cnt) & x_output

End If

If Len(x_numb) > 3 Then

x_numb = Left(x_numb, Len(x_numb) - 3)

Else

x_numb = ""

End If

x_cnt = x_cnt + 1

Loop

x_output = x_output & x_pnt

number_converting_into_words = x_output

End Function

Function get_hundred_digit(xHDgt, y_b As Boolean)

Dim x_R_str As String

Dim x_string_Num As String

Dim x_string As String

Dim y_I As Integer

Dim y_bb As Boolean

x_string_Num = xHDgt

x_R_str = ""

On Error Resume Next

y_bb = True

If Val(x_string_Num) = 0 Then Exit Function

x_string_Num = Right("000" & x_string_Num, 3)

x_string = Mid(x_string_Num, 1, 1)

If x_string <> "0" Then

x_R_str = get_digit(Mid(x_string_Num, 1, 1)) & "Hundred "

Else

If y_b Then

x_R_str = "and "

y_bb = False

Else

x_R_str = " "

y_bb = False

End If
End If

If Mid(x_string_Num, 2, 2) <> "00" Then

x_R_str = x_R_str & get_ten_digit(Mid(x_string_Num, 2, 2), y_bb)

End If

get_hundred_digit = x_R_str

End Function

Function get_ten_digit(x_TDgt, y_b As Boolean)

Dim x_string As String

Dim y_I As Integer

Dim x_array_1() As Variant

Dim x_array_2() As Variant

Dim x_T As Boolean

x_array_1 = Array("Ten ", "Eleven ", "Twelve ", "Thirteen ", "Fourteen ", "Fifteen ", "Sixteen ",
"Seventeen ", "Eighteen ", "Nineteen ")

x_array_2 = Array("", "", "Twenty ", "Thirty ", "Forty ", "Fifty ", "Sixty ", "Seventy ", "Eighty ",
"Ninety ")

x_string = ""

x_T = True

On Error Resume Next

If Val(Left(x_TDgt, 1)) = 1 Then

y_I = Val(Right(x_TDgt, 1))

If y_b Then x_string = "and "

x_string = x_string & x_array_1(y_I)

Else

y_I = Val(Left(x_TDgt, 1))

If Val(Left(x_TDgt, 1)) > 1 Then

If y_b Then x_string = "and "

x_string = x_string & x_array_2(Val(Left(x_TDgt, 1)))

x_T = False

End If

If x_string = "" Then

If y_b Then
x_string = "and "

End If

End If

If Right(x_TDgt, 1) <> "0" Then

x_string = x_string & get_digit(Right(x_TDgt, 1))

End If

End If

get_ten_digit = x_string

End Function

Function get_digit(xDgt)

Dim x_string As String

Dim x_array_1() As Variant

x_array_1 = Array("Zero ", "One ", "Two ", "Three ", "Four ", "Five ", "Six ", "Seven ", "Eight ",
"Nine ")

x_string = ""

On Error Resume Next

x_string = x_array_1(Val(xDgt))

get_digit = x_string

End Function

NUMBERS TO WORDS(RUPEES)

Function word(SNum As String) 'Declare the Variables Dim zDPInt As Integer Dim zArrPlace As
Variant Dim zRStr_Paisas As String Dim zNumStr As String Dim zP As Integer Dim zTemp As String
Dim zStrTemp As String Dim zRStr As String Dim zBp As Integer zArrPlace = Array("", "", " Thousand
", " Lacs ", " Crores ", " Trillion ", "", "", "", "") On Error Resume Next If SNum = "" Then word = ""
Exit Function End If zNumStr = Trim(Str(SNum)) If zNumStr = "" Then word = "" Exit Function End If
zRStr = "" zBp = 0 If (zNumStr > 999999999.99) Then word = "Digit excced Maximum limit" Exit
Function End If zDPInt = InStr(zNumStr, ".") If zDPInt > 0 Then If (Len(zNumStr) - zDPInt) = 1 Then
zRStr_Paisas = word_GetT(Left(Mid(zNumStr, zDPInt + 1) & "0", 2)) ElseIf (Len(xNumStr) - xDPInt) >
1 Then zRStr_Paisas = word_GetT(Left(Mid(zNumStr, zDPInt + 1), 2)) End If zNumStr =
Trim(Left(zNumStr, zDPInt - 1)) End If zP = 1 Do While zNumStr <> "" If (zP >= 2) Then zTemp =
Right(zNumStr, 2) Else If (Len(zNumStr) = 2) Then zTemp = Right(zNumStr, 2) ElseIf (Len(zNumStr) =
1) Then zTemp = Right(zNumStr, 1) Else zTemp = Right(zNumStr, 3) End If End If zStrTemp = "" If
Val(zTemp) > 99 Then zStrTemp = word_GetH(Right(zTemp, 3), zBp) If Right(Trim(xStrTemp), 3) <>
"Lac" Then zBp = zBp + 1 End If ElseIf Val(zTemp) <= 99 And Val(zTemp) > 9 Then zStrTemp =
word_GetT(Right(zTemp, 2)) ElseIf Val(zTemp) < 10 Then zStrTemp = word_GetD(Right(zTemp, 2))
End If If zStrTemp <> "" Then zRStr = zStrTemp & zArrPlace(zP) & zRStr End If If zP = 2 Then If
Len(zNumStr) = 1 Then zNumStr = "" Else zNumStr = Left(zNumStr, Len(zNumStr) - 2) End If ElseIf zP
= 3 Then If Len(zNumStr) >= 3 Then zNumStr = Left(zNumStr, Len(zNumStr) - 2) Else zNumStr = ""
End If ElseIf zP = 4 Then zNumStr = "" Else If Len(zNumStr) <= 2 Then zNumStr = "" Else zNumStr =
Left(zNumStr, Len(zNumStr) - 3) End If End If zP = zP + 1 Loop If zRStr = "" Then zRStr = "No Rupees"
Else zRStr = " Rupees " & zRStr End If If zRStr_Paisas <> "" Then zRStr_Paisas = " and " &
zRStr_Paisas & " Paisas" End If word = zRStr & zRStr_Paisas & " Only" End Function Function
word_GetH(zStrH As String, zBp As Integer) Dim zRStr As String If Val(zStrH) < 1 Then word_GetH =
"" Exit Function 'Converts a Number from 100 to 999 into Word Else zStrH = Right("000" & zStrH, 3)
If Mid(zStrH, 1, 1) <> "0" Then If (zBp > 0) Then zRStr = word_GetD(Mid(zStrH, 1, 1)) & " Lac " Else
zRStr = word_GetD(Mid(zStrH, 1, 1)) & " Hundred " End If End If If Mid(zStrH, 2, 1) <> "0" Then
zRStr = zRStr & word_GetT(Mid(zStrH, 2)) Else zRStr = zRStr & word_GetD(Mid(zStrH, 3)) End If End
If word_GetH = zRStr End Function Function word_GetT(zTStr As String) Dim zTArr1 As Variant Dim
zTArr2 As Variant Dim zRStr As String 'Converts a Number from 10 to 19 into Word zTArr1 =
Array("Ten", "Eleven", "Twelve", "Thirteen", "Fourteen", "Fifteen", "Sixteen", "Seventeen",
"Eighteen", "Nineteen") 'Converts a Number from 20 to 99 into Word zTArr2 = Array("", "Twenty",
"Thirty", "Forty", "Fifty", "Sixty", "Seventy", "Eighty", "Ninety") Result = "" If Val(Left(zTStr, 1)) = 1
Then zRStr = zTArr1(Val(Mid(zTStr, 2, 1))) Else If Val(Left(zTStr, 1)) > 0 Then zRStr =
zTArr2(Val(Left(zTStr, 1)) - 1) End If zRStr = zRStr & word_GetD(Right(zTStr, 1)) End If word_GetT =
zRStr End Function Function word_GetD(zDStr As String) Dim zArr_1() As Variant 'Converts a
Number from 1 to 9 into Word zArr_1 = Array(" One", " Two", " Three", " Four", " Five", " Six", "
Seven", " Eight", " Nine", "") If Val(zDStr) > 0 Then word_GetD = zArr_1(Val(zDStr) - 1) Else
word_GetD = "" End If End Function

CALL IT BY THAT WORD

IN EXCEL NEW YOU CAN CHOOSE WHAT TYPE OF FORMAT YOU
NEED
IF YOU TYPE ON THE CELL AND THE WORDS ARE BIG AND IT IS
HIDING INSIDE CELL YOU CAN’T SEE MEANS DRAG ON THE CELL
AT TOP OR DOUBLE CLICK ON DRAG(+) SYMBOL AT TOP IT WILL
ALIGN TO THE WORDS INSIDE THE CELL
MERGE AND CENTRE IS USED MERGE AND CENTRE IN THE CELL
BEFORE USING THIS ALL REQUIRED CELLS SHOULD BE
SELECTED(IN THAT MERGE CELLS AND UNMERGE CELLS ARE
THERE)
TO ADD ROW OR COLUMN SELECT WHERE YOU NEED TO INSERT
AFTER THAT ROW OR COLUMN RIGHT CLICK IT AND PRESS
[Link] ADDING MULTIPLE ROWS SELECT AND DRAG HOW
MANY ROWS OR COLUMNS YOU WANT AND CLICK INSERT
FOR GIVING SERIAL NUMBER FIRST TYPE ONE NUMBER AND
SMALL BOX(LIKE) ON THE LOWER RIGHT CORNER AND DRAG IT
AND PRESS SMALL BOX ON RIGHT CORNER AND GIVE FILL
SERIES
FOR SELECTING ENTIRE COLUMN CTRL+SPACEBAR
FOR SELECTING ENTIRE ROW SHIFT+SPACEBAR
FOR SELECTING DATA ONLY COLUMN CTRL+SHIFT+DOWN
ARROW
FOR SELECTING DATA ONLY ROW CTRL+SHIFT+SIDE ARROW
FOR ADDING DOLLAR SYMBOL OR CHANGING OR GIVING THE
DECIMAL POINT IS IN HOME(GENERAL)

COMPARING COLUMS
CONDITIONAL FORMATING(HIGHLIGHT CELL RULES) IS USED TO
CHECK DUPLICATE VALUES(NEED TO SELECT ALL BEFORE THIS)
FOR CHECKING IT IS EQUAL OR NOT LIKE TRUE OR FALSE
=FIRST CELL=SECOND CELL .
INSTEAD OF TRUE OR FALSE YOU CAN GIVE WHAT YOU WANT
LIKE CONDITIONAL STATEMENT IN PROGRAMMING LANGUAGE
=IF(FIRSTCELL=SECONDCELL,”FOUND”,”NOT FOUND”)
= IS USED TO COMPARE ONE VALUE WITH THE OTHER ONE
ANOTHER TYPE
VLOOKUP IS USED TO COMPARE ONE VALUE WITH MULTIPLE
VALUE IN OTHER COLUMN
=VLOOKUP(ONECELL,GROUPOFCELL,COLUMNNUMBER,EXACTM
ATCH)
YOU CAN SELECT ONE CELL AND PRESS COMMA AND SELECT
GROUP OF CELL AND PRESS F4(ALONG WITH FUNCTION KEY)
AND GIVE COLUMN NUMBER AS 1 OR 2 LIKE WHICH WE CHOOSE
AND APPROXIMATE MATCH MEANS 1 OR EXACT MATCH MEANS 0
IFERROR IS USED WHEN IN VLOOKUP FOR MATCHING NAMES IT
WILL GIVE THE NAME FOR THE NON MATCHING NAMES IT WILL
GIVE AS NA TO AVOID THAT WE ARE GIVING
=IFERROR(VLOOKUP(ONECELL,GROUPOFCELL,COLUMNNUMBER
,EXACTMATCH),”NOT FOUND”)
IN THE ABOVE VLOOKUP IT WILL GIVE ONLY IT EXACT MATCH
LIKE IN ONE CELL IT IS SKODA AND IN THE GROUPOFCELL IT IS
SKODA INDIA IT WILL NOT GIVE IN ORDER TO GET THAT TYPE WE
NEED TO USE
=VLOOKUP(ONECELL&”*”,GROUPOFCELL,COLUMNNUMBER,EXAC
TMATCH)
HERE & AND * WE ARE GIVING WE ARE SAYING THAT IF THERE IS
ANYTHING EXTRA IN THE WORD RATHER THAN THE FIRST WORD
CONSIDER IT FOR EXAMPLE SKODA ON ONE SIDE AND SKODA
INDIA ON OTHER SIDE IN THIS METHOD IT WILL GIVE THE WORD

CONVERT ROWS INTO COLUMNS AND VICEVERSA

FIRST SELECT ALL AND COPY AND IN THE NEW SHEET SELECT
PASTE->PASTE SPECIAL->TRANSPOSE
ANOTHER TYPE
=TRANSPOSE(SELECT ALL THE CELLS THAT ARE WITH DATA
FROM TOP LEFT)
GROUP ROWS AND COLUMNS IN EXCEL
TO GROUP ROWS OR COLUMS FIRST SELECT WHICH YOU NEED
TO GROUP AND GO TO DATA IN THE RIBBON AND CHOOSE
GROUP

HOW TO REMOVE BLANK ROWS OR COLUMNS IN EXCEL

IN HOME FIND & SELECT IN THAT GO TO SPECIAL IN THAT
BLANKS AND TO DELETE PRESS FN+CTRL+-(MINUS KEY) AND
ENTIRE ROW OR COLUMN
IF A SHEET HAS A ROWS WITH 1 OR 2 VALUES FILLED AND OTHER
VALUES IS NOT THERE MEANS YOU CANNOT REMOVE BY THAT
METHOD YOU CAN REMOVE IT BY
TO COUNT FORMULA IS
=COUNTA(SELECT AND DRAG HOW MANY ROWS YOU WANT)
(COUNTA WILL COUNT CELLS WITH VALUES OR NAME AND
COUNT WILL COUNT CELLS WITH NUMBER NOT TEXT OR BLANK )
CTRL+T IS USED TO CREATE THE TABLE
IN THE TABLE HEADING YOU CAN CHOOSE HOW MANY COUNT
YOU WANT

HOW TO FREEZE ROWS IN EXCEL

IN VIEW FREEZE PANE IN THAT FREEZE FIRST ROW OR
COLUMNS OR PLANE(BOTH FIRST ROW AND COLUMN)

HOW TO CONVERT NUMBERS INTO ALPHABET(IT IS NOT READILY

AVAILABLE IN EXCEL IT IS CREATED BY COPYING AND PASTING
CODE FROM MIRCOSOFT WEBSITE FOR NUMBER TO WORD)
(STEPS TO ACTIVATE DEVELOPER OPTION IN HOME AND IN
OPTIONS AND IN CUSTOMIZE RIBBON PRESS TICK FOR
DEVELOPER)(NEXT STEP IN RIBBON IN DEVELOPER AND CLICK
VISUAL BASIC AND IN INSERT SELECT MODULE AND PASTE AND
PRESS CTRL+S AND CLOSE IT)(IF YOU WANT TO SAVE THIS ALL
TIME YOU CLOSE AND OPEN IT SHOUL BE THERE MEANS YOU
SHOULD USE THE FILE FORMAT XLSM NOT XLSX)

COMBINING DATA FROM MULTIPLE CELL

FOR NORMALLY COMBINING WITHOUT GAP YOU CAN USE
FIRSTCELL & SECONDCELL & THIRDCELL
FOR WITH GAP
FIRSTCELL &” ”& SECONDCELL &” ”&THIRDCELL
(INSIDE DOUBLE QUOTES ANYTHING YOU CAN GIVE)

HOW TO ADD DATE

FOR NORMALLY ADDING DATE YOU CAN TYPR =TODAY() IN CELL
BUT IN THIS METHOD IT WILL CHANGE DATE AUTOMATICALLY
WHEN DAYS PASS
IN ORDER TO AVOID THAT PRESS CTRL+;
FOR TIME CTRL+SHIFT+;
HOW TO CHANGE DATE FORMAT
SELECT THE DATE AND IN RIBBON GO TO DATA AND IN THAT
TEXT TO COLUMNS->NEXT->NEXT->IN DATE CHANGE FORMAT
TO CHANGE THE WAY THEY APPEAR LIKE(12-02-2025 TO 12-2-25 )
BY PRESSING RIGHT CLICK AND FORMAT CELLS AND CHOOSE
TYPES
HOW TO CALCULATE THE AGE FROM THE DATE OF BIRTH
=DATEDIF(FIRSTCELL,TODAY(),”Y”)
THE Y YOU GAVE MEANS IT WILL COME IN YEARS AND M YOU
GAVE MEANS IT WILL COME IN MONTH AND D YOU GAVE MEANS
IT WILL COME IN DAYS.
IN THIS IT IS CALCULATING DIFFERENCE BETWEEN 2 DATES
HOW TO CALCULATE TIME DIFFERENCE
IT IS DONE BY SUBTRACTING 2 TIMES
DAX
IN RIBBON ->DATA->POWER PIVOT WINDOW OR DATA MODEL
RETIREMENT AGE CALCULATION
TO FIND THE RETIREMENT AGE FROM THE DATE OF BIRTH AND
JOINING DATE
=EDATE(DOBCELL,60*12)
IN THIS IT IS CALCULATING NUMBER OF

FORMULA
LOWER(TEXT)
UPPER(TEXT)
IF YOU DRAG THE BOX IN THE LOWER RIGT CORNER THE
FORMULA WILL BE APPLIED TO ALL BOX
WHEN AUTO SUGGESTION POPUP MEANS TO GET IT INTO THE
CELL WE NEED TO PRESS TAB

CHECKBOX
IN DEVELOPER OPTION->INSERT->CHECKBOX IS THERE.
YOU CAN ALSO CHANGE THE NAME OF THE [Link] IS
USED TO MAKE TICK OR NOT.
IF IT IS TICK MEANS YOU NEED TRUE AND NOT TICK MEANS YOU
NEED FALSE YOU CAN MAKE IT WORK BY RIGHT CLICK ON THE
CELL->FORMAT [Link] THE CELL LINK YOU WANT TO
SELECT THE EMPTY CELL WHERE YOU WANT TRUE OR FALSE
OR
INSERT->CHECKBOX
HOW TO INSERT EXCEL IN PPT
IN POWERPOINT->INSERT->OBJECT->CREATE FROM FILE

HOW TO INSERT IMAGES IN EXCEL

INSERT->ILLUSTRATION->PICTURE
WHEN HIDING THE IMAGE WILL BE ON THE SAME PLACE IT WILL
NOT HIDE INORDER TO AVOID THAT RIGHT CLICK ON THE
PICTURE->PICTURE FORMAT->SIZE AND PROPERTIES-
>PROPERTIES->MOVE AND SIZE WITH CELL

HOW TO INSERT PDF IN EXCEL

INSERT->TEXT->OBJECT
OR
IF YOU NEED THE PDF TO BE OPENED IF YOU INSERT ANY IMAGE
AND CLICK ON THE IMAGE FOR THIS RIGHTCLICK ON THE IMAGE
->LINK

HOW TO CONVERT PDF INTO EXCEL

DATA->GETDATA->FROM FILE->FROM PDF AND YOU WANT TO
CHANGE THE LOAD OPTION
RIGHT CLICK ON THE QUERIES AND CONNECTION->LOAD TO-
>TABLE

HOW TO ADD TICK MARK IN EXCEL

IN HOME->CONDITIONAL FORMATING->NEW RULES->INSTEAD OF
2 COLOUR SETS WE ARE CHOOSING ICON SETS
=IF(SELECT CELL=”DONE”,1,0)
HOW TO ADD WATERMARK IN EXCEL
INSERT->TEXT->HEADER AND FOOTER. IN HEADER AND
FOOTER->PICTURE
FROM FORMAT PICTURE YOU CAN CHANGE THE PICTURE SIZE
AND BRIGHTNESS

HOW TO CHANGE CELL SIZE IN EXCEL

YOU CAN RIGHT CLICK AND PRESS FORMAT CELL OR ROW
HEIGHT OR COLUMN WIDTH
OR
PRESS CTRL+A AND IT SELECT ALL CELLS IN SHEET
PRESS ALT AND RELEASE AND PRESS H,O,I IT ADJUSTS COLUMN
WIDTH
PRESS ALT AND RELEASE AND PRESS H,O,A IT ADJUSTS ROW
HEIGHT

HOW TO CREATE BARCODE IN EXCEL

WHILE TYPING NUMBER IN THE CELL IT IS IN GENERAL FORMAT
YOU NEED TO CONVERT IT TO TEXT FORMAT
THE BARCODE SYMBOL IS IN THE FONT BUT IT IS NOT AVAILABLE
BY DEFAULT YOU NEED TO DOWNLOAD IT FROM GOOGLE 3 OF 9
BARCODE FONT AND INSTALL 30F9_NEW.TTF AND YOU CAN SEE
THIS IN FONT SECTION

EXCEL FLASH FILL

FOR EXAMPLE 100-20048400-1173 LIKE THIS MANY COLUMN WILL
BE THERE YOU NEED EACH WORD TO BE IN INDIVIDUAL CELL
LIKE 100,20048400,1173 FOR THIS YOU TYPE 100 AND SAME FOR
ALL CELLS IN SAME ROW AND FOR NEXT COLUMN ALSO SAME
AND AFTER THAT NEXT CELL YOU NEED TO PRESS CTRL+E
THIS IS NOT ALONE USED FOR HIPEN SEPARATED VALUES IT
ALSO USED FOR VALUES LIKE 4 TO 8TH LETTER IN WORDS
HYPERLINK
TYPE ANY WORD IN EXCEL AND PRESS CTRL+K HYPERLINK TAB
WILL OPEN
OR
INSERT->LINK
OR
RIGHT CLICK->LINK
WHEN KEEPING THE CURSOR OVER THE WORD IT SHOWS
LENGTHIER WORD IT CAN BE ADJUSTED BY TYPING WHAT YOU
WANT ON SCREENTIP
FOR MOVING TO THE NEXT SHEET IS ALSO POSSIBLE IN
HYPERLINK(PLACE IN THIS DOCUMENT)

ROUND OFF FORMULA

=ROUNDUP(CELL,NUMBEROFDECIMALDIGITS)
=ROUNDDOWN(CELL,NUMBEROFDECIMALDIGITS)

HOW TO CALCULATE STANDARD DEVIATION IN EXCEL

STANDARD DEVIATION IS THE SQUARE ROOT OF THE VARIANCE
VARIANCE IS THE MEASURE OF [Link] IS CALCULATED
BY TAKING THE AVERAGE OF SQUARED DEVIATION FROM THE
MEAN
IN STATISTICS AND MATHEMATICS THE DEVIATION IS THE
MEASURE THAT IS USED TO FIND OUT THE DIFFERENCE
BETWEEN THE OBSERVED VALUE AND EXPECTED VALUE OF A
[Link] SIMPLE WORDS DEVIATION IS THE DIFFERENCE
FROM THE CENTRE POINT
OBSERVED VALUE IS THE RESULT WHAT YOU GET IN REAL TIME
MEAN IS AVERAGE OF ALL VALUE
TO CALCULATE THE STANDARD DEVIATION WE REQUIRE
MEAN,VARIANCE,DEVIATION AND DEVIATION SQUARE
SUM:=SUM()
COUNT:=COUNT()
MEAN:AVERAGE()
DEVIATION IS EACH VALUE-MEAN VALUE
VARIANCE=(SUMOFDEVIATIONSQUARE)/(TOTAL NUMBER OF
VALUES-1)
STANDARD DEVIATION=VARIANCE^0.5

INDEX MATCH IN EXCEL

IT IS USED TO FIND THE INDEX OR POSITION
=MATCH(FIRSTCELL,GROUPOFCELL,0 OR 1)

HOW TO SORT BY DATE IN EXCEL

TO SORT THE DATE FIRST SELECT ALL DATES AND CHOOSE IT
FORMAT AS SHORTDATES
IN DATA->SORT->CHOOSE COLUMN AND NEWEST TO OLDEST OR
OLDEST TO NEWEST

HOW TO USE AUTOSUM IN EXCEL

IN HOME->SUMMATION SYMBOL(YOU WANT TO DO THIS BY
SELECTING THE CELL WHERE YOU WANT THE ANSWER IT WILL
AUTOMATICALLY CHOOSE CELLS BEFORE IT YOU CAN ALSO EDIT
HOW MANY CELLS TO BE ADDED)

HOW TO SORT DATA IN EXCEL

TO SORT NUMBERS OR ALPHABETS
SELECT THE ROWS WITH NUMBERS AND IN HOME->SORT AND
FILTER
IF YOU HAVE 2 NAMES SAME YOU CAN ALSO GIVE SUBSET IN
SORTING