Excel 2016 – How to force a value in a formula

Problem

One of the more frustrating features in Excel is its ability to automatically change a cells format – based on it’s content.

For example – a cell a currency will automatically change to text.

This often breaks formulas with Excel suddenly deciding that a cell contains text and not numbers.

This is even more frustrating when it happens in report templates where you end up having to look for and fix the error every time you run the report.

Solution

Fortunately there’s a simple solution – wrap the cell reference in the VALUE function.

The VALUE function will force the cell contents to be a value regardless of its formatting settings.

For example – instead of =SUM(B2:D2)) you would use

=SUM(VALUE(B2:D2))

Article Downloads

TIP: You may need to right-click and select 'save link as'.

Related Articles

Leave a Reply

Your email address will not be published. Required fields are marked *