EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home Excel Excel Resources Excel Formula and Functions Excel Function for Range
 

Excel Function for Range

Madhuri Thakur
Article byMadhuri Thakur

Updated August 9, 2023

Excel Function for Range

 

 

Excel Function for Range (Table of Content)
  • Excel Function for Range
  • Examples of Range Function in Excel

Range Function in Excel

Range in Excel is the difference between the maximum and minimum limits of the available numbers in Excel. For example, around 10 different numbers are randomly selected in a list in Excel.

To calculate the Range for these numbers, we need to find the upper and lower values using the MAX and MIN functions in the list of those cells. Once we get the maximum and minimum values out of those numbers, subtract the Max value from the Min value. The returned number will be the range.

There are two kinds of ranges used extensively in Excel, which are illustrated below:

Symmetrical Range: A range that consists of all cells in adjacent positions to each other. Such a range usually shows up as a square or rectangle in a spreadsheet when highlighted. The range shown in the image would be (A1:C4)

Excel function for range 1

Irregular Range: A range consisting of cells that are not contiguous and may not have regular geometrical shapes when highlighted. The range highlighted in the image would be (A1:C4, E1,E4, B6,A7)

Excel function for range 2

Examples of Range Function in Excel

Now, a range would be useless as we have to derive insights from the range’s data. So formulae are used with cell ranges which add the operation we want to perform in the data from the range. For example, if we want to find the sum of the numbers in cells A1 to C4, we would use =SUM(A1:C4)

You can download this Excel Function for Range Template here – Excel Function for Range Template

Excel function for range 3

Example #1 – Finding Maximum and Minimum

1) Finding the maximum and minimum values in a cell range: We use the following functions when looking for minimum and maximum values in a cell range. Please note that this would give us the mathematical result, not the maximum and minimum, as defined by cell number.

  • For Maximum: As illustrated below, we would use the =MAX(Cell Range) function.

Finding Maximum and Minimum example 1.1

  • For Minimum: We would use the =MIN(Cell Range) function as shown below.

Finding Maximum and Minimum example 1.2

2) Suppose we are not interested in the minimum and maximum in the highlighted range but also in the top and bottom k numbers. We can use the following functions to calculate those.

  • For the top k number, say k=3, which means the third-largest number in the range, we would use the function =LARGE(Cell Array,k) for symmetrical ranges or =LARGE((Cell Range),k) for irregular ranges as shown below.

Finding Maximum and Minimum example 1.3

Finding Maximum and Minimum example 1.4

  • A very similar function for finding the kth smallest number in a range would be to use =SMALL(Cell Array, k) for a symmetrical range or =SMALL((Cell Range),k) for an irregular range.

Excel Function for Range- example 1.5

Finding Maximum and Minimum example 1.6

Example #2 – How to Define and Use Ranges in Excel?

We will now look at how to define and use ranges in Excel. First, we need to have data to work with. This can be anything in a spreadsheet ranging from letters to numbers or a combination of both. For the illustrations accompanying this discussion, I am using a sample from a production database that stores data on how many parts are produced in a year.

Excel Function for Range-Example 2.1

Let us say we want to know the range of production numbers entered over the two years. We do this by subtracting the minimum from the maximum value. For this, we need not find each value individually but have to break down the calculation steps and write the formula as follows:

MAX(Cell Range)-MIN(Cell Range)

Please note that the cell range has to be the same in the arguments; otherwise, the formula will not return the correct result.

Excel Function for Range- Example 2.2

We find that the range of products is 368 parts. Now, if we want to find out the occurrence of a particular value in the range, or a range of values within the range, we use another function called COUNTIF. This function has the following syntax:

COUNTIF(Range, value)

Suppose we want to find the month in which we hit more than 300 parts. The formula would be =COUNTIF(C2:C23,”>300″)

Use Ranges in Excel Example 2.3

We find that for 7 months, the production was more than 300 parts. We can also find out if we had any month below a particular number, suppose 100. We would use a nested COUNTIF formula within an IF statement to get a Yes or No answer like this:

=IF(COUNTIF(range,” value”),” Yes”,” No”)

This would look like this:

Excel Function for Range- Example 2.4

The result would be a No as none of the production numbers in the range is below 100. A variation of this can be used to find if we have any production number in a particular value. This would be as follows:

COUNTIF(range,”*”&value&”*”) or COUNTIF(range, value)

The first variation is useful if we want to match two different databases, and the second is when we want to find out if a particular value occurs or not, and if it does, then how many times it reoccurs.

Use Ranges in Excel Example 2.5

Use Ranges in Excel Example 2.6

We can use the MATCH function instead of COUNTIF to find the number of values greater or less than a given value.

Use Ranges in Excel Example 2.7

In the above example, we use the MATCH function to find the number of months with less than 300 parts produced.

Things to Remember

  • We should sort the data in ascending or descending order wherever feasible to simplify operations using ranges.
  • Quotation marks (“”) and asterisks (*) are used in formulae whenever we are looking for substrings or specific text ranges within a range.
  • Irregular ranges are the most common ranges used in business. As such, whenever possible, we should use tables to classify the data before running any operations on them.
  • It must be noted that ranges can be highlighted manually, and Excel displays the number of cells in it as a count at the bottom; however, we can find out the number of rows or columns in a range using the following functions:

ROWS(range)
COLUMNS(range)

Usually, these two functions are not required but are useful for large tables, multiple databases, and recording macros.

Conclusion

Knowledge of range in Excel is an important prerequisite to manipulating data. The range is also used in recording macros and VBA coding; hence, an in-depth understanding of range is necessary for anyone using Excel.

Recommended Articles

This is a guide to Excel Function for Range. Here we have discussed Examples of Range Functions in Excel, along with steps and a downloadable Excel template. You may also look at the following articles to learn more –

  1. VBA Range
  2. VBA Selecting Range
  3. Excel Named Range
  4. Range in Excel

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA

Download Excel Function for Range Template

EDUCBA Login

Forgot Password?

EDUCBA

Download Excel Function for Range Template

EDUCBA

डाउनलोड Excel Function for Range Template

🚀 Limited Time Offer! - 🎁 ENROLL NOW