Suppose, you want to generate non-repeating random numbers between 1 to 30, you can use following formula in A2 and drag down =IFERROR(AGGREGATE(14,6,ROW($1:$30)*NOT(COUNTIF($A$1:$A1, ROW($1:$30))), RANDBETWEEN(1,30-ROWS($1:1)+1)),"") Note: $A$1:$A1 is with reference to A2 as you put formula in A2 and dragged down. Suppose, you had put the formula in G4, this should be replaced with $G$3:$G3….
Category: Tips and Tricks
Tips & Tricks 128 – Used F9 to See Values in the Formula but Values Stick / Formula doesn't gets Restored
We know that great trick that you can select part of the formula and see it values by pressing F9. See the below snip where I have selected part of the formula (see shaded area) and pressed F9 to see its values. Now, if I press enter that part of the formula gets converted to…
Tips & Tricks 127 – Change Default File Extension for Saving
We know that when we save an Excel file, it gets saved as .xlsx file. But sometimes, few users may need to change the default file extension to something else. You can change it in File > Options > Save
Tips & Tricks 126 – Press CTRL+A Three Times to Select Entire Worksheet not Two Times
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…
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…