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

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

 

Sat 16 Apr 2016

Article 38 - 10 Features I would like to see in Excel

By |Saturday, April 16th, 2016|Categories: Articles|Tags: , , , , , , , , , , , , , , , , , , , , , , , , , , , , , |0 Comments

Like all of you, I am dependent upon day to day work in Excel. Excel is a software which is like ocean, unlimited in bounds. It has almost all features but it doesn't have few which everybody cherishes. All of us want something or the other. Below is a list of features which I would like Excel to have.

1. Making Volatile Functions Non-volatile on the basis of a Parameter

We all like TODAY(), NOW(), RAND() and RANDBETWEEN() (there are other volatile functions also). They are very useful but suffer a fatal flaw, if something gets recalculated, they always get recalculated also. Hence, if I am using TODAY() to derive today's day and if I open the sheet tomorrow, TODAY() will change tomorrow's date. I wish that all volatile functions should carry a parameter so that they should not recalculated when the sheet is recalculated. For example, we should have something like TODAY(N), NOW(N),RAND(N) and RANDBETWEEN(N) where N
(more…)

Sat 28 Nov 2015

Tips & Tricks 120 - Filter by Color Drop Down Menu Greyed Out Problem

By |Saturday, November 28th, 2015|Categories: Tips and Tricks|Tags: , , , , , |6 Comments

Sometimes, you find that though your are having colored cells but Filter by Color is greyed out i.e. it can not be selected.

Following are the possible causes -

1. Your sheets may be grouped. If they are grouped, you will notice word "Group" in the title bar of your Excel workbook.

1

Another way to check whether the sheets are grouped or not is to right click on any tab name and option "Ungroup Sheets" will appear:

1

2. There may be blank lines before a color is encountered in your range. Filter doesn't like blank cells. Hence, be watchful.

3. The workbook may be shared. If this is a shared workbooks, the word "Shared" will appear in the title bar of your workbook.

1

To remove sharing > Review tab > Share Workbook > Uncheck the box in Red zone

1

4. Also if first colored cell appears after approximately 10000 rows, Excel may not detect the color. To overcome this behavior, put a color in the first cell and now it will enable the Filter by Color. Only thing is that this colored cell will be extra in your filtered list.

5. Edit on 11-Oct-19 - If Filter by color doesn't appear and the worksheet is compatible (>=2007), you play with the column and do some random coloring, or real - at some point it starts showing the menu item.

Credit - Raymond Hilary