Sat 07 Nov 2015

Article 29 - Traffic Lights Conditional Formatting for Project Status for Text R / A / G

By |Saturday, November 07th, 2015|Categories: Articles, VBA|Tags: , , , , , , , , |1 Comment

We all know that Traffic Lights are great visuals to communicate Project Status. But, we also know that Conditional Formatting for Traffic Lights can be done only through Numbers not through Text.

We need a flexible system that if I enter R (for Red), A (for Amber) and G (for Green), the required Traffic Light should be displayed. (A for Amber can be substituted with Y for Yellow also). Below is a method to achieve the required Traffic Lights.

(more…)

Sat 08 Aug 2015

Tips & Tricks 102 - How to Find all Links in an Excel Workbook

By |Saturday, August 08th, 2015|Categories: Tips and Tricks|Tags: , , , , , |0 Comments

Rogue links troubling your workbooks. You have visually inspected them and still you have no clue where to find them, then read on...

Approach 1 - Easiest Approach

Bill Manville offers a free Add-in which finds all links given in your Excel. The utility is a very popular one and it should be must have for any Excel enthusiast. The size is very small. At the time of writing this article, the size was < 100 KB. You can choose whether to install FindLink as an add-in so that it is available whenever you are running Excel or whether to just open it when you want to use it

The details of this utility is given at this link -

http://www.manville.org.uk/software/findlink.htm

You can download the link from following location -

http://www.manville.org.uk/software/findlink.zip

Approach 2 - Manual Approach

If you don't want to use add-in, use below article for finding links (This article doesn't take care of Conditional Formatting links which you will have to do manually. Findlink add-in takes care of this aspect also.)

Find external references (links) in a workbook

Mon 13 Jul 2015

Solution - Challenge 3 – Generate a Chessboard in Excel

By |Monday, July 13th, 2015|Categories: Solutions|Tags: , , , , |0 Comments

Below is a possible solution to problem posed in Challenge 3 – Generate a Chessboard in Excel

1. Select B2 to I9 starting with B2.

2. Home tab > Conditional Formatting > New Rule > Use a formula to determine which cells to format and put following formula and choose Black Fill Colour

=MOD(ROW()+COLUMN()+1,2)

(more…)

Sat 11 Apr 2015

Article 13 - Generate a Unique / Distinct List out of a List when no blanks in the list / range

By |Saturday, April 11th, 2015|Categories: Articles|Tags: , , , |0 Comments

Suppose you have a list as given below and now task before you is to generate a list of unique / distinct list from column A. We will also be looking into generating Unique values from Columns A, B and C together which can be generalized to any number of columns.

Note - This article is for the list which has no blanks in the range select / list. The case of having blanks in data range will be investigated into another article.

1

We will revert to tackle this problem in 6 ways

A. Manual Way
B. Hybrid Way
C. Formula Way
D. MS Query Way
E. Power Pivot Way
F. VBA Way

(more…)

Sat 10 Jan 2015

Tips and Tricks 73 - Use Conditional Formatting to Highlight Duplicate Cells

By |Saturday, January 10th, 2015|Categories: Tips and Tricks|Tags: , , , |0 Comments

Suppose your data is in range A2:A100 and you want to highlight all those cells which are duplicates.

1. Select A2:A100 and Home Tab > Conditional Formatting > New Rule

2. Put following formula after clicking "Use a formula to determine which cells to format"

=COUNTIF($A$2:$A$100,A2)>1

1

3, Click on Format Button to format the cells accordingly.

Sat 15 Nov 2014

Challenge 3 - Generate a Chessboard in Excel

By |Saturday, November 15th, 2014|Categories: Challenges|Tags: , , , |1 Comment

Below chessboard has been generated in Excel. The challenge before you is to give me steps which will generate this chessboard of 8x8. The approach should be flexible to generate any NxN chessboard (Colouring should not be done manually).

Note - You may choose to post the response in comments section.

1

Sat 02 Aug 2014

Tips & Tricks 36 - Highlight Unique / Duplicate Rows

By |Saturday, August 02nd, 2014|Categories: Tips and Tricks|Tags: , , , |0 Comments

If you want to highlight Unique / Duplicate Rows, follow following steps

1. Select the data range where you want to apply this formatting.

2. Home Tab > Conditional Formatting > New Rule

1

3. Select Format only unique or duplicate values. In Format all: box, you can select duplicate / unique.

4. Format appropriately and press OK.