Excel Formulas: VLOOKUP, XIRR & How to Use Excel – Complete Guide

Excel Formulas, VLOOKUP, XIRR & How to Use Excel: Complete Beginner Guide

Assalam u Alaikum friends! Welcome to our blog. In today's complete guide, we are going to learn about Excel formulas, how to use Excel, Microsoft Excel, spreadsheets, calculations, VLOOKUP, XIRR, IF functions, SUMIF, COUNTIF, Pivot Tables, dashboards, invoices, attendance sheets, percentage calculations, and many other useful Excel features.

Whether you are a complete beginner or already have some experience with Excel, this guide will help you understand Excel step by step with simple examples. We will cover basic Excel formulas, advanced Excel formulas, and practical ways to use Excel for office work, business, accounting, data entry, reports, invoices, and everyday calculations.

Quick Start: You can also explore our related guides about Excel, data entry, spreadsheets, and other useful computer tools.

How to Use Excel

Microsoft Excel is a spreadsheet application used to organize, calculate, analyze, and present information. If you are wondering how do you use Excel or how do I use Excel, the first thing to understand is the Excel worksheet.

An Excel file is called a workbook. A workbook can contain multiple worksheets. Each worksheet is made up of rows and columns. The intersection of a row and column is called a cell.

For example, the cell where column A and row 1 meet is called A1. Similarly, B5 means column B and row 5.

You can enter text, numbers, dates, or formulas into cells. Excel then allows you to perform calculations and analyze your information.

Basic Excel Workflow

1. Open Microsoft Excel.
2. Create a blank workbook.
3. Enter your data.
4. Format the data.
5. Add formulas.
6. Check the results.
7. Create charts or reports if required.
8. Save the workbook.

Microsoft Excel worksheet showing the basic rows, columns, cells, and spreadsheet interface.

Understanding Excel Formulas

An Excel formula is an instruction that tells Excel to perform a calculation. Most formulas start with an equal sign =.

For example:

=10+20

Excel will calculate the result as 30.

Instead of entering numbers directly, you can use cell references.

=A1+B1

If A1 contains 10 and B1 contains 20, the result will be 30.

This is one of the most important concepts when learning how to make formulas in Excel.

Basic Excel Formulas

There are several basic Excel formulas that beginners should learn first. These include addition, subtraction, multiplication, division, SUM, AVERAGE, COUNT, and percentage calculations.

Learning these formulas gives you a strong foundation before moving toward advanced Excel functions.

Excel Addition Formula

The simplest Excel addition formula uses the plus sign.

=A1+B1

If A1 contains 100 and B1 contains 50, the result is 150.

You can also add multiple cells:

=A1+B1+C1+D1

Another option is to use the SUM function:

=SUM(A1:D1)

This is usually more convenient when you have many values.

Excel Formula for Subtraction

To subtract numbers in Excel, use the minus sign.

=A1-B1

If A1 contains 500 and B1 contains 150, the result is 350.

This is the basic Excel formula for minus and can be used for many calculations.

=A1-B1-C1

Subtraction formulas are useful for calculating expenses, balances, discounts, remaining stock, and other business figures.

Excel Formula for Multiplication

The multiplication operator in Excel is the asterisk *.

=A1*B1

This is the basic Excel formula for multiplication.

If A1 contains 10 and B1 contains 5, the result is 50.

You may also see the Excel * in formula when calculating prices and quantities.

=B2*C2

This formula can automatically calculate the total price when B2 contains quantity and C2 contains price.

Excel Division Formula

Excel uses the forward slash / for division.

=A1/B1

If A1 contains 100 and B1 contains 4, the result will be 25.

Division formulas are useful for calculating averages, rates, unit prices, percentages, and other calculations.

Excel SUM Formula

The Excel SUM formula is one of the most frequently used Excel functions.

=SUM(A1:A10)

This adds all numbers from A1 through A10.

You can also use:

=SUM(B2:B20)

The SUM function is useful when calculating sales totals, expenses, salaries, marks, inventory, and invoices.

