Excel Practice - Vision Connects

SUM & AVERAGE (Beginner)

Goal: Calculate total and average expenses from a list.

Steps:

Use =SUM(B2:B6) to get the total expenses.
Use =AVERAGE(B2:B6) to get the average expense.
Select the amounts, then go to Home > Conditional Formatting > Highlight Cell Rules > Greater Than... to highlight values over 150.

Sample Data:

Item	Amount
Rent	1200
Food	300
Travel	150
Supplies	80
Utilities	200

Download CSV

Tip: SUM adds all numbers in a range, while AVERAGE calculates the mean.

Solution

The formulas will produce the following results. Conditional formatting will highlight the amounts for Rent, Food, and Utilities.

Item	Amount
Rent	1200
Food	300
Travel	150
Supplies	80
Utilities	200
Total	1930
Average	386

IF Function (Beginner)

Goal: Categorize sales as 'High' or 'Low' based on a value.

Steps:

In cell C2, type =IF(B2>500, "High", "Low").
Drag the fill handle down to apply the formula to all rows.

Sample Data:

Product	Sales	Category
Laptop	800
Monitor	900
Printer	400
Speaker	700

Download CSV

Tip: The IF function checks a condition. Syntax: IF(condition, value_if_true, value_if_false).

Solution

The formula will automatically fill the 'Category' column based on the sales value.

Product	Sales	Category
Laptop	800	High
Monitor	900	High
Printer	400	Low
Speaker	700	High

AND/OR Functions (Beginner)

Goal: Flag products as 'Priority' if Sales > $400 AND Region is 'North'.

Steps:

In cell D2, use =IF(AND(B2>400, C2="North"), "Priority", "Standard").
Drag the formula down to evaluate all products.

Sample Data:

Product	Sales	Region
Book A	450	North
Book B	300	South
Book C	500	North

Download CSV

Tip: AND requires all conditions to be true. OR requires at least one to be true.

Solution

The 'Flag' column will be populated based on both the sales and region criteria.

Product	Sales	Region	Flag
Book A	450	North	Priority
Book B	300	South	Standard
Book C	500	North	Priority

Text Functions (Beginner)

Goal: Create inventory codes by combining product and category.

Steps:

In cell C2, use =UPPER(LEFT(A2,3)&"-"&B2).

Sample Data:

Product	Category	Code
Laptop	Electronics
Mouse	Accessories
Keyboard	Accessories

Download CSV

Tip: LEFT() extracts text from the start. & joins text strings. UPPER() converts to uppercase.

Solution

The formula generates a standardized code for each item.

Product	Category	Code
Laptop	Electronics	LAP-ELECTRONICS
Mouse	Accessories	MOU-ACCESSORIES
Keyboard	Accessories	KEY-ACCESSORIES

Date Functions (Beginner)

Goal: Calculate invoice due dates that are 30 days from the issue date.

Steps:

In cell C2, use the formula =B2+30.
Ensure the column is formatted as a Date.

Sample Data:

Invoice	Issue Date	Due Date
INV001	2025-01-01
INV002	2025-01-15

Download CSV

Tip: Excel stores dates as serial numbers, allowing you to perform simple math on them.

Solution

The due dates are calculated by simply adding 30 days.

Invoice	Issue Date	Due Date
INV001	2025-01-01	2025-01-31
INV002	2025-01-15	2025-02-14

VLOOKUP (Intermediate)

Goal: Look up an employee's salary from a master table using their ID.

Steps:

On your payroll sheet, use the formula =VLOOKUP(A2, Employees!A:C, 3, FALSE) in the salary column.

Sample Data (on 'Employees' sheet):

ID	Name	Salary
101	Alice	50000
102	Bob	45000
103	Carol	60000

Download CSV

Tip: VLOOKUP is essential for combining data. The 'V' stands for vertical. FALSE ensures an exact match.

Solution

Your payroll sheet will now pull the correct salaries from the master list.

ID	Name	Salary
103	Carol	60000
101	Alice	50000

INDEX & MATCH (Intermediate)

Goal: Look up a product's price, even if the lookup column isn't the first one.

Steps:

Use =INDEX(C:C, MATCH(E2, B:B, 0)), where E2 contains the product name you're searching for.

Sample Data:

Category	Product Name	Price
Electronics	Laptop	1200
Accessories	Mouse	25

Download CSV

Tip: INDEX & MATCH is more flexible and faster than VLOOKUP.

Solution

This combination correctly finds the price even though the lookup column isn't the first one.

Product to Find	Price
Mouse	25

SUMIFS & COUNTIFS (Intermediate)

Goal: Calculate total sales and transaction count for a specific product and region.

Steps:

To sum sales: =SUMIFS(C:C, A:A, "North", B:B, "Book").
To count sales: =COUNTIFS(A:A, "North", B:B, "Book").

Sample Data:

Region	Product	Sales
North	Book	150
South	Book	250
North	Book	300

Download CSV

Tip: The 'S' in SUMIFS and COUNTIFS lets you add multiple criteria.

Solution

The formulas provide a quick summary based on your specific conditions.

Metric	Result
Total Sales (North, Book)	$450
Transaction Count (North, Book)	2

Data Validation (Intermediate)

Goal: Restrict data entry to a dropdown list to prevent errors.

Steps:

Create your list of valid entries (e.g., 'IT', 'Sales', 'HR').
Select the cell(s) where you want the dropdown.
Go to Data > Data Validation, select List, and select your list as the Source.

