Most content inventories contain at least three columns: Index #, Page title, and URL. The Index column provides a hierarchical numbering system for each piece of content.
For example, in the image above, “Cleaning Supplies” has an Index number of 220.127.116.11, which means it is the first child page under Products (18.104.22.168), which is the first child under Home (0.0.0.0)
When implemented correctly, the Index column helps streamline a variety of UX tasks, including:
- reorganizing a sitemap
- planning a migration strategy
- developing interface and functional specifications
- tying the content inventory to other deliverables such as copy decks and wireframes
Unfortunately, creating and maintaining the hierarchical Index column is tedious to build, difficult to maintain, and prone to errors. The bigger and deeper the site, the greater the difficulty and risk.
To alleviate that headache, I created a user-defined function in Microsoft Excel called GetUXIndex().
GetUXIndex() automatically generates a perfectly-structured Index column by comparing the indentation level of every Page Title with the indentation level of the row above it.
How to set up GetUXIndex()
GetUXIndex() does not come packaged with Microsoft Excel. To use this function, you’ll need to add a user defined function to your Personal Excel Macro Workbook. (If you don’t know how to do that, please check out Microsoft’s instructions on how to create a Personal Macro Workbook.)
To add this user defined function to your Personal Excel Macro Workbook, simply copy and paste the following lines of code into a new worksheet in your personal.xlsb file.
Function getUXindex(Selection, Levels)
‘Selection = The current row for which we are generating this Index #
‘Levels = The number of levels in the hierarchical index specified by the user. For example, 3 = #.#.#
Dim container ‘This is the variable that holds the Index # while we build it
Dim IndentCurrent ‘This is the indent level of the current cell
Dim IndentAbove ‘This is the indent level of the cell above this cell
Dim IndexArray() As String ‘This contains the Index # of the cell above this cell
IndentCurrent = selection.IndentLevel
IndentAbove = selection.Offset(-1, 0).IndentLevel
IndexArray() = Split(selection.Offset(-1, -1), “.”) ‘Grab the index # above the current cell and split it up into an array
IndexContainer = “”
If IndentCurrent = 0 Then ‘If this is the top of the inventory, output “0.0.0.0”
For d = 1 To Levels
If d < Levels Then
container = container & “0.”
container = container & “0”
Else ‘If not, compare the Indent Level of the current cell to the Indent Level
‘of cell above it and increment the variables in the IndexArray as needed
For j = 0 To selection.IndentLevel — 2
container = container & IndexArray(j) & “.”
container = container & IndexArray(j) + 1
For b = selection.IndentLevel To Levels — 1
container = container & “.0”
getUXindex = container ‘Output the fully rendered Index # for this row
How to use GetUXIndex()
Once you’ve added the code above to your Personal Macro Workbook and your Title column is properly indented, you can use the GetUXIndex() function just like any other Excel function such as AVG() or SUM().
GetUXIndex() takes two parameters:
- Cell ID — the cell reference for the Page Title cell (for example, B6)
- Levels — the number of decimal places in the Index cell (for example, 4)
Step One: Starting in column A, type in =personal.xlsb!getUXIndex(B6,4) where B6 is the cell ID containing the root of your hierarchy, and 4 is the number of levels to the site itself.
Step Two: Drag the formula of cell A6 down to the last row of your content inventory.
Step Three: When you finish dragging the formula, the GetUXIndex() function will automatically create a unique index number for each row.
And that’s it.
You can create a fully detailed hierarchical Index for your entire content inventory in seconds. And because this is a computed value, the GetUXIndex() will update itself whenever you add, delete, or move rows in your content inventory. (You may need to click CTRL+SHIFT+ALT+F9 to refresh the formulas in your spreadsheet.)
If you experience problems with the GetUXIndex() function, check the following common problems first:
- Before you share the indexed Content Inventory with your client or team, be sure to copy and paste values column A onto itself. This converts the formulas to plain text. Otherwise, anybody without GetUXIndex() will see an error messages in the A column.
- If you start using GetUXIndex() in row 1, the function will return errors. Make sure that you start using GetUXIndex in row 2 or after. (Working on a fix for that.)
- If you get security related errors, you may need to adjust your macro security settings.
- If your organization prohibits the use of macros or macro-enabled workbooks, you may not be able to use GetUXIndex().
If you need additional help, feel free to write to me at email@example.com.