Hiding Excel Worksheets

I received this question from Lisa:

I have information I need in my workbook (on a separate sheet), but I don't want users to be able to see this data. I know they can figure out some of the numbers by formula results, but I can't allow this information to be blatantly displayed in the workbook. Is there a way to easily hide and unhide the sheet so others can’t see?

Lisa, you'll want to use some sort of password protection to accomplish this. You’d select the sheet with the data and then hide the sheet (Format, Sheet, Hide). You can then protect the workbook after you've hidden the sheet (Tools, Protection, Protect Workbook). Use the password option when you do this. Then to unhide the sheet, unprotect the workbook using your password (Tools, Protection, Unprotect Workbook), then unhide the sheet (Format, Sheet, Unhide).

If you need to be able to unhide quickly for yourself, you can use VB code with the password embedded in the code. If this code is in the workbook, you'll want to use a private sub and password protect your VB code so no one else can use it or see your password, but it would be better to just keep it in a separate workbook or in your personal macro workbook. I've also used code to hide and unhide sheets for users in my budget workbooks, so that a sheet becomes viewable when they need to use it and then “goes away” when they are done.

If you would some of the VB code please post a comment and I will post that, too.

How do you get rid of hyperlinks (lots of hyperlinks)?

When you have one or two hyperlinks, it's no problem to right click and then click "Remove Hyperlink", but I recently encountered a situation where there were many hyperlinks... one in each of 20-50 cells on several sheets of a workbook. Each contained a number or a symbol and a hyperlink. The quickest way to get rid of these is:

1. Type a 1 into a cell (preferably with the same formatting)
2. Copy this cell
3. Select the cells containing the hyperlinks (if they are not next to each other use "Ctrl" to select multiple ranges.)
4. "Paste Special" selecting "Multiply" in the Operation section. Be sure to select "All" or "All Except Borders" in the Paste section.

This works on numbers, symbols or text. Since multiplying by 1 always returns the same value you started with, your data remains unchanged. Just the hyperlink is gone.

LINKS - How to get rid of them quickly...

Links can be very helpful, but sometimes getting rid of them can be a pain. Many do the old copy, paste values to get gid of their links. That's OK for a couple of them, but if you have a lot or worse, you can't find the cell or sheet containing the link, it just doesn't work.

Try this instead: Click on "Edit" then "Links" (or type "/ek" for those who prefer keyboard shortcuts). Your links will appear in the box. You can see the source, type and status. Click on the link to select it and then click "Break Link" (or "{Alt}k").

This will convert all the cells associated to that linked document to values. No more links - voila!

Excel Chick is here!

I have been using Excel for many years now and have created many applications, forms, projects, etc. using Excel, its big brother Access, and sometimes even sharing data with their little sister Word. I also enjoy using Visual Basic to make these guys really fun! It just one big happy family... at least when things work the way you want!

Over the years many people have come to me with their questions and I love to help! If you have an Excel question – just ask!