Sat 08 Apr 2017

Tips & Tricks 158 - Overcoming column_index_number problem in VLOOKUP when a column is inserted / deleted

By |Saturday, April 08th, 2017|Categories: Tips and Tricks|Tags: , , , , , , , , , |0 Comments

One of the negative points which gets attributed to VLOOKUP is that whenever a column is added / inserted within the range of VLOOKUP, the column index number doesn't change. Hence, it gives wrong result. Let's consider below dataset and for a given Emp ID, I need gender of that person. Hence, for Emp ID, 754761, the formula would be =VLOOKUP(754761,$A:$G,6,0) and answer would be F. Now, let's delete a column and now the answer would be as column_index_number is still 6. Now, add a column and the answer would be Vang as column_index_number is still 6.


Sat 05 Sep 2015

Tips & Tricks 107 - Autofill on a Filtered List

By |Saturday, September 05th, 2015|Categories: Tips and Tricks|Tags: , , , , , , , |0 Comments

Everybody is quite aware about Autofill. There are various ways to Autofill. Refer to following article for Autofill -

Article 7 – Generate a Sequence of Numbers

Now, apply a filter on your range and all the techniques fail. If you drag, all cells are filled with 1 and no other techniques also work. The reason is that Autofill works only on a contiguous range. Once, you apply filter, the range becomes non-contiguous.

Now, here comes the trick -

1. Apply the filter.
2. Let's assume that first row is 3 and you wanted to fill it in column B.
3. Put following formula in B3 and drag down

If you don't want to drag down -
3.1 Put the above formula in B3.
3.2 Select all the cells including B3.
3.2 Press F2
3.3 CTRL+Enter

Above steps will fill the filtered list with 1, 2, 3.....

Sat 25 Jul 2015

Tips & Tricks 101 - Get Column Name for a Column Number

By |Saturday, July 25th, 2015|Categories: Tips and Tricks|Tags: , , , , |1 Comment

Let's suppose, you have a number in A1 and you want to get the column Name for that.

Hence, if A1=1, you want "A"
Hence, if A1 =26, you want "Z"
Hence, if A1=27, you want "AA" and so on.

The formula to derive the column name would be be -


Mon 13 Jul 2015

Solution - Challenge 5 – Prepare a Graphical Chart

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

Below is a possible solution to the challenge - Challenge 5 – Prepare a Graphical Chart

1. Download the Challenge file - Tiger Count

2. Right click on Tiger's picture > Cut > Open Microsoft Paint or any other picture editor> Paste > Save the picture

3. Select A1:B7 in Tiger Count file.

4. Create a column chart.

5. Remove vertical and horizontal lines, if any, by selecting them and deleting them.

6. Click anywhere in the graph > Design tab > Add Chart Elements > Data Labels > Outside End

7. Click any data label > Home tab > Fill with black Colour > Bold the Text and make Font White. Adjust font size, if needed.

8. Right Click on any data label > Format Data Axis > Size & Properties > Text Direction > Rotate All Text 270


9. Right Click on x axis and repeat step 7 for x axis also.

10. Select Title Box > Fill with Green Colour > Bold the Text and make Font Color White

11. Click anywhere in the graph > In the Fill, select Picture or Texture Fill > Insert Picture from File > Select the Picture of Tiger (At this stage, Excel may try to connect to internet. Choose Work offline here)


12. Right click on any column and fill it with White Colour.

The solution worksheet can be downloaded from here - Solution -Challenge 5 – Prepare a Graphical Chart

Sat 31 Jan 2015

Tips & Tricks 76 - Whenever I Refresh a Pivot, its Column Width Changes

By |Saturday, January 31st, 2015|Categories: Tips and Tricks|Tags: , , , , |0 Comments

You can stop Pivot from doing this behaviour. Take following steps -

1. Right Click on Pivot Table > PivotTable Options
2. Uncheck Autofit column widths on update.


Sat 06 Dec 2014

Challenge 5 - Prepare a Graphical Chart

By |Saturday, December 06th, 2014|Categories: Challenges|Tags: , , , , |1 Comment

This time, it is a challenge to make a chart like below.

The relevant data points and picture are in the Excel file which can be download from here. Tiger Count


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

Sat 06 Sep 2014

Tips & Tricks 49 - My Column Numbers are 1,2,3....rathar than A, B, C.....

By |Saturday, September 06th, 2014|Categories: Tips and Tricks|Tags: , , , |0 Comments

It means your R1C1 reference setting has become enabled. If you haven't done it manually, then it means that you have opened a sheet which was R1C1 enabled and any sheet opened after that will be opened in R1C1 mode only.

To go back to A,B,C column notations which is called A1 style referencing.

1. Office Button > Excel Options > Formulas


2. Uncheck the R1C1 reference style


Sat 06 Sep 2014

Tips & Tricks 48 - Multi Column VLOOKUP

By |Saturday, September 06th, 2014|Categories: Tips and Tricks|Tags: , , , , , |0 Comments

You know VLOOKUP, one of the most loved function of Excel. The syntax is VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

Here look_value can be a single value not multiple values.

Now, you are having a situation where you want to do vlookup with more than 1 values. For the purpose of illustrating the concept, let's say we have 2 values to be looked up.

Below is your lookup table and you want to look up for Emp - H and Gender - F for Age.



Concatenation Approach


@@@ can be replaced by any characters which should not be part of those columns.

By concatenation, you can have as many columns as possible.

CAUTION - Result of entire concatenation should not be having length more than 255. Hence, F2&"@@@"&G2 should not have more than 255 characters.

Another alternative is to use below Array formula -


Note - Array Formula is not entered by pressing ENTER after entering your formula but by pressing CTRL+SHIFT+ENTER. If you are copying and pasting this formula, take F2 after pasting and CTRL+SHIFT+ENTER. This will put { } brackets around the formula which you can see in Formula Bar. If you edit again, you will have to do CTRL+SHIFT+ENTER again. Don't put { } manually.

Sat 26 Jul 2014

Tips & Tricks 35 - Highlight Cells which are different from first Column Cells in a Range (And for Row Cells also)

By |Saturday, July 26th, 2014|Categories: Tips and Tricks|Tags: , , , |0 Comments

Let's say you are having data as given below and you want to highlight all the cells in columns after first column where cell values are different.


B1 is not different from A1 where C1 is different from A1, hence C1 will be highlighted.
In row 2 both B2 and C2 will highlighted and so on.



1. Select the required range. In above case, select A1:C8.

2. ALT+EGSW and Enter (ALT+EGS opens Go to Special and W selects "Row Difference")

And you will have your results.


ALT+EGS to disply

Note - For ROW CELLS, select Column Difference i.e. ALT+EGSM and you get following results (I have changed data set to illustrate the concept. Here comparison is done with first row for following rows -