Create Custom Functions in Excel 2007

Create this sample worksheet to test the function you are about to create

» Making Your Own Excel Functions

Often when you're working with Excel you will use a calculation that is not available as a built-in Excel function. If you make this calculation repeatedly, you can save having to write the calculation each time you use it and instead create a custom Excel function to do the work. Excel custom functions can also be created as Excel add-ins so that they are available for use in all workbooks.

To create a function, open a new Excel workbook and create a small worksheet as shown in the image to the right:

I'll show you how to create a custom function to calculate the selling price of an item using the following formula:

selling price - cost price * (1+markup percentage)

To make this calculation in cell D4 of the example worksheet (given the markup percentage is 50%) you would write this formula and copy it down the column:

=C4*(1+50%)

If you want to make this calculation repeatedly or have someone else do it, you can create a function that can be used in the same was as any other Excel function.

To do this, first display the Developer tab if it isn't yet visible by clicking the Office button, choosing Excel Options > Popular, and enabling the Show Developer tab in the Ribbon checkbox. Click OK.

Next, click the Developer tab on the Ribbon and click the Visual Basic icon to open the Visual Basic window. Locate VBA Project for the workbook you are working on. In our case it's VBA project (Book1). Select Insert >

Read the Full Story

Do you have a comment or question about this article or other small business topics in general? Speak out in the SmallBusinessComputing.com Forums. Join the discussion today!