Terms such as Excel summation formula, Excel total formula, formula in Excel SUM, and sum in Excel generally refer to using SUM to add values.

Average Excel Formula

The AVERAGE function calculates the average of selected numbers.

=AVERAGE(A1:A10)

The average Excel formula is useful for calculating average marks, sales, expenses, employee performance, and many other types of data.

Excel Percentage Formula

Percentage calculations are common in Excel.

Suppose A1 contains total marks and B1 contains obtained marks. You can calculate percentage with:

=B1/A1*100

For example, if total marks are 500 and obtained marks are 425:

=425/500*100

The result is 85%.

This Excel percentage formula can also be used for discounts, profit margins, commission, growth rates, and performance reports.

COUNT and COUNTA Excel Functions

The COUNT function counts cells containing numbers.

=COUNT(A1:A20)

The COUNT Excel formula is useful when you want to know how many numerical values are present in a range.

The COUNT function is different from COUNTA:

=COUNTA(A1:A20)

The COUNTA formula in Excel counts cells that are not empty. It can count text, numbers, dates, and other values.

Excel IF Formula

The IF function allows Excel to make a decision based on a condition.

=IF(A1>=50,"Pass","Fail")

If A1 is 50 or higher, Excel returns "Pass". Otherwise, it returns "Fail".

The Excel IF formula is useful for attendance, grades, sales targets, employee performance, payment status, and many other tasks.

IF AND Formula in Excel

The AND function allows you to test multiple conditions.

=IF(AND(A1>=50,B1>=50),"Pass","Fail")

This formula checks whether both conditions are true.

The IF AND formula in Excel is useful when multiple requirements must be satisfied.

IFS Excel Formula

The IFS function is useful when you have multiple conditions.

=IFS(A1>=90,"Excellent",A1>=75,"Good",A1>=50,"Average",A1<50,"Fail")

This allows you to create several conditions without using many nested IF functions.

The IFS Excel formula is particularly useful for grading systems, performance categories, and business reports.

SUMIF Function in Excel

The SUMIF function adds values based on a condition.

=SUMIF(A2:A20,"Laptop",B2:B20)

This adds only the sales amounts where the product is Laptop.

The SUMIF function in Excel is useful for sales reports, expense analysis, inventory, and departmental calculations.

COUNTIF Excel Formula

COUNTIF counts cells that meet a specific condition.

=COUNTIF(A2:A20,"Completed")

This counts how many cells contain the word "Completed".

The COUNTIF function can be used for attendance records, task tracking, sales targets, status reports, and surveys.

VLOOKUP Formula in Excel

One of the most famous Excel functions is VLOOKUP.

The VLOOKUP formula in Excel is used to search for a value in the first column of a table and return related information from another column.

Basic syntax:

=VLOOKUP(lookup_value,table_array,col_index_num,FALSE)

Product ID	Product	Price
101	Laptop	800
102	Keyboard	40
103	Mouse	20

If you want to find the price of Product ID 102, you can use:

=VLOOKUP(102,A2:C4,3,FALSE)

Excel searches for 102 in the first column and returns the corresponding price from column 3.

This is a simple VLOOKUP example.

The V lookup formula is extremely useful in data entry, employee records, product lists, customer databases, and business reports.

A practical VLOOKUP example in Excel showing how a value can be searched and returned from a table.

VLOOKUP Excel Practical Example

Suppose an employee database contains the following information:

ID	Employee	Department	Salary
1001	Ali	Sales	50000
1002	Ahmed	HR	60000
1003	Usman	IT	75000

If you enter Employee ID 1003 in E2 and want to retrieve the employee's department, use:

=VLOOKUP(E2,A2:D4,3,FALSE)

The formula returns "IT".

For salary:

=VLOOKUP(E2,A2:D4,4,FALSE)

This is why VLOOKUP Excel is widely used in professional spreadsheets.

INDEX and MATCH in Excel

INDEX and MATCH are powerful alternatives to VLOOKUP.

The INDEX function returns a value from a specific position, while MATCH finds the position of a value.

