Top 10 # Xem Nhiều Nhất Xóa Unhide Trong Excel Mới Nhất 3/2023 # Top Like

Download Article

Download Article

Unhiding a Specific Row

Find the hidden row. Look at the row numbers on the left side of the document as you scroll down; if you see a skip in numbers (e.g., row 23 is directly above row 25), the row in between the numbers is hidden (in 23 and 25 example, row 24 would be hidden). You should also see a double line between the two row numbers.

It’s in the drop-down menu. Doing so will prompt the hidden row to appear.

Unhide a range of rows. If you notice that several rows are missing, you can unhide all of the rows by doing the following:

This tab is just below the green ribbon at the top of the Excel window.

If you’re already on the Home tab, skip this step.

This option is in the “Cells” section of the toolbar near the top-right of the Excel window. A drop-down menu will appear.

You’ll find this option in the Format drop-down menu. Selecting it prompts a pop-out menu to appear.

Adjusting Row Height

This tab is just below the green ribbon at the top of the Excel window.

If you’re already on the Home tab, skip this step.

This option is in the “Cells” section of the toolbar near the top-right of the Excel window. A drop-down menu will appear.

It’s in the drop-down menu. This will open a pop-up window with a blank text field in it.

Enter the default row height. Type 14.4 into the pop-up window’s text field.

Community Q&A

Add New Question

The top 7 rows of my Excel worksheet have disappeared. I’ve tried to “unhide” from the Format menu, but nothing happens. What do I do?

You’ll have to unlock the cells (via the format pop-up), then hide them all before unhiding them.

There is a possibility you did not hide the rows but reduced your rows’ height to minimum. Select all rows above and below of your 7 rows and increase rows height from format menu. It will re-adjust the height of rows and your rows will be visible.

This article was written by Jack Lloyd. Jack Lloyd is a Technology Writer and Editor for wikiHow. He has over two years of experience writing and editing technology-related articles. He is technology enthusiast and an English teacher. This article has been viewed 309,392 times.

How helpful is this?

Updated: November 17, 2020

Thanks to all authors for creating a page that has been read 309,392 times.

Robert Theriault

“I had some rows hidden and couldn’t figure out how to unhide the rows. The article solved that problem for me. Thanks” …” more

If you prefer written instruction instead, below is the tutorial.

Hidden rows and columns can be quite irritating at times.

Especially if someone else has hidden these and you forget to unhide it (or even worse, you don’t know how to unhide these).

While I can’t do anything about the first issue, I can show you how to unhide columns in Excel (the same techniques can also be used to unhide rows).

It may happen that one of the methods of unhiding columns/rows may not work for you. In that case, it is good to know the alternatives that can work.

How to Unhide Columns in Excel

There are many different situations where you may need to unhide the columns:

Multiple columns are hidden and you want to unhide all columns at once

You want to unhide a specific column (in between two columns)

You want to unhide the first column

Let’s go through each for these scenarios and see how to unhide the columns.

Unhide All Columns At One Go

If you have a worksheet that has multiple hidden columns, you don’t need to go hunt each one and bring it to light.

You can do that all in one go.

And there are multiple ways to do this.

Using the Format Option

Here are the steps to unhide all columns at one go:

No matter where that pesky column is hidden, this will unhide it.

Note: You can also use the keyboard shortcut Control A A (hold the control key and hit the A key twice) to select all the cells in the worksheet.

Using VBA

If you need to do this often, you can also use VBA to get this done.

The below code will unhide column in the worksheet.

Sub UnhideColumns () Cells.EntireColumn.Hidden = False EndSub

You need to place this code in the VB Editor (in a module).

If you want to learn how to do this with VBA, read a detailed guide on how to run a macro in Excel.

Using a Keyboard Shortcut

If you’re more comfortable using keyboard shortcuts, there is a way to unhide all columns with a few keystrokes.

Here are the steps:

Select any cell in the worksheet.

Press Control-A-A (hold the control key and press A twice). This will select all the cells in the worksheet

Use the following shortcut – ALT H O U L (one key at a time)

If you can get hang of this keyboard shortcut, it could be a lot faster to unhide columns.

Note: The reason you need to press A twice when holding the control key is that sometimes when you press Control A, it only selects the used range in Excel (or the area that has the data) and you need to press the A again to select the entire worksheet.

Another keyword shortcut that works for some and not for others is Control 0 (from a numeric keypad) or Control Shift 0 from a non-numeric keypad. It used to work for me earlier but doesn’t work anymore. Here is some discussion on why it may happen. I suggest you use the longer (ALT HOUL) shortcut that works every time.

