Hiding errors in large data samples

Resolved · Low Priority · Version 2016

Thu 5 Jul 2018, 17:12 Delegate Russel said...

Russel has attended:
Excel Advanced - Formulas & Functions course

Hiding errors in large data samples

I often have to perform Vlookups on data which will return a lot of errors. In order to make the data look cleaner I use an 'Iferror' formula to return zeroes and then a conditional format to make those zeroes blank. It's a bit cumbersome and I'm sure there are better ways to do it.

I hope you can enlighten me!

Many thanks

Thu 5 Jul 2018, 18:44 Trainer Jens said...

RE: Hiding errors in large data samples

Hi Russel,

Thank you for the forum question.

The Iferror function is the best solution, but change 0 to "" then you will not need the conditional formatting.

It will also make your workbook working faster.

Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

Fri 6 Jul 2018, 09:22 Delegate Russel said...

RE: Hiding errors in large data samples

Amazing!

That's much neater, thank you!

Training courses

	98.7 % recommend us
	More live stats...

Training information:

Excel tip:

Display developer tab - Excel 2010

a. In Excel, click on the File tab
b. Select Options from left hand side
c. Choose the Customize Ribbon section
d. Click the box next to Developer in the list of tabs on the right hand side of the dialog box. When ticked the Developer tab will be visible.
e. Click OK to apply your changes

View all Excel hints and tips