Friday, September 08, 2006

Example of Recreating a Business Form in Excel

I just recently completed an enhancement on a business form I'd done for an Excel_Geek $50 Project customer from a few months back. John is in the insurance business, and wanted me to replicate in Excel a manual, paper form they use, so that it would be easier/quicker to fill out, not to mention neater when printed out.

I think what I created illustrates several of the key reasons why using Excel to recreate otherwise manual, paper business forms makes good sense.

  • Using worksheet protection, we can keep users from inadvertently "breaking" the form.
  • Using conditional formating, we can visually cue users which areas in the form they can or need to edit or input data.
  • Using data validation, we can ensure that only the appropriate data in the appropriate formats are input into the form. (I even created a "pick from list" data validation that dynamically changed the list based upon selections made in other parts of the form. Oooooohhh Aaaaaahhh!)
  • And finally, perhaps one of the most compelling reasons to use Excel to recreate manual business forms is that one can use formulas to automate otherwise tedious calculcations.

All of this, and not a single line of VBA!

Thank you, John, for joining the ranks of the Excel_Geek repeat customer club, and to all of my Insiders subscribers, enjoy this file.

To those of you who haven't yet subscribed to the Insiders program, did you know that when you do, you will receive copies of all the $50 Projects I do for others for a year? All for $39.95 per year. That's right. If I do, say, 20 projects per year, you'll get all 20 for less than $2 each. Why would I give to you for nearly free something for which I charged someone else $50? I little economic principle called "marginal benefit." You see, I've already done the real work, which was paid for, so anything additional I get for that work (while merely having to deliver the product via email) has a relatively high net marginal benefit. Besides, how long do you really think I can offer services worth over $100 per hour for a $50 flat fee without some other revenue streams? After all, there is no such thing as a free lunch.

Can you believe the value! Fantastic examples of Excel and VBA projects AND brilliant lessons in economics, too?!? HA! ;-) Just sign up as an Insider today (if you haven't already)!




Wally Banners said...

Your blog rocks! Please I beg you help me please. Am a small biz and I got a new account. all i need to do to keep it is. Provide a way that they can fill an order form on excell and email it back to me. 2 things i need your expertise in is. How to make it look less like an excell worksheet and more like a stand alone form and this:

How do we make a template, mainly make a form where no one else can write on
my cells, they can only write on the cells I want them to write on. Like on
the template


Case No.

All that protected but then the cell next to it available for client to fill
Please help me!
aia saw your front page article of biz form you made. Can I be a subscriber so you can make this form above for me?
Please email me as blog thats listed for username is a freinds of mine tnxs man.

Excel_Geek said...

If you sign up as an Excel_Geek Insider, which is only $39.95 (USD) per year, you'll receive all $50 Projects I do for a year. I'll then "throw in" this project you're requesting for free. To sign up, click here.

You will be taken to a PayPal payment screen to complete your subscription.

You're other option is to simply pay me the $50 (USD) flat fee and I'll do the project for you, but that will cost you more and you'll get less!

Let me know what you want to do.