=INDEX(C2:C10,MATCH(E2,A2:A10,0))

This combination is commonly called INDEX MATCH Excel.

It can be more flexible than traditional VLOOKUP because the lookup column does not always need to be the first column.

The Excel INDEX function is useful when working with larger and more flexible data tables.

Excel CONCAT and CONCATENATE

Sometimes you need to combine text from multiple cells.

The CONCAT function can be used for this purpose.

=CONCAT(A2," ",B2)

If A2 contains "Ali" and B2 contains "Khan", the result becomes "Ali Khan".

The CONCAT Excel function is useful for combining names, addresses, product codes, or other text.

Older versions of Excel also use the CONCATENATE function:

=CONCATENATE(A2," ",B2)

Therefore, CONCAT formula in Excel, CONCAT function in Excel, and CONCATENATE formula are commonly used for joining text.

XIRR Formula in Excel

XIRR is an advanced financial function.

The XIRR formula calculates the internal rate of return for cash flows that occur on different dates.

For example, if you have investments and returns occurring on different dates, XIRR can calculate an annualized rate of return.

Basic syntax:

=XIRR(values,dates)

If your cash flows are in B2:B6 and dates are in A2:A6:

=XIRR(B2:B6,A2:A6)

XIRR is useful for investment analysis, financial planning, business projects, and irregular cash-flow calculations.

IRR Formula in Excel

The IRR function calculates the internal rate of return for a series of regular cash flows.

=IRR(B2:B6)

The IRR formula Excel users choose depends on whether their cash flows happen regularly or on specific dates.

IRR generally assumes regular intervals, while XIRR works with actual dates.

CAGR Formula in Excel

CAGR means Compound Annual Growth Rate.

A common CAGR calculation is:

=(Ending Value/Beginning Value)^(1/Years)-1

For example:

=(150000/100000)^(1/5)-1

You can format the result as a percentage.

The CAGR calculation Excel users perform is useful for analyzing business growth, investments, revenue, website traffic, and other measurements over time.

You may also see searches such as annual growth rate formula Excel or CAGR formula Excel.

Compound Interest Excel Formula

Compound interest means that interest is added to the principal and future interest is calculated on the increased amount.

=P*(1+r/n)^(n*t)

Where:

• P = Principal
• r = Annual interest rate
• n = Number of compounding periods per year
• t = Number of years

The compound interest Excel formula is useful for financial calculations and planning.

Age Calculation Formula in Excel

Excel can also calculate someone's age using their date of birth.

=DATEDIF(A2,TODAY(),"Y")

If A2 contains a date of birth, this formula returns the completed years.

The age calculation in Excel can be useful for employee records, registration forms, customer databases, and membership lists.

Searches such as age calculation formula in Excel, age Excel formula, and calculate days in Excel are common because date calculations are an important part of spreadsheet work.

Ageing Formula in Excel

Ageing calculations are often used for invoices and accounts receivable.

You can calculate the number of days an invoice has been outstanding with:

=TODAY()-A2

If A2 contains the invoice date, Excel returns the number of days since that date.

An ageing report can then categorize invoices as:

• 0–30 days
• 31–60 days
• 61–90 days
• 90+ days

Both ageing formula in Excel and aging formula in Excel can be used to create useful business reports.

Conditional Formatting in Excel

Conditional formatting Excel features allow you to automatically change the appearance of cells based on their values.

For example, you can highlight:

• Sales above a target
• Low inventory
• Failed marks
• Overdue invoices
• Duplicate values
• High expenses

Instead of manually formatting each cell, Excel applies the formatting automatically when the condition is met.

Conditional formatting in Excel automatically highlights important values in a spreadsheet.

How to Make an Invoice in Excel

Excel is useful for creating simple professional invoices.

A basic invoice can contain:

• Business name
• Invoice number
• Customer name
• Date
• Product description
• Quantity
• Unit price
• Total
• Tax
• Discount
• Grand total

If quantity is in B10 and unit price is in C10:

=B10*C10

You can calculate the subtotal with:

