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

2. 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!


Grouping in unequal bands

One of the most common queries is how to achieve grouping in unequal bands.

Rather than creating a massive IF function, use a VLOOKUP with TRUE as the range (using the approximate match). If you haven’t used VLOOKUP, watch the VLOOKUP video tutorial.

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

In the example below column A has a number of sale amounts and we want to group them in bands, but as shown in column D and E they are uneven (1st band is 500 then 4500 etc).

Create the table as shown in D2 to E5. In column D have the trigger numbers and in column E the description you want (this could be a number or text).

Now in Column B you can create a VLOOKUP that uses the sales amount and compares it to the table, pulling back the appropriate description. The key difference is that the range indicator must be TRUE.

Now that you have this column, you can use it in a filter or pivot table.

Grouping in unequal bands

Remember that if you use the TRUE option in VLOOKUP the table has to be in ascending order.