How to find what is in the hidden cells? [quick tip]


Share on facebook
Share on twitter
Share on linkedin

You have been there. You are looking at a complex workbook with some hidden rows /columns. You want to know what is in that hidden cells. Alas, the worksheet is protected.

It is like the special coffee machine on top floor reserved for senior executives. You know it is there, but you cannot get to it. Now what?!?

Simple. Follow this process.

(I am talking about hidden cells, not the coffee machine.)
How to unhide a bunch of cells - unprotected worksheet

If the worksheet is not protected:

  1. Select a few rows including the hidden rows (or do the same for columns)
  2. Right click
  3. Unhide.

If the worksheet is protected and you don’t know the password:

  1. Note the address of the hidden cell you want to expose.
  2. Go to name box (it is beside the formula bar)
  3. Type the cell address
  4. Press enter.
  5. The hidden cell’s value (or formula) will be displayed in formula bar.

See this demo to understand.

Finding the contents of hidden cells - tutorial

How to copy protected / hidden ranges?

If your worksheet has a bunch of ranges that are hidden (and protected), then you may not be able to select them. So how to copy such ranges?

Simple. Just follow above process and then,

  1. Instead of entering a single cell address, enter the entire range address.
  2. Press enter
  3. Now press CTRL+C to copy.
  4. Go to a blank sheet, paste. (If you paste at the same location as copied cells, then all the formula references will remain too!)
  5. Done!

Isn’t there way to protect a cell’s dignity?

Unfortunately, Excel is not a fort. It is more like a sandbox. Everyone is welcome to mess with it. So if you want to keep your secrets, find a database or vault. For the rest, rely on Excel.

Now back to that coffee machine

I am not sure if there is a way cheat the coffee machine. But there is a legitimate way to get there. That is to be awesome in your work. And you are already half-way there. So all the best.

Our Excel quick tips go great with all sorts of coffee and tea. Read, sip & repeat.

Share on facebook
Share on twitter
Share on linkedin

Share this tip with your colleagues

Excel and Power BI tips - Chandoo.org Newsletter

Get FREE Excel + Power BI Tips

Simple, fun and useful emails, once per week.

Learn & be awesome.

Welcome to Chandoo.org

Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME.

Read my storyFREE Excel tips book

Chandoo is an awesome teacher

– Jason

Excel formula list - 100+ examples and howto guide for you

100 Excel Formulas List

From simple to complex, there is a formula for every occasion. Check out the list now.

20 Excel Templates

Calendars, invoices, trackers and much more. All free, fun and fantastic.

IRR and data tables in Excel

Using IRR with Data Tables – Modeling Cash-flow Scenarios in Excel

Do you want to simulate multiple cash-flow scenarios and calculate the rate of return? Then this article is for you. In this page, learn how to,

  • Introduction to IRR & XIRR functions
  • Calculate rate of return from a set of cash-flows with XIRR
  • Simulating purchase or terminal value changes with data tables
  • Apply conditional formatting to visualize the outputs
  • Common issues and challenges faced when using XIRR

6 Responses to “How to find what is in the hidden cells? [quick tip]”

  1. 1924MG says:

    How about named ranges that don't refer to cells on the worksheet?

    eg if I create a new name called MyHiddenData that refers to ="Hello World!" (ie not typed in any cell - directly in the new name dialog box), can the user access the contents of the named range if the worksheet and workbook are protected?

    • 1924MG says:

      Never mind - this will get me access to that hidden range. In the VBE just enter:

      Sub RevealMyData()
      MsgBox (ThisWorkbook.Names("MyHiddenData").Value)
      End Sub

  2. Khushnood Viccaji says:

    After reading this I was reminded of another trick that I would often use, back in the days of Excel 97, when data corruption in workbooks was fairly common.

    Often a workbook would open and then crash Excel completely.
    I would be left without any option to repair / fix the workbook -- in those days 'Open and Repair' and 'Previous versions' were not available like in more recent versions of Excel.

    Quite by chance, I read in a book, how to retrieve the data into another workbook, by using link formulas in a very similar manner !

  3. The tip doesn't work if the formulas are HIDDEN and protected

  4. […] How to find what is in the hidden cells? […]

  5. Sean says:

    Or you can just remove the password by editing the XML file

Leave a Reply