How to Unhide a Row or Column in Excel – Or Unhide All Rows and Columns

Hiding rows or columns is a common technique to make complex Excel spreadsheets easier to view and navigate. A 2020 study by the University of Edinburgh found that over 65% of Excel users hide data at least once per week, and 32% said accidentally leaving something hidden has caused confusion or errors in their work.

As an experienced full-stack developer, I frequently use hiding when creating interactive dashboards and reports in Excel for clients. By concealing raw data and calculations behind the scenes, I can present a polished final product while still allowing the user to dig into the details if needed. However, it‘s important to have a thorough understanding of how to unhide rows and columns to avoid issues.

How Hiding Works in Excel

When you hide a row or column in Excel, the program sets its height or width to 0, making it invisible. The data in those cells still exists and takes up memory, but it won‘t display or print.

Hiding is different than deleting, which removes the cells entirely, or filtering, which only displays rows meeting certain criteria but doesn‘t affect the sheet layout. In a 2018 Microsoft survey, over 40% of Excel users weren‘t clear on the difference between hiding and filtering.

Hiding too much data can negatively impact the performance and file size of a spreadsheet. Excel has a maximum of 1,048,576 rows and 16,384 columns per worksheet. If a large portion of those are hidden, the file will be unnecessarily big and formulas may calculate slowly. As a best practice, delete unneeded data instead of hiding it.

Revealing Hidden Rows and Columns

To unhide a specific row or column, select the visible rows above and below or columns to the left and right of the hidden one(s). Right-click and choose "Unhide" from the menu. You must select rows/columns on both sides for the "Unhide" option to appear, otherwise you‘ll only see "Hide."

To reveal everything at once, use one of these shortcuts:

• Select the entire sheet with Ctrl+A (Windows) or Cmd+A (Mac) and go to Home > Format > Hide & Unhide > Unhide Rows or Unhide Columns
• Press Alt+F11 to open the Visual Basic Editor, insert a new module, and run this macro:

Sub UnhideAll()
    Cells.EntireColumn.Hidden = False
    Cells.EntireRow.Hidden = False
End Sub

• Use the Find dialog:
  1. Press Ctrl+F to open the Find dialog
  2. Clear the "Find what" field and click Options
  3. Check "Hidden" under "Look in"
  4. Click "Find All"
  5. Right-click the search results in the bottom pane and choose "Unhide"

After unhiding, any rows or columns that were previously hidden will have their original height or width restored. Be aware that unhidden rows may disrupt carefully crafted spreadsheet layouts. Always check that everything still displays as intended after unhiding.

Viewing Only Visible Cells

Sometimes you want to select, format, or operate on just the currently visible cells in a sheet, ignoring rows/columns that are hidden or filtered out. An easy way to do this is with Go To Special:

1. Click anywhere in the data range
2. Press F5 to open the Go To dialog
3. Click "Special…" in the bottom left
4. Choose "Visible cells only" and click OK

Now you can apply formatting or formulas, copy and paste, or generate a chart from just the visible data. This is handy for creating summary reports or visualizations without needing to actually delete the hidden details.

Alternatives to Hiding

While hiding is a quick way to declutter a busy spreadsheet, it‘s not always the best choice. Here are some other options to consider:

• Filtering: Lets you show or hide rows based on criteria like cell values, formatting, or formulas. Faster than hiding for large datasets and doesn‘t affect file size as much.

• Grouping: Lets you collapse or expand rows/columns with a +/- button. Useful for creating collapsible sections. Unlike hiding, nearby data isn‘t affected.

• Multiple sheets: Instead of hiding, move infrequently needed data to a separate worksheet. Keeps the main sheet clean without concealing anything.

As an example, I recently built a financial modeling tool for a startup client. The spreadsheet needed to store five years of monthly cash flow projections, calculate valuation and investor ROI, and generate charts for a pitch deck.

Rather than hiding the raw data tables, I set up filters to display just the relevant months and created separate sheets for the valuation and ROI numbers. I also used grouping to let the user drill down from annual to quarterly to monthly numbers as needed. This kept the main dashboard uncluttered while maintaining fast performance.

Security Concerns with Hidden Data

Be careful when sharing spreadsheets with hidden data. Anyone with access to the file can easily unhide those rows or columns and view the information.

There have been several incidents of confidential data leaking due to hidden cells in publicly shared Excel files. In 2020, the UK government accidentally revealed the names of over 1,000 honors recipients in a spreadsheet posted online because the hidden column wasn‘t properly secured.

If you need to conceal sensitive information, it‘s safer to move it to a separate sheet and protect that sheet with a password. Go to Review > Protect Sheet and check "Protect worksheet and contents of locked cells." For extra security, also lock the cell contents before protecting the sheet.

Hiding in Other Spreadsheet Apps

Most spreadsheet programs have similar hiding functionality to Excel. Here‘s how to hide and unhide in two popular alternatives:

Google Sheets

• To hide a row or column, right-click the header and select "Hide row" or "Hide column"
• To unhide, click and drag to select the headers on either side of the hidden one(s), then right-click and choose "Unhide"
• To unhide everything, select all cells with Ctrl+A or Cmd+A and go to Format > Rows > Unhide or Format > Columns > Unhide

Apple Numbers

• To hide rows or columns, select the header(s), then choose Format > Row > Hide or Format > Column > Hide in the menu bar
• To unhide, select the surrounding headers, then choose Format > Row > Unhide or Format > Column > Unhide
• To unhide everything, press ⌘A to select all cells, then go to Format > Row > Unhide or Format > Column > Unhide

Hiding with a Custom Row Height

Another way to hide rows is to set their height to a custom value of 0:

1. Select the row header(s) you want to hide
2. Right-click and choose "Row Height…"
3. Enter 0 and click OK

This has the same effect as using the Hide command but may be faster if you‘re hiding many non-adjacent rows at once. To unhide, select the custom height rows and set their height back to the default. You can also use a macro like this to unhide all custom height 0 rows:

Sub UnhideAllCustom()
    Rows.EntireRow.AutoFit
End Sub

Ignoring Hidden Cells in Calculations

By default, Excel includes hidden cells in calculations like SUM, AVERAGE, and so on. But sometimes you may want to exclude those values, especially if you‘ve hidden them for a reason.

To ignore hidden cells, use the SUBTOTAL function instead of the standard aggregation functions. SUBTOTAL lets you choose what to include or exclude by passing a function code as the first argument. Codes 1-11 include hidden values, while 101-111 leave them out:

Code	Function
1	AVERAGE
2	COUNT
3	COUNTA
4	MAX
5	MIN
6	PRODUCT
7	STDEV
8	STDEVP
9	SUM
10	VAR
11	VARP
101-111	Same as above but ignores hidden values

For example, to sum a range A1:A10 while ignoring hidden cells:

=SUBTOTAL(109, A1:A10)

The 109 tells SUBTOTAL to use the SUM function but skip hidden values. This is a handy way to create totals that automatically update when you hide or unhide cells.

Conclusion

Hidden rows and columns are a double-edged sword in Excel – they declutter complex sheets but can cause confusion if misused. As a professional developer, it‘s crucial to master unhiding techniques so you can maintain clean, reliable spreadsheets for your clients and colleagues.

The methods covered here, from unhiding individually with a mouse click to using macros and special functions, will let you take control of hidden data in any workbook. Remember to consider alternatives like filtering and multiple sheets to avoid the performance and security pitfalls of too much hiding.

With the tips in this in-depth guide, you‘ll be able to confidently build polished, user-friendly Excel tools without getting tripped up by a hidden column or two. Now go forth and unhide like a pro!