Excel 2016 – How to force force value in 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 have

=SUM(VALUE(B2:D2))

Article Attachments

Related Articles

Leave a Reply

Your email address will not be published.