Sat 21 Dec 2019

Tips & Tricks 174 - Create a Desktop Shortcut for Onedrive Documents

By |Saturday, December 21st, 2019|Categories: Tips and Tricks|Tags: , , , , , , , |0 Comments

Sometimes, you might need to create a desktop shortcut to Onedrive documents. Below is a method for Excel documents which can be used for any MS Office document.

First, you would need to find the path of Excel.exe on your computer. (more…)

Sun 01 Dec 2019

Tips & Tricks 173 - Color Code for Official Color of Excel

By |Sunday, December 01st, 2019|Categories: Tips and Tricks|Tags: , , , , , , , |0 Comments

Everybody sees the dark green color of Excel.

The color code for this color is (more…)

Tue 15 Oct 2019

Tips & Tricks 173 - VBA - OR Condition in FIND

By |Tuesday, October 15th, 2019|Categories: Tips and Tricks, VBA|Tags: , , , |0 Comments

FIND is a very powerful function in VBA but it doesn't support OR condition. Hence, if you want to find say two values "A" or "B", then you can code an array within FIND. To do OR in FIND, you will need to use following code (this is a sample code only, there can be many variations of this code) (more…)

Mon 23 Sep 2019

Tips & Tricks 172 - Data Validation for Case Sensitivity

By |Monday, September 23rd, 2019|Categories: Tips and Tricks|Tags: , , |0 Comments

There are scenarios when you want to put a data validation which is case sensitive. Hence you want USA to be entered as USA not as usa or uSA or uSA so on. You just want all capitals. Similarly, sometimes you need only small letters say maverick. Hence, not Maverick, mavericK etc. (more…)

Sun 14 Jul 2019

Tips & Tricks 171 - Calculate Next Working day if date is of Weekend / Holiday

By |Sunday, July 14th, 2019|Categories: Tips and Tricks|Tags: , , |0 Comments

Suppose you are given a date and you are asked to calculate next working day if date is of weekend. If date is a regular workday, then you should show the same date.

For example - 8-Mar-19 is a working day. Hence, you should show the same date. But if this is either 9-Mar-19 or 10-Mar-19 which are Saturday and Sunday, then you must show 11-Mar-19 as the next workday. (more…)

Mon 15 Apr 2019

Tips & Tricks 170 - Convert your formulas to Absolute References

By |Monday, April 15th, 2019|Categories: Tips and Tricks|Tags: , , , , |0 Comments

If your sheet has plenty of formula and you want to convert them into Absolute references i.e.

One way is that you convert each one of them individually or use some logic for Find and Replace. But all these methods have their own limitations. (more…)

Sun 03 Mar 2019

Tips & Tricks 169 - Calculate Previous Working day if date is of Weekend / Holiday

By |Sunday, March 03rd, 2019|Categories: Tips and Tricks|Tags: , , |0 Comments

Suppose you are given a date and you are asked to calculate Previous Working day if date is of weekend. If date is a regular workday, then you should show the same date.

For example - 8-Mar-19 is a working day. Hence, you should show the same date. But if this is either 9-Mar-19 or 10-Mar-19 which are Saturday and Sunday, then you must show 8-Mar-19 as the previous workday. (more…)

Sat 23 Feb 2019

Tips & Tricks 168 - Sum Cells for a Particular Color

By |Saturday, February 23rd, 2019|Categories: Tips and Tricks, VBA|Tags: , , , , , , , |1 Comment

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 ----

Function SumColorCells(Rng As Range, R, G, B)
    Dim Cell As Range
    Application.Volatile
    For Each Cell In Rng
        If IsNumeric(Cell) And Cell.Interior.Color = RGB(R, G, B) Then
            SumColorCells = SumColorCells + Cell
        End If
    Next Cell
End Function

 

Fri 06 Jul 2018

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

By |Friday, July 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

' *** Macro by eforexcel.com *****
Function GetPivotRange(Cell As Range) As String
    GetPivotRange = Application.ConvertFormula(Cell.PivotTable.SourceData, xlR1C1, xlA1)
End Function

 

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