Workbook Info for Excel | metadata through custom functions

Workbook Info add-in icon

Content

What is Workbook Info for Excel

Workbook Info for Excel is a free add-in that exposes workbook, worksheet, table, chart, pivot table, slicer, and name metadata through read-only custom functions and Excel Entity data types.

The functions are read-only and designed for building live documentation, audit sheets, and workbook diagnostics directly in the grid.

Key features

  • Custom functions that return:
    • Workbook-level metadata (name, author, save date, query count, external link count)
    • Worksheet metadata (used range, used rows/columns, protection state, tab color, visibility)
    • Object metadata for tables, charts, pivot tables, slicers, and defined names
    • INFO functions that return Excel Entity values
  • Entity properties can be accessed with dot notation (for example =A1.usedRange) or with FIELDVALUE
  • Nested collections on workbook and worksheet entities (for example worksheets/worksheetCount, tables/tableCount)
  • Task pane Actions tab: insert a demo / Table of Contents worksheet and recalculate the workbook

Why use Workbook Info for Excel

  • Document workbook internals quickly, right into your cell grid using functions, without VBA or Office Scripts
  • Create formula-based audit sheets that stay live as workbook structure changes
  • Use one consistent metadata interface across desktop and web Excel

Example:

=JKP.WORKBOOKINFO()

Installation instructions

Excel for web and Teams

Install Workbook Info (Teams, Web Excel) now

0000000

A new Excel workbook will open in your browser containing this information:

Excel shows the welcome screen and prompt to accept terms for the Workbook Info add-in

Click the "Accept and Continue" button in the task pane to use the add-in. After clicking, Workbook Info will also be available in Excel within Teams.

Manual installation

If the button does not work, open an Excel workbook in your browser and click the Add-ins button. Then search for Workbook Info:

Excel online Home tab with the Get Add-ins button, searching for the Workbook Info add-in

Click Add to install the add-in in Excel for the web.

Desktop Excel

Install Workbook Info (Desktop Excel) now

0000000

After clicking this button, your browser will ask for permission to open Excel. Click Open and Excel will start and show this:

Excel shows the add-in after installation
Click the "Accept and Continue" button in the task pane to use the add-in.

Manual installation

If the button above does not work, you can also install the tool by opening Excel and clicking Add-ins (on the Home tab). Then search for "Workbook Info":

Excel Home tab with the Get Add-ins button, searching for Workbook Info

To add Workbook Info to Excel, click the Add button. 

Instructions

Open the add-ins task-pane to get a list of functions and their explanations by clicking the Formulas tab, Workbook Info button:

Screenshot: Workbook Info button on Formulas tab of Excel ribbon

Taks pane

Clicking the button opens the task pane. It has three tabs:

Reference tab

Screenshot: Workbook Info task pane displaying the reference tab
This tab display the function reference. Clicking on a function reveals a card displaying information about the function.

Actions tab

Screenshot: Workbook Info task pane displaying the Actions tab
The Actions tab currently allows two things:

  1. Inserting a demo worksheet which could serve as the start of a Table of Contents:

    2.  Screenshot: Demo sheet inserted by the Workbook Info add-in
  2. A button that forces a recalculation of all formulas, thus updating the cached object names returned by the functions.

Settings tab

The Settings tab contains a language switcher with an Auto option. Auto follows your Excel app language for the task pane UI. This switcher does not change function names or function-description language, and ribbon language is controlled by the Excel app language.

How to use the functions

All functions belonging to this add-in start with the JKP. prefix. Simply type =JKP. in a cell and the autocomplete list will display them. The addin has two groups of functions. Regular functions and data type functions.

Regular functions

All functions belonging to this add-in start with the JKP. prefix. Simply type =JKP. in a cell and the autocomplete list will display them. The addin has two groups of functions. Regular functions and data type functions. The regular functions often accept an argument. Worksheet selector parameters accept a sheet name or a 1-based index. If omitted, functions use the worksheet containing the formula.

Data type functions (INFO functions)

Functions ending with INFO return data types, which makes it easy to retrieve detailed information pertaining to the data type in question. Here is a screen-shot of the =JKP.WORKBOOKINFO() function result in a cell:

Screenshot: Workbook Info function with details card

Clicking the tiny icon in the cell opens a card.

Inside the card, buttons may appear next to items in the card, allowing you to get that information into the grid:

Screenshot: Workbook Info function with details card containing a button to send items to the cell grid

You can also retrieve information of a data type cell by referring to that cell in a formula and pressing the period:

