Tanjim Reza
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.
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.
- As a result, the entire column is selected.
- Subsequently, press Ctrl + Shift + Right arrow.
- 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.
Thus, you will be able to hide all the columns with no data in Excel successfully. And, the outcome would look like this.
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.
- At this time, press Ctrl + Shift + Right Arrow to select all the columns right to the D column.
- Now, hover your mouse on any column header.
- Afterward, click your right mouse button.
- Subsequently, choose the Hide option from the context menu.
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.
Read More: Excel VBA to Hide Columns Based on Criteria (6 Useful Examples)
Similar Readings
- Excel VBA to Hide Columns Using Column Number (6 Examples)
- Unhide Columns in Excel Shortcut Not Working (6 Solutions)
- Hide or Unhide Columns Based on Drop Down List Selection in Excel
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.
- Afterward, press Ctrl + Shift + Right Arrow.
- Consequently, you will see that you have selected all the columns right to the D column including itself.
- Now, press Ctrl + 0.
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.
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.
- 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
- Afterward, press Ctrl + S to save the code.
- Subsequently, go to the File tab.
- Now, choose the Save As option from the expanded File tab.
- As a result, the Excel Save As window will appear. Click on the Browse option.
- At this time, the Save As dialogue box will appear.
- Choose the Save as type: as .xlsm file and click on the Save button.
- Now, go to the Developer tab >> 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.
- Consequently, the Macros window will appear.
- Choose the desired macro from the Macro Name: options and click on the Run button.
Thus, you will see that all the columns with no data have been hidden.
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.
- Afterward, right-click your mouse button.
- Subsequently, choose the Unhide option from the context menu.
Thus, you will be able to unhide all the columns again. And, the result should look like this.
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!
