Excel Top Tip #10 - Hide #N/A, #VALUE and #DIV/0, and more tips

Here is the email sent to all subscribers on 31 March 2021. To receive Excel top tips just like this, you can subscribe by clicking here.

Hello

This month we’ll follow on from last month's tips, uncovering hidden areas that can shift your level in Excel to pro very quickly, and answer the questions that you and your colleagues never knew to ask and find solutions you never knew existed.

1 - Can I hide errors from my formulas?

Even perfectly correct formulas can create an error sometimes; and if you’re sharing your Excel file with a colleague, a #NA or #VALUE is of little use to inform the recipient what has happened.

To solve this, you can use IFERROR() to send a message instructing the user to check the specific input cells, or simply create a blank cell to prettify your Excel even if the inputs don’t match the required format.

2 - What if I like inconsistent formulas?

There is an option tickbox that controls whether a warning appears when formulas in consecutive cells don’t match - this is ticked by default, but you can turn it off here:

3 - Where is this formatting coming from?

Conditional formatting can be a hidden influence of a cell’s formatting; and if a cell isn’t being affected by your formatting requests then it’s worth checking.

Click here to disable and delete the rules affecting the worksheet or cell(s) selected:

4 - How can I add ‘hrs’ after a value?

Formatting changes the displayed value without changing the actual (underlying) value of a cell. And, using custom formatting you can make a value look like anything - simply use some of the templates to keep the number you want (so it still changes dynamically), then add ‘hrs’ to the end. The preview will show you what the cell will look like.

And with that, go help your colleagues impress your clients and colleagues in Excel. For any other questions, get in touch; why not book a call to talk about how we can help you?

David - The StatementReader Team