Create Custom Functions in Excel 2007

By Helen Bradley | Posted September 02, 2009
» 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!


Comment and Contribute


     

    Get free tips, news and advice on how to make technology work harder for your business.

    Submit
    Learn more
     
    You have successfuly registered to
    Enterprise Apps Daily Newsletter
    • webcast video
      Microsoft Publisher Tips This video shows you how to create great-looking business brochures with Microsoft Publisher 2003.
    • webcast video
      Photoshop Tips In this video, we show you how to improve on or eliminate ugly and unwanted backgrounds.