Excel Formula and Function Summary

 

EXCEL FORMULA AND FUNCTIONS SUMMARY

INTRODUCTION

Horizontal → Row → Represent in Numbers

Vertical → Column → Represent in Alphabets

Workbook → Like Note Book

Worksheet → Like Page Number in Note Book

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 1^st Day.

For Example, → In Excel Value 1 Which is in Numeric Data Format, if you convert to Date Data Format it will written in 1 Jan 1900

 

How to Set Password to Excel workbook

File → Info → Protect Workbook → Encrypt with Workbook

There are Three View Points 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 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 5^th 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 its Works:P5:P9 Contains Number of Grades,L25 Contains Grade A, So Excel Counts How many number 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 its Works: M5:M9,N5:N9,P5:P9 Contains List of Numbers,L25 Contains >75,So Excel Counts How  many number 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 returns 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 its 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 its Work: 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 TableRange 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 not matched 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 Goes 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)