Aug 12

Excel – Hidden Sheets

Tips & Tricks: Using Excel 2010, I recently ran into a situation where I was importing an Excel Worksheet into the Quality Center as a “Data table” resource and noticed that in the “Resource Viewer” for the worksheet after importing it, there were two worksheets listed but my spreadsheet only showed one.  Initially it drove me crazy trying to find where it could be.  I then realized that maybe there was a worksheet missing.  I quickly researched this as I had never dealt with a hidden worksheet before and quickly found there is an easy way to Hide and Unhide worksheets (this also applies to Rows and Columns).

Solution: The solution to revealing a hidden worksheet is very simple, just follow these steps (remember this applies to Excel 2010, the steps are slightly different for older versions of Excel):

  1. Open the Excel spreadsheet you want to work with
  2. Select the “Home” tab
  3. In the “Cells” group (normally on the right), click on the “Format” button
  4. A dropdown will appear and now you want to click on the “Hide & Unhide” optionExcel Unhide Sheet
  5. It will display a “pull right” and in here you will see all your options for hiding and unhiding rows, columns and sheets
  6. If you have a hidden sheet, then at the bottom of the list “Unhide Sheet” will be enabled
  7. Click on it and a dialog box will appear with a list of all the worksheets that are hidden
  8. Select the worksheet you want to display and click the “OK” button
  9. Now the worksheet will appear
  10. If there are no more worksheets hidden, then the “Unhide Sheet” option will be disabledExcel Unhide Sheet option disabled

Test Automation Relevance: This is important to know how to handle because if you are working with the Quality Center “Test Resources” module and you import a spreadsheet, it will bring in all the worksheets including the hidden ones.  Typically I use this feature with QuickTest Pro and I only want to deal with one worksheet, so for the spreadsheet I’m importing into the Quality Center I make a copy of it, delete the hidden worksheet and then “Upload” the file as a new resource.  Then when I access it from QTP I don’t run into any issues as there is only one worksheet to deal with.

by sloporto | About the author:

Related Posts

  • No related posts found.

You must be logged in to post a comment.

Name (required)

Email (required)


XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

Share your wisdom