The safest bet to select entire worksheet is through pressing CTRL+A three times not two times or one time, if you are using shortcut (The safest bet is to press the triangle between 1 and A as marked in Red in the given picture. The tip is for CTRL+A shortcut.) This peculiarity of CTRL+A shortcut…
Category: Tips and Tricks
Tips & Tricks 125 – Sometimes Excel sorts Lowercase first and Sometimes Uppercase in the same range
You have been given following data and you sort this data in Excel and you were expecting that lowercase will be sorted before uppercase. But Excel is sorting on the basis of FIFO i.e. First In First Out for the same alphabet. Hence if A is coming before a, it will get sorted as A,…
Tips & Tricks 124 – VBA – Check if a Range is Blank When Range Contains Formulas returning Blanks
In VBA, often, we have situations where we need to check if a range is blank or not. The popular formula which we see following is used to check whether range is blank or not. WorksheetFunction.CountA(Range("A1:A100")) Sub CheckifBlank() If WorksheetFunction.CountA(Range("A1:A100")) Then MsgBox "Range is Not Blank" Else MsgBox "Range is Blank" End If End Sub…
Tips & Tricks 123 – Change the Color of Border Lines of Active Cell
Notice the color of Active Cell which is Green. Few people may like some other color. Excel doesn't provide a native facility to change this. But Chip Pearson offers an Add-in which provides this functionality and has many custom options also. http://www.cpearson.com/excel/RowLiner.htm
Tips & Tricks 122 – Always Open a Specific Worksheet when Workbook is Opened
This can only be accomplished through VBA. 1. Save your file as .xlsm 2. ALT+F11 3. Locate your Workbook name in Project Explorer Window 4. Double click on ThisWorkbook 5. Put following code in ThisWorkbook (Replace Sheet Name appropriately) Private Sub Workbook_Open() Worksheets("Sheet Name").Activate End Sub
Tips & Tricks 121 – Macro to Protect / Un-protect All or Selective Sheets
There may be scenarios where you want the facility to protect or unprotect all sheets in one go with added option to choose sheets where to perform this operation. Below is the code to perform this. 1. Save your file as .xlsm 2. ALT+F11 3. Locate your Workbook name in Project Explorer Window 4. Right…
Tips & Tricks 120 – Filter by Color Drop Down Menu Greyed Out Problem
Sometimes, you find that though your are having colored cells but Filter by Color is greyed out i.e. it can not be selected. Following are the possible causes – 1. Your sheets may be grouped. If they are grouped, you will notice word "Group" in the title bar of your Excel workbook. Another way to…
Tips & Tricks 119 – Numerology Sum of the Digits aka Sum the Digits till the result is a single digit
In Numerology, it is often a task to add the digits till the result is a single digit. For example, 74 = 7 + 4 = 11 = 1 + 1 = 2 23 = 2 + 3 = 5 78 = 7 + 8 = 15 = 1 + 5 = 6 1234567 =…
Tips & Tricks 118 – Stop Auto Creation to Hyperlinks
We all know that if you enter a web address like www.microsoft.com, http://www.microsoft.com or even e mail address like admin@eforexcel.com, Excel converts these to Hyperlinks which can be clicked. Sometimes, we don't want this behaviour and we want to stop this – Trick 1 – After entering a web address / e mail address, press…
Tips & Tricks 117 – VBA – How to Count a particular character in a String
When we use formulas inside Excel, we have following stock formula to count the number of times a character appears in a string – =LEN(A1)-LEN(SUBSTITUTE(LOWER(A1),"a","")) In this, we are trying to count the occurrence of character "a" in A1. Let's assume that A1 = "Abraham Arthurway". Hence, the answer which we would get will be…