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.

How do you create a delete button for a form with a macro in Excel?

I managed to create a form to add records to a worksheet. Now what I want to do is add a 'Delete Record' button to the form to delete the last entry (or any other entry for that matter). First, how do you identify the entry you wish to delete? My solution is to use the first column as a reference column. Try using a macro like this:

Private Sub CommandButton1_Click()

Dim c As Long

Dim iRecord As String

Dim Deleted As Boolean

 

iRecord = InputBox("Enter Record Number to Delete")

c = Cells(Rows.Count, 1).End(xlUp).Row

Deleted = False

While Not Deleted

If Cells(c, 1) = iRecord Then

Rows(c).Delete shift:=xlUp

Deleted = True

End If

c = c - 1

If c <= 0 Then Deleted = True

Wend

End Sub

How do you flip or reverse a column of data in Excel?

How do you flip or reverse a column of data in an Excel spreadsheet? Well, if you simply want to reverse a list that's already in alpha or numeric order that is easily accomplished using the built in "Sort: feature (you can also easily transpose columns into rows and vice versa).

However, often you will find yourself with a column of data that isn't sorted (and may even be in different formats like a mix of numbers and text). How do you simply flip it so the bottommost items are on top and the top is now the bottom of the column of data?

There are a number of ways to do this, including writing a macro, but the easiest way I can think of is to simply create what I like to call a helper column. First, select the column you want to flip, right click it, and choose Insert to add your new helper column. Add a list of numbers starting with 1 and running down to the number of items in your list. Now select both of those numbers and then double-click the lower right corner of the cell that contains the 2 to quickly fill down the length of your target column.

Select your helper column and any other columns you want sorted along with it. Go to the Data tab and click the Sort button and use this to Sort by your helper column using A to Z or largest to smallest. That's it. Once completed, you can delete your helper column. Simple, eh?

Another more advanced method is to use a macro. Try this:

Sub FlipColumns()

Dim vTop As Variant

Dim vEnd As Variant

Dim iStart As Integer

Dim iEnd As Integer

Application.ScreenUpdating = False

iStart = 1

iEnd = Selection.Columns.Count

Do While iStart < iEnd

vTop = Selection.Columns(iStart)

vEnd = Selection.Columns(iEnd)

Selection.Columns(iEnd) = vTop

Selection.Columns(iStart) = vEnd

iStart = iStart + 1

iEnd = iEnd - 1

Loop

Application.ScreenUpdating = True

End Sub

 

Shared Apartment Expenses for 5 Roommates Spreadsheet

I recently updated my shared apartment expenses worksheet to account for up to five roommates. The purpose of this spreadsheet is to easily track who you owe money to. It's an easy way to divide up rent, cable, electricity, groceries and random other oddities. The instructions are included with the spreadsheet. You can download the shared expenses workbook here.


Shared Apartment Expenses for 5 Roommates Spreadsheet.xls

How do you change the worksheet tab color in Microsoft Excel?

 Excel 2003 Users


If you are wondering how you change the worksheet tabs color in an Excel spreadsheet then the title of this post might be a little deceiving. Unfortunately, this feature is not currently available in Microsoft Excel. It would certainly be helpful to many users to be able to color-code your worksheet tabs. For some unknown reason, Microsoft hasn't implemented this feature yet although I hear it has been available in 1-2-3 and Quattro Pro for quite a while. Still, that's not enough to make me quit using Excel and switching to another program. 

Excel 2007 Users

You can easily change the tab color in Microsoft Excel 2007 by going to the home tab, clicking on the Cells section, going to Format and then hit "Change tab color." Easy.