Push-Button Spreadsheets

Your client owns an auto dealership—we’ll call it Cars R Us—and wants to automate some routine business tasks the sales staff performs countless times a day. While the staff has access to computers, no one is very computer-savvy, so you suggest a customized spreadsheet design that lets salespeople click on buttons to perform specialized calculations and access data.

In this article we will design such a spreadsheet using macros in Excel, and if you follow along, you should be able to apply the basic principles to any number of other specialized tasks for your firm, your clients or your employer. As you’ll see, you do not need experience with spreadsheets to make full use of one.

CREATE THE MENU
Before we start building the spreadsheet, we need to determine what we want it to do. The sales staff has three main tasks that are naturals for automating: It inputs and prints customer inquiry forms, calculates the commission on a given sale and locates the lender with the lowest interest rate for a given customer’s credit score.

With those goals we can assign spreadsheet buttons to perform each task. Once finished, the main menu will resemble exhibit 1, below, although later we’ll eliminate all the unnecessary spreadsheet visuals such as the toolbars.

To create the menu, open a new workbook and choose File, Save As and name the workbook Cars R Us. Double-click on the first sheet tab and rename it Main Menu. Go to View in the toolbar and click on Toolbar, opening Formatting, Drawing and Forms. Place the toolbars anywhere you want by dragging them.

Next, use the Borders icon in the Formatting toolbar to place an outline around the form. Then go into the Fill Color icon on the Formatting toolbar to color the form. Finally, use the Insert WordArt icon on the Drawing toolbar to create the title—Cars R Us Main Menu. Click on the title to drag it to where you want it on the form. We’ll show you later how to hide the grid lines.

Go back to View, Toolbars and open the Forms toolbar to create the buttons that will be attached to macros which we’ll create later. Click on Button in the Forms toolbar as shown in exhibit 2, below.

In pre-2000 versions of Excel, the cursor turns into a crosshair, which you need to drag to the location of your choice. In 2000 and later, just click on the button, and after right-clicking, paste (Ctl+V) it to a location on the screen.

When you release the mouse, the Assign Macro dialog box will pop up because Excel wants to know what macro should run when the user presses the button. Since we haven’t created any macros yet, hit Cancel.

To size the button, use the tiny round fill handles that appear around the button, as shown at right.

If you don’t see the fill handles, left-click on the button to trigger their appearance. While the fill handles are visible, type the button’s label, Complete Customer Inquiry Form. To size the button, place the cursor directly on any fill handle and drag. To move the button, place the cursor anywhere on the button’s border except on a fill handle. A spin arrow (four arrows in four different directions) appears (as shown in exhibit 3, below) and then you can drag the button anywhere on the worksheet.

We could draw the other two buttons in the same way, but since it would be difficult to match the size, just copy the first one by right-clicking on it and choose Copy from the shortcut menu. Then place the cursor where you want the new button to appear on the form, right-click again and choose Paste. Since the button is still on the Clipboard, you don’t need to copy it again. Simply right-click and choose Paste a second time. If necessary, reposition the buttons. Left-click on the buttons to label them as shown in exhibit 1.

To create the customer inquiry form, double-click on the second sheet tab in the workbook and rename it Cust. Inquiry Form. Create and format this form as shown in exhibit 4, below.

A salesperson completes this form and gives it to a customer as confirmation after he or she identifies the car of choice and financial terms have been quoted. Now, type in the formulas below and format the cells appropriately as Currency (with two decimal places) or Percentage (with one decimal place):

cell H21: =H19+H20
cell H22: =H21*0.06
cell H23: =H21+H22
cell H25: =SUM(H23:H24)
cell H28: =(PMT(H27/12,H26*12,H25))

Cell H28 will show #Div/0! since there are no numbers in the spreadsheet yet.
To test the accuracy of your formulas, type in the following test data:

In cell H19: 18000
In cell H20: 2000
In cell H24: -8000
In cell H26: 5
In cell H27: .06