=SUM(D10:D20)

If tax is 5%:

=Subtotal*5%

Then add the tax to the subtotal to calculate the grand total.

A professional invoice created in Microsoft Excel using formulas for automatic calculations.

How to Make a Bill in Excel

Creating a bill in Excel is similar to creating an invoice.

Item	Quantity	Price	Total
Keyboard	2	40	80
Mouse	3	20	60

For the total column, use:

=B2*C2

At the bottom:

=SUM(D2:D10)

This automatically calculates the bill.

You can then add discounts, taxes, payment status, and customer information.

Therefore, Excel can be used for how to make a bill in Excel as well as small-business invoicing.

Monthly Attendance Sheet in Excel

Excel is also excellent for creating employee or student attendance sheets.

You can create columns for:

• Employee Name
• Date
• Present
• Absent
• Leave
• Late
• Total Days

For example, COUNTIF can count attendance:

=COUNTIF(B2:AF2,"P")

This counts the number of cells marked P for Present.

Similarly:

=COUNTIF(B2:AF2,"A")

counts absences.

This is a simple attendance sheet in Excel with formula.

You can use conditional formatting to highlight absent or late days.

A monthly attendance sheet in Excel using formulas to automatically calculate attendance totals.

How to Make a Dashboard in Excel

An Excel dashboard turns raw data into an easy-to-understand visual report.

A dashboard can contain:

• Total Sales
• Total Customers
• Monthly Revenue
• Expenses
• Profit
• Top Products
• Charts
• KPIs

You can use Pivot Tables, charts, formulas, and slicers to build a dashboard.

Steps to Create an Excel Dashboard

1. Prepare clean data.
2. Convert the data into a table.
3. Create Pivot Tables.
4. Create charts.
5. Add important calculations.
6. Arrange everything on one dashboard sheet.
7. Add filters or slicers.

Learning how to make a dashboard in Excel is especially useful for office professionals, analysts, managers, and business owners.

A professional Excel dashboard combining formulas, charts, KPIs and business data in one report.

Pivot Table in Excel

A Pivot Table is one of Excel's most powerful tools for analyzing large amounts of data.

Suppose you have thousands of sales records containing:

• Date
• Product
• Region
• Salesperson
• Quantity
• Revenue

Instead of manually calculating every total, a Pivot Table can summarize the information quickly.

You can create reports such as:

• Sales by product
• Sales by region
• Sales by employee
• Monthly revenue
• Average sales
• Total quantity

Although people sometimes search for Pivot Table formula or Pivot Table in Excel formula, Pivot Tables are primarily a data-analysis feature rather than a single formula.

An Excel Pivot Table summarizing large sales data into an easy-to-read report.

Spreadsheet Formulas

A spreadsheet becomes much more powerful when formulas are used correctly.

Common spreadsheet formulas include:

=SUM(A1:A10)
=AVERAGE(A1:A10)
=COUNT(A1:A10)
=MAX(A1:A10)
=MIN(A1:A10)
=IF(A1>50,"Yes","No")
=SUMIF(A1:A10,"Sales",B1:B10)

These formulas can be combined to create powerful calculations.

The same general concepts are also available in Google Sheets, so many Google spreadsheet formulas work similarly.

Excel Functions vs Excel Formulas

A formula is an expression used to calculate something.

A function is a built-in Excel operation such as SUM, IF, VLOOKUP, COUNTIF, or AVERAGE.

For example:

=A1+B1

is a formula.

=SUM(A1:B1)

uses the SUM function inside a formula.

Understanding Excel formulas and functions makes it much easier to build reliable spreadsheets.

Useful Excel Formulas

Purpose	Formula
Addition	=A1+B1
Subtraction	=A1-B1
Multiplication	=A1*B1
Division	=A1/B1
Sum	=SUM(A1:A10)
Average	=AVERAGE(A1:A10)
Count	=COUNT(A1:A10)
Count non-empty	=COUNTA(A1:A10)
Condition	=IF(A1>50,"Yes","No")
Conditional sum	=SUMIF(A1:A10,"Yes",B1:B10)
Conditional count	=COUNTIF(A1:A10,"Yes")
Lookup	=VLOOKUP(E2,A2:D20,4,FALSE)
Age	=DATEDIF(A2,TODAY(),"Y")
XIRR	=XIRR(B2:B10,A2:A10)
IRR	=IRR(B2:B10)
CAGR	=(B2/A2)^(1/C2)-1

