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'



Find the max number ignoring errors in Excel

When working with Excel, errors can have a bigger effect than just not looking nice. They can also affect later calculations. If I have a list of cells, and some have errors in them (DIV or NA type errors), how can you find the maximum number ignoring the errors.

Problem- Find the max number ignoring errors in Excel

So below we have a list with #N/A errors in it. In C22 we have tried to create a MAX function to see what the biggest number in column C is. Due to the existence of a single error (this column has many but even just one will do it), the answer is shown as a #N/A error.

Find the max number ignoring errors in Excel

You could go and fix each cell so that it shows something other than an error message, but you can also use the AGGREGATE function.

YouTube MAX ignoring error cells

Ignoring error cells with a MAX calc

So below we have started typing the AGGREGATE function. The first criteria is the function number. In this case the dropdown shows that 4 is the correct number as it is a MAX function.

Find the max number ignoring errors in Excel

After the comma, the next dropdown gives you a number of options. In this case we want the 6- Ignore Error Values but depending on what you are doing you may want to use one of the other options.

Find the max number ignoring errors in Excel

Lastly you must tell it what cells to look for the maximum in.

Find the max number ignoring errors in Excel

When you click enter, you will see that it gives the answer because it ignores all the error values in the range of cells.

Want to learn more about Microsoft Excel? If you prefer attending a course and live in South Africa look at the Johannesburg MS Excel 3 Day Advanced Course  or the Cape Town MS Excel 3 Day Advanced training course. If you prefer online learning or live outside South Africa, look at our online MS Excel training courses.

Ignore zeros in average

Avoid AVERAGE, MIN and MAX typical errors