Unhide Columns in Between Selected Columns

There are multiple ways you can quickly unhide columns in between selected columns. The methods shown here are useful when you want to unhide a specific column(s).

Let’s go through these one-by-one (and you can choose to use that you find the best).

Using a Keyboard Shortcut

Below are the steps:

Select the columns that contain the hidden columns in between. For example, if you are trying to unhide column C, then select column B and D.

Use the following shortcut – ALT H O U L (one key at a time)

This will instantly unhide the columns.

Using the Mouse

One quick and easy way to unhide a column is to use the mouse.

Below are the steps:

Hover your mouse in between the columns alphabets that have the hidden column(s). For example, if Column C is hidden, then hover the mouse between Column B and D (at the top of the worksheet). You will see a double line icon with arrows pointing on left and right.

Hold the left key of the mouse and drag it to the right. It will make the hidden column appear.

Using the Format Option in the Ribbon

Under the home tab in the ribbon, there are options to hide and unhide columns in Excel.

Here is how to use it:

Select the columns between which there are hidden columns.

Hover the cursor on Hide & Unhide option.

Using VBA

Below is the code that you can use to unhide columns in between the selected columns.

Sub UnhideAllColumns() Selection.EntireColumn.Hidden = False End Sub

You need to place this code in the VB Editor (in a module).

If you want to learn how to do this with VBA, read a detailed guide on how to run a macro in Excel.

By Changing the Column Width

There is a possibility that none of these methods work when you try to unhide column in Excel. It happens when you change the Column Width to 0. In that case, even if you unhide the column, it’s width still remains 0, and hence you can’t see it or select it.

Below are the steps to change the column width:

In the name box, type any cell address in that column. For example, if it is column C, type C1.

Although the column is not visible, the cursor would go in between B1 and D1 (indicating that C1 has been selected).

Enter a column width value to make the column visible.

This is by far the most reliable way to unhide columns in Excel. If everything fails, just change the column width.

Unhide the First Column

Unhiding the first column can be a little bit tricky.

You can use many of the methods covered above, with a little bit of extra work.

Let me show you a few ways.

Use the Mouse to Drag the First Column

Even when the first column is hidden, Excel allows you to select it and drag it to make it visible.

To do this, hover the cursor on the left edge of column B (or whatever is the leftmost visible column).

The cursor would change into a double arrow pointer as shown below.

Hold the left mouse button and drag the cursor to the right. You will see that it unhides the hidden column.

Go to a Cell in the First Column and Unhide it

But how do you go to any cell in the column that’s hidden?

Good question!

You use the Name Box (it’s left to the formula bar).

Enter A1 in the Name Box. It will instantly take you to the A1 cell. Since the first column is hidden, you won’t be able to see it, but be assured that it’s selected (you’ll still see a thin line just left of B1).

Once the hidden column cell is selected, follow the below steps:

Hover the cursor on the ‘Hide & Unhide’ option.

Select the First Column and Unhide it

Again! How do you select it when it’s hidden?

Well, there are many different ways to skin the cat.

And this is just another method in my kitty (this is the last cat sounding reference I promise).

When you select the leftmost visible cell and drag the cursor to the left (where there are row numbers), you end up selecting all the hidden columns (even when you don’t see it).

Once you have select all the hidden columns, follow the below steps:

Hover the cursor on the ‘Hide & Unhide’ option.

Check The Number of Hidden Columns

Excel has an ‘Inspect Document’ feature that is meant to quickly scan the workbook and give you some details about it.

And one of the things that you can do that ‘Inspect Document’ is to quickly check how many hidden columns or hidden rows are there in the workbook.

This might be useful when you get the workbook from someone and want to quickly inspect it.

Below are the steps on how to check the total number of hidden columns or hidden rows:

Open the workbook

In the Document Inspector, make sure Hidden Rows and Columns option is checked.

This will show you the total number of hidden rows and columns.

It also gives you the option to delete all these hidden rows/columns. This can be the case if there is extra data that has been hidden and is not needed. Instead of finding hidden rows and columns, you can quickly delete these from this option.

You May Also Like the following Excel Tips/Tutorials:

In case you prefer reading a tutorial over watching a video, below is a detailed written tutorial on unhiding sheets in Excel.

When you work with data that is spread across multiple worksheets in Excel, you may want to hide a few worksheets. This could be to avoid the clutter or to not show some data to your client/manager by hiding some worksheets and only keeping the useful ones visible.

