Sun 25 Oct 2015

Article 28 - How to Unhide all Tabs (Worksheets)

By |Sunday, October 25th, 2015|Categories: Articles, VBA|Tags: , , , , , , , , |0 Comments

If you have many hidden worksheets and you want to hide them in one go, Excel doesn't provide an options to do so. There are many ways to to accomplish the same.

Option 1 - Use Custom Views

1. Unhide all of your sheets (you may use Option 2 or Option 3 below for the same also)
2. Views tab > Custom Views > Add and give this view a name (for example "ShowAll")


Sat 08 Aug 2015

Tips & Tricks 104 - Hide a Sheet Securely

By |Saturday, August 08th, 2015|Categories: Tips and Tricks|Tags: , , , , , , , , , , |0 Comments

You want to hide a sheet so that nobody else can open that. This may arise in some situations like you have a dump of employee data which has sensitive information like salary, last rating, age etc. You want to keep a copy of this in your workbook but you want to refer to only few fields which are non-sensitive in nature. Then, you can resort to below trick -

1. Save your sheet as macro enabled i.e. with .xlsm extension.
2. ALT+F11 to open VBA window (This can also be opened by taking right click in the tab and choosing View Code)
3. Locate your workbook name in Project Explore window which is in top left corner. If this is not available, you can either do CTRL+R or View > Project Explorer
4. Click your sheet.
5. You have Sheet Properties window below Project Explorer. If not available, do F4 or View > Properties Window
6. Last option in Properties Window is Visible, which you should set to 2- xlSheetVeryHidden


7. Go back to your workbook / sheet in Project Explorer. Right Click and Insert Module. - This is an important step and many people generally forget about this step.
8. Tools > VBA Project Properties > Protection tab - Check Lock Project for Viewing and put passwords.


Now, your sheet can be unhidden only by those persons who know the password for your VBA project.

Sat 22 Nov 2014

Tips & Tricks 66 - Show the Complete Area of a Worksheet as only Limited Area is being shown

By |Saturday, November 22nd, 2014|Categories: Tips and Tricks|Tags: , , , , |0 Comments

If you can see only limited area of a worksheet

1. Check if the sheet is protected. This you check by going to Review tab and see if Unprotect Sheet Button is appearing.
- If Unprotect Sheet Button is appearing sheet is protected. You need to click it and if password is asked, you need to give the correct password to make the sheet unprotected.
- If Protect Sheet Button is appearing sheet is not protected. Then nothing needs to be done.


2. Press CTRL+A, Right Click on a Column Name A, B, C, D.....and Click Unhide. All columns will be unhidden.

3.  Press CTRL+A, Right Click on a Row Number 1, 2, 3, 4.....and Click Unhide. All rows will be unhidden.


Sat 15 Nov 2014

Tips & Tricks 65 - Show a Limited Area of Worksheet

By |Saturday, November 15th, 2014|Categories: Tips and Tricks|Tags: , , , , |0 Comments

You have a worksheet and you want to show only a limited area to users. Say you want them to see only A1:G50 and remaining you don't want to show them.

Use following steps -

1. Click column H so that entire column is selected. Now CTRL+SHIFT+Right Arrow key so that all columns will get selected right of column H. Now right click and hit Hide. Now all columns right of G will be hidden.

2. Similarly click row 51 so that entire row 51 is selected. Now CTRL+SHIFT+Down Arrow key so that all rows starting 50 are selected. Now right click and hit Hide. Now all rows starting 51 will be hidden.

Using above technique, I have hidden entire area except A1:G20 in below screen shot.


3. If you want to make sure that nobody ever is able to unhide these hidden rows / columns, protect the sheet with password after making the visible area not locked. To make visible area unlocked, select the visible area > Right Click > Format Cells > Protection, here uncheck the Locked Box.


Sat 20 Sep 2014

Tips & Tricks 52 - Show Comments in the Sheet

By |Saturday, September 20th, 2014|Categories: Tips and Tricks|Tags: , , |2 Comments

1. If you want to show all comments in the sheet -

1.1 Go to Review tab and click on Show All Comments.


2. If you want to show selected few comments (not all) in the sheet -

2.1 Click a cell where the comment is.

2.2 Go to Review tab and click on Show / Hide Comments. If step 1 is not followed, Show / Hide Comments will be greyed out. Another option is to right click and you will have Show/Hide Comments in the menu.

Note - This has to be done for all cells individually where you want to show the comments. Selecting all cells and then clicking on Show / Hide Comments will be showing comments only in last selected cell.


Sat 23 Aug 2014

Tips & Tricks 44 - Locate Hidden Rows and Columns

By |Saturday, August 23rd, 2014|Categories: Tips and Tricks|Tags: , , , |0 Comments

To locate hidden rows and columns, you can have following approaches -

1. Locate them manually. Hence, if after row 4, row 6 comes,row 5 is hidden.

2. ALT+EGSY and Enter. It will select all visible cells and it will mark hidden rows and columns with highlight. But on a white background, it may be difficult to judge this highlight. To overcome this, CTRL+A to select complete sheet and fill in a dark colour on the entire sheet and do ALT+EGSY and Enter. Now, you can make out hidden rows and columns. Don't forget to return back to white background after locating hidden rows and columns.

In below Column D and row 5 are hidden. See, it is difficult to make out highlighted column D and row 5.


On a dark background, see column D and row 5 can be clearly made out by dark lines.