How to Unhide All Rows and Columns in Excel or Google Sheets

Being able to unhide all hidden rows and columns in Excel or Google Sheets with just a few clicks can be invaluable. When rows and columns are hidden, it can make analyzing data difficult. Fortunately, Excel and Google Sheets make it simple to unhide all hidden rows and columns in a spreadsheet.

Key Things to Know About Unhiding Rows and Columns

Here are some key things to understand about unhiding rows and columns in Excel and Google Sheets:

• Rows and columns can be hidden manually by the user or through code like VBA macros. This hides them from view but does not delete the data.
• The data in hidden rows and columns is still present in the spreadsheet file. Unhiding brings them back into view.
• There are keyboard shortcuts, commands on the Ribbon, and VBA code to unhide all rows/columns at once.
• Unhiding brings back rows/columns hidden previously by the user or code, but not rows/columns deleted from the spreadsheet.
• Excel offers more options for unhiding such as the Go To Special dialog box, but Google Sheets limits you to the keyboard shortcut or menu commands.
• Unhiding rows/columns may change the aesthetics of your spreadsheet, so check formatting after unhiding.

How to Unhide All Rows in Excel

Here are simple steps to unhide all hidden rows at once in Excel:

Using the Ribbon

1. Select any cell in the spreadsheet.
2. On the Home tab of the Ribbon, click the Format button in the Cells group.
3. Choose Unhide Rows from the dropdown menu.

This will instantly unhide all hidden rows in the entire worksheet.

With Keyboard Shortcuts

1. Select any cell in the spreadsheet.
2. Press Ctrl + Shift + 9 on the keyboard.

This keyboard shortcut unhides all rows in one go.

With VBA Macro Code

Add this VBA macro code to unhide all hidden rows:

Sub UnhideAllRows()
   ActiveSheet.Rows.Hidden = False  
End Sub

Run the macro by going to the Developer tab and clicking Macros (or press Alt+F8).

Unhiding All Columns in Excel

The process to unhide all columns is very similar:

Using the Ribbon

1. Select any cell in the spreadsheet.
2. Go to the Home tab > Cells group > Format button.
3. Choose Unhide Columns from the dropdown.

With Keyboard Shortcuts

1. Select any cell in the spreadsheet.
2. Press Ctrl + Shift + 0 on the keyboard.

Via VBA Macro Code

vb Sub UnhideAllColumns() ActiveSheet.Columns.Hidden = False End Sub

This unhides every hidden column in one action.

Unhiding All Rows and Columns in Google Sheets

Google Sheets provides two straightforward ways to unhide all hidden rows and columns:

With Keyboard Shortcuts

1. Select any cell in the spreadsheet.
2. Press Ctrl + Shift + 9 to unhide all rows.
3. Press Ctrl + Shift + 0 to unhide all columns.

Via the Menu

1. Select any cell in the spreadsheet.
2. Go to the Menu > View > Hidden rows/Hidden columns.
3. Click on Show to unhide all hidden rows or columns.

These methods will instantly make all hidden rows and columns visible again in Google Sheets.

When to Unhide All Rows/Columns in Excel or Google Sheets

Unhiding all hidden rows and columns can be useful in situations like:

• Getting back hidden data – When rows/columns containing data were hidden by mistake or through code/macros.
• Before analyzing data – To ensure complete data is visible before analyzing, summarizing, or visualizing it.
• Fixing formatting issues – Hidden rows/columns may cause formatting issues like wide gaps or misalignment.
• Seeing the full spreadsheet – To view the entire spreadsheet as the author intended before editing or updating it.
• Viewing hidden sheets – Unhiding all rows/columns also unhides entire hidden sheets in a workbook.
• Troubleshooting problems – When facing issues like errors in formulas referring to hidden cells.

Things to Remember When Unhiding Rows and Columns

Here are some tips to keep in mind when unhiding all rows and columns in Excel or Google Sheets:

• Unhide rows/columns before making major edits to avoid unintended changes.
• Be careful when unhiding – it may reveal data meant to be hidden like confidential numbers.
• Unhiding may mess up existing formatting like cell widths, merges, and alignments. Fix accordingly.
• Check for any nested hiding if some rows/columns remain hidden after the unhide action.
• Unhiding deleted rows/columns does not bring them back – it only shows previously hidden ones.
• Save your spreadsheet file before unhiding everything so you can revert in case of issues.
• In Excel, use ‘Go To Special’ to unhide specific hidden rows/columns rather than all at once.

FAQs About Unhiding Rows and Columns

Here are some common questions about unhiding rows and columns in Excel and Google Sheets:

How do I unhide specific rows/columns rather than all at once?

• In Excel – Use the Find & Select menu > Go To Special > Visible cells only. This will let you select and unhide specific hidden rows/columns.
• In Google Sheets – You can only unhide all at once, not specific hidden rows/columns.

I unhid rows/columns but some are still hidden – why?

This happens when rows/columns are nested within other hidden rows/columns. Unhide all twice to ensure everything is visible.

Where did my data go after unhiding rows/columns?

Unhiding does not delete or modify data – it only makes hidden data visible again. If data seems missing, it may have been edited/deleted before rows/columns were hidden initially.

How do I permanently delete hidden rows/columns and their data?

Right click on the row/column header and select Delete instead of unhiding. But double check first as deleted data cannot be recovered.

Can VBA code re-hide rows/columns after I unhide everything?

Yes, if the VBA macro contains code to re-hide specific rows/columns after unhiding, it will hide them again when the macro runs.

Conclusion and Next Steps

Unhiding all hidden rows and columns in Excel or Google Sheets is simple with just a few clicks using keyboard shortcuts or menu commands. This can help recover lost data, fix formatting issues, and analyze complete datasets.

Some best practices are checking for nested hidden rows/columns, watching for formatting disruption, and saving your file before unhiding everything. Be wary of confidential data and remember deleted rows/columns cannot be recovered.

Now that you know how to unhide all hidden rows and columns, you can better work with tricky spreadsheets. Next explore how to freeze rows/columns, sort and filter data efficiently, and master keyboard shortcuts for easy spreadsheet navigation.