Introduction
The MAX function in Excel returns the largest numeric value from a group of numbers. It helps you identify the highest sales, largest salary, most expensive product, maximum profit, and best performance result.
In this tutorial, you will learn MAX Function in Excel Tutorial through practical business examples, from basic calculations to conditional analysis, filtered data, Excel Tables, and performance reporting. Each question introduces a different concept so you can build useful Excel skills.
MAX Function Syntax
=MAX(number1, [number2], ...)- number1: The first number, cell reference, or range.
- number2: Additional numbers, cells, or ranges. This argument is optional.
Example:
=MAX(B2:B10)This formula returns the largest numeric value in cells B2 through B10.
Question 1: Find the Highest Daily Sales
Problem Statement:
A company wants to identify the day with the highest sales during the working week.
Data:
| Day | Sales (₹) |
|---|---|
| Monday | 18,000 |
| Tuesday | 22,000 |
| Wednesday | 15,000 |
| Thursday | 35,000 |
| Friday | 20,000 |
Excel Formula:
=MAX(B2:B6)Expected Result: ₹35,000
Student Task:
Add Saturday’s sales of ₹40,000 in cell B7. Update the formula to include Saturday.
Expected Result: ₹40,000
Challenge: Identify the day with the highest sales by matching the result to the sales table.
Question 2: Find the Highest Value Among Selected Cells
Problem Statement:
A sales manager wants to compare only three selected sales records rather than the entire dataset.
Data:
| Record | Sales (₹) |
|---|---|
| Record 1 | 45,000 |
| Record 2 | 32,000 |
| Record 3 | 48,000 |
| Record 4 | 51,000 |
| Record 5 | 27,000 |
| Record 6 | 40,000 |
Excel Formula:
=MAX(B2,B4,B7)Expected Result: ₹48,000
The formula compares the values in B2, B4, and B7 only.
Student Task:
Find the highest value among Record 2, Record 4, and Record 6.
Expected Formula:
=MAX(B3,B5,B7)Expected Result: ₹51,000
Question 3: Find the Highest Employee Salary
Problem Statement:
The HR department wants to identify the highest monthly salary among its employees.
Data:
| Employee | Monthly Salary (₹) |
|---|---|
| Amit | 45,000 |
| Priya | 52,000 |
| Rahul | 38,000 |
| Neha | 60,000 |
| Karan | 48,000 |
Excel Formula:
=MAX(B2:B6)Expected Result: ₹60,000
Student Task:
Add a new employee named Riya with a monthly salary of ₹65,000 in row 7. Update the formula.
Expected Formula:
=MAX(B2:B7)Expected Result: ₹65,000
Business Challenge: The highest salary does not necessarily mean the employee has the best performance. Salary and performance should be analyzed separately.
Question 4: Understand How MAX Handles Blank Cells, Text, and Zero
Problem Statement:
An inventory team records product prices. Some prices are missing, and one product has a confirmed price of zero.
Data:
| Product | Price (₹) |
|---|---|
| Keyboard | 1,200 |
| Mouse | (Blank) |
| Promotional Item | 0 |
| Headphones | 950 |
| Monitor | Out of stock |
Excel Formula:
=MAX(B2:B6)Expected Result: ₹1,200
MAX ignores blank cells and text in a referenced range. It includes numeric zero, but zero is not the largest value in this example.
Student Task:
Change the Keyboard price to ₹800. Find the new maximum price.
Expected Result: ₹950
Remember: A blank cell, text value, and numeric zero are treated differently. A zero is still a number and can be the maximum when all other numeric values are smaller or negative.
Question 5: Find the Highest Profit, Including Losses
Problem Statement:
A company wants to identify its most profitable day. Its records include both profits and losses.
Data:
| Day | Profit/Loss (₹) |
|---|---|
| Monday | 5,000 |
| Tuesday | -1,200 |
| Wednesday | 8,500 |
| Thursday | -500 |
| Friday | 2,500 |
Excel Formula:
=MAX(B2:B6)Expected Result: ₹8,500
MAX returns the largest numerical value. The positive profit of ₹8,500 is higher than the other profits and negative results.
Student Task:
Change Friday’s result from ₹2,500 to ₹10,000. Find the new maximum.
Expected Result: ₹10,000
Challenge: Can you explain why a positive profit is greater than a negative loss when Excel compares their numerical values?
Question 6: Find the Highest Value Across Multiple Ranges
Problem Statement:
A company stores product prices for two categories in separate columns. The manager wants to find the highest price across both categories.
Data:
| Category A Price (₹) | Category B Price (₹) | |
|---|---|---|
| 120 | 110 | |
| 95 | 185 | |
| 140 | 105 |
Assume Category A prices are in B2:B4 and Category B prices are in D2:D4.
Excel Formula:
=MAX(B2:B4,D2:D4)Expected Result: ₹185
Student Task:
Add a new price of ₹250 in cell D5. Update the formula to include it.
Expected Formula:
=MAX(B2:B4,D2:D5)Expected Result: ₹250
Question 7: Find the Latest Date in a Dataset
Problem Statement:
An HR department stores employee joining dates. It wants to identify the most recent joining date.
Data:
| Employee | Joining Date |
|---|---|
| Amit | 15-Jan-2025 |
| Priya | 03-Jan-2025 |
| Rahul | 22-Jan-2025 |
| Neha | 10-Jan-2025 |
Assume the joining dates are in cells B2 and are stored as valid Excel dates.
Excel Formula:
=MAX(B2:B5)Expected Result: 22-Jan-2025
If Excel displays a number instead of a date, format the result cell as a Date.
Student Task:
Add Karan with a joining date of 28-Jan-2025. Update the formula.
Expected Result: 28-Jan-2025
Business Use: MAX can identify the latest joining date, most recent transaction date, or latest recorded order date when values are stored as valid Excel dates.
Question 8: Find the Highest Sales for a Specific Region Using MAXIFS
Problem Statement:
A company wants to find the highest sales value for each region without manually filtering the dataset.
Data:
| Region | Sales (₹) |
|---|---|
| North | 100 |
| East | 200 |
| North | 300 |
| West | 400 |
| North | 250 |
| East | 180 |
| West | 150 |
Assume regions are in A2:A8 and sales are in B2:B8.
Excel Formula:
=MAXIFS(B2:B8,A2:A8,"North")Expected Result: ₹300
MAXIFS returns the largest sales value among rows where the region is North.
Student Task:
Find the highest sales value for the East region.
Expected Formula:
=MAXIFS(B2:B8,A2:A8,"East")Expected Result: ₹200
Important: MAXIFS is available in Excel versions that support it, including Excel 2019 and Microsoft 365. If your Excel version does not support MAXIFS, you may need an alternative formula.
Question 9: Find the Highest Price in an Excel Table
Problem Statement:
An inventory team regularly adds new products to an Excel Table. The manager wants to find the highest product price without manually updating the formula range each time.
Data:
| Product | Unit Price (₹) |
|---|---|
| Keyboard | 1,200 |
| Mouse | 800 |
| Monitor | 2,500 |
Convert the data into an Excel Table and name it Inventory. Make sure the price column is named Unit Price.
Excel Formula:
=MAX(Inventory[Unit Price])Expected Result: ₹2,500
Student Task:
Add a new product named Printer with a unit price of ₹3,200.
Expected Result: ₹3,200
Why use an Excel Table? Structured references can automatically include new table rows, making formulas easier to maintain as your dataset grows.
Student Challenge: Build a Maximum Sales and Price Report
A company wants to review product prices and monthly sales using Excel.
Data:
| Product | Unit Price (₹) | January Sales (₹) | February Sales (₹) |
|---|---|---|---|
| Keyboard | 800 | 12,000 | 15,000 |
| Mouse | 500 | 8,000 | 9,000 |
| Monitor | 8,000 | 25,000 | 22,000 |
| Printer | 5,000 | 18,000 | 20,000 |
| Webcam | 1,200 | 10,000 | 11,000 |
Your Tasks
- Find the highest unit price.
- Find the highest January sales value.
- Find the highest February sales value.
- Find the highest sales value across both months.
- Add a new product named Headset with a unit price of ₹9,000, January sales of ₹28,000, and February sales of ₹30,000. Update the maximum values.
Answer Key
1. Highest unit price
=MAX(B2:B6)Expected result: ₹8,000
2. Highest January sales
=MAX(C2:C6)Expected result: ₹25,000
3. Highest February sales
=MAX(D2:D6)Expected result: ₹22,000
4. Highest sales across both months
=MAX(C2:D6)Expected result: ₹25,000
5. Updated maximum values after adding Headset
If the new product is entered in row 7:
- Highest unit price: ₹9,000
- Highest January sales: ₹28,000
- Highest February sales: ₹30,000
- Highest sales across both months: ₹30,000
Key Takeaways
- The MAX function returns the largest numeric value from a set of numbers or ranges.
- Use
=MAX(B2:B10)to find the highest number in a continuous range. - Use commas to compare selected cells or multiple ranges.
- Blank cells and text in referenced ranges are ignored, while numeric zero and negative numbers are included.
- MAX in excel can find the latest date when cells contain valid Excel dates.
- Use MAXIFS to find the maximum value that meets one or more conditions.
- Use
SUBTOTAL(4,range)to calculate the maximum of visible rows after filtering. - Structured references make MAX formulas easier to maintain in Excel Tables.
- Combine MAX with IF to identify records matching the highest value.
Frequently Asked Questions (FAQs)
1. What is the MAX function in Excel?
The MAX function returns the largest numeric value from a group of numbers, cells, or ranges.
2. What is the syntax of the MAX function?
The syntax is =MAX(number1, [number2], ...). You can provide individual numbers, cell references, or ranges.
3. Does MAX in excel ignore blank cells?
Yes. Blank cells in a referenced range are ignored.
4. Does MAX in excel include zero?
Yes. Zero is a numeric value, so it can be the maximum when all other numeric values are zero or negative.
5. Does MAX ignore text?
MAX ignores text in referenced ranges. However, text entered directly as a formula argument may cause an error.
6. Can MAX in excel find the latest date?
Yes. If the cells contain valid Excel dates, MAX returns the latest date.
7. What is the difference between MAX and MAXIFS?
MAX returns the largest numeric value across the supplied values. MAXIFS returns the largest value that meets one or more specified conditions.
8. How can I find the maximum value after applying a filter?
Use =SUBTOTAL(4,B2:B10). It calculates the maximum among visible rows and excludes rows hidden by the filter.
9. Can MAX work with negative numbers?
Yes. MAX compares negative and positive numbers and returns the numerically largest value. For example, =MAX(-5,3,10) returns 10.
10. What happens if all cells in a range are blank or contain text?
MAX returns 0 when there are no numeric values to evaluate in the referenced range.