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 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 move 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 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 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 follow data to the worksheet:
Step 4 – Add door types
Switch over to the “Door Types” worksheet and add the following data:
Step 5 – Create 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:
- Lookup_Value – The “key” value in the door schedule worksheet. In this example, type B2 (the current door type).
- Table_array – The range of cells in the door type worksheet. In this example, type the named range “Doors”.
- 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.
- 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. Becasue 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? Leave a comment with your suggestions.
We just had a conversation yesterday about wanting to be able to take better advantage of Excel’s capabilities but not being quite sure…..so thanks for your perfect timing!
Thanks for the comment and I’m glad you found the information useful. If there’s anything in particular you’d like to know more about, please let me know and I’ll try to include it in a future post.
This great. Looking forward to giving it a try.
Any ideas on how to link xcel to Vectorworks?
Looking forward to part 2!
Thanks Joe. I don’t know how to import Excel into Vectorworks but I’ll look into it. Do you want to put the spreadsheet on a drawing sheet or are you looking to import the data into the model?
I think what joe is trying to do, is to have VW data to sync and update automatically in an excel worksheet, since VW it´s a BIM solution this should be posible, but I came across to a bit of an obstacle with OBDC, since thats the only way VW would sync with excel, it would be great if you can cast some light on the subject, thanks for the tutorial.
Thanks for sharing this, Michael. I taught a business practice course for several years to aspiring interior design professionals. In it, we used very basic functions of Excel to calculate personnel costs, overhead, and profit, yielding hourly rates for a hypothetical, small design firm. It has been dismaying over the years to realize the number of fellow architects who have no clue of how to determine what to charge to operate their businesses. I was glad to learn from your post a function or two of Excel that I had not known previously.
It inspires me to consider (again) the possibility of placing a course on business cost calculation online, specific to architects.
I look forward to your next post.
Andy,
Looking forward to your post regarding business cost calculations.
Erik
Andy,
Same as Erik – I’m looking forward to hearing more about your course. Keep me informed!
Michael, just stumbled on your site. Cool! Love what you are doing on here!
Thanks Michael for sharing. I am looking forward for part 2.
Hey Prof K!
Great stuff…
I spent a year traveling to Mid Atlantic cities as a consultant for A’desk…speaking on ” what is BIM” to architects (I often felt like I was the enemy…data is indeed a 4 letter word to uninitiated folks). But I would describe BIM as a graphic representation of excel spreadsheets in an effort to re-frame the design environment.
So these short, easy to understand tutorials are so appropriate for the AEC industry right now.
An insightful and generally low vendor impact group you might be interested in BIMForum. I would be happy to make introductions…Michael Puddicombe from Norwich Engineering is on the Educational Committee.
Hey Prof. Church! I like the Excel analogy for BIM – most people are familiar with Excel at least on a basic level. It’s a good point of entry into the deeper structure of BIM. I agree that “data” can be a scary word for some architects. However, I believe we architects are going to be working with and manipulating more and more data in the very near future. We need to develop greater facility and skills in this area. I will definitely check out BIMForum and an introduction would be great!
The use of that particular analogy is more that little ironic: With Revit, autodesk bought and has developed a product that is essentially a graphic display of huge datasets, but it cannot link directly to Excel – the most commonly used and familiar tool in the MEP end of AEC design!
(When I say ‘cannot’ of course, I mean ‘will not’ … someday autodesk will permit the connection of Revit to Excel, and then trumpet it loudly around the world as a huge innovative step forward ….. their business model of keeping the user’s expectations low, and the shareholder’s margins above 10% seems to working well ….. for the benefit of the latter, of course …. and upper management ………. )
Im really loving all the great content on your blog. I just found you from your comment on my Fancy Hands blogpost. Looking forward to reading the archive.
This is great! Keep up the great work!
Thanks alot
Hi.. is still avilable a course on business cost calculation online, specific to architects.
thanks,
Paola
Hi Paola,
I offer a course on Excel for Architects. Here’s the link:
http://learn.archsmarter.com/courses/excel-for-architects
It covers advanced Excel topics but not cost calculation specifically.
Michael
Great Stuff. Thanks for the information.