Sat 25 Oct 2014

Tips & Tricks 62 - Set the Scroll Area of a Worksheet

By |Saturday, October 25th, 2014|Categories: Tips and Tricks|Tags: , , , |0 Comments

If you want to set a scroll area in a worksheet, for example A1:G50. User will not be able scroll beyond row 50 and column G.

To do this -

1. Right Click on a Sheet tab and click View Code.

OR

Press ALT+F11.

Now VBE Editor will open.

2. Set A1:G50 in Scroll Area to set Scroll Area to A1:G50.

1

3. If you want to reset this, you will have to make ScrollArea blanks.

CATCH - If you close the workbook and open it again, you will find, ScrollArea is blank i.e. this property gets reset. To handle this problem, you will need to do 3 lines of VBA coding and no need to follow steps 2 and 3 of above.

In VBA Editor which you opened in step 1, double click on This Workbook and copy and paste the below code -

Private Sub Workbook_Open()
Sheet1.ScrollArea = "A1:G50" 'Sheet1 can be replaced with your sheet name
End Sub

Save your file with .xlsm extension.

Close the Workbook and reopen again to make the code effective.

But a person knowing the above trick can follow step 1 and remove the code. To handle this problem -

In VBA Editor > Tool > VBA Project Properties > Protection > Check Lock Project for Viewing box and give passwords. Now, unless a person knows the password, he can not remove the code.

Mon 20 Oct 2014

Challenge 1 - Single Formula for Fibonacci Numbers

By |Monday, October 20th, 2014|Categories: Challenges|Tags: |2 Comments

Fibonacci Numbers is a series of numbers where next number is found by adding two previous numbers. It starts with 0, followed by 1 and summing up 0 and 1 gives next number as 1. Now sum up previous two numbers 1 and 1 and result is is 2. Next number would be 1+2 = 3 and next would 2+3 =5 and so on..Hence, below are Fibonacci Numbers.

0, 1, 1, 2, 3, 5, 8, 13, 21, 34, 55, 89...

A good introduction to Fibonacci Numbers is found at

http://www.mathsisfun.com/numbers/fibonacci-sequence.html

The series can be generated very easily by putting 0 in A1 and 1 in A2 and a formula =A1+A2 in A3 which can be dragged down to produce this series.

But it requires a formula to be put into A3 which is not a good way for Excel users like you. In Excel, our endeavour always have to be to find a formula which can be put in first cell and can be dragged down.

Now, the challenge for you is to find that formula which can be put in A1 and dragged down to generate this Fibonacci Sequence.

Note - You may post the answer in the comments section.

Sat 18 Oct 2014

Tips & Tricks 61 - Roman Representation of Numbers

By |Saturday, October 18th, 2014|Categories: Tips and Tricks|Tags: , , |0 Comments

Use ROMAN function.

Hence ROMAN(56) will give LVI.

ROMAN works only for numbers 1 to 3999.

Sat 18 Oct 2014

Tips & Tricks 60 - Remove numbers from string

By |Saturday, October 18th, 2014|Categories: Tips and Tricks|Tags: , , , , , |0 Comments

To remove numbers from a string (for example Vij1aY A. V4er7ma8 contains numbers which are not required), we can use nested SUBSTITUTE function to remove numbers. Use below formula assuming string is in A1 cell -

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(
SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(
A1,1,""),2,""),3,""),4,""),5,""),6,""),7,""),8,""),9,""),0,"")

Note - Since this formula is in multiple lines, hence you will have to copy this in Formula Bar. If you copy this formula in a cell, it will copy this in three rows.

Sat 11 Oct 2014

Article 6 - Convert Text Format into Number Format

By |Saturday, October 11th, 2014|Categories: Articles|Tags: , , , , , , |0 Comments

This article was long pending after I wrote Covert Number Format into Text Format.

This post essentially deals with the fact that if a cell has number in text format, how to convert that value into Number format so that it becomes usable for calculations and for other purposes. (Exclusion - If a number has been entered as accounting format 12- i.e. not with leading minus sign, this article doesn't cover this)

Once again, we will look into this problem statement from 4 angles -

1. Formula Way
2. Manual Way
3. Hybrid Way (Both Manual and formula ways combined together)
4. VBA Way

But first, after conversion, how will you know the cell value is converted into number or not. You can use any one method from below -

(more…)

Sat 11 Oct 2014

Tips & Tricks 59 - Remove Comments from All Sheets in a Workbook

By |Saturday, October 11th, 2014|Categories: Tips and Tricks|Tags: , , , , |0 Comments

In an earlier tip, I talked about how to remove all comments from a sheet. But that works on a sheet. If you want to remove from all sheets in one go rather than doing for every sheet separately, use following trick -

1. Office Button > Prepare > Inspect Document

1

2. A warning message will come, press Yes.1

3. In the next box which comes, DESELECT all box except Comments and Annotations. Press Inspect.1

4. If comments are found, it will say so. Press Remove All. This will remove All comments from the Workbook.1

Sat 11 Oct 2014

Tips & Tricks 58 - Remove Alphabets from a String

By |Saturday, October 11th, 2014|Categories: Tips and Tricks|Tags: , , , |0 Comments

If your string is in cell A1, use following formula to remove all alphabets from a string

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(
SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(
SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(
SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(
SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(
SUBSTITUTE(LOWER(A1),"a",""),"b",""),"c",""),"d",""),"e",""),"f",""),
"g",""),"h",""),"i",""),"j",""),"k",""),"l",""),"m",""),"n",""),"o",""),
"p",""),"q",""),"r",""),"s",""),"t",""),"u",""),"v",""),"w",""),"x",""),"y",""),"z","")

Sat 04 Oct 2014

Tips & Tricks 57 - Remove All Comments from a Sheet

By |Saturday, October 04th, 2014|Categories: Tips and Tricks|Tags: , , |0 Comments

1. Home Tab > Find and Select > Comments - This will select all the cells where comments are there.

1

2. Home Tab > Clear > Clear Comments - This will clear all comments from the sheet

1

Sat 04 Oct 2014

Tips & Tricks 56 - Rank within the Groups

By |Saturday, October 04th, 2014|Categories: Tips and Tricks|Tags: , |0 Comments

Suppose your have data like below table and you want to know rank of students.

1

You will simple put following formula in D2

=RANK(C2,C2:C100)

But what if you are asked to produce rank of students within each school. Hence, every school's rank will start with 1..

Put following formula in D2 for that case for Descending order ranking. (For ascending order, replace ">" with "<" without quote marks)

=SUMPRODUCT((B$2:B$100=B2)*(C$2:C$100>C2))+1

OR

=COUNTIFS(B$2:B$100,B2,C$2:C$100,">"&C2)+1