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
Category: Tips and Tricks
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…
Tips & Tricks 144 – Enter the Last Save Date and Time
1. Open your workbook and ALT+F11 2. Locate your Workbook name in Project Explorer Window 3. Right click on your workbook name > Insert > Module 4. Copy paste the Macro code given 5. Save your file as .xlsm To get Last Save Date, enter following in a cell =LastSaveDate() To get Last Save Time,…
Tips and Tricks 143 – Increment a Number when Workbook is Opened (Invoice or PO Number)
1. Make a backup of your workbook. 2. Open your workbook and ALT+F11 3. Locate your Workbook name in Project Explorer Window 4. Double Click on "This Workbook" 5. Copy paste the Macro code given – Replace Sheet1 and B1 as per your need 6. Save your file as .xlsm if you intend to reuse…
Tips and Tricks 142 – Determine Number of Working Days in a Year
Suppose, you have been given a year in A1 (Say A1 = 2016) and you need to determine the number of working days in a Year, then your formula to determine number of working days would be – =NETWORKDAYS("1JAN"&A1,"31DEC"&A1) The above formula is based on the fact that Saturdays and Sundays are weekends. Starting Excel…
Tips & Tricks 141 – How to Disable Quick Analysis Tool in Excel 2013
In Excel 2013, we have a new feature called Quick Analysis Tool. If you select a range and press CTRL, you are presented with Quick Analysis Tool. It offers various commonly used functions which you can perform. Few people don't like this. To disable it permanently – File > Options > Uncheck the Red Zone…
Tips & Tricks 140 – Multiple Hyperlinks within Excel Text Box
You created a Text Box and you put many words inside that say Yahoo, IBM, Microsoft etc…Now, you have given respective hyperlinks to them. But if you try to click on any hyperlink inside the text box, it will always open only one hyperlink. It is possible to circumvent this behavior by work-around. You can…