Excel for Architects - Part 1

 Next to BIM and CAD software, my bet is that Microsoft Excel is the next most-used software in the architect’s toolbox. I probably use Excel at least once a day. If you’re like me, no one ever showed you how to use it – you just figured it out on your own. Spreadsheets are easy, right?

Excel is a very powerful software for crunching data. You can sort data by various criteria. You can link together multiple spreadsheets. It’s also useful for formatting and presenting that data. You can generate charts and other representations. It’s more than just simple spreadsheets.

This five-part series will take you through some of the more advanced features of Excel. Over the course of this series, we’re going to cover the following topics

• VLOOKUP function
• Pivot tables
• Linking spreadsheets
• Macros and automation
• Advanced formatting and presentation

Part 1 – Using VLOOKUP

VLOOKUP is easily one of Excel’s most useful functions. VLOOKUP lets Excel function more like a database than just a spreadsheet. It lets you search for a value in another location such as another series of cells or another worksheet in the Excel file. If Excel finds that value, it displays other information associated with the value.

Say you’re working on a door schedule. You extracted all your door numbers and door types from your floor plans. You would like to include a brief description of the door type in the schedule. You could do this pretty easily by writing the description once and then using copy/paste to copy it to all subsequent instances of the type. This works great except when the description changes. Then you need to copy and paste it all over again. What if the descriptions are constantly changing? That’s a lot of extra work keeping up with the changes.

However, you can create a separate worksheet in your Excel file that contains a list of all the door types and descriptions. Using VLOOKUP, you create a reference to the door types table in the door schedule spreadsheet. Any changes in the door type spreadsheet will be reflected in the door schedule spreadsheet. Sounds great, right?

One key aspect of VLOOKUP is that you need a “key” or unique identifier to link one value in a spreadsheet to another value. In our door schedule example, we’ll use the door type as the key. The door schedule has a “Door Type” column as does the door type spreadsheet. Excel will use the value of the door type column to link with the door type spreadsheet and get the door description value. Let’s work through an example.

Step 1 – Create a new worksheet

Start by creating a new Excel file. By default, Excel creates a single workbook titled “Sheet1”. Clicking the “+” icon next to the sheet tab (located at the bottom of the screen) creates a new worksheet.

Step 2 – Rename worksheets

Rename “Sheet1” as “Door Schedule” and “Sheet2” as “Door Types”. These are the two worksheets we’ll work with for this example.

Step 3 – Add doors

In the “Door Schedule” worksheet, create columns for “Door #”, “Door Type” and “Description”. Add the following data to the worksheet:

Step 4 – Add door types

Switch over to the “Door Types” worksheet and add the following data:

Step 5 – Create a named range

Named ranges make it easy to refer to specific cells in a worksheet. To create a named range for the door types, start by highlighting the door types (but not the column headers) in the worksheet. Right-click and select “Define name” from the menu. Enter “Doors” as the name. This will make it easier to refer to the door types in the VLOOKUP formula.

If you add additional door types at a later date, you will need to adjust the named range. Go to Formulas > Name Manager in the ribbon. Select the “Doors” name from the list and adjust the range in the “Refers to” section.

Step 6 – Insert function

We want to get the door description based on the door type. To do this, we’ll need to insert a formula using the VLOOKUP function. Click back to the “Door Schedule” worksheet. In the “Description” column for the first door, click Formulas > Insert Function from the ribbon. Type “VLOOKUP” in the search box, select it from the list, and click the “OK” button.

Step 7 – Format VLOOKUP

There are four fields you need to fill out for the VLOOKUP function:

1. Lookup_Value – The “key” value in the door schedule worksheet. In this example, type B2 (the current door type).
2. Table_array – The range of cells in the door type worksheet. In this example, type the named range “Doors”.
3. Col_index_num – The index of the column you want to display. In this example, we want to show the description information so enter “2” in the field.
4. Range_lookup – Enter “false” if you only want exact matches or “true” if the match can be close.

Click “OK” and Excel will display the description of door type A from the Door Types worksheet.

Step 8 – Copy forumula

To apply this formula to the other doors in the door schedule worksheet, copy and paste the formula into the remaining description cells. The formula will pull each door’s description from the “Door Types” worksheet.

Test it out by changing a door’s type to type D. This will change the description to “10′ x 10′ Rolling”. Likewise, switch over to the Door Types worksheet and change a door type’s description. When you switch back to the Door Schedule worksheet, the doors of that type will have the updated description.

Summary

This is a fairly basic example but it shows the power of the VLOOKUP function. Because VLOOKUP creates references to your data, it saves you time when you need to make changes. You no longer need to hunt through your spreadsheet and copy/paste the updated data. As a result, your data is more consistent and less likely to contain errors. Pretty cool, huh?

What other types of spreadsheets could benefit from the VLOOKUP function?