Learning by Doing: The Power of Spreadsheets, Part 4

Last month, we finished creating a simple takeoff spreadsheet in Excel. I’m going to close this series by covering some additional information about spreadsheets and their features.

First, a little more information about left and right clicks, which can be confusing for beginners. In a spreadsheet, left clicks are often used for selecting things, such as a menu item or button. Left clicks are also used for selecting a cell or range of cells. Right clicks are most often used for bringing up context-sensitive menus. Generally, when you right-click on something, a menu will appear with functions related to the thing you selected. For instance, if you right-click on a cell, the menu will include functions such as copying, pasting, deleting and clearing contents.

If your imagination has not kicked into gear after creating your first spreadsheet, here are some examples: estimate recap, general takeoff, feeder takeoff, duct bank calculator, accounts receivable worksheet, time sheets, invoices, time and material bills, bidding records, submittal logs, change order records, accounting systems and complete electrical estimating systems. Because it’s worth repeating, I will say it again: Almost every task that requires math, or the recording and analysis of data, is better done with a spreadsheet.

In regard to using the “F2” key for editing a cell, I have noticed on some new laptop computers, the “F” keys have default alternate functions such as changing the volume and screen brightness. Check with the computer manufacturer if you prefer the standard functions to be the default setting.

Another great feature of spreadsheets is the ability to create multiple pages in a single file. Each page in the file can refer to information on the other pages in the file. For instance, you can combine a price sheet and a recap into one file, and have the totals from the price sheet automatically appear in the recap. If you’re up to trying something more advanced, you can also create a link to information in another file.

One of the best ways to learn more about spreadsheets is to play with them. Open a blank spreadsheet and start clicking on buttons to see what they do. Most of the things you can click on will show a hint if you hover the mouse over it. Another way I learned is related to the saying, “Necessity is the mother of invention.” If I needed to accomplish something, I searched for a way to do it.

For instance, at one time, I was estimating for Canadian customers, and the plans were most often designed using the metric system of measurements. After a couple of projects, I got tired of making the conversions to American Standard with a calculator, so I created a spreadsheet to do the work for me.

Excel’s help features are pretty good. They include traditional help screens, the ability to ask a Microsoft agent questions, and a “community” help feature where other users can answer your questions. My only problem with Excel’s help is sometimes the traditional help screens, while clear, do not show you how to get to the feature or menu you are asking about.

Of course, as in all well-written Windows programs, there is a way around that problem. In the Office 365 version of Excel, the help window is labeled “Tell me what you want to do.” When you type something in the window, it brings up a list of related functions.

For instance, when I type in the word “left,” it brings up five possibilities, including left align, which is what I was looking for. Clicking on these suggestions will either perform the task, or take you straight to the function.

Another great resource is the internet. Simply ask your question in a search engine (Google, Yahoo, Bing, etc.), and you will get hundreds of answers.

While it is possible to do almost any mathematical or data related task with a spreadsheet, sometimes it is not practical. I know of at least a dozen people who have created an electrical estimating system in Excel. Obviously, it can be done, but usually at a great expense of time. You may be better off purchasing an estimating system, as several good ones cost less than $2,500, with reasonable annual maintenance fees.

I imagine the choice depends on your circumstance and abilities. I was tempted once to create my own estimating system, but soon discovered I was better off buying one, as I could see how much estimating time I was going to lose while creating and maintaining the system.

I hope these last four articles have been a good introduction to the power of spreadsheets and that they become a time saving tool for you.

About the Author

Stephen Carr

Estimating Columnist

Stephen Carr has been in the electrical construction business since 1971. He started Carr Consulting Services—which provides electrical estimating and educational services—in 1994. Contact him at 805.523.1575 or steve@electrical-estimating.com, and...

Stay Informed Join our Newsletter

Having trouble finding time to sit down with the latest issue of
ELECTRICAL CONTRACTOR? Don't worry, we'll come to you.