Find Lowest 3 Values in Excel (With Example)

Understanding Excel’s Key Array Functions: SMALL and TRANSPOSE

To successfully extract multiple lowest values simultaneously, we must utilize specialized functions designed for handling subsets of data. The two primary functions involved are SMALL and TRANSPOSE. Understanding their individual roles is crucial before combining them into a powerful array structure.

The SMALL function is an essential statistical tool in Excel. It returns the k-th smallest value in a data set. For example, if you want the absolute smallest value, k equals 1; if you want the fifth smallest value, k equals 5. Its syntax is straightforward: =SMALL(array, k), where ‘array’ is the range of numerical data and ‘k’ is the position (rank) of the value you wish to retrieve. By providing an array constant (e.g., {1, 2, 3}) for the ‘k’ argument, we instruct the function to retrieve multiple smallest values simultaneously.

The TRANSPOSE function serves a critical role in reshaping the output. When the SMALL function is forced to return an array of multiple results (the lowest 1st, 2nd, and 3rd values), it naturally outputs these results horizontally, even if we want them displayed vertically in a column. The TRANSPOSE function takes this horizontal array and flips it, delivering the results vertically across multiple cells. This makes the output cleaner and easier to read in a standard report format.

The Core Array Formula for Extracting Lowest Values

Combining these two functions yields an elegant and dynamic solution for extracting the lowest three values in a vertical format. This combination forms an array formula, meaning it must be entered correctly (often requiring Ctrl+Shift+Enter in older versions of Excel, although modern versions handle dynamic arrays automatically).

You can use the following standard formula structure in Excel to find the lowest 3 values in a particular range:

=TRANSPOSE(SMALL(B2:B13, {1,2,3}))

This particular formula instructs Excel to perform three steps: first, it identifies the 1st, 2nd, and 3rd smallest values within the range B2:B13 using the SMALL function combined with the array constant {1,2,3}. Second, this result is a horizontal array of the three values. Third, the TRANSPOSE function converts this array into a vertical list, ensuring the output appears neatly stacked in three consecutive cells.

Step-by-Step Example: Finding the Lowest Scores in a Dataset

To illustrate the power of this technique, let us consider a practical scenario. Suppose we have the following dataset in Microsoft Excel that displays the points scored by various basketball players over a competition period. Our goal is to quickly identify the three players who scored the fewest points.

This data spans the range B2:B13, representing 12 different scores. Analyzing this list manually can be time-consuming and error-prone, especially as the list size increases. We need a reliable, dynamic method to isolate the minimum three values in the Points column.

The crucial step is setting up the output area. Since we are looking for three values, we must select three consecutive cells, ideally in a separate column (e.g., column D or E), where the results will be displayed vertically. Selecting the correct number of output cells is fundamental when dealing with older array formulas, though modern dynamic array versions often handle spill ranges automatically.

Implementing the Formula in Practice

We will designate cell D2 as the starting point for our results. Since the formula returns three values, the output will occupy cells D2, D3, and D4.

We can type the following formula into cell D2 to achieve the desired vertical output:

=TRANSPOSE(SMALL(B2:B13, {1,2,3}))

When entering this formula, if you are using a version of Excel that supports Dynamic Arrays (e.g., Microsoft 365), you simply press Enter. The results will automatically “spill” into the required adjacent cells (D2:D4). If you are using an older version of Excel, you must first select the entire output range (D2:D4), then type the formula, and finally commit it using the special key combination: Ctrl + Shift + Enter. This action signals to Excel that you are entering an array formula, surrounding it with curly braces { } automatically.

The following screenshot demonstrates the application of this formula structure, resulting in the successful identification of the bottom three scores:

Analyzing the Results and Verification

Upon successful implementation of the array formula, the output cells (D2:D4) are populated with the numerical values representing the lowest scores. From the resulting display, we can clearly see that the lowest 3 points values are 11, 12, and 15. These figures represent the lowest performance metrics within the entire range B2:B13.

It is essential to verify the results to ensure the formula executed as intended. We can manually inspect the original dataset in the Points column (B2:B13) to confirm these findings. Verification confirms that 11 is the absolute minimum (1st smallest), 12 is the next smallest (2nd smallest), and 15 is the third smallest value in the list.

This manual verification process confirms the accuracy and efficiency of the combined TRANSPOSE and SMALL functions. The process is instantaneous, regardless of how many rows the data occupies, making it a highly scalable solution for large-scale data analysis within Excel.

The visual representation of the sorted data further emphasizes that the formula correctly identified these extremes:

Modifying the Formula for Horizontal Output

While vertical presentation is often preferred for report generation, there are instances where displaying the results horizontally (in a single row) is more suitable, perhaps for integration into existing tables or headers. Achieving horizontal output requires only a slight modification to our core formula.

Since the SMALL function inherently returns an array horizontally when provided with multiple ‘k’ values, we simply need to remove the TRANSPOSE wrapper function. This simplifies the formula structure while preserving the core logic of finding the k-th smallest values.

To return these three values horizontally, starting in cell D2 (which would then spill into E2 and F2), we use the following simplified array formula:

=SMALL(B2:B13, {1,2,3})

If using dynamic arrays, this formula automatically spills across three cells. If using older Excel versions, remember to select the three desired output cells (D2:F2) before entering the formula and pressing Ctrl + Shift + Enter.

The formula now successfully returns the lowest 3 values from the Points column horizontally instead of vertically, demonstrating the flexibility achieved by including or excluding the TRANSPOSE function.

Conclusion: Mastering Data Analysis with Excel Functions