Excel Introduction

What is Excel?

Excel is a spreadsheet program developed by Microsoft, used for organizing, analyzing, and
presenting data in a structured format. It allows users to input data into cells arranged in columns
and rows, perform calculations and analysis using built-in functions, and create visual
representations of data through charts and graphs. Excel is widely used across various industries
for tasks ranging from simple data entry to complex financial modeling and analysis.

History and Development

The first version of Excel was released in 1985 for the Apple Macintosh, and it has since become a
standard application in the Microsoft Office suite for Windows and macOS platforms. Over the
years, Excel has evolved with new features and enhancements while retaining its core functionality
of spreadsheet management and analysis.

Key Features and Functions

➢ Data Organization: Excel allows users to organize data efficiently using cells, columns, and
rows, making it easy to input, edit, and manage large datasets.
➢ Mathematical Functions: Users can perform various mathematical calculations such as
addition, subtraction, multiplication, division, and more using Excel's built-in functions and
formulas.
➢ Data Analysis: Excel provides tools for data analysis, including sorting, filtering, pivot tables,
and data validation, enabling users to gain insights and make informed decisions.
➢ Charting and Graphing: Users can create visually appealing charts and graphs such as bar
charts, line graphs, pie charts, and more to represent data trends and patterns.
➢ Financial Functions: Excel offers a wide range of financial functions for tasks like budgeting,
forecasting, financial modeling, loan calculations, and investment analysis.
➢ Automation and Macros: Advanced users can automate tasks and create custom functions
using Excel's VBA (Visual Basic for Applications) programming language, allowing for increased
productivity and efficiency.
➢ Collaboration: Excel supports collaboration features such as sharing workbooks, tracking
changes, and commenting, making it suitable for team projects and data sharing.
➢ Templates and Add-ins: Excel provides pre-designed templates for various purposes such as
budgeting, calendars, invoices, and more. Users can also enhance functionality through
downloadable add-ins and extensions.

Why Use Excel?

➢ Popularity: Excel is the most widely used spreadsheet program globally, making it a standard tool
in many industries and organizations.
➢ Ease of Use: It has a user-friendly interface with intuitive features, making it easy for beginners to
start using and learning.
➢ Versatility: Excel can be used for both personal and professional tasks such as budgeting,
accounting, data analysis, project management, and more.
➢ Scalability: Users can start with basic tasks and progress to more advanced operations as their
skills improve, thanks to Excel's extensive capabilities.
➢ Community Support: There is a vast online community of Excel users, forums, tutorials, and
resources available for learning and troubleshooting.
➢ Continuous Development: Microsoft regularly updates and supports Excel, ensuring compatibility
with new technologies and providing security patches and feature enhancements.
 Mathematical Functions
SUMIF:
• SUMIF is used to sum values in a range based on a single condition or criteria.

• Formula: =SUMIF (range, criteria, [sum_range])

• Application:

➢ Summing sales amounts for a specific product.

➢ Calculating total expenses for a particular category.
➢ Summing scores above a certain threshold in a gradebook.

Example: Suppose we have a sales dataset with product categories in column A, sales amounts in
column B (in rupees), and we want to find the total sales for the "Electronics" category.

Product Category Sales Amount (₹)

Electronics ₹1000

Clothing ₹500

Electronics ₹1500

Home Appliances ₹800

Electronics ₹1200

Clothing ₹700

Formula: =SUMIF (A2:A7, "Electronics", B2:B7)

This formula sums the sales amounts (column B) where the product category is "Electronics" (column
A). The result will be ₹3700.

SUMIFS:
• SUMIFS is an extension of SUMIF that allows summing values based on multiple
conditions or criteria.

