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
Category: Tips and Tricks
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…
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.