Your Microsoft Excel File Needs a Hidden Backend Worksheet

Nearly every Microsoft Excel workbook I own—especially those I share with others—contains a hidden backend worksheet. It’s essentially the engine room of my workbook and helps me keep my spreadsheets organized and functional. Here’s why you should have one too.

While hiding a worksheet is a good way to keep the inner workings of a workbook out of view, it’s not a foolproof security measure, so it shouldn’t be used for sensitive information or passwords.

How to Hide Your Backend Worksheet

I’ll talk about the things you should have in your backend worksheet shortly, but first, I’ll show you the two ways to hide it.

Hiding a Worksheet the Well-Known Way

All visible worksheets in an Excel workbook are displayed as tabs across the bottom of the Excel window. However, providing there is more than one worksheet in your workbook, you can hide certain sheets from view in just a couple of clicks.

Simply right-click the backend worksheet tab at the bottom of the Excel window, and click “Hide.”

The sheet then instantly disappears from the worksheet menu.

In case someone unhides your backend worksheet, it’s worth adding a note at the very top to make it clear that they don’t need to make any changes. Something like “This worksheet is for reference only” will do the trick.

If you need to unhide it again, right-click any visible tab, and click “Unhide.” Then, in the Unhide window, select the relevant worksheet (or more than one by holding Ctrl as you click them), and click “OK.”

Bear in mind, however, that many people know about hiding and unhiding worksheets in this way, so I’d only recommend you use this option if the workbook is for your eyes only.

Making a Worksheet Very Hidden

If you plan to share your workbook with others, go one step further and make your backend worksheet very hidden through the VBA editor, as fewer people are aware of this extra level of invisibility.

To do this, first, press F12 to save your workbook. Then, press Alt+F11 to open the VBA window. Before you make a worksheet very hidden, you need to activate the two panes that enable you to change a worksheet’s visibility status. First, click “View” on the ribbon, and select “Project Explorer.” Then, click “View” again, but this time, select “Properties Window.”

Now, in the Project Explorer pane, expand the options until you see a list of the worksheets in your workbook, and click the backend worksheet you want to hide.

Then, in the Properties window, click “xlsSheetVeryHidden” in the Visible drop-down menu.

Finally, click “Save” in the top-left corner of the VBA window, and click “X” in the top-right corner to close it.

Now, even if someone unhides the hidden worksheets by right-clicking a sheet tab, the very hidden sheet won’t reappear.

To show the very hidden sheet again, head back to the VBA window (Alt+F11), select the backend worksheet in the Project Explorer pane, and change the Visible status to “xlSheetVisible” before saving and closing the VBA window.

Keep Your Lookup Tables Out of the Way

For me, the main reason I set up a hidden backend worksheet is so that I’ve got somewhere to store my lookup tables. This is because they often take up valuable real estate if stored in a visible worksheet. What’s more, lookup tables usually contain important information that can be accidentally edited if they’re not safely tucked away.

In this example, I use the XLOOKUP function to generate student grades based on their scores. In fact, I have the same setup on each tab in my workbook.

Storing the reference tables in a hidden backend worksheet—rather than in the same worksheet as the calculations themselves—saves room in each worksheet for information that needs to be seen. Also, if I share my workbook with others, keeping the lookup tables out of the way means they’ll be able to focus on the information that matters.

Hide the Datasets That Feed PivotTables

Continuing the theme of maintaining worksheet tidiness, a hidden backend worksheet is particularly useful if you get your main data insights from PivotTables. After all, nobody needs to see large, clunky datasets if they’ve been tidily converted into a PivotTable on a visible worksheet.

Here, the hidden backend workbook contains a dataset that is 700 rows down and 13 rows across.

On the other hand, this corresponding PivotTable is more condensed, easier to manipulate, occupies fewer cells, and is easier to read.

As a result, the visible version of the dataset is much easier to interpret and manipulate than the hidden version, and it leaves more room in the worksheet for extra data visualization, like a PivotChart, slicers, or a timeline.

As with lookup tables, hiding the source dataset also protects it from unwanted modifications.

Store Values That Drive Calculation

If, like me, you prefer referencing cells in your formulas rather than hard-coding values, a hidden backend worksheet can be a great place to store values that fuel your calculations. What’s more, you can name the precedent cells in the backend worksheet to make referencing them easier.

Let’s say you need to work out the total cost of each product after 20% tax is added. However, because the tax only changes every 12 months, you don’t need constant access to the cell containing this variable.

Instead, you can create a named precedent cell in your backend worksheet. First, select the cell where the calculation value will go, and use the name box in the top-left corner of the Excel window to name it. Then, type the value into the cell, and add text to an adjacent cell so that you know what the value represents.

Now, you can reference this named cell in your formula, even if the worksheet is hidden or very hidden:

=[@Cost]*Tax

Then, when the tax value changes the following year, you can temporarily unhide the hidden backend worksheet to update the value.

Create an End Point for 3D References

As an academic proofreader, I create a new worksheet each month to keep a tally of my monthly word count (cell D2). I then use those monthly values to keep a running total of the number of words I’ve read overall (cell H2).

As a result, when I add a new worksheet for a subsequent month, I need Excel to include it in the running total. This is where my hidden backend worksheet comes in handy.

In cell H2, I typed:

=SUM('Feb 2023:Backend'!D2)

where

• Feb 2023 references the first worksheet in my workbook,
• Backend is the name of my hidden backend worksheet, which was the rightmost tab before it was hidden, and
• D2 is the cell in each worksheet that contains the figure I want to sum.

Because the backend worksheet was the rightmost tab in the workbook when I hid it, it stays in this position, even when I add new worksheets. This means that any monthly worksheets I add subsequently will be picked up in the 3D reference.

Finally, to ensure I don’t accidentally add a value to cell D2 in the hidden backend worksheet, I’ve filled it in red and added a note, “Leave red cell blank.”

Don’t Lose Data That Needs to Be Kept Safe

You can use a hidden backend worksheet to store data that needs to be preserved but doesn’t currently need to be on show in the unhidden worksheets.

Much like when you’re clearing out your attic, there are some things you’re sure you’ll need in the future but don’t need at the moment, so you’ll stick them in a box for safekeeping. The hidden backend worksheet serves the same purpose.

For example, here, I’ve kept the lookup tables from previous years, even though they’re no longer referenced in any formulas.

This means that I can refer back to this data at a later date if necessary, like if I want to analyze historical trends or re-use old figures.

Store Personal Notes and Reminders

A final purpose of a hidden backend worksheet is to store textual information that you can refer to if needed, such as instructions, contact details, reminders, or a list of deadlines. This saves having to create a separate workbook or Word document altogether, and keeping the details out of sight means your workbook stays well-organized.

If you end up having too much information on your hidden backend workbook, simply add more! Just make sure you keep them all well-maintained so that you’re not storing unnecessary data and, consequently, making your Excel file larger than it needs to be.

---

As well as using a hidden backend worksheet, you should also add a homepage worksheet to your Excel file that welcomes collaborators, gives instructions on how to use the workbook, directs people to the best places for support, and contains key information like data snapshots and deadline dates.