[thrive_leads id=’2430′]
Hello, and welcome to the first video in this three-part series, 7 Efficient Ways to Create Accurate Engineering Calculations in Excel. My name is Charlie Young. I’m a professional engineer and founder of EngineerExcel.com. In the first video, I’ll show you how to use Excel to build error-free and easily verified calculation tools in Excel.
Excel is the best all-around tool engineers can use for performing calculations and analyzing data. If you master advanced Excel techniques, you’ll be able to produce accurate engineering designs in an efficient manner. As a result, you’ll become extremely valuable at work. Of course, with Excel, you can build spreadsheets to perform repetitive calculation and data analysis. This foundational feature of Excel enables you to save time and avoid reinventing the wheel for each and every project you work on.
The problem, though, is that Excel has a lot of commands that are confusing at times, and misusing them can lead to errors. Sometimes these mistakes can go undetected, which can be disastrous to an engineering project. But if you master it, you’ll be able to perform your engineering work both more efficiently and with greater accuracy. You can avoid those annoying errors that keep reoccurring and save days of pain and frustration on complex projects. You’ll even drastically shorten the time you spend on simple Excel calculations.
And when you get Excel working for you, rather than the other way around, you’ll free up time for other important work, and you’ll become extremely valuable to your employer. Just how valuable? A 2016 article by Time Magazine found that in some industries, engineers can boost their salary up to 8% with Microsoft Excel skills. That means an engineer starting out at $65,000 per year can make approximately $33,000 more in the next five years, and a whopping $409,000 more over a 40-year career. Now, I don’t know about you, but an extra $400,000 would go a long way toward funding my hobbies. And even if the 8% assumption is off by a factor of two, the result is still an extra $16,000 in the next five years, and $204,000 more throughout an entire career. Bottom line is, it pays to know Excel.
I recognized this opportunity early in my own career, so I’ve been researching and developing my Excel techniques for engineering over the last decade more. I had to learn many of these things the hard way, through trial and error, and lots and lots of Google searches. I tried a few training courses, but they were for general office work and just not geared towards the specific ways that I needed to use Excel as an engineer. But now that I’ve refined my techniques, I have been able to leverage Excel’s capabilities to efficiently analyze hundreds of thousands of lines of data, and create highly precise calculation tools in Excel that save hundreds of hours and thousands of dollars year after year.
I love sharing these techniques with other engineers, too, so, I created EngineerExcel.com, where I’ve helped thousands of engineers get more done in Excel. And, I’ve also put together this three-part video series to give you seven tips to help you work more efficiently and accurately in Excel. In this first video, I’ll show you a few of my favorite accuracy and efficiency-boosting Excel tips. We’ll look at a problem involving the heat transferred out a box made of insulating brick. We know the wall thickness, the average area, the thermal conductivity, and the inner temperature and ambient temperature. Now, if we’re doing this calculation frequently we’d want to build a spreadsheet to make it easy to perform the analysis quickly and accurately. So, let’s get into it.
Okay, so here we are in that spreadsheet and I’ve already gone ahead and started setting it up. And we’ve got this input section here in the upper left, where we have our inputs for wall thickness, area, thermal conductivity, interior temperature, and the ambient temperature. I’d also like to point out that the units in this problem are from mismatched systems of units. We’ve got some units that are from the English system of units, and then we’ve got other metric or SI units, and this is pretty typical in engineering. Now depending on where you’re getting your data from and what sources, you might have to deal with mixed systems of units, and if we know these quantities are always going to be provided in these units, we can just bake the unit conversion right into the spreadsheet.
Now, we could go either way with this conversion. We could go from English to SI or SI to English, but in this example and in most cases, it’s going to be much easier just to convert everything to SI units. Now, the wall thickness is in inches, and the area is in square feet, so we’d like to convert those to meters and square meters, respectively. Now, many people might just either recall a unit conversion that they’ve committed to memory, or they might look up a unit conversion somewhere online, but this can lead to mistakes if you mistype a unit conversion factor. What’s even worse, is that if the conversion factor is lumped into the formula, it can make the formula really difficult to understand later on for yourself or someone else who comes later, and looks at the formula and sees this hard-coded number in there and isn’t quite sure how it fits into the equation. So, rather than using a conversion factor that we look up or hard-coding anything into the spreadsheet, we’ll use a function in Excel called the Convert function, which is made specifically for converting between systems of units.
So, the syntax for the convert function goes like this, it’s equals convert. Now, the first argument is going to be the number, which we want to convert to a different system of units. So, in our case it’s the wall thickness, so it says value of four right here. The next argument is going to be the From unit. In this case, that is inches, and the units are text strings so they need to be entered in double quotations. So, we’re converting from units of inches to units of meters. So, that’s how we would set up this function to convert the wall thickness from units of inches to inches of meters. If I go ahead and hit Enter we get that new result in units of meters.
A handy technique that makes this formula a little bit quicker to implement in your spreadsheet is rather than typing in these units, in the from unit and to unit arguments, we can go ahead and delete out what I’ve typed in here and if you’ve already got your units entered into a cell in the spreadsheet you can just go ahead and select that cell. So, this is the cell containing the abbreviation for inches, and then we’ll delete out the argument for To unit and go ahead and select the cell containing the unit meters. And if we hit Enter, of course, we still get that same unit conversion.
[thrive_leads id=’2430′]
So, that’s just a quicker way to enter in that convert function, and a convert function works with many different units. We can also use it to convert Area, from 24 feet squared to meters squared. Just like that. So, now all that’s left to do is to come down here into our Results section and calculate the heat transfer out of this box that’s lined with insulating brick. Now, of course, we can always enter a formula by using standard cell references in Excel, so we just type equals, select the cell containing the value for the thermal conductivity, multiply that by the cell containing the value for the area. But of course, as you know, that leaves behind this equation that contains a bunch of cell references that, first of all, need to be entered correctly, and secondly, if you or someone else ever wants to verify the equation, it’s really difficult to understand how the formula has been set up, what the inputs are to the equation when it’s referencing these obscure, pretty ambiguous cell references.
So, instead of cell references we can use something Excel called Named Cells. And by using named cells we’re essentially assigning a variable to a cell and then we can use that variable within our calculations later on. So, I’m just gonna escape out of that cell, and we’re gonna create the names for the cells that we want to use in the calculation. And the way we do that is go ahead and select the cell, then go up into the Name box here, and just type the name that we would like to use for that cell. So, this is the Thermal Conductivity, we’ll call it k; the Interior Temperature, which we’ll call T underscore hot; the Ambient Temperature, which we’ll call T underscore cold; the Wall Thickness, which we’ll call L; and finally, the Area, which we’ll call A.
And now that we’ve created these named cells we can still go ahead and enter the equation, and just like we would have before, by typing equals, going in and then selecting these cells. Now, as you can see, what happens when we select the cell, rather than using a cell reference, it’s using the name here and the formula. But what’s great about named cells is that we can also just treat it like a variable so we can enter this equation in, just as we see it shown over here. So, it’d be k times A, times the quantity T underscore hot, minus T underscore cold, divided by the wall thickness, L. And, as you can see, now we’ve got this formula. It’s very closely representing the formula that we’re used to seeing. It’s easy to tell at a glance what this formula is calculating, which inputs it’s using and how the formula is set up. So, now of course, we can just hit Enter and get a result.
So, this is all great if you’ve got a brand new spreadsheet that you’re just setting up. That’s a good way to set up named cells and to use them in an equation, but what happens if you have an existing spreadsheet and you’ve already got a bunch of formulas that are using cell references and you’d like to update it to named ranges? Maybe because you’d want to make it easier to verify, or you wanna make it easier to make additions to the spreadsheet in the future. Well, there is a way that you can update a spreadsheet to use named ranges and I’ll show you how to do that on this next tab.
So, on this sheet I’ve done the calculation for heat transfer using the standard cell reference method, by referring to cell C7, C13, and so on. But, I’m gonna show you how you can batch create the named cells, and update the calculations with just a few simple steps. So, I’m gonna escape out of this cell, go up here to column C, right click on it. I’m gonna insert a new column, make it a little smaller, because what we are gonna do here is we are going to enter in the cell names that we’d like to apply, and I’ll show you a quick technique to enter those names and then create all the name selections at once. So, the Thermal Conductivity, we’re gonna call that k, the Interior Temperature again is going to be T hot, Ambient T cold, Wall Thickness L, and Area A.
So, now if we go ahead and we select the cells containing the variable names, as well as the values, we have them all selected, we go up here into the Formulas tab, and select Create from Selection. Excel’s automatically going to determine where the names are at relative to the values. So, as you can see it’s chosen Left column, and that’s where the names are, they’re in the left column of the selection. Now, if we click OK, Excel has automatically created these names for us. So, the value for Thermal Conductivity’s been given the name k. Let me check that up here in the name box. The value for Interior Temperature has been given the name T hot, and so on down the line. You can see that all those named cells have been created rather quickly just by using Create from Selection.
So, now the input cells have names applied to them, but our equation down here is still using the old cell references. So, what we need to do is to apply those newly created names to this formula in this cell. So, I’m gonna escape out of here again, and we’re still in the Formula bar, and we’re gonna click this little arrow right next to Define name, and we’re gonna choose Apply Names. Now, all the names that we’d like to apply have already been selected automatically for us, so what we need to do here is just click OK. And now, if we click on this Heat Transfer calculation cell, you can see that the equation has been updated to use these name ranges, and it’s no longer using cell references, which of course makes this equation much easier to understand and easier to troubleshoot in the future.
In the next video, I’ll show you how you can import engineering data from the web to a spreadsheet using Power Query in Excel. Then, I’ll show you how to go beyond the common Vlookup function to create much more powerful lookup tables in Excel. But first, I’d love to hear what you’re thinking. Leave a comment below with any questions, and tell me how your job would be better if you could be more efficient at creating accurate spreadsheets to solve complex engineering challenges in Excel. Thanks again for watching, and I’ll talk to later.
[thrive_leads id=’2430′]
