Although you can find Excel spreadsheet opened on at least one computer in every engineering company every day, there are many new programs which compensate Excel’s drawbacks and upgrade daily engineering workflow significantly. But will Excel engineering spreadsheets be out of the game completely?
There are many engineering fields where Excel does a lot of work. With beginner Excel formulas, you can create magnificent engineering, management, financial or other calculating model, which you will benefit from. However, at some points, Excel stops being useful and from there you need related software to complete your task.
For example, you need to create numerical, iterative calculation. In the spreadsheet, you will make it with defined number of iterations, but you need to manually adjust your table every time, to copy formulas in new rows, to delete. In many cases it is not feasible - it is slow or not even possible. Especially in hydraulic civil engineering iterative calculations are very often used and often created in Excel, but related software certainly works better, faster, more accurate and advances where Excel stops.
You can create estimating model for your company, for example table with formulas, with all material and equipment you have. Then, you need to go through table finding items, put amount in required cell, then manually create quote document, by copy/pasting items in separate file…And probability that you will make a mistake is big. You can’t divide your project in segments, get insight in every project phase, without creating new model every time. You don’t have database where you will simply select desired items for your quote. All this things construction estimating software does.
So, should you leave use of Excel just for simple tasks?
By learning more advanced Excel techniques you can make progress and get more work done in less time. Things like Pivot Tables, Array formulas, Hyperlinks, IF, SUMIF…can make a difference. But…There is nothing more powerful than creating MACROS in Excel. That’s the thing that questions all Excel’s drawbacks. Macros can automate repetitive tasks, but not just that.
Macros can be recorded with Macro Recorder, so program saves your subroutine. However much more powerful thing is the VBA – Visual Basic for Applications programming language which you can access through Excel’s ribbon. This is the language that Excel uses when you record macro. Inside Visual Basic Editor - the magic can happen. By learning basic programming concepts very complex things could be solved.
There are two ways for creating a subroutine:
1. Recording with the Macro Recorder
2. Writing the code from in the VBA Editor
Combining Excel’s great built-in functionalities with some basic programming can make Excel competitive with many industry software products and even simpler and easier for use. Of course, we don’t compare it with some famous large scale software, created by many developers, but we want to say that you can make your spreadsheet super efficient, capable of solving almost any kind of engineering task, without tedious work.
Don’t worry if you do not have any programming knowledge, you will get it fast, because you are an engineer and engineers can do anything ;) Also, you don’t need to be an Excel expert to start creating macros, it is enough that you know basic things.
Here are some great FREE resources where you can start your VBA journey:
Here are pages dedicated to use of Excel in engineering:
At the second link you can find instructions and complete code examples applied on numerical methods in engineering and other engineering and complex mathematical application.
Check also an example of complete estimating application, created in Excel - by us ;)
Start today and you will have benefits through whole your life. After you master VBA basics and get some practice, feel free to start creating more complex things, there is a lot of help online, including complete codes and code snippets. And rely on your engineering mind, because engineers can do anything ;)