Sat 26 Nov 2016

Tips & Tricks 150 - Save Each Worksheet as Different Workbooks

By |Saturday, November 26th, 2016|Categories: Tips and Tricks, VBA|Tags: , , , , , , , |0 Comments

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 all 20 worksheets.

(more…)

Sat 12 Nov 2016

Tips & Tricks 149 - Battery Charge Remaining

By |Saturday, November 12th, 2016|Categories: Tips and Tricks, VBA|Tags: , , , , , , , |0 Comments

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
(more…)

Sat 29 Oct 2016

Tips & Tricks 148 - Get Battery Charging Status

By |Saturday, October 29th, 2016|Categories: Tips and Tricks, VBA|Tags: , , , , , , |0 Comments

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
(more…)

Sat 15 Oct 2016

Tips & Tricks 147 - Find the Location of Excel.Exe

By |Saturday, October 15th, 2016|Categories: Tips and Tricks, VBA|Tags: , , , , , |1 Comment

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 Excel
2. Right click on Windows Task Bar > Start Task Manager > Processes tab (Task Manager can also be opened by pressing CTRL+SHIFT+ESC)
(more…)

Sat 01 Oct 2016

Tips & Tricks 146 - When I input a number, Excel automatically inserts a decimal with 2 decimal points

By |Saturday, October 01st, 2016|Categories: Tips and Tricks|Tags: , , , , |0 Comments

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

1

Sat 17 Sep 2016

Tips & Tricks 145 - Determine the First Sunday or any other Day given Weeknumber

By |Saturday, September 17th, 2016|Categories: Tips and Tricks|Tags: , , , , , , , , , |0 Comments

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 so on.

The above formula assumes that WEEKNUM function has Sunday as the starting day for the week. If you have any other day for the week as the starting day of the week, you will have to customize the above formula as per the need.

 

Sat 03 Sep 2016

Tips & Tricks 144 - Enter the Last Save Date and Time

By |Saturday, September 03rd, 2016|Categories: Tips and Tricks|Tags: , , , , , , |0 Comments

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, enter following in a cell
=LastSaveTime()

To get Last Save Date & Time Both, enter following in a cell
=LastSaveDateTime()

Note - You will have to format your result cells appropriately in Date / Time / Timestamp format

< Download the workbook illustrating the same Last Saved Date Time >

 

 

Sat 20 Aug 2016

Tips and Tricks 143 - Increment a Number when Workbook is Opened (Invoice or PO Number)

By |Saturday, August 20th, 2016|Categories: Tips and Tricks, VBA|Tags: , , , , , , , |0 Comments

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 Macro again.

< A workbook illustrating this can be downloaded from Invoice Number Generator >

'**** Macro Starts

Private Sub Workbook_Open()
Worksheets("Sheet1").Range("B1") = Worksheets("Sheet1").Range("B1") + 1
End Sub

Sat 06 Aug 2016

Tips and Tricks 142 - Determine Number of Working Days in a Year

By |Saturday, August 06th, 2016|Categories: Tips and Tricks|Tags: , , , , , , , |1 Comment

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 2010, you can control the weekends in the formula and function is NETWORKDAYS.INTL

=NETWORKDAYS.INTL("1JAN"&A1,"31DEC"&A1,"0000110")

In the string "000110" - First digit is Monday and last digit is Sunday. 1 defines that particular day as weekend.

If you have got your list of holidays in a range say B1:B20 (B1:B20 should contain dates in date format), you can have following formulas

=NETWORKDAYS("1JAN"&A1,"31DEC"&A1,B1:B20)

=NETWORKDAYS.INTL("1JAN"&A1,"31DEC"&A1,"0000110",B1:B20)

Sat 23 Jul 2016

Tips & Tricks 141 - How to Disable Quick Analysis Tool in Excel 2013

By |Saturday, July 23rd, 2016|Categories: Tips and Tricks|Tags: , , , , , , , , , , |0 Comments

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.

1

Few people don't like this. To disable it permanently -

File > Options > Uncheck the Red Zone

1