Skip to content
Free Excel Tutorials
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Data Analysis

  • Conditional Formatting Color Scales Examples in Excel
  • How To Compare Two Lists in Excel
  • Chart Axes in Excel
  • Number and Text Filters Examples in Excel
  • Error Bars in Excel

References

  • How to use Excel LOOKUP Function
  • How to use Excel OFFSET function
  • How to calculate two-way lookup VLOOKUP in Excel Table
  • How to get relative column numbers in a range in Excel
  • Count rows with at least n matching values

Data Validations

  • Excel Data validation date in specific year
  • Excel Data validation must begin with
  • Excel Data validation number multiple 100
  • How To Create Drop-down List in Excel
  • Excel Data validation must contain specific text

Tag: SMALL function

SMALL function: Description, Usage, Syntax, Examples and Explanation

by

What is SMALL function in Excel? SMALL function is one of Statistical functions in Microsoft Excel that returns the k-th smallest value in a data set. Use this function to return values with a particular relative standing in a data set. Syntax of SMALL function SMALL(array, k) The SMALL function syntax has the following arguments: Array:  An array …

Continue Reading

How to calculate next scheduled event in Excel

by

To get the next scheduled event from a list of events with dates, you can use an array formula based on the MIN and TODAY functions to find the next date, and INDEX and MATCH  to display the event on that date. Formula {=MIN(IF((range>=TODAY()),range))} Note: this is an array formula and must be entered with Control + …

Continue Reading

Extract all partial matches in Excel

by

This tutorial shows how to Extract all partial matches in Excel using the example below; Formula =IF(F5>ct,””,INDEX(data,AGGREGATE(15,6,(ROW(data)-ROW($B$5)+1)/ISNUMBER(SEARCH(search,data)),F5))) Explanation To extract all matches based on a partial match, you can use use an array formula based on the INDEX and AGGREGATE functions, with support from ISNUMBER and SEARCH. In the example shown, the formula in G5 …

Continue Reading

Extract multiple matches into separate columns in Excel

by

This tutorial shows how to Extract multiple matches into separate columns in Excel using the example below; Explanation To extract multiple matches to separate cells, in separate columns, you can use an array formula based on INDEX and SMALL. In the example shown, the formula in F5 is: {=IFERROR(INDEX(names,SMALL(IF(groups=$E5,ROW(names)-MIN(ROW(names))+1),COLUMNS($E$5:E5))),””)} This is an array formula and …

Continue Reading

Extract multiple matches into separate rows in Excel

by

This tutorial shows how to calculate Extract multiple matches into separate rows in Excel using the example below; Explanation To extract multiple matches to separate cells, in separate rows, you can use an array formula based on INDEX and SMALL. In the example shown, the formula in E5 is: {=IFERROR(INDEX(names,SMALL (IF(groups=E$4,ROW(names)-MIN(ROW (names))+1),ROWS($E$5:E5))),””)} This is an …

Continue Reading

Find lowest n values in Excel

by

This tutorial shows how to Find lowest n values in Excel using the example below; Formula =SMALL(range,n) Explanation To find the n lowest values in a set of data, you can use the SMALL function. This can be combined with INDEX as shown below to retrieve associated values. In the example shown, the formula in …

Continue Reading

Get nth match in Excel

by

This tutorial shows how to Get nth match in Excel  using the example below; Formula =SMALL(IF(logical,ROW(list)-MIN(ROW(list))+1),n) Explanation To get the position of the nth match (for example, the 2nd matching value, the 3rd matching value, etc.), you can use a formula based on the SMALL function. In the example shown, the formula in G5 is: …

Continue Reading

Highlight bottom values in Excel

by

This tutorial shows how to Highlight bottom values in Excel using the example below; Formula =A1<=SMALL(data,N) Explanation To highlight the smallest (bottom) values in a set of data with conditional formatting, you can use a formula based on the SMALL function. In the example shown, the formula used for conditional formatting is: =B4<=SMALL(data,input) Note: Excel contain a …

Continue Reading

Sum bottom n values in Excel

by

