Assume numbers are typed in range A1:A2 of multiple worksheets in a workbook. The task is the compute the MODE of these numbers. Mode is defined as the value which appears most frequently in a range of cells. So, if one types 1,3,4,3,5,6 in range A1:A6, then the mode will be 3 – 3 appears maximum number of times in the range.
In MS Excel, there is a built in way to compute the MODE. The formula for the same is
Unfortunately, MODE() is not a 3D function and therefore, something like this return a #REF error
This behavior seems somewhat vague because other basic Mathematical and Statistical functions such as SUM(), COUNT(), AVERAGE(), MAX(), MIN(), VAR(), and STDEV() work just fine across multiple worksheets.
Depending upon the version of MS Excel which you are using, there could be two ways to solve this problem.
Solution for MS Excel 2010 and higher versions
If you are using the Power Query add-in, then a few simple steps (no formulas at all) can solve this problem. The result will be dynamic and refreshable (just as in a Pivot Table). In Power Query, there is a feature to append data from multiple worksheets into one running range. Once this is done, the running range can be transferred to an MS Excel worksheet on which the MODE function can be used.
Solution for all versions of MS Excel
To compute MODE across multiple worksheets, you may refer to my solution in this workbook.