You are working with your spreadsheet and all of a sudden, you get following error – To know where is the problem, click OK on this. Go to Formulas > Error Checking > Circular Reference This will list a problem. Work on this problem cell and remove circular reference. If there are more problem cells,…
Tips & Tricks 8 – Change Order of Legends in a Chart
1. Click the chart and click on Select data. 2. Here in the left pane, you can click on a legend name and make it up and down through click on up / down arrows.
Tips & Tricks 7 – Breaking Long Formula in Multiple Rows
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…
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.