It may not be the flashiest or hippest software, but at some point you are almost certainly going to have to use Microsoft Excel. “Of course”, you say to yourself, “I know how to use Excel. We did that in grade three”. The problem is, while most of us can use Excel, not all of us know how to use it wisely. If you find yourself awash in ‘#REF!’ and ‘#DIV/0!’, maybe it’s time for a refresher.
Luckily, the eloquent Keith Davidge was happy to share his knowledge at our first Excel Seminar on October 21. Drawing on his workplace experience, Keith covered a wide variety of Excel topics that ASHRAE students should find useful.
One simple trick we learned was how to use ‘IF’ statements to get rid of annoying errors. When a cell uses another cell in a formula, this is called referencing. When a cell that is referenced is blank, the formula that references it can return an error. We can get around this problem by adding a logical test to our formula, for example =IF(K5<>””,D5/K5,””) . This tells Excel to perform the calculation ‘D5/K5′ only if there is a value in K5, and if not then to leave it blank.
Another frustrating source of errors and confusion can be the misuse of relative references. By default, cell and range references are relative. This means that when you copy, for example, the formula =SUM(A1:A3) into column B, it will change to become =SUM(B1:B3). This is sometimes very useful, but not always. Ways around this are to cut (ctrl+x) or insert rows instead of copying (ctrl+c). Another solution is to use absolute references instead, which look like this: =SUM($A$1:$A$3).
It’s one thing to sort through a messy spreadsheet you’ve created yourself, but trying to figure out someone else’s mess is a different battle. This, we learned, can be made a bit easier with the ‘trace precedents’ and ‘trace dependents’ options, found under the formulas tab. These show the relations between cells with a handy arrow.
Conditional formatting was another highlight of the seminar. Excel 2013 has a number of cool formatting options, like data bars and color scales, that are easy to incorporate into your worksheet. However, it is important to remember that while these formats are there to assist in visualizing data they shouldn’t hinder readability.
More seminars are planned for the future, so stay tuned. Our next event will be dealing with some important ASHRAE standards that you need to know, both now and later in your career.
Written by Cassandra Heide