EXCEL
FORMULA AND FUNCTIONS SUMMARY
INTRODUCTION
Horizontal → Row →Represent in Numbers
Vertical → Column →Represent in Alphabets
Workbook → Like Notebook
Worksheet → Like Page Number in Notebook
Formula Bar → Shows the Content of Active Cell
Name Box →Shows the Name of Active Cell → You can select the Range and Give Some Name
Excel Extension →.xlsx
Delete Vs Clear Contents
Delete (The Entire Row or Column will be Deleted and the Row Below or Column Next to it Take its Position)
Clear Content (Only the Content in Row and Column will change No Changes in Position)
Hide Rows and Column (Right Click on Row Headings or Column Headings and Select Hide)
Unhide Rows and Column (Right Click on Row Headings or Column Headings and Select unhide or Double Tab)
Date Format
Excel Treat Date has Special Type of Number Value
Earliest Date in Excel Starts with 1 January 1900 Which Means 1 in Number Format which represent 1st Day.
For Example, → In Excel Value 1 Which is in Numeric Data Format, if you convert to Date Data Format it will write in 1 Jan 1900
How to Set Password to Excel workbook
File → Info →Protect Workbook → Encrypt with Workbook
There are Three Viewpoints in Excel (Right Bottom)
Normal (Default Layout)
Page Layout (Worksheets are Divided into Pages, Visualize Printout While working in Excel)
Page Break Preview (To Adjust Page Brake Borders)
Formula Vs Function
Formula is an Expression which calculates value of the Cell.
Function is a predefined Formula.
Mathematical Operation in Excels
I. Addition =A2+B2
II. Subtraction=A2-B2
III. Multiplication=A2*B2
IV. Average=(A2+B2+C2)/3
Note: Why we are giving cell Reference instead of Actual
Number, because by giving cell reference it will make Dynamic Change, Excel has a functionality to Dynamic changes in cell reference.
Instead of Making Dynamic Changes if you want to Lock Specific Cell Reference (Constant) you can Add Dollar Symbol, Shortcut to Add Dollar F4
Ex: Addition =A2+$B$2
MATHEMATICAL FUNCTION
I. Sum Function (Addition)
=SUM(A1,B1,C2)
=SUM(A1:A6)
II. Min Function (To Find Minimum Value)
=Min(A1:A6)
III. Max Function (To Find Maximum Value)
=Max(A1:A6)
IV. Average Function (To Find Average Value)
=Average(A1:A6)
V. Rank.Avg Function (To Find Average and Assigns the Rank based on Average)
=RANK.AVG(number,ref,order)
Number →Whose Rank want to Find.
Ref →Range of cells that you want to calculate the average rank.
Order →Descending Order → Highest to Lowest →0 (Default)
Order →Ascending Order → Lowest to Highest →1
Example: RANK.AVG(P5,P$5:P$9,0)
VI. Sum Product Function (Sum Means Addition, Product Means the result of Multiplication. Sum Product function use to Sum Up After Multiplying Two or More Range)
=SUMPRODUCT(M5:P5,M11:P11)
How its Work =((M5*M11)+(N5*N11)+(O5*O11)+(P5*P11))
VII. Random Function (Use to Generate Random Number)
=RAND( )
VIII. Random Between Function (Use to Generate Random Number Between two given value)
=RANDBETWEEN(BOTTOM,TOP)
=RANDBETWEEN(40,10)
IX. Modulus Function (To Find the remainder of two numbers after division)
=MOD(M5,P5)
X. Absolute Function (Absolute converts negative numbers to positive numbers. Positive numbers and zero (0) are not affected)
=ABS(M5)
XI. Round Function (To rounds a number to a specified number of digits)
= ROUND(Number,NumDigits)
Number →Number you want to round.
Number Digit → Rounded to How Many Digit ( 0→ No Decimal, 1 →One Decimal, 2→Two Decimal, -1 →Nearest Multiply to 10, -2 →Nearest Multiply to 100)
=ROUND(M5,2)
XII. Square Root Function (To Find Square Root of any Positive Number)
=SQRT(M5)
TEXTUAL FUNCTIONS
Textual
Functions are use to perform operation on Text type of Data
I. Trim Function (Use to Remove Extra Space before, after and in between the words)
=TRIM(TEXT)
=TRIM(L5)
II. Concatenate (Join Several Text String into one String)
=CONCATENATE(STRING1,STRING2,STRING3)
=CONCATENATE(L4,M4,N4)
=CONCATENATE(L4, “ has scored “,L5,”in Maths”)
Result: Emil has scored 95 in Maths
III. Substitute Function (Used to replace part of the text with new text)
=SUBSTITUTE(TEXT,OldText,NewText)
For Example: Spelling Mistake Stubent need to replace with Student
=SUBSTITUTE(K7,”b”,”d”)
Note: This Function is case sensitive
IV. Upper and Lower Function (Used to convert Uppercase Text to Lowercase or Lowercase Text to Uppercase)
=UPPER(L1)
=LOWER(L2)
V. Length Function (Used to Count No of Characters in the Text)
=LEN(A1)
VI. Left Function & Right Function (How Many Nos of Characters from Text Need to Return Form Left or Right)
=LEFT(M5,4) it Means in M5
Text area Need to Return 4 Characters Form Left
=RIGHT(M5,3) it Means in M5
Text area Need to Return 3 Characters Form Right
VII. Mid Function (How Many Nos of Characters from Text Need to Return, Starts Form which No of Character)
=MID(M5,5,6) it Means in M5
Text area Need to Returns 5th Characters and goes up to 6 characters
LOGICAL FORMULAS
Logical Functions use to compare one or more data,
Logical Functions use to test multiple conditions instead of single condition.
Example For Single Condition: =M5=>100, So to check M5 is greater than or
Equal to 100 then Return True or else false
Example For Multiple Condition:
I. To check all the conditions are true then Return True or Else False.
II. To check anyone of the conditions are True or Not.
I. And Function (To Check all the conditions are True)
=AND(M5>75,N5>75,P5>75)
How its Work: If M5,N5,P5 all Threes are Greater than 75 Then Return True or Else False.
II. OR Function (To Check Any one of the Condition are
True)
=OR(M5<35,N5<35,P5<35)
How its Work: If M5,N5,P5 Among Three anyone of them are Lesser than 35 Then Return True or Else False
III. IF Function (To checks whether conditions are met, if met
(True) returns one value and if not met (false) returns another value.)
=IF (M5>=35,”All Pass”,”Fail”)
How its Work: If M5 is Greater than or Equal to 35 then All pass, If M5 Lesser than 35 then Fail.
IV. Nested IF Function (Within One if Function we can use another if Function. When you have multiple conditions to meet, if met (True) returns one value and if not met (false) replaced by another IF function to make a further test.)
=IF(C4<35,”Fail”,IF(C4<50,”C”,IF(C4<60,”B”,IF(C4<70,”A”,”Pass in Distance”))))
How its Work: Its Work Like Grade Allotted based on Mark
V. IF & AND Function (Combination of AND and IF, if AND Function Returns True → IF Function returns one Value, if AND Function Returns False →IF Function returns another value.)
=IF(AND(M5>75,N5>75,P5>75),”Passed
With Distinction”,”A Grade”)
VI. IF & OR Function (Combination of OR and IF, if OR Function Returns True → IF Function returns one Value, if OR Function Returns False →IF Function returns another value.)
=IF(OR(M5<35,N5<35,P5<35),”Failed”,”Promoted to Next Class”)
VII. CountIf Function (Used to Count Number of cells based on criteria.)
Notes: Criteria means True or False Condition
=COUNTIF(Range,Criteria)
=COUNTIF(P5:P9,L15)
How it Works: P5:P9 Contains Number of Grades, L25 Contains Grade A, So Excel Counts How many numbers of A Grades in P5:P9 Range.
VIII. CountIfs Function (Use to Count Number of cells based on two or more criteria.)
=COUNTIFS(Range1,Criteria1,Range2,Criteria2, Range3,Criteria3,)
=COUNTIFS(M5:M9,L22,N5:N9,L22,P5:P9,L22,)
How it Works: M5:M9,N5:N9,P5:P9 Contains List of Numbers, L25 Contains >75, So Excel Counts How many numbers of Greater than 75 in M5:M9,N5:N9,P5:P9 Range.
IX. SumIF Function (Same as like CountIf Function only difference, Instead of Counting Number of cells based on criteria, SumIF Function Adds Cells Values based on Criteria.)
=SUMIF(RANGE,CRITERIA,SUMRANGE)
=SUMIF(R5:R9,L14,M5:M9)
How its Work: R5:R9 Contains List of All Transaction for Ex: Cash Deposit, Cash Withdraws and Other Transaction, M5:M9 Contains Value of the Transaction, L14 Contains Cash Deposit. Now SUMIF Works Like Filtering Value Which Contain Transaction Name Cash Deposit and Add those Filtered Value.
DATE TIME FORMULA FUNCTIONS
I. Today Function (To Return Today’s Date)
=TODAY()
II. Now Function (To Return Current Date and Time)
=NOW()
III. Day Function (To Return Day of the Date in Numeric Format)
=DAY(A1)
How its Work ! A1 contain 22/11/2022 it will return 22
IV. Month Function(To Return Month of the Date in Numeric Format)
=MONTH(A1)
How its Work ! A1 contain 22/11/2022 it will return 11
V. Year Function(To Return Year of the Date in Numeric Format)
=YEAR(A1)
How its Work ! A1 contain 22/11/2022 it will return 2022
VI. Days Function (Use to Calculate Difference between Two Date)
=DAY(EndDate,StartDate)
=DAY(A2,A1) it Will Return in Days
VII. Date Difference Function (Use to calculate Difference Between Two Dates, it will return in Day, Month or Year)
=DATEDIF(StartDate,EndDate,”D”) →To Find Day Difference
=DATEDIF(StartDate,EndDate,”M”) →To Find Month Difference
=DATEDIF(StartDate,EndDate,”Y”) →To Find Year Difference
=DATEDIF(A2,A1,”D”) it Will Return in Days
LOOKUP FUNCTIONS
I. VLookup (VLookup Stands for Vertical Lookup, use to Lookup (Search) data in a table organized Vertically. VLOOKUP supports Approximate Match and Exact Match.)
=VLookup(LookupValue,TableRange,ColumnIndexNumber,LookupMatchingType)
Lookup Value → Which Value You Want to Match → Refer That Cell
Table Range → From which Table (Table Organized Vertically) you want to retrieve the Data and Match the Lookup Value
Column Index Number → Mention the Column Number from the Table which Value to Return.
LookupMatchingType → For Exact Match 0, For Approximate Match 1.
=VLOOKUP(L5,$L$14:$M$21,2,0)
How it Works: To Check the L5 value in L15 to L21 Form the Table, if its Exactly Matched to L5 Return the Corresponding Value From the table M14 to M21.
Note: VLOOKUP can only look to the right. In other words, you can only retrieve data to the right of the column that holds lookup values
II. HLookup (HLookup Stands for Horizontal Lookup, use to lookup (Search) data in a table organized Horizontally. HLOOKUP supports Approximate Match and Exact Match.)
=HLookup(LookupValue,TableRange,RowIndexNumber,LookupMatchingType)
Lookup Value → Which Value You Want to Match → Refer That Cell
Table Range → From which Table (Table Organized Horizontally) you want to retrieve the Data and Match the Lookup Value
Row Index Number → Mention the Row Number from the Table which Value to Return.
LookupMatchingType → For Exact Match 0, For Approximate Match 1.
=HLOOKUP(M5,$M$15:$S$17,2,0)
How its Works: To Check the M5 value in M15 to S17 Form the Table, if its Exactly Matched to M5 Return the Corresponding Value From the table M16 to S16.
Note: HLOOKUP can only look to the bottom. In other words, you can only retrieve data to the bottom of the row that holds lookup values
III. Index Function (Use to Get a value in a list or table based on Column Location and Row location, Index Function gives value of Matched Cell)
=INDEX(TableRange,RowNumber,ColumnNumber)
=INDEX(M5:P9,M12,N13)
IV. Match Function (Use to Locate the Position of a lookup value in the Table, Match Functions only gives the position of the Matched Cell Not a Value of the Matched Cell)
=MATCH(LookupValue,TableRange,LookupMatchType)
LookupMatchingType → For Exact Match 0, For Approximate Match 1.
=MATCH(M10,M5:M9,0)
V. Index Match Function (Its a Combination of Index Function and Match Function, Its a Combination of VLookup Function and HLookup Function, Index Match Functions are incredibly flexible – you can do horizontal and vertical lookups, 2-way lookups, left lookups, case-sensitive lookups, and even lookups based on multiple criteria)
=INDEX(TableRange,MATCH(LookupValue,TableRange,LookupMatchType),ColumnNumber)
=INDEX(Table,RowNumber,MATCH(LookupValue,Table,LookupMatchType))
=INDEX($M$14:$N$21,MATCH(L5,$N$14: :$N$21,0)1)
How it Works: Match Function Returns the Position of Matched Cell, which is Row Number for Index Function, based on the Match Function Result Index Function Returns the Value of Matched Cell
DATA TOOLS
A. Sorting
I. Largest to Smallest
Home Menu → Sort & Filter → Largest to Smallest
II. Smallest to Largest
Home Menu → Sort & Filter → Smallest to Largest
III. A to Z
Home Menu → Sort & Filter → Sort A to Z
IV. Z to A
Home Menu → Sort & Filter → Sort Z to A
V. Custom Sort
Home Menu → Sort & Filter → Custom Sort
Example:
Sort by →Maths Score → Sort on Value → Largest to Smallest
Then Add Level
Then by → Science Score → Sort on Value → Largest to Smallest
Then Add Level
Then by → English Score →Sort on Value → Largest to Smallest
B. Filters (Shortcut Key Alt+A+T)
I. Text Filters
Home Menu → Sort & Filter → Filter
II. Number Filters
Home Menu → Sort & Filter → Filter
III. Custom Filters
Home Menu → Sort & Filter → Filter
C. Data Validation (Data Validation used to Prevent wrong inputs During Data Entry)
I. Number Validation
Data Menu → Data Validation → Settings → Allow → Whole Number → Data Between → 0 to 100
How its Work: If you Enter Any Number Above 100 or Below 0 (Negative Number) It Will Through Error
II. List Validation
Data Menu → Data Validation → Settings → Allow → List → Source → Select the Table Range where you enter Predefined List
How its Work: Instead of Entering Data it shows predefine List of Data’s, User Can Select anyone of them form the List
III. Length Validation
Data Menu → Data Validation → Settings → Allow →Text Length → Data Less than → Maximum →10
How its Work: User cannot input more than 10 Characters in the particular Cell
IV. Input Message
Data Menu → Data Validation →Input Message → Title → Write Message Title to Display → Input Message → Write Description About Message to Display
How its Work: Input Message use to Display when User Inputting the Data in Particular cell
V. Error Alert
Data Menu → Data Validation → Error Alert → Style → Select One of the Icons → Title → Write Error Message Title to Display → Error Message → Write Description About Error Message to Display
How its Work: Error Message Displayed when User Enter Data, Which Broke the Rules that we already defined.
D. Text to Columns
I. Delimited
Data Menu → Text to Column → Delimited → Next → Select Anyone of the Symbol (Tab, Semicolon, Comma, Space, Other Symbols) → Next → Finish
How its Work: In Selected cell if you want to Separate Data based on Character Such as Tab, Semicolon, Comma, Space or Other Symbols (In Case of other Symbol, Type the Symbol)
II. Fixed Length
Data Menu → Text to Column → Fixed Width → Add Lines to Desire Position by Drag & Drop → Next → Finish
How its Work: If we cannot separate Data by character, but we can know Column Length for Each Set of Data.
E. Remove Duplicates
I. Remove Duplicates Based on Exact Value (Remove Data’s only if it’s Matched in All Columns, even if one Column did not match it will not remove Duplicate)
Data Menu → Remove Duplicates → Expand the Selection → Select All Columns → Ok
II. Remove Duplicated Based on Column Specific (Instead of All Columns has Exact Value, even if one Column has Exact Value it will remove Duplicate)
Data Menu → Remove Duplicates → Expand the Selection → Select the Specific Column → Ok
SOME IMPORTANT DATA AND TABLES FORMATTING
A. Conditional Formatting (To Visually Highlights the data based on Specific Rules, this makes data more understandable for readers)
I. Highlight Cells Rules
Example 🙁Highlights Students who have Score less than 35 Marks in Any of the Subject.)
Select the Table Range → Home Menu → Conditional Formatting → Highlights Cells Rules → Less Than → Type Value 35 → Select one of the Colour use to Highlight → Ok
II. Top/Bottom Rules
Example: (Highlights Top 10 Items, Top 10 %, Bottom 10%, Above Average, Below Average etc.)
Select the Table Range → Home Menu → Conditional Formatting → Top/Bottom Rules → Top 10 % etc. → Type Value → Select one of the Colour use to Highlight → Ok
III. Data Bars (Same as Like Above)
IV. Colors Scales (Same as Like Above)
V. Icons Sets (Same as Like Above)
VI. Conditional Formatting Based on Custom Rule
Example: Based on Low to High the Colour Gradient want to Go from Light to Dark (Sky Blue to Dark Blue)
Select the Table Range → Home Menu → Conditional Formatting → New Rules → Format Cell Based on Value → Minimum Type → Lowest Value → Minimum Colour → Sky Blue → Maximum Type → Highest Value → Maximum Colour → Dark Blue → Ok
B. View Menu
I. GridLines (To Hide Cell Line)
View Menu → Gridlines → Uncheck
II. Formula Bar (To Hide Formula Bar)
View Menu → Formula Bar → Uncheck
III. Headings (To Hide Row Names (ABC…) and Column Name (123…))
View Menu → Headings → Uncheck
C. Freeze Pane
I. Freeze Pane (Freeze Above and Left of Active Cell)
View Menu → Freeze Panes → Freeze Panes
II. Freeze Top Row
View Menu → Freeze Panes → Freeze Top Row
III. Freeze Top Column
View Menu → Freeze Panes → Freeze Top Column
PIVOT TABLES
Why PivotTable?
Pivot Table is a Report
Pivot Table are used to summarize large number of Data’s Quickly.
Pivot Table is used to summaries, sort, reorganize, group, count, total or average data stored in a table.
Pivot Table allow to transfer columns into rows and rows into columns.
Pivot Table allows grouping by any field (column), and using advanced calculations on them.
How to Create PivotTable?
Insert Menu → PivotTable → From Table/Range → Select a Table/Range from Worksheet → New Worksheet or Existing Worksheet → Ok
Now you can see New Sheets Added with PivotTable, In Right Side You Can See PivotTable Fields Pane
I. Choose Fields
Select the Fields you want to summarize, Then Drag and Drop to Drag Fields
II. Drag Fields
a. Filters
b. Columns (Non-Numeric fields are added to Columns)
c. Rows (Date and Time fields are added to Rows)
d. Values (Numeric fields are added to Values)
Notes:
I. We can Group Dates Automatically into Years, Quarters, Days. To do this
Right Click on Date Row → Group → Select or Unselect Years, Quarters, Days → Ok
II. To Group two Heading Select Those Two Headings → Right Click → Group
III. Slicer Option (Slicers are Similar to Filter option, only difference it shows Filter Button)
Click anywhere on Pivot Table → PivotTable Analyze Menu → Insert Slicer → Select the Column Name which you want to Apply Filter (Slicer Button) → Ok
IV. If you want to Sort Data → In PivotTable → Select Grand Total Column → Right Click → Sort → Sort Smallest to Largest or Sort Largest to Smallest
V. To Refresh PivotTable, you can Right Click on PivotTable → Refresh.
PIVOT CHART
PivotCharts complement (Fulfills) PivotTables by adding visualizations to PivotTable, and allow you to easily see comparisons, patterns, and trends.
Both PivotTables and PivotCharts enable you to make decisions about critical data.
Difference between Pivot Chart and a Normal Chart?
A standard chart use range of cells, on the other hand, a pivot chart is based on data summarized in a pivot table.
A pivot chart is already a dynamic chart, but you have to make changes in data to convert a standard chart into a dynamic chart.
I. Any Changes in PivotTable Reflect in PivotChart
II. Any Changes in PivotChart Will Reflect in PivotTable
How to Create Pivot Chart?
Once PivotTable Created →Click Anywhere on PivotTable → Insert Menu →
PivotChart → Select One of the Chart (Example: Column Chart)
