Dressing It Up: The Power of Spreadsheets, Part 3

This month, we complete the takeoff spreadsheet. We left off last month having typed in some text. Let’s do a little formatting now.

Most formatting is done by highlighting a cell or range of cells, then left-clicking on a command in the ribbon or right-clicking in the highlighted range to bring up a menu. Let’s start by highlighting a large range. Left-click and hold on cell A5, then drag to cell K30, and release the mouse button. Now, right-click in the highlighted area, and then left-click on “Format Cells.” A dialog box appears with tabs across the top. Left-click on the “Border” tab. We can use this function to add lines almost anywhere.

Let’s add lines around the highlighted cells. Left-click on the thin, solid line in the Style section of the dialog box, left-click on both small graphics labeled “Outline” and “Inside,” then click on “OK” at the bottom of the dialog box. Voila! The cells you highlighted have lines around them.

Now let’s dress it up a little more. To make Column C very narrow, point to the line between the headers for Columns C and D, left-click and drag until the column is the width you want. Next, let’s change the line under Row 6 to a bold line. Highlight from A6 to K6, right-click in the highlighted area, left-click on “Format Cells,” left-click on the Border tab and select one of the two bolder solid lines in the Style section. The area in the center of this dialog box shows the current formatting of the highlighted cells. It should show a line all the way around and a vertical line in the center. Click on the bottom line, click “OK” and you will have a bold line separating your column headers from the data below.

Let’s put the automation in next. In cell B8, type the formula “=SUM(D8:K8)”, and hit enter. A zero should appear in the cell. Now try typing some numbers in the cells to the right. Every number you type will be added to the total in cell B8. Next, copy that formula to the other rows below. Right-click on cell B8 and click “Copy.” Then, highlight cells B9–B29. Hold down the Ctrl key (Command on a Mac) and type V. (This is a keyboard shortcut for pasting.) Your formula should now be in all of the cells you highlighted.

It’s a good time to go over the inner workings of copying and pasting formulas. If you point at cell B8, the edit line above the column names will show you the contents. In this case, it should read “=SUM(D8:K8)”. Point to each of the cells below D8, and you will see the row numbers in the formulas have automatically adjusted to be the same as the row the formula was pasted into. This feature saves a ton of time when it comes to typing formulas. The program usually adjusts the cell references in formulas you copy and paste.

Now, I want to center some text. Highlight cells A5 though K6. In the home ribbon is a section with graphic representations of alignments. The top row is where the text and numbers will align vertically. The bottom row is horizontal alignment. Left-click on the center icon in the bottom row, and the highlighted text will be centered.

Next, highlight from A5 to K30. Right-click on the highlighted area, left-click on “Format Cells” and left-click on the Border tab. Left-click on the double line in the style box, and then left-click on the graphic labeled “Outline.” This will put a double line all the way around your takeoff sheet. Delete any numbers you entered to test the formulas, and delete the page numbers we put in last month. Save the file, and you have finished creating your first template.

If you find more rows or columns are necessary, simply right click on a row or column header and left-click on “Insert.” In the case of inserting new rows, you will need to copy the totaling formula to the new rows.

Finally, here is a hard rule for using templates. After opening a template to start a new takeoff, the first thing you must do is perform a Save As and give it a new name, so you don’t overwrite your blank template.

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.