And in some cases, you may have a workbook that has some hidden sheets and you want to unhide some or all of these worksheets.

In this tutorial, I will show you some methods to unhide worksheets in Excel (manually as well as automatically using VBA). I will also show you how to selectively unhide worksheets based on the name or a condition.

So let’s get started!

Unhiding Sheets Manually

If you only have a few worksheets that are hidden, you can manually unhide some or all of these worksheets.

Suppose you have an Excel workbook that has 10 worksheets that are hidden.

Below are the steps to manually unhide worksheets (one at a time):

The above steps would unhide the select worksheet.

Note: Unfortunately, there is no in-built functionality in Excel to quickly unhide all the hidden worksheets (or a way to select more than one worksheet and unhide it). As of now, you need to use the unhide dialog box where you can only select one worksheet to unhide.

Unfortunately, there is no in-built functionality in Excel to quickly unhide all the hidden worksheets (or a way to select more than one worksheet and unhide it). As of now, you need to use the unhide dialog box where you can only select one worksheet to unhide.

While there is no-inbuilt functionality to unhide in bulk, you can easily do this with a simple VBA macro code.

Unhide All Sheets At One Go

With VBA, you can easily unhide worksheets in bulk.

For example, if you have 10 hidden worksheets, you can create a simple VBA code to unhide all the worksheets or you can unhide based on a condition (such as unhide only those where there is a specific prefix or year in the name).

Note: The methods covered in this tutorial doesn’t require you to save an Excel workbook in a macro-enabled format (.XLSM) to use the VBA code.

Using Immediate Window

VB Editor in Excel has an immediate window where you can type a line of code and instantly execute it right away.

Below are the steps to use this above line of code to unhide sheets through immediate window:

In the Immediate window, copy and paste the following line of code: For each Sheet in Thisworkbook.Sheets: Sheet.Visible=True: Next Sheet

Place the cursor at the end of the line

Hit the Enter key

That’s it!

The above steps would instantly unhide all the sheets in the workbook.

Once done, you can close the VB Editor.

The best part about this is that you can do this on any workbook. You don’t need to worry about saving the workbook in a macro-enabled format. Just execute a line of code and instantly unhide all the sheets in the workbook.

Let me also quickly explain the below VBA code that we have used in the immediate window to unhide sheets:

For each Sheet in Thisworkbook.Sheets: Sheet.Visible=True: Next Sheet

The above code uses a For Next VBA loop to go through all the sheets in the workbook and set the visible property to TRUE. Once the visible property of all the sheets is changed, the code will end.

The colon (:) used in the code above is equivalent to a line break. While it looks like a single line of code, it has three parts to it which are separated by two colons.

If you’re interested in learning more about the immediate window and some awesome things you can do with it, here is a detailed tutorial about it.

In case you have to unhide worksheets quite often, another good way could be to have the macro code to unhide sheets in the Personal macro workbook and save the icon in the Quick Access Toolbar.

This is by far the most efficient way to unhide sheets in Excel (most useful when you get a lot of workbooks with hidden sheets and you have to unhide these).

The trick here is to save the code to unhide sheets in the Personal Macro Workbook.

Below is the code that you need to add to the Personal Macro Workbook:

Sub UnhideAllSheets() For Each Sheet In Sheets Sheet.Visible = True Next Sheet End Sub

Below are the steps to add this code to the Personal Macro Workbook:

In the Record Macro dialog box, change the Store macro in setting to – Personal Macro Workbook.

Remove any existing code and copy and paste the above code.

Close the Vb Editor

The above steps allow you to make the Personal Macro Workbook visible in the VB Editor and place the code to unhide sheets in it.

Now all you need to do is add this code to the Quick Access Toolbar so that you can use it anytime from any workbook.

Below are the steps to add this code to the Quick Access Toolbar:

Select the macro code to unhide sheets

The above steps would add this macro code to unhide sheets in the Quick Access Toolbar.

Unhide Sheets With Specific Text in the Name

With VBA, you can also unhide sheets based on the name.

For example, suppose you have a workbook that contains sheets with years in the name and you want to unhide all the ones where the year is 2020.

You can use the below code to unhide all the sheets with the text 2020 in it:

Sub UnhideSheetsWithSpecificText() For Each ws In ThisWorkbook.Worksheets ws.Visible = xlSheetVisible End If Next ws End Sub

