Excel has all the right tools you need to build a solid,
stable quoting system that saves time and is easy to use for Excel-challenged
employees. This multipart series will show you how.
This is the fourth and final installment of our four-part
series about using Excel to build a quoting system. So far, we’ve set up
the basic worksheet for our hypothetical company, AxeBuilders,
typed in and protected our standard data, and covered creating drop-down
list boxes to dynamically select list data, and covered creating check
boxes to dynamically select instrument options. In this installment,
we will actually create our estimate page.
Creating A Dazzling Estimate Page
Fortunately, the hardest part is over! Now comes the creative work of building an estimate page that
can be sent to customers. First, let’s do a little more work on the estimate
page so that we can personalize each proposal. Add a block on the Data
Input sheet to include the customer’s information, as shown below:
Now, let’s create our Estimate page by right-clicking
on the Standard Features worksheet, selecting Insert, then double-clicking
on the Worksheet icon. Then, double-click on the Sheet1 tab and retype the
name as “Estimate”. We will then have a blank Estimate worksheet positioned
between the Data Input sheet and the Standard Features sheet, as shown below:
Now, let’s create a simple Estimate page. First, type
the information in the Estimate sheet as shown below:
Then, we will copy over the Customer Information block
in such a way so that when it is changed on the Data Input sheet, it will
change in the Estimate. To do that, click on cell A5 in the Estimate sheet,
type “=” and hit Enter, then click on cell A10 on the Data Input sheet and
hit Enter. “Name” will appear in cell A5 of the Estimate sheet. Then, click
and drag the small black box in the lower right corner of the box to include
cells A5-A10 of the Estimate sheet. The results will appear as below:
Then, click and drag the small black box over one column
to include the range A5-B10. The results will appear as below:
Now, if we change the customer’s information on the Data
Input sheet it will automatically be updated on the Estimate sheet.
Next, let’s create a block for the product information
and pricing, as shown below:
Note that I have the values for the Instrument and Color
fields included in the Estimate. To do this on your sheet, set the contents
of cells B4 and B17 on the Estimate sheet to cells C1 and C2 on the Data
Now, go to the Data Input sheet and select the Instrument
Package “G12” and the Color “Black” from their respective Combo Boxes. The
Estimate page will be updated as shown below:
Now, let’s introduce the LOOKUP feature of Excel in order
to fill out the rest of the product information and calculate the pricing.
Using Excel’s LOOKUP Function
Excel’s LOOKUP function enables the user to look up information
in a multi-column, multi-record list based upon the value of one of the fields.
For instance, since we’ve selected the G12 model, we can use the LOOKUP function
to pull in to the estimate the value of the other standard features of the
G12, including its type, number of strings, neck length, and price.
However, before we use the LOOKUP feature on a list, we
have to make sure that the list is sorted. LOOKUP will not work properly
on an unsorted list (just as an unsorted dictionary would be useless to us).
Let’s start by sorting the Standard Features list. Select cells A1-F5 on
the Standard Features sheet, as shown below:
Then, select Sort from the Data menu; the following window
We can use this window to set the sorting methods on our
list. It turns out that the default sort value of this list is just what
we want, but note that you can change the column, change the sort order,
and do up to three levels of sorting. Also note that Excel intelligently
noticed that the first row was a header row that contained column headings.
Now, click OK to sort the list. It will appear as below:
Now that the list is sorted, let’s use the LOOKUP feature.
Click on cell B14 of the Estimate sheet, then select
Function from the Insert menu. A window will pop up that will allow you to
select from Excel’s many built-in functions. Click on the Lookup & Reference
function category, and select Lookup, as shown below:
Click OK, and the following window will appear:
Click OK again, and the following box will appear:
Note that next to the input area for Lookup_value is an icon that looks like a miniature spreadsheet. Click
on that icon, then select the Data Input sheet, then click on cell C1, then
hit Enter. The LOOKUP function box will be updated as shown below:
Note that the Lookup_value has
been set to cell C1 on the Data Input sheet, and the value “B5” confirms
that. Next, type “Model” into the Lookup_vector input
area and hit Enter. The function box will be updated as below:
Note that the values for the Model list (B4, B5, G12 and
G6) have been set to the Lookup_vector. Since we
created a Named Range (List) called Model, we can use the name reference rather than
selecting the cells. This is much handier than it even appears in this example,
as you’ll find out as you explore Excel more deeply. For now, it saves us
a little time and helps us to work more efficiently and accurately.
Finally, we want to set the Result_vector to
the range that we want to look up based upon which model instrument we select.
In this case, we want to specify the type of instrument based upon our model
selection, so type “InstType” into the Result_vector input area, and hit Enter. Excel then performs
the lookup, and the result is placed into the cell we selected, as shown
Let’s use the Named Range
feature to make the rest of our work easier as well. Click on cell C1 in
the Data Input sheet, highlight “C1” in the Name Box, and type “MyModel”.
Now, we can use the reference MyModel instead of ‘Data
Input’!C1 to refer to the model that we have selected.
Repeat the process as follows:
Now, our Estimate sheet will appear as follows:
Now, if you experiment with changing the instrument model,
you will note how all the other related information changes as well. Use
the Instrument Package Combo Box to select the G6 model, and have another
look at the Estimate sheet:
Now, it’s time to tie in the Options functionality. Set
cells A19-A21 of the Estimate sheet to refer to cells A4-A6 of the Data Input
sheet. Then click on cell B19 of the Estimate sheet and select Function from
the Insert menu. Then select the Logical function category and click on IF,
then hit Enter. The following box will appear:
Click on the small spreadsheet icon to the right of Logical_test, then click on cell
C4 of the Data Input sheet. Next, type “Yes” in the Value_if_true field,
and “No” in the Value_if_false field, as shown
Then hit Enter. The result will appear as follows:
Repeat this for the Gloss Finish and Fretboard Inlays
options, then experiment with changing the option values via the Data Input
sheet. You now have the basic features in place for your estimate page! What
remains is to set up the pricing features and jazz up the formatting.
First, let’s go to the Options sheet and set the prices
for the instrument options as follows:
Type the price information into column B next to each
option. Next, highlight the range A1-C3 and sort the range using column A1
in ascending order.
Now, notice I’ve introduced a problem that will need to
be fixed: since we changed the sort order of the Options list, the check
boxes are now out of order on the Estimate sheet (as you’ll notice if you
compare the options listed in cells A4-A6 on the Data Input sheet with their
respective Check Boxes). To fix this problem, we need to use Named Range references rather than relative
references for selecting our options. In fact, the more you use the Named Range
features, the more flexible and efficient your workbooks will be.
Go to cells C4-C6 in the Data Input sheet and use the
Name Box to name each cell “Opt_Active”, “Opt_Gloss”, and “Opt_Inlays”, respectively.
Then, go to cells A4-A6 in the Data Input sheet and change the option names
to match the names in the descriptions of the Check Box fields to restore
the original option order.
This is a real-world type of challenge that you will encounter.
Forethought can eliminate many of these goofs, but proper design techniques
will protect you from most of them regardless of how well you plan ahead.
Finally, after cleaning up the styling of the sheet and
adding the pricing and totals, we have a working, presentable estimate sheet,
as shown below:
In the sample workbook that comes with this lesson, I’ve
protected all the sheets but the data input sheet, so the only information
that the salespeople can change is on the data input sheet.
Note, with the protection that we’ve added, only those
with password access can alter the critical product information and pricing
data, and users cannot alter the estimate in any way beyond generating and
modifying proposals that you can be confident are accurate and look great.
With the building blocks we’ve covered in these lessons,
you can build a quoting system much more complex and powerful than Axebuilders’ model. I encourage you to explore other tools
in the Control Toolbox; when used in Excel, these tools can be used to build
complete applications that would normally take a full-time software development
team months or years if attempted from scratch.