This table provides a practical Excel formulas list with examples.

Advanced Excel Formulas

Once you understand basic formulas, you can move to advanced Excel functions.

Some useful advanced functions include:

• VLOOKUP
• INDEX
• MATCH
• XIRR
• IRR
• SUMIF
• COUNTIF
• IFS
• AND
• CONCAT
• IFERROR
• SUMIFS
• COUNTIFS
• XLOOKUP
• FILTER
• UNIQUE

The best way to learn advanced Excel formulas is not to memorize everything at once. Instead, learn formulas according to the type of work you perform.

How to Use Excel Formulas Correctly

When learning how to use Excel formulas, focus on understanding cell references.

For example:

=A2*B2

If you copy this formula down, Excel automatically changes it to:

=A3*B3
=A4*B4

This is called a relative reference.

You can also use absolute references with $.

=$A$1*B2

Here, A1 remains fixed when the formula is copied.

Understanding relative and absolute references is extremely important for professional spreadsheet work.

Excel Calculator

Excel can also work as a powerful Excel calculator.

=100+200
=500-125
=25*4
=1000/5
=15%*5000

You can also combine calculations:

=(500+200)*10%

This makes Excel useful for quick calculations as well as large datasets.

How to Operate Excel

If you are completely new, learning how to operate Excel becomes easier when you focus on a few basic areas.

First, learn how to enter data.

Then learn how to select cells, copy and paste, format numbers, resize columns, insert rows, create formulas, sort data, filter data, and save files.

After that, learn functions such as SUM, AVERAGE, IF, COUNTIF, SUMIF, and VLOOKUP.

Once you are comfortable with these features, you can start learning Pivot Tables, charts, dashboards, and advanced formulas.

Microsoft Excel Formulas

Microsoft Excel formulas can be used in almost every type of office work.

Sales

=Quantity*Price

Profit

=Sales-Cost

Percentage

=Profit/Sales*100

Attendance

=COUNTIF(B2:AF2,"P")

Average

=AVERAGE(B2:B20)

Lookup

=VLOOKUP(E2,A2:D20,4,FALSE)

The important thing is to understand the logic behind each formula rather than simply copying formulas without understanding them.

Google Sheets and Excel Formulas

Google Sheets offers many functions similar to Microsoft Excel.

For example:

=SUM(A1:A10)
=AVERAGE(A1:A10)
=IF(A1>50,"Pass","Fail")
=COUNTIF(A1:A20,"Completed")

Many formulas for Google Sheets are therefore familiar to Excel users.

However, some features and functions can differ between the two spreadsheet applications, so always check the specific function requirements when moving a workbook between platforms.

Excel Formula Errors

Sometimes Excel formulas return errors.

#DIV/0!

This usually happens when a formula attempts to divide by zero or an empty cell.

#N/A

This often occurs when a lookup cannot find the requested value.

#VALUE!

This can happen when the formula contains incompatible data types.

#REF!

This indicates that a formula contains an invalid cell reference.

#NAME?

This can occur when Excel does not recognize a function or name.

Learning to understand these errors is an important part of becoming good at Excel.

Tips for Learning Excel

If you want to become good at Excel, don't try to memorize an all Excel formulas list in one day.

Instead, learn in stages.

Start with addition, subtraction, multiplication, division, SUM, and AVERAGE.

Then learn IF, COUNTIF, SUMIF, and conditional formatting.

After that, learn VLOOKUP, INDEX MATCH, Pivot Tables, charts, dashboards, and financial formulas such as IRR and XIRR.

Most importantly, practice formulas with real-world examples.

Create an invoice.

Create an attendance sheet.

