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,…
Category: Tips and Tricks
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…
Tips & Tricks 139 – Convert from Julian Dates to Excel (Gregorian) Dates
In Tips and Tricks 138, we covered conversion from Excel Dates to Julian Dates. Here, we want to look at reverse. For 7 Digits Julian Dates, following formula should be used =DATE(LEFT(A1,4),1,RIGHT(A1,3)) For 5 Digits Julian Dates, following formula should be used depending upon which century (Note – Julian dates are most likely to fall…
Tips & Tricks 138 – Convert from Excel Date (Gregorian Date) to Julian Date
Q. First what is a Julian Date? A. A Julian date has either 7 digits or 5 digits date and these are generally used in old IT legacy systems. 7 Digits – YYYYDDD – 2016092 (This is 1-Apr-2016. 92 means that this is 92nd day from 1-Jan in that year) 5 Digits – YYDDD –…
Tips & Tricks 137 – VBA – Delete Blank Rows for a Range Quickly (without Looping)
Suppose, you have data like this and you want to delete rows 3,4,6,8,11 & 12 as they are blanks. You need not loop through all cells but you can perform this operation is one shot. You can use below code for the same On Error Resume Next Range("A1:A13").SpecialCells(xlBlanks).EntireRow.Delete On Error GoTo 0
Tips & Tricks 136 – Quickly Copy a Formula or a Value in a Large Number of Cells
Say, you have written a formula (or put a value) in cell B1 and you want to fill in this formula till B10000. You can drag the formula but higher the number of cells, more tedious it becomes. Fortunately, we have a way to do this very fast. 1. Put formula in B1. 2. CTRL+C…
Tips & Tricks 135 – I input (c) or (C) and it gets converted to Copyright Symbol..I don't want this
There are many ways to overcome this. 1. a. Put following in a cell and press Enter c) b. Go back to the cell and type first bracket to complete (c) 2. a. You can type (c) preceded with some special character and press enter. b. Go back to that cell and delete that special…