Images loading below. While you wait, can you answer this Excel related tip? Guess the answer then click SEE ANSWER to see how it can be done.

1. How do you copy from Excel (see 1 below) and paste into Word (2) WITHOUT seeing the filter button, gridlines, comment notes and more?

Guess and then view answer to see how it can be done!

2. How do you take all the formatting from chart (see 1 below) and PASTE it onto another chart (2)? Guess and then see how it can be done with 'See Answer'



Vlookup increment column number

In order to get a Vlookup increment column number formula, i.e. as you drag the VLOOKUP formula across is automatically knows to increase the column number by 1 (or some other number), you need to change where Excel gets the column number from.

NEW: Go to  VLOOKUP Explanations with downloadable exercises and detailed solution

In the VLOOKUP syntax of

VLOOKUP(lookup value, table array,col index number, [range_lookup])

most people type in a number for the col index number e.g. a 5 to represent the 5th column.

In order to make it automatic so that you can copy the formula across and have the numbers change you can look at creating a trigger row i.e. a row that contains the numbers of the columns you want to use. The VLOOKUP formula will then look at this row to determine which column to use. It is easier to explain if you watch the video clip on the VLOOKUP column index trick.

There are also other ways to do it. Depending on the structure of your spreadsheet you may be able to use the COLUMNS function (returns the number of the column it is in).

Another more sophisticated way would be to use a MATCH function and lookup up the column number based on the name of the column.

Calculate PAYE for sales people in Excel