Microsoft Excel Easy Notes for Beginners to Advanced Level with Example?

1. What Is Microsoft Excel?

Microsoft Excel is a spreadsheet application used to store, organize, calculate, analyze, and visualize data. It is widely used in education, accounting, business reporting, administration, and data analysis.

Applications of Excel

• Student marksheets and attendance management.
• Salary, invoices, budgets, and financial calculations.
• Sales reports and inventory management.
• Data cleaning, sorting, filtering, and analysis.
• MIS reports, charts, dashboards, and business summaries.

Learning path: Excel Basics → Formulas → Functions → Data Analysis → PivotTables → Dashboards → Power Query → Advanced Automation.

2. Basic Excel Terms

3. Data Entry and Formatting

Click a cell, enter text, numbers, dates, or formulas, and press Enter. Use the Home tab to change font, alignment, number formats, borders, and cell colors.

Create an Excel Table

1. Select your data including the heading row.
2. Press Ctrl + T.
3. Confirm the table range and check My table has headers if applicable.
4. Click OK.

Tables make sorting, filtering, and expanding data easier. Use consistent headings and avoid blank rows within datasets.

4. Basic Excel Formulas and Operators

Excel formulas begin with an equals sign (=).

Practical Example: Calculate Total Amount

If A2 contains Quantity and B2 contains Rate, enter this formula in C2:

=A2*B2

For quantity 5 and rate 200, the total is 1,000.

Percentage Calculation

=B2/C2*100

If B2 contains marks obtained and C2 contains maximum marks, this calculates the percentage. Format the result as a normal number if multiplying by 100, or use =B2/C2 and apply Percentage format.

5. Important Excel Functions with Examples

Example: Total Sales

If sales amounts are stored in C2:C100:

=SUM(C2:C100)

Example: Average Sales

=AVERAGE(C2:C100)

6. Logical and Conditional Functions

IF Function

=IF(B2>=40,"Pass","Fail")

Returns Pass when B2 is at least 40; otherwise, it returns Fail.

AND Function

=IF(AND(B2>=40,C2>=40),"Pass","Fail")

Returns Pass only when both conditions are true.

OR Function

=IF(OR(B2>=80,C2>=80),"Eligible","Not Eligible")

Returns Eligible if either condition is true.

COUNTIF and SUMIF

=COUNTIF(B2:B100,">=40")
=SUMIF(A2:A100,"Laptop",C2:C100)

COUNTIF counts cells meeting a condition. SUMIF adds amounts corresponding to matching criteria.

IFERROR

=IFERROR(A2/B2,0)

Returns 0 if the calculation produces an error. Choose an appropriate alternative result for your reporting needs.

7. VLOOKUP, XLOOKUP and INDEX-MATCH

VLOOKUP

Looks for a value in the first column of a range and returns a value from another column in the same row.

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

Searches for E2 in the first column of A2:C100 and returns the corresponding value from the third column. FALSE requests an exact match.

XLOOKUP

=XLOOKUP(E2,A2:A100,C2:C100,"Not Found")

Searches A2:A100 for E2 and returns the corresponding value from C2:C100. XLOOKUP is available in Microsoft 365 and supported newer Excel versions, but not every older version.

INDEX and MATCH

=INDEX(C2:C100,MATCH(E2,A2:A100,0))

MATCH finds the position of E2 in the lookup range; INDEX returns the value at that position from column C.

8. Text and Date Functions

Note: DATEDIF expects the start date first and end date second. Ensure the dates are valid Excel dates.

9. Data Analysis Tools

Sorting and Filtering

Use Data → Sort to order records and Data → Filter to show records that match selected conditions.

Conditional Formatting

Highlight low marks, duplicate entries, top values, or unusual numbers automatically. For example, select B2:B100 and choose Home → Conditional Formatting → Highlight Cells Rules → Less Than → 40.

Data Validation

Use Data → Data Validation to control allowed entries. For example, create a dropdown containing Present, Absent, and Leave for an attendance sheet.

Remove Duplicates

Make a backup, select your dataset, then choose Data → Remove Duplicates. Select the columns used to identify duplicates before confirming.

What-If Analysis

Goal Seek can find an input value required to achieve a target formula result. Find it under Data → What-If Analysis → Goal Seek in supported desktop versions.

10. PivotTables and PivotCharts

A PivotTable summarizes a large dataset without requiring a separate formula for every group.

How to Create a PivotTable

1. Organize data into columns with unique headings.
2. Select a cell in the dataset.
3. Choose Insert → PivotTable.
4. Choose the location and click OK.
5. Drag fields into Rows, Columns, Values, and Filters.

Example: Place Product in Rows and Sales Amount in Values to summarize sales by product. Use Insert → PivotChart to visualize the summary.

11. Creating an Excel Dashboard

An Excel dashboard displays important business information in one interactive view.

Dashboard components

• KPI cards for total sales, profit, quantity, and orders.
• Column charts for product comparisons.
• Line charts for monthly sales trends.
• PivotTables for category and region summaries.
• Slicers and timelines where supported for interactive filtering.

Basic Dashboard Workflow

1. Clean and structure the source dataset.
2. Calculate key metrics.
3. Create PivotTables and charts.
4. Arrange visuals on a separate Dashboard sheet.
5. Apply consistent colors and clear labels.
6. Test filters and refresh the source data.

12. Power Query for Data Cleaning

Power Query helps import, transform, combine, and clean data through repeatable steps. It is available in modern Excel versions, though specific connectors and features vary.

Typical uses

• Import data from Excel files and CSV files.
• Remove blank rows and duplicate records.
• Split columns and change data types.
• Append similar tables or merge related tables.
• Refresh the transformation steps when new data arrives.

Basic Steps

1. Choose Data → Get Data or From Table/Range.
2. Apply transformations in Power Query Editor.
3. Choose Close & Load.
4. Use Refresh to update results when the source changes.

13. Excel with AI

AI tools can assist with formula writing, explaining errors, suggesting data-cleaning steps, and planning reports. Availability depends on the tool, account, subscription, and Excel version.

Example prompts for an AI assistant

Always test AI-generated formulas on sample data and verify the results before using them in business reports.

14. Important Excel Keyboard Shortcuts

15. Practical Project: Sales Report

Enter the following sample data into Excel.

Assuming the headings are in row 1, enter this formula in F2 and fill down:

=D2*E2

Project Tasks

1. Calculate total sales using SUM.
2. Find total sales for Laptop using SUMIF.
3. Count records for the North region using COUNTIF.
4. Create a PivotTable showing sales by product.
5. Create a column chart for product sales.
6. Build a dashboard with total sales and product comparisons.

Sample Answers

Total sales:
=SUM(F2:F6)

Laptop sales:
=SUMIF(B2:B6,"Laptop",F2:F6)

North region record count:
=COUNTIF(C2:C6,"North")

Expected total sales for this sample is 170,000.

16. Excel Practice Exercises

1. Calculate the average of marks in B2:B20.
2. Show Pass if marks are 40 or above.
3. Find the highest value in D2:D100.
4. Count orders with status Delivered.
5. Find the price for a product using XLOOKUP.
6. Summarize monthly sales using a PivotTable.
7. Import a CSV file and remove duplicates with Power Query.
8. Create a dashboard with sales, region, and product charts.

=AVERAGE(B2:B20)
=IF(B2>=40,"Pass","Fail")
=MAX(D2:D100)
=COUNTIF(E2:E100,"Delivered")
=XLOOKUP(G2,A2:A100,C2:C100,"Not Found")

17. Frequently Asked Questions