Uncategorized

How to Unhide columns in Excel (& everything else)!

Sam

27 Sep 2019 • 5 min read

Wondering how to unhide columns in Excel?

Need to unhide columns in Excel? What about whole workbooks, individual cells and even formulas? Confused about why it’s important to locate and unhide all data in Excel? If you answered yes, even once, then this article is for you. I have spent so much time pulling my hair out, working on a spreadsheet, trying to find the reason for an error only to discover that hidden columns in Excel was at fault! I have spoken to and received a lot of input from other experts. I am not alone. Hidden data in Excel is frustrating, especially when you have inherited a workbook from a previous employee. It’s also sometimes tricky to unhide. There are many reasons someone may hide data in excel spreadsheets. The most common reason is to provide a clean presentation of data to your audience, without any unnecessary information cluttering up your visual. Sometimes you simply want to hide sensitive or private data in an Excel spreadsheet.

What types of data can you hide and unhide in Excel?

You can hide columns and rows in Excel, as well as individual cells and entire worksheets. Those can hold a lot of information. Here is a list of some of the types of data you can hide:

Comments
Formulas
Numbers
Hyperlinks
Dates

Typically, these types of data are hidden using standard built-in Excel tools and work very well. But eventually, you will need to unhide all columns and rows in Excel

Why should you unhide columns in Excel?

Before you can unhide columns in Excel you must first find them. You have to do the same thing before you unhide rows in Excel. This is an important step, especially when inheriting spreadsheets and verifying final data. Read more about why finding hidden data is important, and some methods to do just that. Hiding rows and columns in Excel does not delete the data or the formulas within them and sometimes that can come back to haunt you. What if someone forgets to unhide rows in Excel or delete hidden data from a master spreadsheet before its finalised? Such as when purchase agreements are prepared. Often they contain an Excel spreadsheet detailing multiple products and their respective data. Imagine you have a Product spreadsheet, showing all twenty of your products and their commercial pricing options:

But, imagine that Products 5-13 are out of stock. That means that not all data is necessary or relevant to display. A common option is to hide rows and columns in Excel to present only the important data to buyers. Selection_141

As you can see, rows containing unavailable Products are now hidden. Easy! Unhiding them, however, isn’t always so simple. Perhaps something got overlooked while manually unhiding columns and rows in Excel, or perhaps you didn’t even know there was any hidden data in your spreadsheet. Such oversights can lead to devastating financial and reputational backlash. We recently published a blog examining the Excel Spreadsheet horror stories of a number of companies and organizations. One particularly avoidable disaster happened when a spreadsheet containing contract purchase details was converted to a PDF. Almost 200 unwanted contracts were included when they suddenly became unhidden in the conversion.

How to unhide rows in Excel, and everything else!

There are a couple of different ways to unhide all data in excel. Each solution gives you the same final result, but most take several steps to accomplish and not all solutions apply to all types of data. Some data types have different methods of unhiding. To unhide rows in Excel you follow the same process as to unhide columns in Excel, but keyboard shortcuts are different. Unhiding sheets and workbooks are different still. What about the white text on white background in Excel trick?! We recognized a need for an easy, all inclusive tool. So we built an entire toolbox of RPA Bots to fill the void:

ExceleeUH : Unhide everything in your sheet, or workbook. ExceleeCK : Unhide everything in your sheet, or workbook and highlight it with a colour map based on its data type. Simply sign up (free) and send your Excel file to check@excel.officebots.io or unhide@excel.officebots.io depending upon which action you would like to perform. Below is an example of ExceleeCK in action. Excel file with hidden data.

The Bot will return to you the file, unhiding sheets, rows, and columns, and ensuring all fonts are black, and since using ExceleeCK – highlighting cells as follows: Yellow = Text Red = Formulas, Hyperlinks, etc Green = Numbers Blue = Dates Purple = Unknown Grey = Empty Cell ExceleeCK - Excel unhide all

As you can see, there is more to this spreadsheet than meets the eye. Now you can easily identify any issue in your file that may be cause by hidden data.

Alternatives methods & tools to unhide data in Excel

There are a lot of Excel add-ins out there and they can be real time savers. Some can do parts of what ExceleeCK and ExceleeUH can do. However, these options require installing plugins into your Excel, which is not something everyone is comfortable with, especially if you’re already using a lot of other plugins. None we have tried include a one-step process that is easy and quick!

Why OfficeBots?

We think that interacting with bots via email provides the easiest user interaction possible. This is a one-step tool to unhide all sheets in Excel, or whole workbooks and all of the other data within them. There is no need to install anything, or learn and remember code or sequences. Once you are signed up, you will be able to access a full and growing toolbox of useful OfficeBots to help you with PDF, Excel, CSV, Word, image, and audio files! As a 🇩🇪 German GmbH with servers in Germany, we offer a safe environment for the processing of your files. No files are retained once your task is complete and we comply with strict data privacy laws. If you have any tasks you would like to have automated please comment or reach out directly. Keep in mind that we provide Custom OfficeBots to automate specific and more complex tasks, such as invoice processing, or interacting with your database and existing systems to save you as much time as possible in the office! Hope you’ll find our Excelee Bot Family useful. Let us know what you think in the comments!