Tell Excel 2007 Not to Display a Formula Error in a Cell

Excel 2007 introduces a brand new Function – the =IFERROR() Function which is a great way to “hide” any potential Formula Error Messages from displaying. You know these error messages – #DIV/0!, #NULL – They are ugly to look at and they can harm your professional reputation.

Watch how you can use =IFERROR() in Excel 2007. Or, if you are using Excel 2003, I show you how to accomplish the same goal but with a few more steps using the =ISERROR() Function nested inside an =IF() Function.

(Note: This is a repost. I now have a Podcast on iTunes and I need to feed this video to the iTunes Store.)

You can subscribe to my iTunes Podcast by clicking on this link.

Learn how to “Master Excel in Minutes – Not Months!”

Preventing Formula Errors from Displaying

Formula Errors in Excel – e.g. #DIV/0! or #NULL! – are ugly! And, when you send a spreadsheet that contains formula errors to your client or to your boss … Well, that is not good.

It plants the seed of doubt in the minds of your client or your boss that you have not thoroughly checked the accuracy of your assumptions. Why are there errors showing? What else is missing?

Excel 2007 makes it easy to prevent these formula errors from displaying. We have a new Function in Excel 2007 – The =IFERROR() function and it only requires two arguments. Be warned, however, that if you share your Excel 2007 worksheet with colleagues who are using earlier versions of Excel, this new function will not – well, function! (I could not resist using this pun)

Watch this video to learn how to use the new =IFERROR() function and also the nested =ISERROR() function that is required for people who use Excel 2003 or older versions.

If you enjoyed this Excel tip and my style of training, I invite you to visit my online store to shop securely for my DVD, “The 50 Best Tips for Excel 2007.”

Click here to watch this Excel Video Lesson on YouTube

You can view and download this video – for free – as a Podcast from the iTunes store. Follow this link to my Podcast, Danny Rocks Tips and Timesavers at the iTunes store.

Related Video Lessons:

Use the =TODAY() Function to Identify Past Due Invoices

Here is another response to a viewer request. The letter asks for my help in identifying, counting and totaling the amount of “Past Due” invoices. In the viewer’s letter, she wanted me to use the =NOW() Function. This function returns the current date and time (Hour, Minute, Second) from your computer’s system clock. The =TODAY() Function is similar, but it returns only the current date. Both the =NOW() and =TODAY() Functions are “volatile.” This means that the value that they return will automatically update according to your computer’s system clock. This makes them excellent reference points in formulas that identify “Past Due” invoices.

In addition to using the =IF() Function to identify the invoices that are “Past Due,” I also demonstrate two other functions: =COUNTIF() to total the number of “Past Due” invoices and =SUMIF() to give me the total dollar amount that is “Past Due.” I recreate these formulas, this time, using “named cell ranges” in the formulas.

Finally, I show you a great new Filtering Feature in Excel 2007 – the ability to filter by time period e.g. “Next Week!”

Related Videos

Check out my new DVD, “The 50 Best Tips, Tricks, and Techniques for Excel 2007.” It contains over 5 1/2 hours of training for Excel 2007. You can locate the specific tip that you want to learn – and in @ 6 minutes, you will have received all of the information that you need to become more productive in this area.

Use the =COUNTIF() Function to see how many badges have been claimed

A viewer wants to know how many badges have been claimed and how many have not yet been claimed. In Excel, use the =COUNTIF() Function to get the answer(s) to this question.

If you have ever struggled with using “Nested =IF() functions” you will welcome the =COUNTIF() Function – it is so much easier to use because the arguments for the function follow a logical progression.

My viewer also wanted to see the names of the guests who have claimed or who have not yet claimed their badges. To answer this request, I use a Data Filter – very easy to use and change.

Learn Excel quickly. Over 5 1/2 hours of Excel 2007 training on my DVD. Each lesson averages @ 6 minutes.

Shop for the DVDs in “The 50 Best Tips for…” series at my online bookstore.

Three Frustrations When Calculating TIme in Excel

When I am training a class to use MS Excel, I always get at least one question about performing time calculations. I sense that the person asking the question is frustrated. When I ask, “How many of you are frustrated when you try to perform a calculation to total time periods in Excel?” nearly every hand is raised! And they are right; Excel’s time calculations can cause frustration.

In this video lesson, I will show you how to overcome three common frustrations that you may have when performing time calculations in Excel.

Here are the steps to follow in this Excel Training Video:

  1. Format the cell that contains the formula =End_Time – Start_Time) to the Custom Number Format h:mm
  2. I encourage you to use this formula instead of a simple formula: =IF(End_Time < Start_Time, End_Time + 1, End_Time)-Start_Time.
  3. This will eliminate any possible “Negative times” which result in a cell filled with ######### A Negative Time is frequently the result of “Time Periods that Span Midnight. e.g. Start @ 11:00 PM and End @ 3:00 AM
  4. TIP: Use this Formula =MOD(End_Time – Start_Time, 1) This is the MODULUS Function.
  5. When you calculate the total number of hours and the result exceeds 24 hours, use this Format [h]:mm

Search My Index of Excel Video Lessons

 “The 50 Best Tips for …” is now available at the online store for The Company Rocks

Related Videos