If you correctly entered everything, H28 should show $255.19 (exhibit 5, below). Excel returns this value in red because a payment is a cash outflow. Using the same steps outlined above, add three more buttons to this worksheet: Print, Clear All and Return to Main Menu. Remember, we still have to attach macros to the buttons.

THE COMMISSIONS LOOKUP TABLE
The next two forms in the workbook use formulas that will rely on two lookup tables. If necessary, add three new worksheets to the workbook by right-clicking on a worksheet and choose Insert three times. Double-click on tab 5 and rename it Lookup Rates; rename tab 6 Lookup Lenders. Return to the Lookup Rates worksheet. A salesperson’s commission is dependent on the car’s sales price, with the following commission structure:

Lookup tables can be aligned so that the search procedure occurs vertically, as in this case, or horizontally. Vertically aligned lookup tables require use of the VLOOKUP formula, while horizontally aligned lookup tables need the HLOOKUP formula. In our example, the sales price is listed in one column, which must be searched from top to bottom—that is, vertically—to determine the right category for a given sale, so a VLOOKUP formula is needed.

In either case, Excel requires the table to follow certain conventions. First, the numbers that determine which rate is appropriate must appear in the left-most column (or first row if an HLOOKUP table). Second, the numbers in the left-most column must be sorted in ascending order from lowest to highest. Finally, if the left-most column contains a range of values, as in our example, the first column of the table must show only the lowest number of the range. Based on this information, create and format the Lookup Rates worksheet as shown in exhibit 6, below.

Let’s name the lookup table COMM_Rates so it will be easier to refer to later. To do that, highlight D15 through E19. Note that you should not include the column headings or the blank line in between the headings and the first row of the table. Click on the Name Box and type COMM_ Rates as shown in exhibit 7, below, and hit Enter.

Next, we’ll create the Lender Rates lookup table with the following information:

Note that this table also is aligned vertically and thus also will require the use of the VLOOKUP function. Create the Lender Rates form as shown in exhibit 8, below.

Highlight B14 through J17, click on the Name Box and type LENDER_ Rates and hit Enter. When we use this lookup table in future formulas, we will refer to it by its name, LENDER_Rates. Now that we’ve created and named the two lookup tables, let’s go back and create the forms that will use these tables.

THE COMMISSIONS WORKSHEET
Double-click on tab 3 and rename it Commissions. Create and format the worksheet as shown in exhibit 9, below, including the buttons.

The VLOOKUP formula that will be entered in F15 has three required components. The first tells Excel what value a user is searching for in the lookup table. Here, it’s the sales price of the car that is entered in F13 to determine the commission rate. The second tells Excel the location of the lookup table. Because we named the table, we can simply use that name, COMM_RATES. The third component tells Excel which column of the lookup table should be returned. Because the commission rates are in the second column of the lookup table, we will use 2. So enter the following formulas:

In F15: =VLOOKUP(F13, COMM_RATES,2)
In F17: =F13*F15

As a test, enter 2100 as the price in F13. If you entered everything correctly, the commission rate should show 6% and the commission should be $1,272 as shown in exhibit 10, below.

THE LENDERS WORKSHEET
Now we will add the final form to the customized application, the Lenders form. Double-click on tab 4 and rename it LENDERS. Create and format the form as shown in exhibit 11, below.

To use the LENDERS form, a salesperson enters the customer’s credit score (as obtained from the customer’s credit report) into E12. The form automatically supplies the names of all lenders and interest rates that will lend at that score. Start by naming E12 Score. To do that, place the cursor in E12 and type Score into the Name Box and hit Enter. Input the following formulas in the appropriate cell (it’s easiest to type the first one and then cut and paste the rest):

C17: =VLOOKUP(score,Lender_Rates,2)
D17: =VLOOKUP(score,Lender_Rates,3)
C18: =VLOOKUP(score,Lender_Rates,4)
D18: =VLOOKUP(score,Lender_Rates,5)
C19: =VLOOKUP(score,Lender_Rates,6)
D19: =VLOOKUP(score,Lender_Rates,7)
C20: