Most Essential Excel Formulas And
Functions For Any Career
(The Ultimate Essential Guide for Microsoft Excel Users)

AMAM Mubeen


Copyright 2021 AMAM Mubeen
All Right Reserved. This book is Protected by copyright .it is
prohibited, reproduction, storage in retrieval system or transmission
any form Without publisher’s permission.


Author Biography
I am expert in Microsoft office, tally ERP 9, accounting & finance
related subjects and a degree holder (specialization in finance), I
was working in different positions such as senior accountant and
financial manager of contract-based company. I have been teaching
more than five years and I delivered various lecture programs and
my all-lecture programs are much dynamics & interesting as well. I
speak English and Tamil languages.


Why do you read this Book?
Excel formulas and functions are very important in the daily life,
especially in cooperative world to obtain the meaningful massage
to managerial people For decision making and They are essential
to manipulating data and obtaining useful information from your
Excel workbooks. without Excel formulas and functions, it is
difficult to take important decisions.

You will learn the following essential things related with
Microsoft Excel Formulas and Functions.
01 Introduction to Microsoft Excel- Brief introduction about excel,
Microsoft excel interface, introduction formulas & functions, write
basics formulas and perform calculations from scratch, identify
formulas and functions and know the difference between the two.
02 Mathematical Functions - How to use the popular mathematical
function in excel with real practical example such as SUM,
AVERAGE, MAX, MIN, ROUND, SUMIF, COUNT, COUNTA,
COUNTIF functions
03 Text Functions - How to use the essential text function in excel
with real practical excel sheets which are LEFT, RIGHT,
CONCATENATE, UPPER, LOWER, MID, PROPER, TRIM,
REPT, EXACT functions
04 Date and Time Functions- How to use the essential & popular
date & time functions in excel with real practical excel sheets
which are NOW, TODAY, YEAR, MONTH, DAY, HOUR,
MINUTE, SECOND, TIME.
05 Logical Functions- How to use the lookup function in excel
which are IF and VLOOKUP functions with real practical excel
Examples.


06 Most Usage Shortcut Keys In Excel


Table of Contents
01 Introduction to Microsoft Excel
1.1 Microsoft Excel
1.2 Microsoft Excel Formulas and Functions


02 Mathematical Functions
2.1 Sum Function
2.2 Average Function
2.3 Max Function (Maximum Value)
2.4 MIN Function (Minimum Value)
2.5 ROUND Function
2.6 SUMIF Function
2.7 COUNT Function
2.8 COUNTA Function
2.9 COUNTIF Function

03 Text Functions
3.1 LEFT Function
3.2 RIGHT Function
3.3 CONCATENATE Function
3.4 UPPER Function
3.5 LOWER Function
3.6 PROPER Function
3.7 MID Function
3.8 TRIM Function
3.9 REPT Function
3.10 EXACT Function

04 Date And Time Functions
4.1 NOW Function


4.2 TODAY Function
4.3 YEAR Function

4.4 Month Function
4.5 DAY Function
4.6 HOUR Function
4.7 MINUTE Function
4.8 SECOND Function
4.9 TIME Function

05 Logical Functions
5.1 IF Function
5.2 VLOOKUP Function

06 Most Usage Shortcuts Keys In Excel
6.1 Most Usage Shortcut keys in Excel


01 Introduction to Microsoft Excel
1.1 Microsoft Excel
What is Microsoft Excel?
Microsoft Excel is a spreadsheet program Which Is One of the Application
for the Microsoft Office. We can Manipulate the Data By using Excel
Formulas and Functions.

Interface Of Microsoft Excel?

1 Ribbon tab
Excel Ribbon contain all the menus and tools in the ribbon tabs. There are
nine tabs on the Excel Ribbon those are File, Home, Insert, Page Layout,
Formulas, Data, Review, View, and Help. The Home tab is the default tab
when you open the Excel sheet.
2 Ribbon Groups