Source Data for List:

Department List
IT
Sales
HR

Download CSV

Tip: Data Validation is crucial for creating templates that ensure data integrity.

Solution

The result is interactive. The selected cells in your main table will now have a dropdown arrow, allowing users to only pick from your predefined list. This prevents typos and keeps data consistent.

PivotTables & Slicers (Pro)

Goal: Summarize sales data and create interactive filters.

Steps:

Select your data and go to Insert > PivotTable.
Drag fields to the Rows, Columns, and Values areas to build your report.
With the PivotTable selected, go to PivotTable Analyze > Insert Slicer.

Sample Data:

Region	Product	Sales
North	Book	150
South	Book	250
North	Pen	80

Download CSV

Tip: PivotTables are the most powerful reporting tool in Excel. Slicers make them interactive.

Solution

The PivotTable provides an instant summary of your data. Slicers add interactive buttons to filter it.

Sum of Sales	Product		Grand Total
Region	Book	Pen
North	150	80	230
South	250		250
Grand Total	400	80	480

SUMPRODUCT (Pro)

Goal: Calculate total revenue in one step without a helper column.

Steps:

Use the formula =SUMPRODUCT(B2:B4, C2:C4).

Sample Data:

Product	Quantity	Price
Laptop	10	1200
Mouse	50	25
Keyboard	30	75

Download CSV

Tip: SUMPRODUCT is efficient for weighted averages and other calculations that normally require extra columns.

Solution

The formula calculates (10*1200) + (50*25) + (30*75) in one step.

Metric	Result
Total Revenue	$15,500

Cell Locking & Protection (Pro)

Goal: Create a template where users can only edit specific input cells.

Steps:

Select the input cells (e.g., B2:B3). Right-click > Format Cells.
Go to the Protection tab and uncheck the 'Locked' box.
Go to the Review tab and click Protect Sheet.

Starting Template:

Item	Value
Quantity (Input)
Price (Input)
Total (Formula)	=B2*B3

Download CSV

Tip: This is essential for building robust models that prevent accidental formula deletion.

Solution

The result is an interactive sheet. Users will be able to type in the 'Value' cells for Quantity and Price, but if they try to click on or edit the 'Total' formula cell, they will be blocked by an error message, protecting your formula from being changed.

XLOOKUP Function (Pro)

Goal: Use the modern XLOOKUP to find an employee's department.

Steps:

In cell F2, use the formula: =XLOOKUP(E2, A2:A5, C2:C5, "Not Found")

Sample Data:

Emp ID	Name	Department	Lookup ID
101	Alice	Sales	103
102	Bob	IT	105
103	Carol	HR
104	Dave	Sales

Download CSV

Tip: XLOOKUP replaces VLOOKUP. It's more powerful, flexible, and easier to use.

Solution

The formula correctly finds the department for existing IDs and returns a custom message for non-existing ones.

Lookup ID	Result
103	HR
105	Not Found

Intro to Power Query (Pro)

Goal: Combine two tables into one master table without formulas.

Steps:

Format your data as Excel Tables (Ctrl+T).
Load each table into Power Query via Data > From Table/Range.
Use the Merge Queries feature to join the tables on a common column.
Click Close & Load to output the new merged table.

Sample Data:

Table 1: Sales

ProductID	Units
P1	10
P2	5

Table 2: Products

ProductID	Name
P1	Laptop
P2	Mouse

Download Sales CSV Download Products CSV

Tip: Power Query automates data cleaning. Once set up, just click 'Refresh' to update when source data changes.

Solution

The final output is a single, clean table combining the information from both source tables.

ProductID	Units	Name
P1	10	Laptop
P2	5	Mouse

PMT Function for Loans (Pro)

Goal: Calculate the fixed monthly payment for a loan.

Steps:

In cell D2, use the formula: =PMT(B2/12, C2*12, -A2)

Sample Data:

Loan Amount	Annual Rate	Years	Monthly Payment
2000000	8%	20

Download CSV

Tip: The PMT function is essential for financial analysis, helping you model payments for cars, homes, or business loans.

Solution

The formula calculates the fixed monthly payment required to pay off the loan.

Loan Amount	Annual Rate	Years	Monthly Payment
2,000,000	8%	20	₹16,728.84

Excel Practice Exercises

SUM & AVERAGE (Beginner)

Steps:

Sample Data:

Solution

IF Function (Beginner)

Steps:

Sample Data:

Solution

AND/OR Functions (Beginner)

Steps:

Sample Data:

Solution

Text Functions (Beginner)

Steps:

Sample Data:

Solution

Date Functions (Beginner)

Steps:

Sample Data:

Solution

VLOOKUP (Intermediate)

Steps:

Sample Data (on 'Employees' sheet):

Solution

INDEX & MATCH (Intermediate)

Steps:

Sample Data:

Solution

SUMIFS & COUNTIFS (Intermediate)

Steps:

Sample Data:

Solution

Data Validation (Intermediate)

Steps:

Source Data for List:

Solution

PivotTables & Slicers (Pro)

Steps:

Sample Data:

Solution

SUMPRODUCT (Pro)

Steps:

Sample Data:

Solution

Cell Locking & Protection (Pro)

Steps:

Starting Template:

Solution

XLOOKUP Function (Pro)

Steps:

Sample Data:

Solution

Intro to Power Query (Pro)

Steps:

Sample Data:

Solution

PMT Function for Loans (Pro)

Steps:

Sample Data:

Solution