How to completely hide a sheet (Advanced hiding technique) - Microsoft Excel

 Generally, when you hide a excel sheet in the following method. The hide does disappear but can be easily unhidden unless you have protected the workbook.

    

Using the VBA environment, it is possible to hide sheet where in one cannot unhide it using the normal unhide user interface.

Below is the method.

1. Hit Alt+F11, you will be presented with a screen like below.

2. Now click on the sheet which you would want to "deep hide" in the top left section. Now in the bottom left section which has the sheet properties (as shown below),  click on drop down next to "Visible". Select 2.- xlSheetVeryHidden option. Close the window.

3. Now the sheet which was hid using VBA window "Sheet 2" is not available for unhiding.

But you would need to be careful to remember that there is a sheet which is "deep hidden".

Thanks for visiting. If you have any questions reach out to me directly at askme.kkhelps@gmail.com

How to bucket values with LOOKUP function in Microsoft Excel

Lets look at methods to bucket or bin a set of values. Imagine you have the data in the following format.

There are two methods to achieve this:

Lets say we have to bucket the above values into 0-100, 100-500 and greater than 500 categories.

1. Using Nested If function: We have to write several nested IFs from lower value category to higher to categorize. Which I feel takes longer time, complex and error-prone. The length of the formulae is huge as well.

Imagining your value in cell B2 here is the nested IF formula:

=IF(AND(B2>=0,B2<100),"0-100",IF(AND(B2>=100,B2<500),"100-500",">500"))

2. Using LOOKUP function: In this method, the LOOKUP function is used in the following manner to assign categories. The formulae is shorter and neatly organized. Scalability is better in this method than nested IF. 

Imagining your value in cell B2 here is the LOOKUP formula:

=LOOKUP(B2,{0,100,500},{"0-100","100-500",">500"})

LOOKUP formula explained in detail:

Components of LOOKUP formula:

• LOOKUP_VALUE: Cell reference of the value which needs to be categorized.
• LOOKUP_VECTOR: Cut off values for each category. In the above case we are defining the values at 100,500 intervals. Hence the lookup vector would be {0,100,500} in our example.
• RESULT_VECTOR: Category names to be assigned to the intervals we entered in the previous point. In the above example it is {"0-100","100-500",">500"}

Thanks for visiting. If you have any questions reach out to me directly at askme.kkhelps@gmail.com