You have a sheet and you want to put a space after each character. For example, A1 contains 12345, you want to make it 1 2 3 4 5. B1 contains Mango12 and you want to make it M a n g o 1 2. The same need to be accomplished by a simple macro….
Category: Tips and Tricks
Tips & Tricks 153 – Sum only Visible Columns
You can use SUBTOTAL or AGGREGATE function to sum visible rows but Excel doesn't provide the facility to sum only visible columns. Look below. Columns B and D are hidden. Hence, our function should sum up only A, C, E and F. G2 has the formula =SUM(A2:F2) If you hide the columns, the sum stays…
Tips & Tricks 152 – While Printing a Sheet, Don't Print Page Number on First Two Pages and Print 1 on 3rd Page
One user had a specific requirement. His first two pages were cover page and Table of Contents. Hence, he wanted to start his page number from 3rd page. He wanted to display page X of Y. Hence, if the worksheet had 10 pages, his 3rd page should say 1 of 8……Last Page should say 8…
Tips & Tricks 151 – Saving in ODS (OpenDocument Spreadsheet) but Don't Need Excel Warning Message – "Do you want….."
Question – Sometimes, I may require to work in different Office Suites and to maintain compatibility, I use Open Document Format. But whenever, I save in Open Document Format (ODS – OpenDocument Spreadsheet), I get the Excel warning like below. I don't need this. What is the way out? Answer – This requires a registry…
Tips & Tricks 150 – Save Each Worksheet as Different Workbooks
You have got a workbook and you want to save each worksheet as different workbook – You can adopt following method to do so if worksheets are not many in number. 1. Right click on a worksheet tab 2. Move or Copy 3. Select new book 4. Save this new workbook 5. Do it for…
Tips & Tricks 149 – Battery Charge Remaining
1. Make a backup of your workbook. 2. Open your workbook and ALT+F11 3. Locate your Workbook name in Project Explorer Window 4. Right click on your workbook name > Insert > Module
Tips & Tricks 148 – Get Battery Charging Status
1. Make a backup of your workbook. 2. Open your workbook and ALT+F11 3. Locate your Workbook name in Project Explorer Window 4. Right click on your workbook name > Insert > Module
Tips & Tricks 147 – Find the Location of Excel.Exe
Generally Excel.Exe will be located in C:\Program Files (x86)\Microsoft Office\Office15 in case of Excel 2013 / Windows 7. During installation, if another path is chosen, Excel.Exe's location changes. Default Path is also dependent upon Office and Windows combinations. Sometimes, it may not be locatable at all. To find the location of Excel.Exe – 1. Start…
Tips & Tricks 146 – When I input a number, Excel automatically inserts a decimal with 2 decimal points
Sometimes, you might face a problem that you input a number say 567 and it automatically becomes 5.67. If you enter 8, it becomes 0.08. To correct this problem > File > Options > Advanced and uncheck Red Zone
Tips & Tricks 145 – Determine the First Sunday or any other Day given Weeknumber
If you have been given a week number and has been asked to find the first Sunday for that week, you can use following formula =CEILING(("1JAN"&A1)-14,7)+8+7*(5-1) Where A1 has the year say A1=2016 5 is the Week Number which you can replace. For Finding Monday, add 1 in the formula, add 2 for Tuesday and…