How do you avoid displaying errors in Excel spreadsheet formulas?

How do you avoid errors in Excel formulas? You don't need an online degree to figure this one out, but I do assume you have a basic understanding of simple logic formulas such as IF(). Sometimes, when you create a new formula you may get an error message in return (Here's a complete list of errors you may see in Excel: #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, #NULL!). In some cases, you'll want to know when a formula error occurs, but more often than not you would rather avoid these messages. You can do so by using an IF() function to check for an error. Use this standard format:

=IF(ISERROR(OriginalFormula),"",OriginalFormula)

For example, the formula below displays a blank if the division results in an error.

=IF(ISERROR(A1/B1),"",A1/B1)

 

Skip the Weekends Date Formula in Excel

I have a Date Started data in column A and Date Completed in column B. Column C I want to list how many days it took to complete the project so I subtract A from B (=B2-A2). However, I do not want to include weekends. So, if the start date is Friday and end date is Monday, currently my simple formula would display 4 days. I would like to create a formula that automatically skips over Saturday and Sundays, thus displaying the correct number of days as 2. How can this be accomplished?

Well, Excel actually has a built in function for this very reason. It is called NETWORKDAYS. It returns the number of whole workdays between two dates. To accomplish my task I simply use this formula, starting in cell C2: =NETWORKDAYS(A2,(B2-1)) Now you can calculate the number of work days there are between the start and end dates of a project by automatically excluding weekends. How cool is that?!

How do you change the default number of sheets in a new Excel workbook?

Every time you open a new workbook in Microsoft Excel, by default, it opens with three worksheets. You can, of course, insert more sheets or delete ones that you don't need. The good news is the unused sheets don't occupy additional memory or increase your file size, but if you are like me, the extra sheets just annoy me for some reason. I like to keep my workbook nice and clean. What can be done about this?

Well, you can actually change the default value of the number of sheets the Excel automatically opens with. How do you do this?

For Excel 2003: Go to: Tools>Options and click General in the Options dialog box. Next, change the setting for "Sheets in new workbook". Now all new workbooks will have the number of sheets you specify. I typically change this number to one unless I know I am going to have a monster spreadsheet.

For Excel 2007: Select the office window button>Excel Options. On the popup menu you will see the section "when creating new workbooks." Change the "include this many sheets" from three to whatever you desire.