• Formula: =SUMIFS (sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

• Application:

➢ Summing sales for a specific product category and month.

➢ Calculating total expenses for a particular category within a certain date range.
➢ Summing scores for students who passed a subject and belong to a specific grade
level.

Example: Extending the previous example, let's use SUMIFS to find the total sales for the "Electronics"
category in January.
Product Category Sales Amount (₹) Month

Electronics ₹1000 January

Clothing ₹500 January

Electronics ₹1500 February

Home Appliances ₹800 January

Electronics ₹1200 January

Clothing ₹700 February

Formula: =SUMIFS (B2:B7, A2:A7, "Electronics", C2:C7, "January")

This formula sums the sales amounts (column B) where the product category is "Electronics" (column
A) and the month is "January" (column C). The result will be ₹2200.

COUNTIF:
• COUNTIF is an Excel function used to count the number of cells in a range that meet a
single condition or criteria.

• Formula: =COUNTIF (range, criteria)

• Application:

➢ Counting the number of students who passed an exam (scored above a certain
threshold).
➢ Counting the number of orders above a certain amount.
➢ Counting the number of times a particular product was sold.

Example: Let's consider a dataset of students' exam scores (in Indian grading system) in column A and
we want to count the number of students who scored above 60.

Student Score

A 65

B 55

C 70

D 50

E 75

F 62

Formula: =COUNTIF (B2:B7, ">60")

This formula counts the number of scores in column B that are greater than 60. The result will be the
count of students who scored above 60, which is 3 in this case.
COUNTIFS:
• COUNTIFS is an extension of COUNTIF that allows counting cells based on multiple
conditions or criteria.

• Formula: =COUNTIFS (criteria_range1, criteria1, [criteria_range2, criteria2], ...)

• Application:

• Counting the number of female students who passed with a score above 70.

• Counting the number of orders from a specific region placed after a certain
date.

• Counting the number of times a product was sold in a particular month.

Example: Extending the previous example, let's use COUNTIFS to count the number of students who
scored above 60 and belong to a specific grade (A grade).

Student Score Grade

A 65 A

B 55 B

C 70 A

D 50 C

E 75 A

F 62 B

Formula: =COUNTIFS(B2:B7, ">60", C2:C7, "A")

This formula counts the number of scores above 60 in column B and belonging to grade "A" in column
C. The result will be the count of students who scored above 60 and got an "A" grade, which is 2 in this
case.

AverageIf and AverageIfs:

• : These functions calculate the average of values that meet specific criteria.

• Formulae =AVERAGEIF(range, criteria, [average_range]) =AVERAGEIFS(average_range,

criteria_range1, criteria1, [criteria_range2, criteria2], ...)

• Application:

➢ Calculating the average marks of students who scored above a certain threshold.
➢ Finding the average sales amount for a particular product category.
➢ Determining the average temperature during specific months of the year.

Example: Suppose we have a dataset of students' exam scores (in Indian grading system) in column
A, and we want to calculate the average score for students who scored above 60.
Student Score

A 65

B 55

C 70

D 50

E 75

F 62

Formula=AVERAGEIF (B2:B7, ">60")

This formula calculates the average score of students who scored above 60. The result will be the
average score of 65.

Nested IF:
• Nested IF allows multiple conditions and actions within a single formula.

• Formula =IF (condition1, value_if_true1, IF(condition2, value_if_true2, value_if_false))

• Application:

➢ Assigning grades based on score ranges.

➢ Categorizing products based on sales performance.

Example: Let's categorize students' scores into grades using nested IF based on the grading system:

Student Score

A 65

B 55

C 70

D 50

E 75

F 62

Formula in cell C2= IF(B2>=75, "A", IF(B2>=60, "B", IF(B2>=50, "C", "D")))

This formula assigns grades A, B, C, or D based on score ranges. Copy the formula to other cells to
apply to the entire dataset.

IFERROR Statement:
• IFERROR handles errors gracefully by specifying a value or action if an error occurs.

• Formula=IFERROR(formula, value_if_error)
 • Application:

➢ Handling division by zero errors.

➢ Dealing with lookup errors.

Example: Suppose we have a division operation where errors might occur:

Formula in cell C2=IFERROR (B2/A2, "Error: Division by Zero")

This formula calculates the division of B2 by A2 and displays "Error: Division by Zero" if an error
occurs.

AND, OR, NOT:

AND Function:
• The AND function in Excel returns TRUE if all arguments are TRUE, and FALSE
otherwise.

• Formula=AND (logical1, [logical2], ...)

• Application:

➢ Checking if multiple conditions are met simultaneously.

➢ Filtering data based on multiple criteria.
➢ Validating data entries against multiple rules.

Example: Suppose we have a dataset of students' scores (in grading system) in column A, and we want
to check if a student passed (scored 50 or above) and belongs to a certain grade.

Student Score Grade

A 65 A

B 55 B

C 70 A

D 45 C

E 75 A

F 30 D

Formula in cell D2 for checking if a student passed and is in grade A==AND(B2>=50, C2="A")

This formula checks if the score in B2 is 50 or above AND the grade in C2 is "A". It returns TRUE only if
both conditions are met.

OR Function:
• The OR function in Excel returns TRUE if any argument is TRUE, and FALSE otherwise.

• Formula=OR (logical1, [logical2], ...)

• Application:
 ➢ Checking if at least one condition is true among multiple conditions.
➢ Creating flexible criteria for data analysis.
➢ Triggering actions based on multiple scenarios.

Example: Continuing with the previous example, let's use the OR function to check if a student passed
or is in grade A.

Formula in cell E2for checking if a student passed or is in grade A==OR(B2>=50, C2="A")

This formula checks if the score in B2 is 50 or above OR the grade in C2 is "A". It returns TRUE if any of
the conditions are met.

NOT Function:
• The NOT function in Excel reverses the logical value of a condition. It returns TRUE if
the argument is FALSE, and vice versa.

• Formula=NOT (logical)

• Application:

➢ Reversing conditions for validation purposes.

➢ Filtering data based on negative conditions.
➢ Controlling conditional formatting rules.

Example: Let's use the NOT function to check if a student failed (scored below 50).

Formula in cell F2 for checking if a student failed (scored below 50) =NOT(B2>=50)

This formula checks if the score in B2 is NOT greater than or equal to 50. It returns TRUE if the condition
is FALSE, indicating that the student failed.

UPPER Function:
• The UPPER function in Excel converts text to uppercase letters.

• Formula=UPPER(text)

• Application:

➢ Standardizing text formatting for consistency.

➢ Preparing data for case-insensitive comparisons.
➢ Enhancing readability in reports and documents.

Example: Suppose we have a dataset of city names in column A with mixed cases, and we want to
convert them all to uppercase.

City

Mumbai

Delhi

chennai

Hyderabad
City

KOLKATA

Bengaluru

Formula in cell B2 for converting to uppercase=UPPER(A2)

This formula converts the text in A2 to uppercase. Copy the formula to other cells to apply to the entire
dataset.

LOWER Function:
• The LOWER function in Excel converts text to lowercase letters.

• Formula=LOWER (text)

• Application:

➢ Standardizing text formatting for consistency.

➢ Preparing data for case-insensitive comparisons.
➢ Enhancing readability in reports and documents.

Example: Continuing with the previous example, let's use the LOWER function to convert city names
to lowercase.

Formula in cell C2 for converting to lowercase =LOWER(A2)

This formula converts the text in A2 to lowercase. Copy the formula to other cells to apply to the entire
dataset.

PROPER Function:
• The PROPER function in Excel capitalizes the first letter of each word in a text string.

• Formula=PROPER(text)

• Application:

➢ Correcting capitalization errors in text data.

➢ Formatting names and titles properly.
➢ Improving presentation of text in reports and documents.

Example: Let's use the PROPER function to capitalize the first letter of each word in a dataset of names.

Name

rAjesh kumar

sunita singh

mOhan lal

pOoja gupta

kiran kumar jha

Name

rohan sharma

Formula in cell B2 for proper capitalization =PROPER(A2)

This formula capitalizes the first letter of each word in the text in A2. Copy the formula to other cells
to apply to the entire dataset.

LEFT Function:
• The LEFT function in Excel extracts a specified number of characters from the
beginning (left) of a text string.

• Formula=LEFT(text, num_chars)

• Application:

➢ Extracting prefixes or codes from text data.

➢ Separating first names from full names.
➢ Parsing fixed-length data such as phone numbers or IDs.

Example: Suppose we have a dataset of mobile numbers in column A, and we want to extract the first
few digits to identify the telecom operator.

Mobile Number

9876543210

8765432109

7979797979

9898989898

9090909090

8888888888

Formula in cell B2 for extracting the operator code =LEFT(A2, 4)

This formula extracts the first four digits (operator code) from the mobile number in A2. Copy the
formula to other cells to apply to the entire dataset.

MID Function:
• The MID function in Excel extracts a specified number of characters from the middle
of a text string, starting at a specified position.

• Formula=MID(text, start_num, num_chars)

• Application:

➢ Extracting specific portions of text based on position and length.

➢ Parsing data with variable lengths and structures.
 ➢ Working with structured data like codes or serial numbers.

Example: Continuing with the previous example, let's use the MID function to extract the middle
portion of the mobile number.

Formula in cell C2** for extracting the middle portion=MID(A2, 4, 3)

This formula starts at the fourth position of the mobile number in A2 and extracts three digits. Copy
the formula to other cells to apply to the entire dataset.

RIGHT Function:
• The RIGHT function in Excel extracts a specified number of characters from the end
(right) of a text string.

• Formula=RIGHT(text, num_chars)

• Application:

➢ Extracting suffixes or codes from text data.

➢ Working with file extensions or codes at the end of strings.
➢ Parsing data with fixed endings or patterns.

Example: Let's use the RIGHT function to extract the last four digits (PIN code) from a dataset of
addresses.

Address

ABC Street, 123456

XYZ Road, 987654

PQR Avenue, 456789

LMN Lane, 321098

RST Nagar, 789012

GHI Colony, 654321

Formula in cell B2 for extracting the PIN code=RIGHT(A2, 6)

This formula extracts the last six digits (PIN code) from the address in A2. Copy the formula to other
cells to apply to the entire dataset.

TRIM Function:
• The TRIM function in Excel removes leading and trailing spaces from a text string, and
it also removes extra spaces between words, leaving only a single space between
words.

• Formula=TRIM(text)

• Application:

➢ Cleaning up text data imported from external sources.

 ➢ Preparing data for analysis and comparison.
➢ Ensuring data consistency in text-based operations.

Example: Suppose we have a dataset of employee names with extra spaces in some cells, and we want
to clean up the names using the TRIM function.

Employee Name

John Doe

Jane Smith

Alex Brown

Emily Johnson

Michael Thomas

Sarah Williams

Formula in cell B2 for cleaning up names =TRIM(A2)

This formula removes extra spaces from the name in A2. Copy the formula to other cells to apply to
the entire dataset.

LEN Function:
• The LEN function in Excel returns the number of characters in a text string, including
spaces.

• Formula=LEN(text)

• Application:

➢ Checking the length of text strings for validation purposes.

➢ Extracting substrings based on character position.
➢ Analyzing data patterns and structures.

Example: Continuing with the previous example, let's use the LEN function to check the length of
employee names.

Formula in cell C2 for calculating name length=LEN(B2)

This formula calculates the number of characters in the cleaned name in B2. Copy the formula to other
cells to apply to the entire dataset.

EXACT Function:
• The EXACT function in Excel compares two text strings and returns TRUE if they are
exactly the same (including case sensitivity), otherwise it returns FALSE.

• Formula=EXACT(text1, text2)
 • Application:

➢ Comparing text strings for accuracy and consistency.

➢ Validating data entries against reference values.
➢ Ensuring data integrity in text-based operations.

Example: Let's compare cleaned employee names to a reference list to check for exact matches.

Employee Name Reference Name

John Doe John Doe

Jane Smith Jane Smith

Alex Brown Alex Brown

Emily Johnson Emma Johnson

Michael Thomas Michael Thomas

Sarah Williams Sarah Williams

Formula in cell D2 for exact comparison =EXACT(B2, D2)

This formula compares the cleaned name in B2 with the reference name in D2. It returns TRUE if they
are exactly the same and FALSE otherwise. Copy the formula to other cells to apply to the entire
dataset.

CONCATENATE Function:
• The CONCATENATE function in Excel joins multiple text strings into a single text string.
It can combine text from different cells or add additional text as needed.

• Formula=CONCATENATE(text1, [text2], ...)

• Application:

➢ Combining first name and last name into a full name.

➢ Creating customized messages, email addresses, or file paths.
➢ Concatenating data from multiple columns into a single cell.

Example: Suppose we have a dataset of students' first names and last names in separate columns, and
we want to create full names using the CONCATENATE function.

First Name Last Name

Rahul Sharma

Priya Patel

Rohit Singh
First Name Last Name

Anjali Gupta

Karan Verma

Nisha Reddy

Formula in cell C2 for creating full names=CONCATENATE (A2, " ", B2)

This formula concatenates the first name in A2, adds a space (" "), and then concatenates the last name
in B2. Copy the formula to other cells to apply to the entire dataset.

The result will be full names like "Rahul Sharma", "Priya Patel", "Rohit Singh", and so on in column C.

FIND Function:
• The FIND function in Excel is used to find the starting position of a specific character
or substring within a text string. It returns the position of the first occurrence of the
substring.

• Formula=FIND(find_text, within_text, [start_num])

• Application:

➢ Extracting specific parts of a text string based on their position.

➢ Validating data format or structure within a cell.
➢ Performing text manipulation based on positional information.

Example: Suppose we have a dataset of mobile numbers in column A, and we want to find the position
of the first hyphen ("-") in each number to extract area codes.

Mobile Number

987-654-3210

876-543-2109

797-979-7979

989-898-9898

909-090-9090

888-888-8888

Formula in cell B2** for finding the hyphen position=FIND("-", A2)

This formula finds the position of the first hyphen in the mobile number in A2. Copy the formula to
other cells to apply to the entire dataset.

SUBSTITUTE Function:
• The SUBSTITUTE function in Excel replaces occurrences of a specified substring within
a text string with another substring.
 • Formula=SUBSTITUTE (text, old_text, new_text, [instance_num])

• Application:

➢ Replacing specific characters or substrings with desired values.

➢ Cleaning up data by removing or modifying unwanted characters.
➢ Standardizing data format across a dataset.

Example: we have a dataset of addresses, but some addresses contain incorrect postal codes (PIN
codes) that need to be corrected.

Address

123, ABC Street, 560001

XYZ Road, 560010

4567, PQR Avenue, 561234

LMN Lane, 560102

890, RST Nagar, 560005

GHI Colony, 560080

We want to correct the postal codes that are mistakenly entered and replace them with the correct
codes "560001" for all addresses.

Solution: We can use the SUBSTITUTE function to correct the postal codes in the addresses.

1. Apply SUBSTITUTE Function: We will use the SUBSTITUTE function to replace the incorrect
postal codes with the correct code "560001".

Formula in cell B2 for correcting postal codes =SUBSTITUTE(A2, "560010", "560001")

This formula replaces "560010" with "560001" in the address in cell A2. Copy the formula down to
apply it to the entire dataset.

After applying the formula, the corrected addresses in column B will be:

Corrected Address

123, ABC Street, 560001

XYZ Road, 560001

4567, PQR Avenue, 561234

LMN Lane, 560001

890, RST Nagar, 560005

GHI Colony, 560080

TODAY Function:
• The TODAY function in Excel returns the current date (today's date) as a serial number.

• Formula =TODAY()

• Application:

➢ Automatically updating dates in spreadsheets.

➢ Calculating durations or deadlines based on current dates.
➢ Tracking time-sensitive tasks or events.

Example: Suppose you are managing a project with deadlines, and you want to calculate the number
of days left until the deadline.

Task Deadline Days Left

Task A 2024-03-31 =Deadline cell - TODAY()

Formula in cell C2 for calculating days left =B2 - TODAY()

This formula calculates the number of days left until the deadline date in cell B2 based on today's date.

NOW Function:
• The NOW function in Excel returns the current date and time as a serial number.

• Formula =NOW()

• Application:

➢ Tracking real-time updates in spreadsheets.

➢ Timestamping entries or transactions.
➢ Calculating durations with time precision.

Example: You are maintaining a log for customer inquiries, and you want to timestamp each entry
automatically.

Inquiry Timestamp

Inquiry 1 =NOW()

Inquiry 2 =NOW()

Inquiry 3 =NOW()

Formula in cell B2 for timestamping=NOW()

This formula automatically inserts the current date and time when a new inquiry is logged.

DAY, MONTH, YEAR Functions:

• These functions extract the day, month, and year from a given date, respectively.

• Formulas=DAY(date) =MONTH(date) =YEAR(date)

• Application:

➢ Analyzing date components separately.

 ➢ Grouping data by day, month, or year for analysis.
➢ Creating customized date formats.

Example: Let's extract the day, month, and year from a given date (e.g., "2024-03-17").

Formulas in respective cells for extracting components

=DAY("2024-03-17") // Returns 17 =MONTH("2024-03-17") // Returns 3 =YEAR("2024-03-17") //

Returns 2024

These functions are helpful when you need to perform operations or analysis based on specific date
components.

DATE Function:
• The DATE function in Excel creates a date based on the provided year, month, and day
values.

• Formula=DATE(year, month, day)

• Application:

➢ Creating date values dynamically based on other data.

➢ Performing date arithmetic and calculations.

Example: Suppose you want to create a date value for India's Independence Day: 15th August 1947.

Formula for creating the date=DATE(1947, 8, 15) // Returns the date 15th August 1947

DATEIF Function:
• The DATEDIF function in Excel calculates the difference between two dates in various
units (days, months, years).

• Formula=DATEDIF(start_date, end_date, unit)

• Application:

➢ Calculating age based on birthdates.

➢ Finding durations between project milestones.
➢ Tracking time intervals for specific events.

Example: Let's calculate the age based on a person's birthdate.

Name Birthdate Age

Person A 1990-05-15 =DATEDIF (B2, TODAY (), "Y")

Formula in cell C2 for calculating age =DATEDIF(B2, TODAY(), "Y")

This formula calculates the age of the person in cell B2 based on today's date.

DATEADD Function:
• The DATEADD function adds or subtracts a specified number of days, months, or years
to a given date.

• Formula =DATEADD(start_date, number, "unit")

 • Application:

➢ Projecting future dates based on current data.

➢ Adjusting dates by a certain period for planning purposes.
➢ Calculating due dates or deadlines.

Example: Suppose you want to calculate the date 30 days from today.

Formula for calculating future date=DATEADD(TODAY(), 30, "D") // Returns the date 30 days from
today

EOMONTH Function:
• The EOMONTH function in Excel returns the last day of the month, a specified number
of months before or after a given date.

• Formula=EOMONTH(start_date, months)

• Application:

➢ Generating end-of-month dates for financial reporting.

➢ Scheduling payments or invoices based on month ends.
➢ Planning activities around month boundaries.

Example: Suppose you want to find the end of the month for a given date (e.g., "2024-03-17").

Formula for finding end of the month =EOMONTH("2024-03-17", 0) // Returns the date 31st March
2024

WEEKDAY Function:
• The WEEKDAY function in Excel returns the day of the week as a number (1 for Sunday,
2 for Monday, and so on).

• Formula =WEEKDAY(date, [return_type])

• Application:

➢ Organizing and analyzing data based on weekdays.

➢ Creating schedules or calendars.
➢ Automating tasks based on specific days of the week.

Example:
WEEKDAY Function with Return Type 2: When using return type 2 with the WEEKDAY function, the
week starts on Monday (where Monday is considered as the first day of the week, represented by 1),
and Sunday is considered as the last day of the week (represented by 7).
Formula =WEEKDAY(date, 2)
Example: Let's determine the day of the week for the Indian Republic Day, which is on January 26th,
2024.
Formula for finding the day of the week with return type 2:
=WEEKDAY("2024-01-26", 2)
The formula above calculates the day of the week for January 26th, 2024, with return type 2. Here's
how the days are represented:
• Monday: 1
• Tuesday: 2
• Wednesday: 3
• Thursday: 4
• Friday: 5
• Saturday: 6
• Sunday: 7
The returned value for Republic Day (January 26, 2024) with return type 2 would be 6, indicating
that it falls on a Saturday according to this numbering convention.