I Have 21 Excel Videos Rated 5-Stars on YouTube

YouTube Logo

YouTube Logo

Here is a listing of my 21 Excel Video Lessons that are rated “5-Stars” on YouTube.

I hvae organized the videos by category. The First Hyperlink will take you to to the videos on this site. The “indented” Hyperlink will take you to the videos on my YouTube site –  DannyRocksExcels.

I hope that you find a few tips to save you time or answer a question. I welcome your feedback. Enjoy!

Pivot Tables

“What-if” Analysis

Consolidation and SubTotals

Filter & Sort Lists in Excel

Financial Functions in Excel

Logical & Lookup Functions in Excel

Text Functions

Formula Auditing

Formatting and Conditional Formatting

Paste Special Options

Excel Charts

News! My DVD, “The 50 Best Tips for Excel 2007” is now available to purchase. I invite you to visit my online bookstore for more details.

Related Videos

The versatile and valuable =IF() Function

MS Excel’s =IF() Function is a very versatile function and it is used in a variety of situations. Watch this short video lesson to learn how to use it when Grading Scores, Calculating Shipping Rates and “Trapping Errors.”

Here are the steps to follow in this lesson:

  1. The =IF() Function requires three arguments: A “Logical Test” – (which will return either a TRUE of a FALSE Response); the Value if “True,” and the Value if “False.”
  2. In my first example I have set up a simple scoring system for grades. For the Value if TRUE, I have entered “Pass.” Notice that the TEXT must be enclosed in ” “.
  3. In my second example, I have “nested” several =IF() functions inside the Value if FALSE Function Argument. You can “nest” up to 7 different IF() Functions in a formula.
  4. Notice how useful the =IF() Function is when you want to “trap an error.” Especially when you are starting to build a spreadsheet and many cells contain Zeros or NULL Values.

Find the Excel Video Lesson that you want – Index to All Excel Topics

News! My DVD, “The 50 Best Tips for Excel 2007” is now availabe to purchase. I invite you to visit my online bookstore for more details.

How to Prepare Your Zip Codes for Mailing Labels

Clients call me up in a panic. “Danny, we are sending out a direct mail piece and Excel has screwed up our Zip Codes – they only contain 4 numbers! The Post Office can’t deliver the mailing piece this way. Can you help us?”

Yes I can!

The problem is not with Excel. The problem is not choosing the correct formatting for the Columns / Fields that contain Zip Codes or Postal Codes.

At the risk of jeopardizing some lucrative consulting fees, I will demonstrate how to solve this problem in this short Excel Video Tutorial.

Here are the steps to follow in this Excel Video Lesson:

  1. When you have control over your data, pre-format your Zip Codes column using the “Formant Cells, Number, Special, Zip Code” format.
  2. If your data contains cells with both 4 and 5 digit Zip Codes, use this formula:
  3. =IF(A2<=9999, 0&A2, A2)
  4. Now, you will see some cells formatted as “Text” – aligned to the Left side of the cell and other cells formatted as “Numbers” – aligned to the Right side. Take the next step:
  5. First, Copy the data (the cells w/ the =IF() Function) and choose Paste Special, Values to return the results of the formulas.
  6. With the cells still selected choose, Data, Text-to-Columns and choose “Fixed Length.” After previewing your data, choose the “Format as Text”  button and Finish the Wizard.

Find the Excel Training Video that you want – Index to all Excel Topics

News! My DVD, “The 50 Best Tips for Excel 2007” is now availabe to purchase. I invite you to visit my online bookstore for more details.

Learn how Excel calculates time

Today’s lesson builds on the knowledge we gained in the prior video. So, rather than calculate “Dates”in Excel, we will calculate “Time.” Excel stores both Dates and Times as Serial numbers in a cell. This enables us to calculate the amount of time between two dates or two times.

Learning how to properly format the cells that calculate time is crucial to achieving correct results. It is also important to observe the proper syntax for entering times in cells (00:00:00 PM) or Hours: Minutes: Seconds AM or PM)

Here are the steps to follow in this Excel Video Lesson:

  1. Enter a Time function. e.g. =NOW() in a cell. Use the Keyboard Shortcut Ctrl+Shift+~ to reveal the serial number for that time.
  2. Excel counts time beginning at 12:00 AM or Midnight. So, the serial number for 6:00AM is 0.25
  3. If you only want to “Time Stamp” a cell, use the Keyboard Shortcut Ctrl+Shift+: (This entry will not update!)
  4. The =TIME() function requires three arguments (Hours, minutes, seconds)
  5. Be careful when performing calculations the show the difference between Start Time and End Time. If the End Time “spans midnight,” Excel will return an error unless…
  6. You use an =IF() Function in this calculation =IF(End_Time<Start_Time, End_Time+1, End_Time)-Start_Time
  7. To Increment a series of cells by 90 minutes use =Start_Time+TIME(1,30,0) and then copy the formula across the cells in the series.
  8. Formats matter! To correctly calculate hours that exceed 24 hours: Use this format – [h]:mm in the cell that contains the calculation.

Find the Excel Video Lesson that you want – Index to all Excel Topics

News! My DVD, “The 50 Best Tips for Excel 2007” is now availabe to purchase. I invite you to visit my online bookstore for more details.

Related Video Lessons