The above uses the For Next loop to go through each worksheet in the workbook. The IF Then condition then checks the name of the worksheet and if it contains the specified text (which is 2020 in this code), it will change the visible property to make it visible.

And if the name doesn’t contain the specified text, it will leave it as is.

You can also modify this code to hide sheets based on the text in the name.

For example, if you want to quickly hide all the worksheets where the name contains the text ‘2020’ in it, you can use the below code:

Sub HideSheetsWithSpecificText() For Each ws In ThisWorkbook.Worksheets ws.Visible = xlHidden End If Next ws End Sub

Note: You can save this code in a regular module inside VB Editor or you can save this in the Personal Macro Workbook. In case you save it in a regular module and need to use it again later, you need to save the workbook as a macro-enabled workbook (.XLSM format).

Unhide Selected Sheets (Based on User Selection)

You can also use VBA to give the user the flexibility to choose whether to unhide a sheet or not.

This can be done by showing a message box that asks the user to select whether to unhide a sheet or not. If selected, it unhides that sheet, else it moves to the next one.

Below is the code that will do this:

Sub UnhideSheetsUserSelection() For Each sh In ThisWorkbook.Sheets Result = MsgBox("Do You Want to Unhide " & sh.Name, vbYesNo) If Result = vbYes Then sh.Visible = True End If Next sh End Sub

The above code goes through each sheet in the workbook and checks whether it’s already visible or not. If it’s hidden, then it shows the message box with the name of the worksheet.

As a user, you can now decide whether you want to keep this sheet hidden or unhide it.

This can work well if you have some worksheets that are hidden and you want to take a call for every sheet individually.

Note: You can save this code in a regular module inside VB Editor or you can save this in the Personal Macro Workbook. In case you save it in a regular module and need to use it again later, you need to save the workbook as a macro-enabled workbook (.XLSM format).

Here is a tutorial where I show how to save the code in the regular module in Excel (search for the ‘Where to put this code’ section in this article)

Unhide All or Selected Sheets Using Custom View

This is a less known method in case you want to quickly unhide all the worksheets (or some selected worksheets).

For example, suppose you have an Excel workbook with 10 worksheets. You can create a view where all these 10 sheets are visible. In the future, if you have some sheets hidden and you want o go back to the view where all the sheets were visible, you can do that by selecting the already saved custom view.

Don’t worry, you don’t lose any changes you made after creating the custom view. All custom view does is takes you back to the Excel view when you created it. So if some worksheets were visible when you created the view and are now hidden, selecting that custom view would unhide these sheets.

The intended use of Custom View is to allow users to create different views. For example, if you’re an analyst, you can create different views for different departments in your organization. So you can have a specific set of worksheets (or cells/rows/columns) visible for one department and another set for another department. Once you have these views, instead of changing this manually, you simply activate the view for a department and it will show you worksheets (or rows/columns) relevant for them only.

Below are the steps to create a custom view in Excel:

Unhide all the worksheets to begin with

Enter any name for this view where all the sheets (or selected sheets) are visible

Once the view is created, you can anytime ask Excel to activate this view (which would make all those sheets visible that were visible when you created the view).

Below are the steps to show/activate a custom view:

In the Custom Views dialog box, select the view that you want to show

This would instantly unhide sheets and show those that were visible when you created that custom view.

Unhiding Sheets that are ‘Very Hidden’

Sometimes, despite having some hidden sheets in your workbook, you would not be able to unhide it manually.

This could be because these sheets are not just hidden – these are ‘very hidden’.

You can still unhide these ‘very hidden’ sheets by using the VBA code that we have covered above.

Just copy-paste the below code in the immediate window and hit enter and it would instantly unhide all the sheets (hidden as well as very hidden).

For each Sheet in Thisworkbook.Sheets: Sheet.Visible=True: Next Sheet

I also have a full tutorial on how to hide sheets and make these very hidden (in case you’re interested in learning)

You may also like the following Excel tutorials:

Bottom line: Learn a few different ways to unhide (show) multiple sheets at the same time with a VBA macro or add-in.

Skill Level: Intermediate

Cannot Unhide Multiple Sheets in Excel??

As you probably know, you cannot unhide two or more sheets at the same time in Excel. The Unhide menu only allows you to select one sheet at a time.

#1 – Use the VBA Immediate Window to Unhide All

The fastest way to make all the sheets visible in Excel is to use a macro (VBA). The following line of VBA code uses a For Next Loop to loop through each sheet in the active workbook and make each sheet visible.

