Excel is more than just digital graph paper. It’s a serious tool for analyzing and computing data. In order to access this power, however, you need to understand formulas.

This post is part of ArchSmarter’s Excel for Architects series. You can find all of the previous posts in the series here.

If you’re like me, you started using Excel as a way to create nice looking tables of data – things like building programs or drawing lists. Lots of text and some numbers. Nothing too crazy. If I was feeling a little bold, I’d add a simple formula to add or subtract some cells. That’s about it.

I knew I was using only about 10% of the software but I wasn’t sure what else it could do or how I could access the other functions. I’d heard about formulas but they seemed really confusing. Plus, I was an architect, not a bean counter.

It wasn’t until I ran into a number problem that I realized the true power of Excel. I needed to analyze the leasable area for a large mixed-use project I was working on. We were getting different area numbers from the developers. Since no one likes losing area, I had to dig through the data to figure out what was going on.

So I rolled up my sleeves, took a deep breath and plunged into the world of Excel formulas. A few hours later, I had a lean and mean spreadsheet that accurately calculated the leasable area. Using the formulas I had built, we could quickly play out scenarios for our client. This spreadsheet ended up being a really useful tool during the design phase.

### Get started with Excel formulas

Inserting a formula into a cell is real easy. Just type an equals sign (=) followed by the formula. You can click the formula icon to open the “Insert Function” dialog.

You can also access all of the Excel functions through the “Formulas” ribbon. All of the formulas are grouped into categories. Click the category you want then select the formula from the list. This will open a dialog where you can input the formula parameters.

What’s the best way to learn Excel formulas? I’m a big believer in learning by doing. Take a spreadsheet you’ve created and see how you can make it better with formulas. Not sure which ones to try? Here’s my list of 12 Excel formulas every architect should know:

#### 1. SUM

Adds together all the values in the specified range. The range can be a single column or multiple columns. You can even specify individual cells by using a comma to separate the values.

* =SUM(A5:A25)*

#### 2. IF

Returns one value if a condition is true and another if the condition is false. Useful for getting a quick overview of your data. You can also use AND or OR within the IF statement to build complex logic.

* =IF(A2>B2, “NEED AREA”, “AREA OK”)*

#### 3. SUMIF

Performs the SUM function only on instance that meet certain criteria. Use SUMIFS to specify multiple criteria.

* =SUMIF(A1:A7, “>0”)*

* =SUMIFS(A1:A7, A1:A7, “>100”, A1:A7, “<200”)*

#### 4. COUNT, COUNTA, COUNTBLANK

Counts the number of items in the specified range. COUNT only counts numbers, not text or blank cells. COUNTA counts cells that are not empty. This includes number, text and other types of data. COUNTBLANK counts only cells that are blank.

* =COUNT(A5:A25)*

* =COUNTA(A5:A25)*

* =COUNTBLANK(A5:A25)*

#### 5. COUNTIF

Similar to COUNT but will count only instances that meet the specified criteria. Use COUNTIFS to specify multiple criteria. For instance, rooms that are greater than 200 SF but less than 500 SF.

* =COUNTIF(A1:A8, “>100”)*

* =COUNTIFS(A1:A8, “>100”, A1:A8, “<200”)*

#### 6. AVERAGE

Returns the average or arithmetic mean of the specified range of cells.

* =AVERAGE(A5:A25)*

#### 7. MIN

Returns the smallest number in the specified range of cells. This might be useful for finding the smallest area in a programming spreadsheet.

* =MIN(A5:A25)*

#### 8. MAX

Similar to MIN but this formula returns the largest number in a range of cells.

* =MAX(A5:A25)*

#### 9. VLOOKUP

VLOOKUP helps Excel function more like a database than just a spreadsheet application. With it, you can search for values based on other values, which can be located in another part of the worksheet or in a completely different worksheet. In the formula, you need to specify the key value, the range of values you want to search, and the column number of the value you want to return. VLOOKUP is a little tricky to use so I highly recommend checking out this step-by-step guide.

* =VLOOKUP(B3,$A$17:$B$20,2)*

#### 10. ROUND

Rounds a number to a specified number of digits. Can also use ROUNDUP and ROUNDDOWN to specify the direction of rounding.

* =ROUND(7.86, 1)* results in 7.9

