While writing the tip on Removing Numbers from String, I encountered this problem. My formula was too long to be contained on the page. Hence, I had to span it in many rows. To do this, you just need to press ALT+ENTER at the place where you want to continue the formula to a new…
Author: Vijay A. Verma
Article 2 – Single Underline / Double Underline in Accounting
Accountants were the first and foremost users of spreadsheet programs and they are still the dominant group using Excel. If you are working for accountants, you need to know this concept as these are critical formats for the accountants. They, sometimes, use Single Underlining and Double Underlining in their statement of accounts. Single Underlining is used to…
Tips & Tricks 6 – Assign a Shortcut Key to a Symbol
In many scenarios, you may be using a symbol very often. For example, new Indian Currency Symbol ₹. Every time, you need to insert symbol which is many steps process and introduces inefficiency. You can use following method to assign a short-cut key to a symbol. (Below example is actually not a short cut key but…
Tips & Tricks 5 – Allow Entry of Dates if they are not Weekends
1. Select the Cell > Data Tab > Data Validation > Data Validation 2. Under Settings Tab, in Allow, select Custom 3. Put following formula =WEEKDAY(A1,16)>2 4. Customize your Input and Error Message as per your requirements.
Tips & Tricks 4 – Allow Entry of Date for a Particular Day only
1. Select the Cell > Data Tab > Data Validation > Data Validation 2. Under Settings Tab, in Allow, select Custom 3. Let's assume that we want users to enter only those dates which are Wednesdays. Put following formula in =WEEKDAY(A1)=4 4 is for Wednesdayday. 1 is for Sunday and 7 is for Saturday other…
Tips & Tricks 3 – Add Year to or Subtract Year from a Given Date
In many business problems, you might encounter situations where you will need to add or subtract years from a given date. Let's say A1 contains Date and B1 contains numbers of years. If you want to add Years to a given date, formulas would be – =EDATE(A1,12*B1) =DATE(YEAR(A1)+B1,MONTH(A1),DAY(A1)) If you want to subtract Years from…
Tips & Tricks 2 – Add Month to or Subtract Month from a Given Date
Very often, you will have business problems where you have to add or subtract month from a given date. One scenario is calculation for EMI Date. Say, you have a date of 10/22/14 (MM/DD/YY) in A1 and you want to add number of months which is contained in Cell B1. The formula in this case…
Tips & Tricks 1 – A List is Unique or Not (Whether it has duplicates)
Assuming, your list is in A1 to A1000. Use following formula to know if list is unique. =MAX(FREQUENCY(A1:A1000,A1:A1000)) =MAX(INDEX(COUNTIF(A1:A1000,A1:A1000),,)) If answer is 1, then it is Unique. If answer is more than 1, it is not unique.
Article 1 – Convert Number Format into Text Format
The very first post which I want to write about is conversion of numbers into text. For example, if have a numeric value of 2.23, it should be converted to text value of 2.23. We may need this in many data manipulation scenarios. I will not go into why we need this but directly jump…