For Each ws In Sheets:ws.Visible=True:Next

You can run this code in the VB Editor’s Immediate Window in three easy steps:

Alt+F11 (opens the VB Editor Window)

Ctrl+G (opens the Immediate Window)

Paste the following line of code in the Immediate Window and press EnterFor Each ws In Sheets:ws.Visible=True:Next

The screencast below shows how to implement these steps.

The colon character “:” used in the code allows you to basically combine multiple lines of code into one line. This makes it possible to run in the Immediate Window because the Immediate Window only evaluates one line of code at a time.

#2 – Use a Macro to Unhide Multiple Sheets

If you are scratching your head at that line of code in #1, this section should help explain it better.

The macro below is basically that same line of code, but it is broken up into multiple lines. This makes it much easier to read and understand.

Sub Unhide_Multiple_Sheets() Dim ws As Worksheet For Each ws In ActiveWorkbook.Worksheets ws.Visible = xlSheetVisible Next ws End Sub

Download the file that contains the macro.

Unhide Multiple Sheets chúng tôi (64.2 KB)

The lines in the code above that start with “For” and “Next” represent a For-Next Loop Statement. The first line “For Each ws In ActiveWorkbook.Worksheets” tells the macro to loop through each worksheet in the worksheets collection of the workbook.

When the “Next ws” line of code is hit, the macro jumps back up to the first line of code within the loop and evaluates it again. It continues to loop through all the sheets in the workbook’s worksheet collection (Activeworkbook. Worksheets).

We can then use “ws” inside the loop to change the current worksheet’s properties. In this case we are setting the “Visible” property of the sheet to be visible (xlSheetVisible). The visible property has three different properties to choose from:

xlSheetHidden

xlSheetVeryHidden

xlSheetVisible

Here is the documentation on the VBA Visible property from Microsoft. And checkout my article on the For Next Loop for a detailed explanation of how it works.

Unhide Sheets That Contain a Specific Name

What if we only want to unhide the sheets that contain the word “pivot” in the sheet name?

We can add a simple IF statement to the macro to only unhide sheets that contain a specific name or text.

Sub Unhide_Sheets_Containing() Dim ws As Worksheet For Each ws In ActiveWorkbook.Worksheets ws.Visible = xlSheetVisible End If Next ws End Sub

Download the file that contains the macro.

Unhide Multiple Sheets chúng tôi (64.2 KB)

The InStr function searches for text in a string and returns the position of the first occurrence of the text. It is short for InString, and the function is similar to the SEARCH or FIND functions in Excel.

So in this case we are looking for any sheet that contains the word “pivot” in the sheet name. The “ws.name” reference returns the name of the worksheet that is currently being evaluated in the For-Next loop.

If the word “pivot” is NOT found in the sheet name, then the IF statement will evaluate to False and the code will skip all lines until it gets to the “End If” line. Therefore, the sheet that is currently being evaluated in the loop will keep its current visible property (visible or hidden).

This macro works great if you are hiding and unhiding sheets every day/week/month for a report that you maintain. Run the macro to unhide specific sheets when you open the workbook. After you are finished, run the same code but change the visible property to xlSheetHidden to re-hide the sheets (you can create a new macro for this).

#3 – Use Tab Hound or Tab Control

The screencast below shows how simple this is.

This makes the process of unhiding multiple sheets really fast!

Tab Hound also contains additional ways to filter the sheet list. You can type a search in the search box, filter for all visible or hidden tabs, and even filter by tab color. This makes it easy to find the sheets you are looking for and then perform actions on them like hiding/unhiding.

This video also shows how to quickly hide and unhide multiple sheets with Tab Hound.

If you are producing weekly or monthly reports, and want to make sure all the right sheets are hidden before you send it out, the Tab Control add-in can save you a lot of time.

Here is a scenario that we commonly face…

We need to update a workbook with new data this week and make some changes before emailing it out. Those updates require us to unhide a few sheets, make the changes, then hide the sheets again. It can be a time consuming process if you have to hide/unhide a lot of sheets.

The Tab Control add-in is included with Tab Hound.

Unhiding multiple sheets at the same time in Excel will require code or a macro. There is one other way using Custom Views, but it has limitations if you use Excel Tables (and I love Tables).

Hopefully you learned some VBA code that you can implement. You can also add the macros to your Personal Macro workbook to run them anytime you need.

If coding isn’t your thing then checkout the Tab Hound add-in. It will save you time and make your life a lot easier. (win-win!) 🙂