Screenshot: Workbook Info function demonstrating dot notation

The functions are dynamic-array aware. Some functions return an array of items (such as JKP.WORKSHEETS). You can point subsequent functions at that array and expect Excel to understand.  

Note that these functions are NOT volatile. If workbook data changes, they do not automatically update. To force a refresh, press Control+Alt+Shift+F9 (or use the task pane Actions tab and click Recalculate). Alternatively, do a workbook-wide search and replace (for example, replace JKP. with JKP.).

Function reference

Functions are grouped by category. Each table lists the function name, description, syntax, parameters, and error behavior. For INFO functions, the description includes the available Entity properties.

Workbook functions

Name
Description
Syntax
Parameters
Error behavior
JKP.WORKBOOK
Returns a two-column spilled list of workbook metadata. The left column contains property names, the right column contains the values. Keys include worksheetCount, tableCount, chartCount, pivotTableCount, slicerCount, nameCount, queryCount, and externalLinkCount.
JKP.WORKBOOK()
(none)
(none)
JKP.WORKBOOKINFO
Returns an Excel Entity data type with workbook metadata. It includes scalar summary properties plus nested collections and matching ...Count properties.
Entity properties: name, author, lastSavedBy, lastSaveTime, creationDate, worksheetCount, worksheets, tableCount, tables, pivotTableCount, pivotTables, slicerCount, slicers, chartCount, charts, nameCount, names, queryCount, queries (queries[*] has name, error, refreshDate, rowsLoadedCount, loadedTo, loadedToDataModel), externalLinkCount, externalLinks (externalLinks[*] has id (URL))
JKP.WORKBOOKINFO()
(none)
(none)

Worksheet functions

Name
Description
Syntax
Parameters
Error behavior
JKP.WORKSHEETS
Returns a spilled list of worksheet names.
JKP.WORKSHEETS()
(none)
(none)
JKP.WORKSHEET
Returns the caller worksheet name, or a worksheet name by selector.
JKP.WORKSHEET([nameOrIndex])
Worksheet selector (omit for caller sheet, name: sheet name, or 1-based index).
  • #N/A: Named item does not exist
  • #NUM!: Index out of bounds
  • #VALUE!: Wrong argument type
JKP.OFFSETSHEET
Returns the worksheet name relative to the caller worksheet. Offset is a whole number.
JKP.OFFSETSHEET(offset)
offset: whole number (e.g., -1, 1).
  • #NUM!: Out of bounds
  • #VALUE!: Wrong argument type
JKP.USEDRANGE
Returns the used range address as text.
JKP.USEDRANGE([sheet])
Worksheet selector (omit for caller sheet, name: sheet name, or 1-based index).
  • #N/A: Named item does not exist
  • #NUM!: Index out of bounds
  • #VALUE!: Wrong argument type
JKP.USEDROWS
Returns the number of used rows.
JKP.USEDROWS([sheet])
Worksheet selector (omit for caller sheet, name: sheet name, or 1-based index).
  • #N/A: Named item does not exist
  • #NUM!: Index out of bounds
  • #VALUE!: Wrong argument type
JKP.USEDCOLUMNS
Returns the number of used columns.
JKP.USEDCOLUMNS([sheet])
Worksheet selector (omit for caller sheet, name: sheet name, or 1-based index).
  • #N/A: Named item does not exist
  • #NUM!: Index out of bounds
  • #VALUE!: Wrong argument type
JKP.PROTECTED
Returns TRUE/FALSE indicating whether a worksheet is protected.
JKP.PROTECTED([sheet])
Worksheet selector (omit for caller sheet, name: sheet name, or 1-based index).
  • #N/A: Named item does not exist
  • #NUM!: Index out of bounds
  • #VALUE!: Wrong argument type
JKP.WORKSHEETINFO
Returns an Excel Entity data type with worksheet metadata plus nested collections and matching ...Count properties for tables, charts, pivot tables, slicers, and sheet-scoped names.
Entity properties: name, usedRange, usedRows, usedColumns, protected, tabColor, visibility, tableCount, tables, chartCount, charts, pivotTableCount, pivotTables, slicerCount, slicers, nameCount, names
JKP.WORKSHEETINFO([nameOrIndex])
Worksheet selector (omit for caller sheet, name: sheet name, or 1-based index).
  • #N/A: Named item does not exist
  • #NUM!: Index out of bounds
  • #VALUE!: Wrong argument type

Table functions

