Excel Hide Columns with No Data (4 Effective Ways)

There are 16384 columns in an Excel spreadsheet. Naturally, we don’t need all the columns of Excel for our calculation. For a cleaner and better look, it is better to hide the extra columns of Excel except for the dataset’s required columns. In this article, I will show you 4 effective ways to hide columns with no data in Excel.


Download Practice Workbook

You can download our practice workbook from here for free!


4 Effective Ways to Hide Columns with No Data in Excel

Suppose, you have a dataset of 10 employees’ salaries. So, on a column basis, you have your last data in the C column. Now, you want to hide all the other columns except the first 3 columns. You can accomplish this using any of the four effective ways described below.

Sample Dataset to Hide Columns in Excel with No Data

We have used the Office 365 version of Microsoft Excel here. But, you don’t need to worry. You can apply all these 4 ways in any version of Excel. But, if you face any problems doing so, please leave a comment below.


1. Hide Columns with No Data Using Format Tool

You can use the Format tool to hide unnecessary columns in your Excel file. Follow the steps below to do so.

📌 Steps:

  • First and foremost, click on the D column header.

Select the D Column

  • As a result, the entire column is selected.
  • Subsequently, press Ctrl + Shift + Right arrow.

Press Ctrl + Shift + Right Arrow to Hide Columns with No data in Excel

  • At this time, you can see you have selected all the unused columns of your file.
  • Afterward, go to the Home tab >> Cells group >> Format tool >> Hide & Unhide option >> Hide Columns option.

Use Format Tool to Hide Excel Columns with No Data

Thus, you will be able to hide all the columns with no data in Excel successfully. And, the outcome would look like this.

Dataset with Hidden Columns

Read More: How to Hide Multiple Columns in Excel (5 Easy Methods)


2. Hide Columns with No Data Using Hide Command

You can also use the context menu to hide unused columns. Go through the columns to achieve this result.

📌 Steps:

  • At the very beginning, click on the header of the D column.

Click the Header of D Column to Hide Columns with No Data in Excel

  • At this time, press Ctrl + Shift + Right Arrow to select all the columns right to the D column.

Press Ctrl + Shift + Right Arrow

  • Now, hover your mouse on any column header.
  • Afterward, click your right mouse button.
  • Subsequently, choose the Hide option from the context menu.

Choose the Hide Option to Hide Columns in Excel with No Data

As a result, you will see that all the columns except the first three columns are hidden. For example, the outcome should look like this.

Dataset with Hidden Columns

Read More: Excel VBA to Hide Columns Based on Criteria (6 Useful Examples)


Similar Readings


3. Use a Keyboard Shortcut to Hide Columns with No Data

Besides, you can use a keyboard shortcut to hide the blank columns of your Excel file. Just follow the simple steps below to do this.

📌 Steps:

  • Initially, click on the D column header.

Select the Column D Header

  • Afterward, press Ctrl + Shift + Right Arrow.

Press Ctrl + Shift + Right Arrow to Select Required Columns

  • Consequently, you will see that you have selected all the columns right to the D column including itself.
  • Now, press Ctrl + 0.

Use Keyboard Shortcut to Hide Columns with No Data in Excel

As a result, you will see that you have hidden all the unused columns of your dataset. For instance, it would look like the following outcome.

Dataset with Hidden Columns

Note:

Here, when pressing 0, you can not press the Num 0 here. Rather, you have to press the 0 under the Function buttons.

Read More: How to Hide Selected Columns in Excel (5 Easy Methods)


4. Apply a VBA Code

Last but not least, you can use a VBA code to hide the unused columns of your dataset. Follow the steps below to do this.

📌 Steps:

  • First, go to the Developer tab >> Visual Basic tool.

Access the Visual Basic Tool

  • As a result, the Microsoft Visual Basic for Applications window will appear.
  • Afterward, click on Sheet5 from the Microsoft Excel Object options.
  • Subsequently, the code window will appear.
  • Now, write the following code here.
Sub Hide_Blank_Columns()
    Columns("D:XFD").Hidden = True
End Sub

Write VBA Code in VBA window to Hide Columns with No Data in Excel

  • Afterward, press Ctrl + S to save the code.
  • Subsequently, go to the File tab.

Access the File Tab

  • Now, choose the Save As option from the expanded File tab.

Choose the Save As Option

  • As a result, the Excel Save As window will appear. Click on the Browse option.

Excel Save As Window

  • At this time, the Save As dialogue box will appear.
  • Choose the Save as type: as .xlsm file and click on the Save button.

Save the Excel as .xlsm to Enable Macro

  • Now, go to the Developer tab >> Visual Basic tool.

Access the Visual Basic Tool

  • As a result, the Microsoft Visual Basic for Applications will open again.
  • Select the Sheet5 option and click on the Run icon button.

Run the VBA Code to Hide Columns with No Data in Excel

  • Consequently, the Macros window will appear.
  • Choose the desired macro from the Macro Name: options and click on the Run button.

Run the Macro to Hide Columns with No data in Excel

Thus, you will see that all the columns with no data have been hidden.

Dataset with Hidden Columns

Read More: Excel VBA: Hide Columns Based on Cell Value (15 Examples)


How to Unhide Hidden Columns

Now, you might need to unhide the columns again for some purposes. To unhide the columns again, follow the steps below.

📌 Steps:

  • Initially, click on the last column (C column here) header and drag your mouse rightward.

Select the Hidden Columns

  • Afterward, right-click your mouse button.
  • Subsequently, choose the Unhide option from the context menu.

Choose the Unhide Option to Unhide Columns

Thus, you will be able to unhide all the columns again. And, the result should look like this.

Dataset with Unhidden Columns

Read More: How to Unhide Columns in Excel All at Once (4 Quick Ways)


Conclusion

To conclude, in this article, I have shown 4 effective ways to hide columns with no data in Excel. I would suggest you go through the full article carefully and practice thoroughly. I hope you find this article helpful and informative. If you have any further queries or recommendations, please feel free to comment here.
And, visit ExcelDemy for many more articles like this. Thank you!


Related Articles

Tanjim Reza

Tanjim Reza

Hello! I am Md. Tanjim Reza Tanim. I have just completed my B.Sc from Naval Architecture & Marine Engineering Department, BUET. Currently, I am working as an Excel & VBA content developer. I always had a great fascination with Microsoft Excel and its cool functions and formulas. Here, I am learning every day about new functions and formulas and working on applying MS Excel to the analysis of our real-life problems. I have great enthusiasm for learning any kind of new things, writing articles, and solving real-life problems.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo