Excel hides things. Don't help it!
Love It Or Hate It
Microsoft Excel is ubiquitous. Its highly visible output and lack of constraints on variable names, types, or placement make it an unintimidating entry to data work. However, its strengths are its weaknesses. Visible output hides formulas, and disembodied numbers float around without names, format, or constraints. The lack of labels or linear progression across cells or worksheets tests the forbearance of even the greatest puzzle enthusiasts.
Because Excel offers a cornucopia of hiding options, the burden is on you to design worksheets that keep data and results tidy, understandable, and visible. To help you via things-not-to-do, here are my top 10 ways to overcome the hide-and-seek champion:
10. Formula Arrays Don't Automatically Expand
When you append or refresh your data, Excel won't automatically adjust formula arrays to match (Arrays are the range of cells you've selected). You may only realize this issue if you visually inspect the formula array, which is often hard because the array is referencing a worksheet you cannot see while writing your formula. (An Excel table object will incorporate new data rows, but you may find table objects will try to help you in other unwanted and invisible ways, too.) A design choice to adopt is to put each data source on its own worksheet then insert rows (the array will expand to match, but you risk duplicating some data).
In this example, the new data row is outside the original selected cells array—bad times.
9. Hardcoding Numbers In Formulas
Every Excel formula is a pocket dimension capable of containing many things. A common finding is hardcoded inputs, which are easily missed if the formula is long or complex. Seeing only the results may not communicate that such inputs are lurking around. Design with transparency in mind. Also, keep your formulas short and columns well labeled so what is behind each mystery cell should be obvious.
Note how the five is hiding in the formula. Instead, move that input somewhere visible and give it a meaningful label.
8. Too Much Worksheet, Not Enough Screen
A single worksheet can hold a diverse cast of data, inputs, calculations, or lookups. Each one needs pixel real estate. Like urban sprawl, your single spreadsheet grows downwards and out into alphabetically ascending columns (BC is good for history, bad for Excel). The danger is when you look at a spreadsheet from near cell A1 but miss things hiding in the unseen parts of the worksheet.
This example shows the selected area a 34-inch ultrawide monitor can see at 100% zoom.
7. Covered By An Object
Excel is a poor choice for plots, charts, and graphs at scale. Yet you can make something nice if you know the right incantations. Moreover, you want to proudly display your results without the underlying data distracting your audience. Excel allows you to hide such a thing by dragging your chart atop your data to smother it visually.
Building a chart on a worksheet apart from the underlying data on a different worksheet can be a pain. You can build the chart and then cut and paste it elsewhere. You can also present it in a printed PDF or PowerPoint.
6. Invisible Text
Many analyses require a type of data scaffolding to keep things organized. However, some think that in high society, it is improper to expose your guests to such unseemly infrastructure. So, some people change the font color to match the background to render such nuisances invisible and entirely prone to being accidentally overridden.
Instead of hiding it, spread the love and make space for it in your tables. If you have one-off inputs in your workbook, consider parking them on a separate worksheet for parameters or assumptions.
5. Hidden Columns and Rows
Why rely on tricks when Excel offers native options for hiding things? Next April Fools' Day, hide some rows and columns in a friend's workbook and watch as simple calculations or cut-and-pastes go awry.
Hiding rows and columns is usually done to hide placeholders or to bring two items closer together to compare. Consider designing your spreadsheet to keep comparable things together, use filters, or design a separate summary. If you hide a row or column, make a prominent note nearby because Excel's sliver of pixels is easily overlooked (you can see it in the example between columns C and E).
4. Hidden Worksheets
Sometimes, external stakeholders will use your workbook, and you want to protect against meddlers. You can hide entire tabs so only the interesting bits show. All this hiding is reasonable until, over time, you forget something is hidden (out of sight, out of maintenance). Nothing inspires your client's confidence like them unhiding a worksheet to find #REF!, #VALUE!, #NAME! and other spreadsheet profanities.
Consider if unhiding and protecting the worksheet can help with transparency or build stakeholder trust. If hiding the sheet is mission-critical, ensure your process or documentation keeps it visible to those updating the workbook.
3. Visual Basic for Applications (VBA)
Often referred to as macros, VBA subroutines are powerful yet fragile. Excel is so natural at hiding things; its ribbon Developer tab is hidden by default. Great, hide the tools that allow you to do more hiding. Even if you get the VBA editor open (hint: Alt + F11), you must find the sheet or module housing the code and then decipher the code. If you think someone's spreadsheets are bad, wait until you read their code.
Consider if a change in workbook design can overcome the need for custom VBA macros. Otherwise, if learning VBA, focus on referencing things (cells, worksheets, and so forth), arrays, loops, and conditional statements. Give a clear and visible explanation in the workbook about how the macro works.
2. Hidden Names
Since you cannot see if a value has a named range in its formula anyways, why not hide the named ranges too? This trick can be really fun, especially since a single copied worksheet can spread all these named ranges like the fourteenth-century Excel plague. (Not to be confused with the excessive-border-formatting plague of 1900.)
Instead of hiding named ranges, just don't. Just. Don't.
1. Super Secret Hiding
Sometimes, worksheets are too sensitive, special, or sacred to be left in the hands of mere Excel mortals. Enter the very hidden worksheet. One does not simply hide a very hidden worksheet; only VBA runes can invoke such power!
But you didn't learn any of this from me...
This website reflects the author's personal exploration of ideas and methods. The views expressed are solely their own and may not represent the policies or practices of any affiliated organizations, employers, or clients. Different perspectives, goals, or constraints within teams or organizations can lead to varying appropriate methods. The information provided is for general informational purposes only and should not be construed as legal, actuarial, or professional advice.