Each Ribbon tabs contain multiple groups. Each group is containing specific
comments related with the group.
3 Formula Bar


Formula bar is showing the formula for the active cell.
4 Name Box
Name box display the address of the active cell.
5 Columns
A column is a vertical row of cells. Spreadsheet columns are usually
identified by letters.
6 Rows
A row is horizontal of cells. Spreadsheet rows are usually identified by
numbers
7 Active Cell
Currently selected cell in the spreadsheet. when you write the active cell’s
Address. first, we need to write column letter then we need to write Row
number. in this example K12 is Active cell Address.
8 Sheet tabs
The Excel workbook contain multiple worksheets but still the default sheets
are three and the name for those are sheet1, sheet 2 and sheet 3.

1.2 Microsoft Excel Formulas and Functions
Microsoft Excel Formulas
A formula is a simple calculation to calculate the cell’s values. We can use *,
/, +, -, or ().
Formula will ALWAYS start with an = sign. Here are some examples of few
formulas.
=A1+A2(Add the values)
=A1-A2 (Deduct the values)

=A1*A2(Multiple the values)
=A1/A2(Divide the value)

Microsoft Excel Functions
The Function are programmed into the Microsoft excel, based on our
Requirements we can use it such as AVERAGE, SUM, MAX Etc. Here are
some examples of a few excel functions.


=AVERAGE (number1, [number2], ...)
=SUM (number1, [number2], [number3], ...)
=MAX (number1, [number2], ...)

Practical Example for Excel Formulas – Question

Gross Salary=Emp. Basic Salary + Overtime Allowances
Net Salary = Gross Salary-Advance
Per day Net Salary=Net Salary/30 Days
Q1) Calculate the Gross Salary and Net Salary of Each Employees by Using
Formulas?
Q2) Calculate Per day Salary based on Net salary for Each Employee’s by
Using Formulas?

Practical Example – Answer
Gross Salary Calculation
Step -01: Use Adding (+) Sign:
Select the Cell, Which You Want to Apply the Formula. Then, Choose Basic
salary cell for the first employee after that, write adding sign (+).After that,
select overtime allowance cell for first employee. The below picture Cleary
explain.



Step -02 : Hit The Enter Key :
After Entering the Formula Hit the Enter Key in the keyboard. then, The
Excel Add the Values Which We Selected Cells.

Step -03 : Drag Or Double Click in the Plus Sign (+):


If You Drag Your mouse In the Bottom of Right Corner in the F3 cell, You
Are Able to See Plus Sign (+), If you Double Click in The Plus Sign (+) The
Excel Automatically. Add the values of Entire Cells OR Click on Plus Sign
(+) And Drag Up to Last Cell.

Net Salary Calculation
Step -01: Use Deduction (-) Sign:
Select the Cell, Which You Want to Apply the Formula. Then, Choose Gross
Salary cell for the first employee, after that, hit deduction sign (-) in the key
board. Finally, select Advance cell for first employee. The below picture
Cleary explain.


Step -02 : Hit The Enter Key :
After Entering the Formula, Hit the Enter Key in the keyboard then, The
Excel Deduct the Values Which We Selected Cells

Step -03: Drag Or Double Click in the Plus Sign (+):
If You Drag Your mouse In the Bottom of Right Corner in the H3 cell, You
Are Able to See Plus Sign (+), If you Double Click in The Plus Sign (+) The
Excel Automatically Deduct Entire Cells or Click on Plus Sign (+) And Drag

Up to Last Cell.


Per day salary Calculation
Step -01: Use Deduction (/) Sign:
Select the Cell, Which You Want to Apply the Formula. Then, choose Net
Salary cell for the first employee. after that, hit Divided sign (/) In the key
board. finally put 30 because usually one month equal to 30 days.

Step -02 : Hit The Enter Key :
After Entering the Formula Hit the Enter Key in the keyboard. then, The
Excel Divided the Values Which We Selected Cell.