Very often in Excel you want to summarize a set of data to pull out insights. The MIN and MAX functions can help you do exactly that. Which month had the highest amount of sales? Which month had the lowest?
The data above records sales by month for 14 months. Before we use MIN and MAX, let’s use filtering to quickly find the lowest and highest month.
Alternative to MIN and MAX: Filters
If you didn’t already know the MIN and MAX functions, you can get to the right answer using filtering. Simple select your data set and click the filter button in the menu bar
You can choose from A-Z (lowest to highest) or Z-A (highest to lowest). This feature works with numbers even though the buttons are shown as A-Z and Z-A.
Sort in Ascending Order
As it turns out, our first month of sales data had the lowest value.
Sort in Descending Order
In January of 2019 we had our highest month of sales.
While filtering can work to find the highest and lowest values, at scale the formulas MIN and MAX work a lot more efficiently, especially if the size of your data gets out of control.
The only “arguments” taken in by the min function are the numbers you’d like to analyze. The result will be the absolute lowest number in that set. You can do this as a list (B3,B4,B5, etc) or an array like we did in this example. The result of our MIN formula is $1,825.
The MAX formula functions the same way. Simply insert the array of data you’re looking to analyze and get your result! This will return the largest piece of data in your set.
NOTE: If you were going to continually add on this data set, you could change the formula to include the entire column, ie MAX(B:B) and it would automatically update if a larger value came along.
Interested in finding the 2nd highest value (or 3rd, 4th, etc) or second lowest value? In that case you’ll want to use the SMALL and LARGE functions.