Name
Description
Syntax
Parameters
Error behavior
JKP.TABLES
Returns table names on the specified worksheet, or all workbook tables when omitted. Returns #N/A! when there are no matching items for the requested parent (for example, no tables on that worksheet).
JKP.TABLES([sheet])
Worksheet selector (omit for all workbook tables, name: sheet name, or 1-based index).
  • #N/A: Named item does not exist
  • #N/A!: No items found for the requested parent (for example, no tables on that worksheet)
  • #NUM!: Index out of bounds
  • #VALUE!: Wrong argument type
JKP.TABLEROWS
Returns the number of data body rows in a table.
JKP.TABLEROWS(tableName)
tableName: table name.
  • #N/A: Table does not exist
  • #VALUE!: Wrong argument type
JKP.TABLECOLUMNS
Returns the number of columns in a table.
JKP.TABLECOLUMNS(tableName)
tableName: table name.
  • #N/A: Table does not exist
  • #VALUE!: Wrong argument type
JKP.TABLEADDRESS
Returns the table range address as text.
JKP.TABLEADDRESS(tableName)
tableName: table name.
  • #N/A: Table does not exist
  • #VALUE!: Wrong argument type
JKP.TABLECOMMENT
Returns the comment associated with the table. This uses a Preview API; if the API isn't supported in your Excel build, this function returns an empty string.
JKP.TABLECOMMENT(tableName)
tableName: table name.
  • #N/A: Table does not exist
  • #VALUE!: Wrong argument type
JKP.TABLEINFO
Returns an Excel Entity data type with table metadata. Place in a cell, then access properties using dot notation or FIELDVALUE().
Entity properties: name, worksheet, rowCount, columnCount, address, comment
JKP.TABLEINFO(tableName)
tableName: table name.
  • #N/A: Table does not exist
  • #VALUE!: Wrong argument type
JKP.TABLESINFO
Returns a matrix of Excel Entity data types, one per table. Spills vertically. Each entity has the same properties as TABLEINFO.
Entity properties: name, worksheet, rowCount, columnCount, address, comment (one entity per row in the spilled result)
JKP.TABLESINFO([sheet])
Worksheet selector (omit for all workbook tables, name: sheet name, or 1-based index).
  • #N/A: Named item does not exist
  • #NUM!: Index out of bounds
  • #VALUE!: Wrong argument type

Chart functions

Name
Description
Syntax
Parameters
Error behavior
JKP.CHARTS
Returns chart names on the specified worksheet, or all workbook charts when omitted. When omitted, charts are returned as Sheet::ChartName. Returns #N/A! when there are no matching items for the requested parent (for example, no charts on that worksheet).
JKP.CHARTS([sheet])
Worksheet selector (omit for all workbook charts, name: sheet name, or 1-based index).
  • #N/A: Named item does not exist
  • #N/A!: No items found for the requested parent (for example, no charts on that worksheet)
  • #NUM!: Index out of bounds
  • #VALUE!: Wrong argument type
JKP.CHARTTYPE
Returns the chart type.
JKP.CHARTTYPE(chartName)
chartName: chart name.
  • #N/A: Chart does not exist
  • #VALUE!: Ambiguous chart name; more than one chart has the same name
  • #VALUE!: Wrong argument type
JKP.CHARTTITLE
Returns the chart title text.
JKP.CHARTTITLE(chartName)
chartName: chart name.
  • #N/A: Chart does not exist
  • #VALUE!: Ambiguous chart name; more than one chart has the same name
  • #VALUE!: Wrong argument type
JKP.SERIESCOUNT
Returns the number of series in a chart.
JKP.SERIESCOUNT(chartName)
chartName: chart name.
  • #N/A: Chart does not exist
  • #VALUE!: Ambiguous chart name; more than one chart has the same name
  • #VALUE!: Wrong argument type
JKP.CHARTINFO
Returns an Excel Entity data type with chart metadata. Place in a cell, then access properties using dot notation or FIELDVALUE().
Entity properties: chartId, name, worksheet, chartType, title, seriesCount, topLeftCell
JKP.CHARTINFO(chartName)
chartName: chart name.
  • #N/A: Chart does not exist
  • #VALUE!: Ambiguous chart name; more than one chart has the same name
  • #VALUE!: Wrong argument type
JKP.CHARTSINFO
Returns a matrix of Excel Entity data types, one per chart. Spills vertically. Each entity has the same properties as CHARTINFO.
Entity properties: chartId, name, worksheet, chartType, title, seriesCount, topLeftCell (one entity per row in the spilled result)
JKP.CHARTSINFO([sheet])
Worksheet selector (omit for all workbook charts, name: sheet name, or 1-based index).
  • #N/A: Named item does not exist
  • #NUM!: Index out of bounds
  • #VALUE!: Wrong argument type

