You are wondering that if you can copy and paste all defined names in the sheet. Do following 1. Formulas Tab > Use in Formula > Paste Names 2. Click on Paste List to paste all defined names.
Author: Vijay A. Verma
Tips & Tricks 12 – Converting Date to a Quarter
Assuming date is in Cell A1. You want to convert it into a quarter (1, 2, 3 & 4). Jan to Mar is 1, Apr to Jun is 2, Jul to Sep is 3 and Oct to Dec is 4. =CEILING(MONTH(A1)/3,1) OR = ROUNDUP(MONTH(A1)/3,0)
Tips & Tricks 11 – Convert a Number to Named Month
I am deleting this post as I have written a proper article on this and below is a link to this https://excelbianalytics.com/wp/article-24-convert-a-number-to-a-month-name/
Tips & Tricks 10 – Convert from Word Tables to Excel
The best way to do this would be through utilities. The best one which I found out and is free is following – http://watermark-images.com/convert-word-to-excel.aspx Another way is to copy Word tables one by one (or in one go if layout permits) into Excel – http://office.microsoft.com/en-in/excel-help/copy-a-word-table-into-excel-HP010254130.aspx
Tips & Tricks 9 – Circular Error is Coming..How to find where is the problem
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.