Say, you have written a formula (or put a value) in cell B1 and you want to fill in this formula till B10000. You can drag the formula but higher the number of cells, more tedious it becomes. Fortunately, we have a way to do this very fast. 1. Put formula in B1. 2. CTRL+C…
Category: Tips and Tricks
Tips & Tricks 135 – I input (c) or (C) and it gets converted to Copyright Symbol..I don't want this
There are many ways to overcome this. 1. a. Put following in a cell and press Enter c) b. Go back to the cell and type first bracket to complete (c) 2. a. You can type (c) preceded with some special character and press enter. b. Go back to that cell and delete that special…
Tips & Tricks 134 – Last Working Day of the Year
If a year is given in A1 say 2016, below formula can be used to know the last working day of the year (format the result as date) =WORKDAY("1JAN"&A1+1,-1) The above formula assumes that your weekends are Saturday and Sunday. But, if your weekends are different (e.g. in gulf countries), you can use following formula…
Tips & Tricks 133 – First Working Day of the Year
If a year is given in A1 say 2016, below formula can be used to know the first working day of the year (format the result as date) =WORKDAY(EOMONTH("1JAN"&A1,-1),1) The above formula assumes that your weekends are Saturday and Sunday. But, if your weekends are different (e.g. in gulf countries), you can use following formula…
Tips & Tricks 132 – Financial Year Formula (e.g. 2015-16 or FY16)
A good number of countries don't follow calendar year as the financial year. For example, India's financial year start is 1-Apr and finishes on 31-Mar. Hence, currently (20-Feb-16), the financial year is 2015-16 (It is also written as FY16). On 1-Apr-16, it will become 2016-17 (It is also written as FY17). Now if a date…
Tips & Tricks 131 – Repeat a Number and Increment and Repeat….
Suppose, you have been given the task of repeating a number and increment that number and repeat it. For example – 1,1,1,1,2,2,2,2,3,3,3,3…..(Here, we are repeating it 4 times and incrementing and repeating 4 times again and so on) Then you can use following formula =ROUNDUP(ROWS($1:1)/4,0) Suppose, you want to start the number with 5 not…
Tips & Tricks 130 – Generate Sequential Numbers and Repeat them
Suppose, you have been given the task to generate a sequence of numbers and repeat them. For example – 1,2,3,4,1,2,3,4,1,2,3,4 You can use the below formula and drag down – =MOD(ROWS($1:1)-1,4)+1 Replace 4 with with any other number to generate any other sequence. Hence, if you want to generate 1,2,3,4,5,6,7,8,9,10,1,2,3,4,5,6,7,8,9,10,1,2,3,4,5,6,7,8,9,10 then formula becomes – =MOD(ROWS($1:1)-1,10)+1…
Tips & Tricks 129 – Generate Non Repeating Random Numbers through Formula
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….
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