Pivot table functions

Name
Description
Syntax
Parameters
Error behavior
JKP.PIVOTTABLES
Returns pivot table identifiers on the specified worksheet, or all pivot tables when omitted. Identifiers are returned as Sheet::PivotName. Returns #N/A! when there are no matching items for the requested parent (for example, no pivot tables on that worksheet).
JKP.PIVOTTABLES([sheet])
Worksheet selector (omit for all workbook pivot tables, name: sheet name, or 1-based index).
  • #N/A: Named item does not exist
  • #N/A!: No items found for the requested parent (for example, no pivot tables on that worksheet)
  • #NUM!: Index out of bounds
  • #VALUE!: Wrong argument type
JKP.PIVOTLOCATION
Returns the address of the pivot layout range as text.
JKP.PIVOTLOCATION(pivot)
Pivot selector (recommended: Sheet::PivotName; also accepts a single pivot name or a spilled range of pivot identifiers).
  • #N/A: Pivot table does not exist
  • #VALUE!: Ambiguous pivot table name; more than one pivot table has the same name
  • #VALUE!: Wrong argument type
JKP.PIVOTSOURCE
Returns the pivot source as a range/table address, or "Data model" when the source isn't a local range or table.
JKP.PIVOTSOURCE(pivot)
Pivot selector (recommended: Sheet::PivotName; also accepts a single pivot name or a spilled range of pivot identifiers).
  • #N/A: Pivot table does not exist
  • #VALUE!: Ambiguous pivot table name; more than one pivot table has the same name
  • #VALUE!: Wrong argument type
JKP.PIVOTSOURCETYPE
Returns LocalRange, LocalTable, or Unknown.
JKP.PIVOTSOURCETYPE(pivot)
Pivot selector (recommended: Sheet::PivotName; also accepts a single pivot name or a spilled range of pivot identifiers).
  • #N/A: Pivot table does not exist
  • #VALUE!: Ambiguous pivot table name; more than one pivot table has the same name
  • #VALUE!: Wrong argument type
JKP.PIVOTINFO
Returns an Excel Entity data type with pivot table metadata including location, source, and layout settings.
Entity properties: pivotId, name, worksheet, id, locationAddress, filterAxisAddress, rowLabelsAddress, columnLabelsAddress, dataBodyAddress, sourceType, source, allowMultipleFiltersPerField, enableDataValueEditing, refreshOnOpen, useCustomSortLists, layoutType, showRowGrandTotals, showColumnGrandTotals, showFieldHeaders, preserveFormatting, subtotalLocation
JKP.PIVOTINFO(pivot)
Pivot selector (recommended: Sheet::PivotName; also accepts a single pivot name or a spilled range of pivot identifiers).
  • #N/A: Pivot table does not exist
  • #VALUE!: Ambiguous pivot table name; more than one pivot table has the same name
  • #VALUE!: Wrong argument type
JKP.PIVOTSINFO
Returns a matrix of Excel Entity data types, one per pivot table. Spills vertically. Each entity has the same properties as PIVOTINFO.
Entity properties: pivotId, name, worksheet, locationAddress, sourceType, source (one entity per row in the spilled result)
JKP.PIVOTSINFO([sheet])
Worksheet selector (omit for all workbook pivot tables, name: sheet name, or 1-based index).
  • #N/A: Named item does not exist
  • #NUM!: Index out of bounds
  • #VALUE!: Wrong argument type

Slicer functions

Name
Description
Syntax
Parameters
Error behavior
JKP.SLICERS
Returns slicer identifiers on the specified worksheet, or all slicers when omitted. Identifiers are returned as Sheet::SlicerName. Returns #N/A! when there are no matching items for the requested parent (for example, no slicers on that worksheet).
JKP.SLICERS([sheet])
Worksheet selector (omit for all workbook slicers, name: sheet name, or 1-based index).
  • #N/A: Named item does not exist
  • #N/A!: No items found for the requested parent (for example, no slicers on that worksheet)
  • #NUM!: Index out of bounds
  • #VALUE!: Wrong argument type