This tutorial shows how to Sum bottom n values in Excel. You can use a combination of SUMPRODUCT function and SMALL function to get the sum bottom  n values in the example below; Formula =SUMPRODUCT(SMALL(range,{1,2,n})) Explanation If you need to sum or add the bottom values in a range, you can do so with a formula that uses the …

Continue Reading

Sum bottom n values with criteria in Excel

by

This tutorial shows how to Sum bottom n values with criteria in Excel. You can use a combination of SUM function, SMALL function and IF function to get the sum bottom in the example below; Formula {=SUM(SMALL(IF(range1=criteria,range2),{1,2,3,N}))} Explanation To sum the bottom n values in a range matching criteria, you can use an array formula based on the …

Continue Reading

nth smallest value in Excel

by

This tutorial shows how to calculate nth smallest value in Excel using the example below; To get the 2nd smallest value, 3rd smallest value, 4th smallest value, and so on, from a set of data, you can use the SMALL function. Formula =SMALL(range,nth) Explanation In the example shown, the formula in G5 is: =SMALL(times,F5) How …

Continue Reading

nth smallest value with criteria in Excel

by

This tutorial shows how to calculate nth smallest value with criteria in Excel using the example below; To get the 2nd smallest value, 3rd smallest value, 4th smallest value, and so on, where each value matches supplied criteria, you can use an array formula that uses the SMALL and IF functions. Formula {=SMALL(IF(criteria,values),n)} Explanation In …

Continue Reading

Break ties with helper column and COUNTIF in Excel

by

This tutorial shows how to Break ties with helper column and COUNTIF in Excel using the example below; Formula =A1+(COUNTIF(exp_rng,A1)-1)*adjustment Explanation To break ties, you can use a helper column and the COUNTIF function to adjust values so that they don’t contain duplicates, and therefore won’t result in ties. In the example shown, the formula in D5 …

Continue Reading

9 Mathematical Computations Example using Excel Statistical Function

by

In addition to formulas, another way to conduct mathematical computations in Excel is through functions. Statistical functions apply a mathematical process to a group of cells in a worksheet. This chapter gives an overview of some very useful statistical functions like: AVERAGE function, AVERAGEIF function, MEDIAN function, SUMIF function, MIN function, MODE function, STEDV function, LARGE function, SMALL function. Average To calculate the average of a …

Continue Reading

Learn Basic Excel

Ribbon
Workbook
Worksheets
Format Cells
Find & Select
Sort & Filter
Templates
Print
Share
Protect
Keyboard Shortcuts

Categories

  • Charts
  • Data Analysis
  • Data Validation
  • Excel Functions
    • Cube Functions
    • Database Functions
    • Date and Time Functions
    • Engineering Functions
    • Financial Functions
    • Information Functions
    • Logical Functions
    • Lookup and Reference Functions
    • Math and Trig Functions
    • Statistical Functions
    • Text Functions
    • Web Functions
  • Excel VBA
  • Excel Video Tutorials
  • Formatting
  • Grouping
  • Others

Logical Functions

  • IFNA function: Description, Usage, Syntax, Examples and Explanation
  • IF, AND, OR and NOT Functions Examples in Excel
  • How to use IFS function in Excel
  • IFS function: Description, Usage, Syntax, Examples and Explanation
  • IFERROR function: Description, Usage, Syntax, Examples and Explanation

Date Time

  • How to calculate most recent day of week in Excel
  • Basic timesheet formula with breaks in Excel
  • DAYS function: Description, Usage, Syntax, Examples and Explanation
  • Get days, months, and years between dates in Excel
  • How to calculate nth day of year in Excel

Grouping

  • Group times into unequal buckets in Excel
  • Group numbers at uneven intervals in Excel
  • Group numbers with VLOOKUP in Excel
  • How to randomly assign data to groups in Excel
  • Calculate conditional mode with criteria in Excel

General

  • Zoom Worksheet in Excel
  • Find Most Frequently Occurring Word in Excel Worksheet
  • Count cells that do not contain errors in Excel
  • Convert column letter to number in Excel
  • How to Insert Cells, Row and Rows in Excel
© 2025 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning