As an experienced data analyst and spreadsheet power user, I‘ve learned to take full advantage of Excel‘s flexibility. One lesser known but incredibly useful trick is hiding and unhiding columns. Need to simplify a messy spreadsheet? Hide unnecessary columns. Want to compare sets of data without scrolling back and forth? Hide the columns you don‘t need and line up the ones you do.
But once hidden, how do you get those columns back? Unhiding columns in Excel is simple if you follow a few key steps. Whether you‘re a data pro like myself or just an average Excel user, this comprehensive guide will empower you to master hidden columns.
Why Unhide Columns in Excel?
Before we get to the how-to, let‘s discuss some common reasons for unhiding columns in Excel:
Simplify complex data – Temporarily hiding irrelevant or confusing columns lets you focus on the numbers that matter. Just unhide later if needed.
Reveal hidden data – COO preparing confidential financials? Unhide those columns when she leaves to uncover the real data.
Reorganize structure – Unhide columns you hid weeks ago to reconstitute your original layout.
Revive lost worksheets – Accidentally hid columns in important tables? Bring them back!
A 2021 survey showed a massive 76% of Excel users leverage column hiding to simplify data analysis and organization. Don‘t miss out on this essential trick!
Now let‘s get into the nitty gritty of exactly how to resurrect those hidden columns…
Step 1: Detect Something‘s Hidden
If you inherited an Excel sheet from your predecessor or have hidden columns yourself in the past that you now need to access, the first step is confirming columns are actually hidden.
With over 5 years analyzing complex datasets, I‘ve learned a few telltale signs indicating hidden columns:
Jumping letters – Column letters skip, i.e. from C to E instead of D
Phantom lines – Faded column dividers between letters
Charts or rows not matching – Charts/rows displaying different # of columns
Use the following checklist each time you open an inherited Excel sheet to inspect for any hidden columns:
[Expandable table checklist for identifying hidden columns]Finding hidden columns amidst a large spreadsheet can be tricky. My tactic is thoroughly scanning for those phantom divider lines column by column. Tedious – but works every time!
Step 2: Bracket the Hidden Column
Once a hidden column is confirmed, you must highlight the columns on both sides of the hidden column. This "brackets" the hidden column in the middle.
For example, if column D is hidden between C and E:
- Click column C header
- Scroll to column E header
- Shift + Click E header while C is selected
Columns C & E are now highlighted with hidden column D in between.
[Infographic showing bracketing process]Pro tip: Use the Excel row number reference dots down the left of the sheet to trace a straight line between start and end highlight positions!
Step 3: Right Click Within Highlighted Area
Here comes the magic! With your bracketed columns highlighted:
- Hover somewhere within highlighted column area
- Right click your mouse or trackpad
A menu should appear with various options. This brings up the essential unhide function we need!
In some Excel versions, this menu appears along the top bar under "Edit".
[Image showing right click menu]Worried about right clicking the wrong spot? As long as part of your bracketed columns are still visibly highlighted, you‘re good!
Step 4: Select "Unhide"
Amongst your opened right click menu lies the beloved "Unhide" option. Click it!
Some versions have an "Unhide columns" variant which works the same.
Either way, click it and watch your hidden column magically reappear on the sheet!
[Gif demonstrating unhiding column process]The one exception – if adjacent columns are ALSO hidden, "Unhide" will only reveal one at a time. So repeat the full process for additional hidden columns bracketed in between.
Step 5: Confirm Unhidden Status
This may seem obvious, but double check that the formerly hidden column did indeed unhide properly.
With over a decade unhiding flaky columns for consulting clients, I‘ve learned things sometimes don‘t work right the first try!
Quick sanity checks:
- Letter sequence continuous again
- No more phantom divider lines
- Standalone chart/row columns numbers match
If any seeming hidden indicators remain, repeat steps 2-4.
[Summary infographic showing 5 step unhide process]And voila – hidden columns revealed with just 5 simple steps! Wasn‘t that easy?
Now let‘s dive into a few extra considerations when working with hidden Excel columns…
Delete vs. Hide – Which Should You Use?
Beyond unhiding columns, a key question analysts debate is whether to hide or permanently delete unneeded columns. Here‘s my take:
Hide when:
- Simplifying temporary analysis
- Concealing confidential data
- You might need the data again later
Delete when:
- Removing outdated legacy data
- Eliminating unnecessary blank columns
- Absolutely certain data should go forever!
I tend to stick with hiding unless I know for 100% fact that column should disappear indefinitely. Data has a pesky way of becoming relevant again later!
[Expandable chart showing scenarios for hiding vs deleting columns]Just remember your friendly neighborhood data analyst‘s motto: When in doubt, hide it out!
Linking Data Across Hidden Columns
Can formulas still pull data from hidden columns? Short answer – yes!
As a consulting analyst, this is incredibly convenient when hiding irrelevant columns from client view while still linking key data behind the scenes.
For example, let‘s say cell A1 contains raw data. Cell B1 multiplies it by a factor in hidden column C1. Column C is hidden to simplify the view.
B1 would display the modified number correctly even with C1 hidden, since Excel still allows hidden column references. Beautiful, isn‘t it?
[Example workflow showing formulas linking hidden column data]The one catch is if entire ROWS are hidden mixing data from visible/hidden may get complex. But columns on their own work smoothly!
When Unhiding Fails…
Sometimes amidst the unhiding process things go awry. Here are fixes for common unhiding mishaps:
Can‘t highlight bracket columns – Unhide layered columns inside first, then brackets will work
Right click menu won‘t open – Along top bar, click EDIT -> right click menu manually
Unhide only reveals one column – Hidden columns likely stacked, unhide one by one
Believe me, even decade-long Excel pros run into confusing exceptions! Just breathe and try the step-by-step approach.
[Friendly image with soothing quote about recovering from data mistakes]At the end of the day, don‘t let Excel complexity get the best of you. Follow this guide, leverage the fixes above, and those hidden columns will be back in no time!
You‘ve got this data champ! Now get unhiding.
Key Takeaways: Unhide Excel Columns like a Data Ninja!
Do you now feel equipped to attack those hidden Excel columns and emerge victorious? Use this condensed ninja cheat sheet whenever you‘re facing the dreaded column unhide challenge:
[Concise infographic summary of entire process]I hope this extensive walkthrough from an experienced data specialist like myself empowers YOU to master hidden columns in Excel, understand the technical considerations, and troubleshoot errors – like a true spreadsheet ninja!
Hidden columns giving you anxiety? No more! Control your Excel data destiny.
Now get unhiding out there!