JKP.SLICERITEMS
Returns a two-column spilled list of slicer items and whether each item is selected.
JKP.SLICERITEMS(slicer)
Slicer selector (recommended: Sheet::SlicerName; also accepts a single slicer name or a spilled range of slicer identifiers).
  • #N/A: Slicer does not exist
  • #VALUE!: Ambiguous slicer name; more than one slicer has the same name
  • #VALUE!: Wrong argument type
JKP.SLICERINFO
Returns a matrix of Excel Entity data types with slicer metadata (position, style, filter state). For a single slicer selector it returns a 1x1 matrix; if slicer is a spilled range/dynamic array, it returns a spilled list of entities. The items property (when available) is a collection of ITEMINFO entities (each item has name and selected; selected may be blank for slicer types/hosts where selection state isn't available).
Entity properties: slicerId, name, worksheet, id, caption, topLeftCell, left, top, width, height, style, sortBy, isFilterCleared, itemCount, items (items[*] is ITEMINFO with name, selected)
JKP.SLICERINFO(slicer)
Slicer selector (recommended: Sheet::SlicerName; also accepts a single slicer name or a spilled range of slicer identifiers).
  • #N/A: Slicer does not exist
  • #VALUE!: Ambiguous slicer name; more than one slicer has the same name
  • #VALUE!: Wrong argument type
JKP.SLICERSINFO
Returns a matrix of Excel Entity data types, one per slicer. Spills vertically. Each entity has the same properties as SLICERINFO, except it intentionally does not include items/itemCount. Call JKP.SLICERINFO() (or JKP.SLICERITEMS()) when you need slicer items.
Entity properties: slicerId, name, worksheet, caption, style, isFilterCleared, topLeftCell (one entity per row in the spilled result)
JKP.SLICERSINFO([sheet])
Worksheet selector (omit for all workbook slicers, name: sheet name, or 1-based index).
  • #N/A: Named item does not exist
  • #NUM!: Index out of bounds
  • #VALUE!: Wrong argument type

Name functions

Name
Description
Syntax
Parameters
Error behavior
JKP.NAMES
Returns defined names. Use "WORKBOOK" for workbook-scoped only, or a sheet name for sheet-scoped names. Returns #N/A! when there are no matching items for the requested parent (for example, no names in that scope).
JKP.NAMES([scopeOrSheet])
scopeOrSheet (optional): "WORKBOOK" or worksheet name.
  • #N/A: Sheet does not exist
  • #N/A!: No items found for the requested parent (for example, no names in that scope)
  • #VALUE!: Wrong argument type
JKP.NAMEREFERS
Returns the RefersTo formula/text for a defined name.
JKP.NAMEREFERS(name)
name: defined name.
  • #N/A: Name does not exist
  • #VALUE!: Wrong argument type
JKP.NAMETYPE
Returns a classification: RANGE, CONSTANT, or FORMULA/LAMBDA.
JKP.NAMETYPE(name)
name: defined name.
  • #N/A: Name does not exist
  • #VALUE!: Wrong argument type
JKP.NAMEVISIBLE
Returns TRUE/FALSE indicating whether a defined name is visible.
JKP.NAMEVISIBLE(name)
name: defined name.
  • #N/A: Name does not exist
  • #VALUE!: Wrong argument type
JKP.NAMECOMMENT
Returns the comment associated with a defined name.
JKP.NAMECOMMENT(name)
name: defined name.
  • #N/A: Name does not exist
  • #VALUE!: Wrong argument type
JKP.NAMEINFO
Returns an Excel Entity data type with defined name metadata. Place in a cell, then access properties using dot notation or FIELDVALUE().
Entity properties: name, formula, value, type, visible, comment, scope, sheet
JKP.NAMEINFO(name)
name: defined name.
  • #N/A: Name does not exist
  • #VALUE!: Wrong argument type
JKP.NAMESINFO
Returns a matrix of Excel Entity data types, one per defined name. Spills vertically. Use "WORKBOOK" for workbook-scoped only, or a sheet name for sheet-scoped names. Each entity has the same properties as NAMEINFO.
Entity properties: name, formula, value, type, visible, comment, scope, sheet (one entity per row in the spilled result)
JKP.NAMESINFO([scopeOrSheet])
scopeOrSheet (optional): "WORKBOOK" or worksheet name.
  • #N/A: Sheet does not exist
  • #VALUE!: Wrong argument type

Change history

  • Build 1.0.0.0, 2026-04-27: First release

Credits

Workbook Info is developed by Jan Karel Pieterse (JKP Application Development Services).

Thanks to users and testers who provided feedback on formulas, entity behavior, and task pane UX.

Comments

Loading comments...