Sat 30 Dec 2017

Excel Quiz 58

By |Saturday, December 30th, 2017|Categories: Quizzes|Tags: , , , |0 Comments

Excel Quiz 58

A quiz based on Excel functions and formulas

Sat 23 Dec 2017

Tips & Tricks 168 - Sum Cells for a Particular Color

By |Saturday, December 23rd, 2017|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 Dec 2017

Challenge 68 - Generate a Particular Sequence

By |Saturday, December 16th, 2017|Categories: Challenges|Tags: , , , , , |1 Comment

Study the sequence below and you need to write a formula which when dragged down should generate the below sequence.

For visual representation purpose, the sequence is in 5 columns but you need to generate the sequence in one column only.

You need to generate upto 100 entries.

The solution to this challenge will be published after a month i.e. on 16-Jan-18.

Sat 09 Dec 2017

Solution - Challenge 67 - Find Duplicates and Show the Count

By |Saturday, December 09th, 2017|Categories: Solutions|Tags: , , |0 Comments

Below is a possible solution to the problem Challenge 67 - Find Duplicates and Show the Count

Use a helper column in which put following formula -

=LEFT(SUBSTITUTE(IF(ISNUMBER(FIND("(",A2)),SUBSTITUTE(A2,"("," "),SUBSTITUTE(A2&"-","-","%")),
" ","%",2),FIND("%",SUBSTITUTE(IF(ISNUMBER(FIND("(",A2)),SUBSTITUTE(A2,"("," "),
SUBSTITUTE(A2&"-","-","%"))," ","%",2))-1)

In the last column, put following formula -

=IF(COUNTIF($B$1:B2,B2)=1,COUNTIF(B:B,B2),"")

The solution work can be downloaded from Solution - Find Duplicates and Show the Count

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 02 Dec 2017

Excel Quiz 57

By |Saturday, December 02nd, 2017|Categories: Quizzes|Tags: , , , |0 Comments

Excel Quiz 57

A general quiz on Excel