Auditing an Excel file with a ton of tabs is one of the least enjoyable experiences in Excel. When I worked in consulting, it wasn’t uncommon for me to receive a file from a client containing 50 more spreadsheet tabs. And as an analyst, I had the unfortunate task of having to review all of them.
While there are several different audits you can perform on an Excel file, one thing I like to do whenever I get a tab-heavy file is create an inventory of all of the tabs. Doing so provides several benefits:
- It gives me a sense of how the model is structured
- It allows me to categorize the tabs by function: input, output, calculation, etc.
- After categorizing them, I can usually tell which tabs are the most important
- Also after categorizing them, I can usually cross off a number of unimportant tabs that I don’t need to review
- It encourages me to account for tabs that might be hidden
- It provides me a checklist, so I can track every tab I’ve reviewed
Not every Excel file you receive will warrant an audit at this level of detail, but it’s good to have this tab inventory option for when the situation calls for it.
The purpose of the following macro is to automate this tab inventory process
If you have an Excel file with only a few tabs, your best bet is just to create your inventory manually. If, however, you have a file with huge number of tabs, let’s say 20 or more, you’re probably better off using a macro. The process of copying the name of each tab, over a very large file, can get clunky and you’re much more likely to make a mistake.
Below is the code for the macro that is needed to create an inventory of all of your tabs. I don’t have a many posts about VBA on this blog, but to be safe, my assumption will be that you haven’t used VBA before and don’t have an interest in going through the details of the code. Therefore, I’ll go through the process of implementing the code in detail.
Sub ExcelTabInventoryMacro() Dim wksht As Worksheet On Error Resume Next Application.DisplayAlerts = False NewTabName = "Tab_Inventory" Application.Sheets(NewTabName).Delete Application.Sheets.Add Application.Sheets(1) Set wksht = Application.ActiveSheet wksht.Name = NewTabName For i = 2 To Application.Sheets.Count wksht.Range("A" & (i - 1)) = Application.Sheets(i).Name Next Application.DisplayAlerts = True Sheets("Tab_Inventory").Select Rows("1:1").Select Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove Range("A1").Select ActiveCell.FormulaR1C1 = "Tab Name" Range("B1").Select ActiveCell.FormulaR1C1 = "Type" Range("C1").Select ActiveCell.FormulaR1C1 = "Comments" Range("A1").Select End Sub
How to Implement the Macro
Please note that the example workbook is a macro enabled file. Therefore, to either use the file or implement the code yourself, you’ll need to have macros enabled to be able to use it.
Step 1: Open the Visual Basic interface
The easiest way to do this is to hit ALT + F11. If you have the “Developer” tab enabled, you can also go to that section of the ribbon and click on “Visual Basic”.
To enable the Developer tab, go to File → Options → Customize Ribbon
Then click the checkbox for “Developer”
Step 2: Within Visual Basic, click Insert → Module to create a new macro
Step 3: Paste the macro code above into your newly created module
Step 4: Hit the Play button to run your macro
You can also hit F5 to run your macro.
Step 5: Exit out of the Visual Basic interface
You can either close out of Visual Basic by hitting the “X” in the upper right, or just hit ALT + F11 again.
Once you return to Excel, you’ll see you have a new tab in your file titled “Tab_Inventory”, with the initial inventory headers in place for you. (I only put in the titles of the headers, and none of the formatting I’d typically do, as I wanted to keep the code light) From here you can use this baseline to build out your tab inventory.