* =ROUNDUP(7.23, 0)* results in 8

* =ROUNDDOWN(8.85, 1)* results in 8.8

#### 11. FLOOR and CEILING

These two functions round a number up (CEILING) or down (FLOOR) to the nearest specified multiple. Useful when rounding currency.

* =FLOOR(A1, 10)*

=CEILING(A2, 0.25)

#### 12. CONCATENATE

Use the CONCATENATE function to join two cells together. This function is great for piecing together text that resides in separate columns. You can also use an ampersand (&) instead of typing out CONCATENATE.

* =CONCATENATE(B1, “, “, A1)*

* =A3& ” ” & B3*

### A few more things about formulas

Named ranges are great to use with formulas. Rather than typing the cell range (like A3:B4), you can enter the name (like “Level1Area”). Plus, if the range changes, just update it once in the “Name Manager”. You don’t need to update each formula.

You can review all the available formulas by going to the “Formulas” ribbon and clicking one of the icons in the “Function Library” section. All of the formulas are organized by category. Likewise, you can click the “Insert Function” button to see all the available functions.

You can include one formula in another formula. This is known as “nesting function”. In Excel 2013, you can nest up to 64 functions.

Copying and pasting formulas can sometimes be tricky. By default, Excel will increment the cell ranges when you paste a formula. Sometimes this is useful, particularly if you’re using SUM to add up a row of values. However, sometimes you want to calculate specific cells. In order to do this, use a “$” before the cell to designate it as an absolute reference. For example, if I want to multiply cell B4 with cell D3, I would type my formula as “=B4*D3”. Now, if I want to copy this formula down the column but I still want to multiply by cell D3, I would type the formula at “=B4*$D$3”. This designates cell D3 as an absolute reference so Excel doesn’t increment it.

### How about you?

How do you use formulas in your spreadsheets? If so, what’s your favorite formula? Leave a comment below!

**Want to see these formulas in action? Download the Excel sample file.**

Neil Stroup says

Thanks for a great roundup of essential formulas. I find the SUMIF function to be invaluable when analyzing cost data. Say I’m considering a range of design elements to include (or exclude) from a project. I list the elements and their costs in two left-hand columns. I can then create columns to the right for different cost scenarios, using an X or bullet to indicate which items are to be included. In each column I then use SUMIF to sum all the costs of the bulleted items in each scenario. Items can easily be toggled on or off, allowing for real time adjustment and cost comparison between scenarios.

Michael Kilkelly says

Thanks for the tip Neil! Using that method, it sounds like you can quickly work through different cost scenarios. That’s a much better method than creating separate spreadsheets for each option. Great example of working smarter!

Steven D Papke says

Neil, that is an intriguing idea. Can you email me or post an example in excel demonstrating this functionality? I can’t seem to figure out how to make it work. Thanks. Great article Michael.

David Brook says

I also find conditional formatting of rows to alternate each row another colour/ shade very useful for those exceptionally wide tables, provided you’re not using cell shading otherwise.

“=MOD(ROW(),2)=1” applied to the area which you want to shade, and then set the fill style you desire to shade every other row.

Michael Kilkelly says

I totally agree. That method greatly improves the legibility of the spreadsheet plus you don’t have to deal with filling the cells manually. Thanks David.

Kris says

There are some great examples there, I will try using a few of them in my next spreadsheeting,

If anyone can suggest a formula that can convert time in decimal hours (eg. 4.75 = 285min) to ‘time’ hours (ie. 04:45:00) and back again? This would be super useful for setting up / filling timesheets, one of the most used spreadsheets in an architects’ office.

Peter Farrell says

Most of what you need is accomplished by formatting the cell to either display TIME (4:45) or a NUMBER (4.75), the only bit you need to add is to either multiply or divide the value by 24. To go from decimal hours (4.75) to ‘time’ hours divide 4.75 by 24 hours. Then format the answer cell as the appropriate time format.

Why you ask? Because Excel converts numbers to time on the basis of 0-1 being equal to 24hrs, so 4.75/24 = 0.19791667 which, when you format the answer cell as time (00:00:00), will yield 4:45. To go back, you multiply 4:45 * 24 (that’s really 0.19791667 * 24) and you’re back to 4.75.

michael kilkelly says

Great tip. Thanks Peter!