How to Create Dynamic Engineering Diagrams in Excel

Sometimes, a picture is worth a thousand words – especially in engineering. That’s why we have engineering drawings. Excel makes it easy to add diagrams to your worksheets to illustrate what’s going on in a problem using shapes. To add a shape, go to the Insert tab and choose Shapes:

This will show a list of all the available shapes. You can add them one at a time by left-clicking on a shape. If you’re going to need the same shape multiple times, you can right-click on the shape, and choose Lock Drawing Mode. We’ll use this to add a diagram to Worksheet 01f.

We’ll draw three rectangles: a large flywheel, a bearing, and a shaft. Press Escape on your keyboard to exit the drawing mode. Then right-click on the shaft and choose Send to Back.

To change the color of a shape, click it, then go to the Drawing Tools Format tab. Click Shape Fill and choose a new color:

For this example, we’ll change the fill color on the bearing to emphasize that it’s different from the flywheel. We’ll also copy that shape (Ctrl-C) and paste the copy (Ctrl-V) onto the end of the shaft. You can adjust the position of shapes by clicking and dragging or with the arrow keys to fine-tune positions.

We can combine values in the cells with the diagram to add labels for the dimensions. It’s easy to add labels that will update the diagram’s labels when the input values are changed. We’ll draw arrows to indicate some of the dimensions from the input section – first, the distance between the two bearings. Go to Insert > Shapes, then choose the arrow. To draw a straight horizontal arrow, hold the Shift key as you click and drag.

See also  Calculating the Integral of an Equation in Excel with VBA

The default arrow will only have a head on one side. For a double-headed arrow, go to Format > Shape Outline > Arrows, and choose a double-headed arrow.

The other dimension we’ll indicate on this diagram is the distance between the flywheel and the first bearing. Copy (Ctrl-C) and paste (Ctrl-V) the double-headed arrow, drag it into position, and resize it to fit (click and drag one end of the arrow while holding Shift to keep it horizontal).

To label the dimensions so that they’ll update when an input cell changes, select the cell right above the arrow. We’ll link it back to the input cell by entering in an equals sign, then clicking the desired input. For the first arrow we drew, the bearing distance, this will be =D7. Add the units (mm) in the next cell. We can do the same for the overhanging distance – type =D8 into the box above the arrow, and add the units in the next cell.

We can also add the mass of the flywheel. In the cell next to the flywheel, type m=. Move over one cell, and set it equal to the mass value (=D5). Add the units in the adjacent cell.

Now your diagram is labeled with dimensions that will update:

This is just another example of how to document your calculations in Excel. In the past three sections, we’ve covered many options for documentation, from adding comments to displaying formulas and diagrams. These Excel features can help explain the problem you’re trying to solve, all in the same spreadsheet as your calculations.

See also  Intro to Excel VBA User Defined Functions
Scroll to Top