Dashboard Top 5 Values / Bottom 5 Values

There are times when we want to generate top and low values on dashboards.

It's easy when you know the Large and Small function. 

In this example I have a set of data in Column A. I use the =Large formula in column B and =Small formula in column D to represent the sub dataset I need. 

Data
Top 5 Values
Top Formula
Lowest 5 Values
Lowest Formula
91
=LARGE(A:A,1)
28
=SMALL(A:A,1)
87
87
=LARGE(A:A,2)
29
=SMALL(A:A,2)
79
84
=LARGE(A:A,3)
34
=SMALL(A:A,3)
51
80
=LARGE(A:A,4)
35
=SMALL(A:A,4)
69
79
=LARGE(A:A,5)
45
=SMALL(A:A,5)
29




28




66




34




91




80




56




55




71




74




84




79




45




56




63






Syntax
LARGE(array,k)
Array     is the array or range of data for which you want to determine the k-th largest value.
K     is the position (from the largest) in the array or cell range of data to return.
If array is empty, LARGE returns the #NUM! error value.
If k ≤ 0 or if k is greater than the number of data points, LARGE returns the #NUM! error value.
If n is the number of data points in a range, then LARGE(array,1) returns the largest value, and LARGE(array,n) returns the smallest value.

You can use =ISError to remove the error, =ISError(large(A:A,1),””) 

So the next time you are asked to generate top values, break out this trick and WOW your audience.

Worksheet and workbook specifications and limits

Some people have asked me what are the limitations of worksheets and workbooks. I had this sitting on my computer and pass it along to you.


Feature
Maximum limit
Open workbooks
Limited by available memory and system resources
Worksheet size
1,048,576 rows by 16,384 columns
Column width
255 characters
Row height
409 points
Page breaks
1,026 horizontal and vertical
Total number of characters that a cell can contain
32,767 characters
Characters in a header or footer
255
Sheets in a workbook
Limited by available memory (default is 3 sheets)
Colors in a workbook
16 million colors (32 bit with full access to 24 bit color spectrum)
Named views (view: A set of display and print settings that you can name and apply to a workbook. You can create more than one view of the same workbook without saving separate copies of the workbook.) in a workbook
Limited by available memory
Unique cell formats/cell styles
64,000
Fill styles
256
Line weight and styles
256
Unique font types
1,024 global fonts available for use; 512 per workbook
Number formats in a workbook
Between 200 and 250, depending on the language version of Excel that you have installed
Names in a workbook
Limited by available memory
Windows in a workbook
Limited by available memory
Panes in a window
4
Linked sheets
Limited by available memory
Scenarios (scenario: A named set of input values that you can substitute in a worksheet model.)
Limited by available memory; a summary report shows only the first 251 scenarios
Changing cells in a scenario
32
Adjustable cells in Solver
200
Custom functions
Limited by available memory
Zoom range
10 percent to 400 percent
Reports
Limited by available memory
Sort references
64 in a single sort; unlimited when using sequential sorts
Undo levels
100
Fields in a data form
32
Workbook parameters
255 parameters per workbook
Filter drop-down lists
10,000

Calculation specifications and limits
Feature
Maximum limit
Number precision
15 digits
Smallest allowed negative number
-2.2251E-308
Smallest allowed positive number
2.2251E-308
Largest allowed positive number
9.99999999999999E+307
Largest allowed negative number
-9.99999999999999E+307
Largest allowed positive number via formula
1.7976931348623158e+308
Largest allowed negative number via formula
-1.7976931348623158e+308
Length of formula contents
8,192 characters
Internal length of formula
16,384 bytes
Iterations
32,767
Worksheet arrays
Limited by available memory
Selected ranges
2,048
Arguments in a function
255
Nested levels of functions
64
User defined function categories
255
Number of available worksheet functions
341
Size of the operand stack
1,024
Cross-worksheet dependency
64,000 worksheets that can refer to other sheets
Cross-worksheet array formula dependency
Limited by available memory
Area dependency
Limited by available memory
Area dependency per worksheet
Limited by available memory
Dependency on a single cell
4 billion formulas that can depend on a single cell
Linked cell content length from closed workbooks
32,767
Earliest date allowed for calculation
January 1, 1900 (January 1, 1904, if 1904 date system is used)
Latest date allowed for calculation
December 31, 9999
Largest amount of time that can be entered
9999:59:59

Charting specifications and limits
Feature
Maximum limit
Charts linked to a worksheet
Limited by available memory
Worksheets referred to by a chart
255
Data series (data series: Related data points that are plotted in a chart. Each data series in a chart has a unique color or pattern and is represented in the chart legend. You can plot one or more data series in a chart. Pie charts have only one data series.) in one chart
255
Data points (data points: Individual values that are plotted in a chart. Related data points make up a data series. Data points are represented by bars, columns, lines, slices, dots, and other shapes. These shapes are called data markers.) in a data series for 2-D charts
32,000
Data points in a data series for 3-D charts
4,000
Data points for all data series in one chart
256,000

PivotTable and PivotChart report specifications and limits
Feature
Maximum limit
PivotTable reports (PivotTable report: An interactive, crosstabulated Excel report that summarizes and analyzes data, such as database records, from various sources, including ones that are external to Excel.) on a sheet
Limited by available memory
Unique items per field
1,048,576
Row (row field: A field that's assigned a row orientation in a PivotTable report. Items associated with a row field are displayed as row labels.) or column fields (column field: A field that's assigned a column orientation in a PivotTable report. Items associated with a column field are displayed as column labels.) in a PivotTable report
Limited by available memory
Report filters in a PivotTable report
256 (may be limited by available memory)
Value fields in a PivotTable report
256
Calculated item (calculated item: An item within a PivotTable field or PivotChart field that uses a formula you create. Calculated items can perform calculations by using the contents of other items within the same field of the PivotTable report or PivotChart report.) formulas in a PivotTable report
Limited by available memory
Report filters in a PivotChart report (PivotChart report: A chart that provides interactive analysis of data, like a PivotTable report. You can change views of data, see different levels of detail, or reorganize the chart layout by dragging fields and by showing or hiding items in fields.)
256 (may be limited by available memory)
Value fields in a PivotChart report
256
Calculated item formulas in a PivotChart report
Limited by available memory
Length of the MDX name for a PivotTable item
32,767
Length for a relational PivotTable string
32,767

Shared workbook specifications and limits
Feature
Maximum limit
Users who can open and share a shared workbook (shared workbook: A workbook set up to allow multiple users on a network to view and make changes at the same time. Each user who saves the workbook sees the changes made by other users.) at the same time
256
Personal views (view: A set of display and print settings that you can name and apply to a workbook. You can create more than one view of the same workbook without saving separate copies of the workbook.) in a shared workbook
Limited by available memory
Days that change history (change history: In a shared workbook, information that is maintained about changes made in past editing sessions. The information includes the name of the person who made each change, when the change was made, and what data was changed.) is maintained
32,767 (default is 30 days)
Workbooks that can be merged at one time
Limited by available memory
Cells that can be highlighted in a shared workbook
32,767
Colors used to identify changes made by different users when change highlighting is turned on
32 (each user is identified by a separate color; changes made by the current user are highlighted with navy blue)
Excel tables in a shared workbook
0 (zero)
 Note   A workbook that contains one or more Excel tables cannot be shared.