Look Who it Was Random video

Excel - Eliminate #VALUE Errors in Excel Totals | Custom Number Format Trick - Episode 1112

1,196 views · Published 30 September 2009 · 2:15 · Indexed 25 September 2026

Channel: MrExcel.com · 2009 · Education

Watch on YouTube

Microsoft Excel Tutorial: Eliminate #VALUE Errors in Excel Totals | Custom Number Format Trick.

Welcome back to the MrExcel netcast, where we tackle your toughest Excel questions and provide solutions to make your data analysis easier. In this episode, we're diving into the issue of #VALUE errors in totals. This is a common problem that many Excel users face, but fear not, we have a solution for you.

Today's question comes from David, who sent us a spreadsheet where he was trying to add up numbers for various weeks. However, he kept getting a #VALUE error and couldn't figure out why. Upon further investigation, we discovered that the error was occurring because some cells were left blank and others had a space instead of a number. This may seem like a small issue, but it can cause major problems when trying to perform calculations.

To solve this issue, we recommend replacing the blank cells and spaces with zeros. This may not look visually appealing, but we have a trick to make it work. By using a custom number format, we can hide the zeros and spaces while still allowing them to be included in calculations. This way, your totals will be accurate and the error will disappear.

We want to thank David for sending in his question and we hope this solution helps others who may be facing the same issue. Don't forget to subscribe to our channel for more Excel tips and tricks, and we'll see you next time for another netcast from MrExcel. Thanks for watching!"

Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/

You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-on-youtube/

Table of Contents:
(00:00) #VALUE error in Excel Total
(00:23) Investigating the cells causing the error
(00:35) Clever formatting causing the problem
(00:57) Suggestion to use zero instead of blank space
(01:07) Using Custom Number Format to hide zeros
(01:30) Solution to eliminate value error
(01:51) Thanking David for the question
(02:01) Clicking Like really helps the algorithm

#excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial 

This video answers these common search terms:
Adding up numbers
Cell reference
Custom Number Format
Negative numbers
Pivot table
Positive numbers
Spreadsheet
Value error
Value error disappearance
Zeros

Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads/1152655/



David asks why his total formulas are getting #VALUE errors. Episode 1112 shows this common problem and a solution.

More from this channel