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…
Author: Vijay A. Verma
Challenge 1 – Single Formula for Fibonacci Numbers
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 =…
Tips & Tricks 61 – Roman Representation of Numbers
Use ROMAN function. Hence ROMAN(56) will give LVI. ROMAN works only for numbers 1 to 3999.
Tips & Tricks 60 – Remove numbers from string
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…
Article 6 – Convert Text Format into Number Format
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…
Tips & Tricks 59 – Remove Comments from All Sheets in a Workbook
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 2. A warning message…
Tips & Tricks 58 – Remove Alphabets from a String
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","")
Tips & Tricks 57 – Remove All Comments from a Sheet
1. Home Tab > Find and Select > Comments – This will select all the cells where comments are there. 2. Home Tab > Clear > Clear Comments – This will clear all comments from the sheet
Tips & Tricks 56 – Rank within the Groups
Suppose your have data like below table and you want to know rank of students. 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…
Tips & Tricks 55 – Quickly Fill in Cells with Dates from M to N
1. Put the start date in the cell where you want your first date to be. Let's put 6/1/14 as an example. 2. Select that Cell and in Home Tab, go to Fill and Select Series 3. Select Series in Columns if you want Column to be populated (Most likely case). Put End Date in in stop…