Sat 06 Jan 2018

Tips & Tricks 169 - Get the Source of a Pivot Table

By |Saturday, January 06th, 2018|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
5. Copy & Paste the below code in this module

Now, you can call this function like

=GetPivotRange(E5)

Where E5 is any cell in your Pivot Table

 

Sat 23 Dec 2017

Tips & Tricks 168 - Sum Cells for a Particular Color

By |Saturday, December 23rd, 2017|Categories: Tips and Tricks, VBA|Tags: , , , , , , , |0 Comments

This can be accomplished with VBA only. There is no native formula within Excel to support this. This is a fairly small piece of code..

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
5. Copy and Paste the below code in the module
6. Save your file as .xlsm

7. Call your macro as

=SumColorCells(A1:C6,255,255,0)

In place of A1:C6, your can put your range.

Next 3 digits are RGB codes. For Yellow, they are 255, 255 and 0...To know the RGB code of any colour, select your cell and click on down pointing arrow on Fill Colour symbol and go to custom to pick up RGB code.

1

1

---- Macro by E for Excel ----

 

Sat 09 Dec 2017

Tips & Tricks 167 - Shortcut for Refresh All Pivot Tables Not Working

By |Saturday, December 09th, 2017|Categories: Tips and Tricks|Tags: , , , , |0 Comments

To refresh a Pivot table, the shortcut is ALT+F5. If you want to refresh all Pivots in the workbook, the shortcut is CTRL+ALT+F5. This magical shortcut would do the trick and you don't need any macro to perform this task.

But on most of the computer this shortcut may not work. The reason is most of the CTRL+ALT+Fn shorcut keys are reserved by Intel HD Graphics card. You will need to de-reserve this key combination.

Control Panel > Intel HD Graphics (or anything related to Graphic Card, you can put Graphics in Search of Control Panel) > Options and Support > Disable the hot keys even though CTRL+ALT+F5 is not listed there.

1

Sat 18 Nov 2017

Tips & Tricks 166 - Convert a Number to Weekday Name

By |Saturday, November 18th, 2017|Categories: Tips and Tricks|Tags: , , , , , |0 Comments

Suppose you want to return 1 = Sunday, 2 = Monday…..7 = Saturday

=TEXT(A1&"Jan2017","dddd")

To show only 3 characters of the Weekday Name

=TEXT(A1&"Jan2017","ddd")

You can add a number to A1 if you want to show some other Weekday Name

Say, if you want to show 1 = Monday, 2 = Tuesday…….7 = Sunday, just add 1 to A1

=TEXT(A1+1&"Jan2017","dddd")

Say, if you want to show 1 = Friday, 2 = Saturday…….7 = Thursday, just add 5 to A1

=TEXT(A1+5&"Jan2017","dddd")

Sat 04 Nov 2017

Tips & Tricks 165 - Convert Weekday Names to Numbers

By |Saturday, November 04th, 2017|Categories: Tips and Tricks|Tags: , , , , , , , , , |0 Comments

Suppose Cell A2 contains weekday names like Sunday, Monday.....(or Sun, Mon...), then following formula can be used to return the numbers. Sunday will be 1 and Saturday will be 7.

=ROUND(SEARCH(LEFT(A2,2),"SuMoTuWeThFrSa")/2,0)

=MATCH(LEFT(A2,2),{"Su","Mo","Tu","We","Th","Fr","Sa"},0)

If we want to return some other number to weekdays, then formula can be tweaked accordingly. For example, to make Mon = 1 and Sun = 7

=ROUND(SEARCH(LEFT(A2,2),"MoTuWeThFrSaSu")/2,0)

=MATCH(LEFT(A2,2),{"Mo","Tu","We","Th","Fr","Sa","Su"},0)

Sat 07 Oct 2017

Tips & Tricks 164 - All Existing Charts have Disappeared

By |Saturday, October 07th, 2017|Categories: Tips and Tricks|Tags: , , , , , , , , , |0 Comments

Accidental Pressing of CTRL+6 toggles hiding / unhiding of all objects which includes charts also. Pressing CTRL+6 will unhide all charts (objects) again.

Sometimes, Charts (objects) can be hidden through Selection Pane (Home tab>Find and Select>Selection Pane > This has Show All / Hide All buttons. In this case, you can show / hide appropriately.

Please note that both above are mutually exclusive. Hence, if use CTRL+6 to hide the objects, you can not use Show All of Selection Pane to show all objects and vice versa.

Sat 30 Sep 2017

Tips & Tricks 163 - Convert Alphabets to Numbers

By |Saturday, September 30th, 2017|Categories: Tips and Tricks|Tags: , , , , , , |0 Comments

If you want to convert a, b, c....z to 1, 2,3....26, the you can use following type of formula -

=CODE(UPPER(A1))-64

Sun 03 Sep 2017

Tips & Tricks 162 - Convert a Month Name to Month Number

By |Sunday, September 03rd, 2017|Categories: Tips and Tricks|Tags: , , , , , , , , , , , , , , , , , , , , , , , , , , |0 Comments

Suppose, you have text denoting month in cell A1. Let's say A1 = "Sep" or A1="September", then you can use following formula to convert this to month number

=MONTH(1&A1)

=--TEXT(1&A1,"m")

In case, cell A1 contains  the partial month name say "Septe", then in place of A1 in the above formulas, you can write LEFT(A1,3).

Sat 01 Jul 2017

Tips & Tricks 161 - When is Thanksgiving Day in a Year

By |Saturday, July 01st, 2017|Categories: Tips and Tricks|Tags: , , , , , , , , , |1 Comment

Last time, we discussed about finding Labor Day in a given year. This time, it is is about Thanksgiving Day in a year. Thanksgiving day is 4th Thursday in a November.

Hence, earliest possible day when 4th Thursday can happen is on 22-Nov.

(more…)

Sat 03 Jun 2017

Tips & Tricks 160 - When is Labor Day in a Given Year

By |Saturday, June 03rd, 2017|Categories: Tips and Tricks|Tags: , , , , , , , , , |0 Comments

While 1st May is celebrated as Labour Day in most of the countries but in USA/Canada, it is celebrated on 1st Monday of September.

Suppose the year is given in cell A1, you can use following formula to calculate the date for Labor Day

=CEILING(DATE(A1,9,1)-2,7)+2

Note - This utilizes the knowledge gained in Article 34 - Show Date for a Coming or Previous Day (Say Coming Monday, Previous Friday)

1