How to Use the MAX Function in Excel

The MAX function helps you quickly identify the largest value in a dataset. In this lesson, you’ll learn how the function works and see how it can be used to find metrics like the highest service ticket or biggest revenue day in an HVAC dataset. You’ll also see how using an entire column can make the formula automatically update as new data is added.

Download the Excel file used in this tutorial:

Step By Step Instructions

The MAX Function

1. Start with the value column you want to analyze

Identify the numeric field you want to evaluate, such as daily revenue, repair ticket totals, or service sales.
Make sure you are selecting a column that contains numbers, since MAX is used to return the largest value in a range.

2. Use the MAX function on a selected range

Enter the MAX function and select the range that contains your values.
This returns the highest number in that group of data.
In the example from the video, the function is used to find the highest revenue value in the list.

3. Try the first method by selecting the full column

One approach is to use MAX with the entire column.
This is especially useful when your dataset will continue to grow over time.
If new rows are added later, the result will update automatically because the whole column is already included.

4. Try the second method by selecting only the current data range

Another approach is to use MAX with only the visible range of existing data.
In the video, this is done by selecting the current list quickly with keyboard shortcuts.
This method works well for static datasets where you do not expect new entries to be added.

5. Understand the difference between the two approaches

When you use the full column, the formula stays flexible and captures future data.
When you use only the current range, the result is accurate for now, but it will not include new values added below the selected area.
This distinction matters if you update your spreadsheet daily, weekly, or monthly.

6. Use the full-column method for dynamic files

If your HVAC data is updated regularly, selecting the whole column is the safer option.
This helps ensure the MAX result always reflects the latest highest value without needing to adjust the formula manually.

7. Use the fixed-range method for controlled reports

If your file is finalized and no new rows will be added, selecting only the current range can be perfectly fine.
This can also make the worksheet easier to review if you want the formula tied only to a specific block of data.

8. Recognize how this connects to MAXIFS

The video also introduces the idea of MAXIFS as the next step.
While MAX finds the single highest value in a full range, MAXIFS lets you find the highest value within a category.
For example, this would let you find the largest value for one equipment type or one service category instead of the entire dataset.

9. Apply it to real HVAC reporting

Use MAX when you want to quickly spot the biggest sales day, the largest repair invoice, or the highest-value service ticket.
It is one of the fastest ways to identify peak values in operational and financial data.

10. Choose the method that matches how your file is maintained

Use the full column when your file grows over time.
Use a fixed range when your data is static.
Either way, MAX gives you a quick answer to one of the most common Excel questions: what is the highest value in this list?

FAQ

The MAX Function in Excel

Q1. What does the MAX function do in Excel?
The MAX function returns the largest value in a group of numbers. It’s commonly used to identify peak values such as the highest sales amount, largest repair ticket, or biggest expense in a dataset.

Q2. Why is the MAX function useful for business analysis?
Finding the largest values in your data helps highlight important events or outliers, such as the highest revenue day, most expensive job, or largest transaction. This can reveal patterns in performance or pricing.

Q3. How do you use the MAX function in Excel?
The basic syntax is simple:
=MAX(range)
You select the range of numbers you want Excel to evaluate, and the function returns the highest value within that range.

Q4. Should I select a fixed range or the entire column?
If your dataset grows over time, selecting the entire column can be helpful because the formula will automatically include new values added later.

Q5. What is the difference between MAX and MAXIFS?
The MAX function returns the largest value in a dataset without conditions. The MAXIFS function allows you to find the maximum value based on specific criteria, such as the highest revenue for a particular service type or product category.

Q6. When should I use the MAX function instead of other formulas?
Use MAX when your goal is simply to find the largest value in a dataset. If you need the smallest value instead, Excel provides a similar function called MIN.

Get more lessons like this
by joining our newsletter

Receive weekly Excel tutorials, tips, and KPIs straight to your inbox

Databoards

Analysis & Development