Create a sales report.

Create a monthly budget.

Create a dashboard.

This practical approach will help you understand how to use Excel sheet much faster than simply reading formula definitions.

Excel Formulas Cheat Sheet

Here is a quick list of important formulas covered in this guide:

• Addition: =A1+B1
• Subtraction: =A1-B1
• Multiplication: =A1*B1
• Division: =A1/B1
• SUM: =SUM(A1:A10)
• AVERAGE: =AVERAGE(A1:A10)
• COUNT: =COUNT(A1:A10)
• COUNTA: =COUNTA(A1:A10)
• IF: =IF(A1>=50,"Pass","Fail")
• AND: =AND(A1>50,B1>50)
• IFS: =IFS(A1>=90,"A",A1>=80,"B",A1>=70,"C")
• COUNTIF: =COUNTIF(A1:A20,"Yes")
• SUMIF: =SUMIF(A1:A20,"Sales",B1:B20)
• VLOOKUP: =VLOOKUP(E2,A2:D20,4,FALSE)
• INDEX MATCH: =INDEX(C2:C20,MATCH(E2,A2:A20,0))
• CONCAT: =CONCAT(A2," ",B2)
• Age: =DATEDIF(A2,TODAY(),"Y")
• XIRR: =XIRR(B2:B10,A2:A10)
• IRR: =IRR(B2:B10)
• CAGR: =(Ending/Beginning)^(1/Years)-1

All Excel Formulas with Examples in Excel Sheet Download

If you are creating an Excel learning resource, it can be helpful to prepare a separate workbook containing formulas and examples.

A useful workbook could contain different sheets such as:

1. Basic Formulas
2. Logical Functions
3. Lookup Functions
4. Text Functions
5. Date Functions
6. Financial Functions
7. Attendance Examples
8. Invoice Example
9. Sales Report
10. Dashboard Example

This makes it easier for beginners to practice each formula with real data.

Instead of trying to learn all Excel formulas with examples in Excel sheet download at once, learners should use a structured workbook and practice one category at a time.

Frequently Asked Questions About Excel

What are the basic Excel formulas?

The most useful basic Excel formulas include SUM, AVERAGE, COUNT, addition, subtraction, multiplication, division, IF, and percentage calculations.

How do I use Excel formulas?

Start a formula by typing = in a cell, then enter numbers, cell references, operators, or functions. For example, =A1+B1 adds the values in A1 and B1.

What is the VLOOKUP formula in Excel?

VLOOKUP searches for a value in the first column of a table and returns related information from another column.

What is XIRR in Excel?

XIRR is a financial function used to calculate an annualized return when cash flows occur on different dates.

What is SUMIF used for?

SUMIF adds values that meet a specific condition. It is useful for sales reports, expenses, inventory, and other conditional calculations.

Can Excel calculate age?

Yes. A formula such as =DATEDIF(A2,TODAY(),"Y") can calculate completed years from a date of birth.

Can I make invoices in Excel?

Yes. Excel can be used to create invoices and bills with product names, quantities, prices, taxes, discounts, totals, and automatic calculations.

Final Thoughts

Microsoft Excel is much more than a simple calculator. It can be used to organize information, perform calculations, analyze business data, create invoices, track attendance, build reports, prepare dashboards, and make financial calculations.

If you are a beginner, start with basic Excel formulas such as SUM, AVERAGE, addition, subtraction, multiplication, and percentage calculations.

Then move toward IF, AND, IFS, COUNTIF, SUMIF, and conditional formatting.

Once you understand these functions, learn VLOOKUP formula in Excel, INDEX MATCH, Pivot Tables, dashboards, XIRR, IRR, CAGR, and other advanced functions.

The key to mastering Excel is practice. Don't just memorize formulas. Create small projects and use formulas to solve real problems.

Whether your goal is office work, data entry, accounting, business reporting, freelancing, or simply becoming more comfortable with spreadsheets, learning how to use Excel can be a very useful skill.

Start with the basics, practice every day, and gradually move toward advanced Excel formulas and functions.