Get free access to our online edition!

Nuts & Volts Magazine (October 2006)

Spreadsheets - The Forgotten Analog Design Tool

By Thomas Henry    View Digital Edition  

Nowadays, there’s a ton of software kicking around to greatly simplify the design and construction of electronic circuits. Packages to capture schematics, perform simulations, lay out printed circuit boards, and so forth, abound. The trouble is, most non-professionals can’t justify the typically stiff expense of these, especially when not used on a daily, income-producing basis. But there is one software tool that most everyone can afford (in fact, you probably already own it) and it really does speed up the creation of many new circuits: the spreadsheet.

Like Edgar Allan Poe’s The Purloined Letter, the spreadsheet has been in full view for over 20 years now and yet has remained virtually invisible to the average electronics enthusiast. Far from being only a financial tool, it can easily be coerced into performing a number of useful, repetitive, and complex operations that really take the tedium out of customizing circuits.

In the June 2005 issue of Nuts & Volts, Peter Stark got the ball rolling by demonstrating how to apply spreadsheets to the digital world. The emphasis in this article is on analog design — an intimidating area to many since it often involves difficult or wearisome calculations. Not so anymore if you let the software help! You’ll learn how to get started and master the general methods, and as a side benefit, create some handy reference sheets you can put to use right away.

As mentioned, you may already have a spreadsheet program on your personal computer. But if not, don’t worry! The Resources sidebar shows several places on the Web where you can find inexpensive or even free spreadsheet programs. Most of these behave similarly, so the instructions given below (tested in Microsoft’s Excel program) will work with little or no change regardless of the package. If this all sounds intriguing, let’s dive in and see what’s possible.

Suppose you need to amplify an audio signal by a certain fixed amount. The standard inverting op-amp configuration comes to mind, and indeed it’s easy to compute the gain of such a circuit: just divide the feedback resistance by the input resistance. But in this situation, we need to work backwards. We already know the desired gain and wish to learn which pair of resistors will create it.

You could try a bunch of different values on your pocket calculator, recomputing the gain for each pair until you hit the magic number. But a slicker way is to simply let the spreadsheet program compute the ratio for every conceivable pair, and then read off the required combination. Best of all, if you print it out, you can punch it for a three-ring binder and have a ready reference for future work.

This is a simple example, but worth carrying out if for no other reason than that you can use it as a template for many different situations involving resistor pairs. Keep reading to learn how to implement it.

The Three Parts of an Electronics Sheet

To be most useful, an electronics spreadsheet should consist of three parts: a simple diagram or schematic showing to what situation the calculations apply, a brief verbal description of the circuit’s purpose, and the calculation table. If you include these three parts in every spreadsheet you create, then not only will you avoid the old “hmmm ... I forget what this is supposed to do” syndrome later on, but you can share it with other users and get your point across precisely.

So, the first step is to draw a simple schematic. For convenience, this is placed in the upper left-hand corner of the sheet. It could be drafted using nothing more than Windows Paint (which comes along for free with that operating system), the line-art tools that are part of Microsoft Office, or even a standalone drafting package that has an export option. Most spreadsheet programs let you embed a graphic directly. For example, in Excel, you go to the menu bar and select Insert>Picture>From File. File types like .gif, .jpg, .bmp, etc., are handled with ease.

Okay, so you’ve got the diagram in place now. Next to this, write up a brief description of what the sheet does and how to interpret the results. As mentioned above, this not only serves as a memory jog later on, but also makes the sheet more useful to others. In Microsoft Excel you could put this in a text box by clicking on that icon (it looks like a sheet of paper with a capital letter A on it), or you could simply enter the description in an empty cell, letting it spill over as needed.

Setting Up a Resistor Template

Now it’s time to work on the calculation table. For the inverting amplifier example, we’ll need to fill out a row and a column with the standard 5% values for resistors. You might object, thinking this to be a tedious task, but two things help here. First, remember that you need only do this once and can then use the sheet as a template for other resistor calculations later on. Also, there are a number of software tricks to greatly reduce the amount of typing required. Just follow these steps and see how fast it can be:

  1. Leave a couple blank rows at the top. Now starting at cell B3, enter the 23 standard 5% resistor values one after another down the column: 10, 11, 12, 13, 15, 16, 18, 20, 22, 24, 27, 30, 33, 36, 39, 47, 51, 56, 62, 68, 75, 82, and 91.
  2. The following entries come in decades. That is, 10 becomes 100, 11 becomes 110, eventually moving into the thousands, the tens-of-thousands, and so on. To automate this, with the cursor in the empty cell immediately below the last number you punched in (91), enter a formula that will create a result 10 times the value of the number in the top cell of that column (10).

    Most modern spreadsheets let you do this with a combination of keyboard and mouse point-and-clicks. For example (depending what row you started with), the formula in this cell might appear as =B3*10. That’s what the formula looks like but, of course, it’ll be evaluated and displayed as 100, the next entry in the resistor sequence.
  3. Now simply replicate that cell over the remaining rows below. In Microsoft Excel, this is done by placing the cursor over the little square on the lower right-hand corner of the cell. The pointer will change to a cross. Now simply drag it down to row 141 and release the mouse button. All of the remaining values are magically created, from 10 Ω to 10 MΩ!

    The replication process has copied the formula to successive cells, but since a relative reference is assumed, it’s revised to =B3*10, then to =B4*10, and so on. In some older spreadsheet programs, you may have to carry out this operation with a combination of copy and paste, but it’s still faster than typing everything in by hand since you can do dozens of numbers at once. While this may read long, in practice, you can create all 139 values in a minute or two and with very little keyboard work. (Of course, if you’re a zippy typist ...)
  4. Format the column any way you’d like. You could make the entries display as ordinary fixed-point numbers with commas, or in scientific notation, or whatever. It’s up to you, since the full precision values are maintained internally no matter how they appear on the screen.

So, you’ve got a column of standard values now, and these will refer to one of the resistors in our example. But with all those zeroes kicking around, it can be a little hard to read. So, in the first column (referenced as A on my system and deliberately left blank so far), type in the usual shorthand version for each number: 1K for 1,000, 330K for 330,000, and so on. These are just labels to make reading the table a little easier and don’t affect the calculations in any manner. You might want to format these with bold print to make them stand out a bit (see Figure 1).

10    10          1K    1000         100K    100000
11    11        1.1K    1100         110K    110000
12    12        1.2K    1200        120K    120000
13    13        1.3K    1300        130K    130000
15    15        1.5K    1500        150K    150000
16    16        1.6K    1600        160K    160000
18    18        1.8K    1800        180K    180000

FIGURE 1. By exploiting a spreadsheet program's built-in Copy/Paste operations, you can create this array of resistor values with very little typing. Bold print labels for easy human interpretation are placed alongside the actual values used for computation. The numbers should actually be entered sequentially, although they're shown split into thirds here to fit on the page.

Now we need to create a row of resistor values along the top. You could perform steps similar to what we’ve just completed, but there’s a faster way. Most spreadsheets have a variety of matrix operations built in, including one called Transpose. The purpose of this operation is to copy a row as a column and vice versa.

Consider the two columns so far as a matrix containing 139 rows and two columns. Invoke the Transpose function to create a new matrix from it containing two rows and 139 columns. In Excel, you can access this operation from the Paste Special menu. If the details hang you up somehow, be sure to check out the Help screens in your program.

Assuming everything’s gone well, you’ll rapidly end up with a copy of the first two columns, but arranged now as the top two rows. See Figure 2, and also notice how the schematic and text description referred to earlier appear at the top. Be sure to store your work in progress. You might also want to save this as a template, since it can be used for lots of different circuits involving two resistors.

Inverting Op-Amp Gain
This sheet shows the gain of an inverting op-amp for standard 5% valued resistors. Read the values of R1 down the left-hand column, and the values of R2 across the top row.

                10           11           12           13           15           16           18           20           22           24           27
                10           11           12           13           15           16           18           20           22           24           27
10    10    1.0000    1.1000    1.2000    1.3000    1.5000    1.6000    1.8000    2.0000    2.2000    2.4000    2.7000
11    11    0.9091    1.0000    1.0909    1.1818    1.3636    1.4545    1.6364    1.8182    2.0000    2.1818    2.4545
12    12    0.8333    0.9167    1.0000    1.0833    1.2500    1.3333    1.5000    1.6667    1.8333    2.0000    2.2500
13    13    0.7692    0.8462    0.9231    1.0000    1.1538    1.2308    1.3846    1.5385    1.6923    1.8462    2.0769
15    15    0.6667    0.7333    0.8000    0.8667    1.0000    1.0667    1.2000    1.3333    1.4667    1.6000    1.8000
16    16    0.6250    0.6875    0.7500    0.8125    0.9375    1.0000    1.1250    1.2500    1.3750    1.5000    1.6875

FIGURE 2. A portion of a basic design sheet. The entire table spans all values of 5% resistors from 10 Ω on up to 10 MΩ.

A Multitude of Calculations

Now comes the fun part — seeing the calculations fall into place! Plop the cursor down in the first empty cell at the intersection of R1 = 10 ohms and R2 = 10 ohms). Then enter the formula to calculate the ratio of these two. It should look something like this: =C$17/$B18 depending on which row and column you started with. With most spreadsheet programs, you can do a combination of keystrokes and mouse drag-and-drops to quickly enter this formula.

Pay close attention to the dollar signs in this expression. Normally, a spreadsheet defaults to a relative reference scheme (as it did when we entered the resistor values earlier). But in this case, we need to specify an absolute row or column, and that’s the purpose of the dollar sign flags. For example, C$17 says to reference the value which must be in row 17. On my system, this will be an R2 value. Then we divide this by $B18, and this refers to a value which must lie in the B column. The B column contains the values of R1.

Finally, using drag-and-drop if your software permits it, or else a Copy/Paste operation, replicate this formula across the entire spreadsheet. Bang — in the blink of an eye, you’ll have the gain of an inverting op-amp for every possible pair of standard 5% value resistors! You should end up with a sheet like the one in Figure 2.

A More Complex Example

The problem is a common one: what will the output of a voltage divider be for various resistor strings, and for different input voltages? Figure 3 shows a portion of the resulting sheet. In this case, the computation depends on three quantities: R1, R2, and the applied voltage Vin. To make the sheet truly general-purpose, place the value of Vin in its own cell and label it. Position it in the upper left-hand corner of the table, so that it’s easy to spot.

Voltage Divider
This sheet shows the outputs of a voltage divider for standard 5% valued resistors. Vin (currently set at 10V) can be changed to any desired input voltage and the sheet be recalculated. Read the values of R1 down the left-hand column, and the values of R2 across the top row.

Vin = 10     10          11            12           13           15           16           18           20           22           24           27                                                                              
                   10          11            12           13           15           16           18           20           22           24           27                                                         
10    10       5.0000    5.2381    5.4545    5.6522    6.0000    6.1538    6.4286    6.6667    6.8750    7.0588    7.2973
11    11       4.7619    5.0000    5.2174    5.4167    5.7692    5.9259    6.2069    6.4516    6.6667    6.8571    7.1053
12    12       4.5455    4.7826    5.0000    5.2000    5.5556    5.7143    6.0000    6.2500    6.4706    6.6667    6.9231
13    13       4.3478    4.5833    4.8000    5.0000    5.3571    5.5172    5.8065    6.0606    6.2857    6.4865    6.7500
15    15       4.0000    4.2308    4.4444    4.6429    5.0000    5.1613    5.4545    5.7143    5.9459    6.1538    6.4286
16    16       3.8462    4.0741    4.2857    4.4828    4.8387    5.0000    5.2941    5.5556    5.7895    6.0000    6.2791
18    18       3.5714    3.7931    4.0000    4.1935    4.5455    4.7059    5.0000    5.2632    5.5000    5.7143    6.0000

FIGURE 3. A slightly more complex design sheet. Again, only a portion is shown. Notice the parameter Vin, which appears in the upper left-hand corner of the table.

Now, have any calculations refer to this parameter instead of using an embedded constant. On my system, a typical entry looks like =$B$16*C$17/($B18+C$17). Again, watch the dollar signs. In this case, $B$16 refers to the absolute location of column 16 and row B, which contains the input voltage. When the sheet is filled out, you’ll see the output level for every combination of standard resistor values strung across 10 V.

If you need to do it again, for 15 V say, simply change that one single value in the upper left-hand corner and recalculate; the entire sheet is updated in a trice, and placing Vin in an easy-to-find locale makes it simple to experiment with many different voltages.

Much More Than DC Circuits

Creating spreadsheets for simple DC circuits is useful, and a great way to get started. But there are many other unusual applications just waiting to be discovered. How about making a sheet to calculate the decibel equivalents of voltage ratios?

This is not only a handy reference to keep in your lab notebook, but also makes a great study aid should you wish to learn more about decibels and get an intuitive feel for what they mean. Figure 4 shows the layout.

Decibel Tables
These tables show the decibel equivalents for various ratios of output to input voltages (voltage gain).

Vout/Vin          dB             Vout/Vin            dB            Vout/Vin    dB
1               0.0000                 51         34.1514                       1    0.0000
2               6.0206                 52         34.3201                     10    20.0000
3               9.5424                 53         34.4855                   100    40.0000
4             12.0412                 54         34.6479                 1000    60.0000
5             13.9794                 55         34.8073               10000    80.0000
6             15.5630                 56         34.9638             100000    100.0000
7             16.9020                 57         35.1175           1000000    120.0000
8             18.0618                 58         35.2686         10000000    140.0000
9             19.0849                 59         35.4170       100000000    160.0000
10           20.0000                 60         35.5630
11           20.8279                 61         35.7066
12           21.5836                 62         35.8478

FIGURE 4. Decibel tables are great for home study.

To design this one, create a column of voltage ratios, starting at one and going as high as you want. For larger ratios, set out a second area where the jumps are bigger. Then simply copy in the standard formula =20*LOG10(A11). In this case, all references are relative and merely refer to the adjacent number in the A column, so there are no dollar signs. And LOG10 is nothing more than the base-10 logarithm function, part of every spreadsheet program’s bag of tricks.

Bring on the Capacitors

Now let’s up the ante a bit and see how to incorporate capacitance into our spreadsheet calculations. Here’s a practical example. Good quality audio circuits typically employ bandwidth limiting. This is to ensure that frequencies below and above the audio spectrum are rejected. The result is a circuit free of subsonic rumble or supersonic interference.

Figure 5 shows straightforward — yet reliable — circuits to handle both the low pass and high pass operations. Notice that one formula applies to both circuits, so you get double the bang for your buck here. That is, if the combination of R and C gives a cutoff frequency of 1,000 Hz, then in the low pass circuit, frequencies above this value are attenuated, and in the high pass version, frequencies below it are rolled off.

Bandwidth Limiting in an Inverting Amplifier
This sheet shows the -3 dB points for bandwidth limiting (which works for both low pass and high pass) in an inverting amplifer, at various standard values of R and C.

               10 pF                  22 pF               47 pF               100 pF            220 pF            470 pF           0.001 µF       0.0022 µF    0.0047 µF
               1.00E-11             2.20E-11          4.70E-11          1.00E-10         2.20E-10         4.70E-10       1.00E-09       2.20E-09      4.70E-09
10   10    1,591,549,431    723,431,560     338,627,538    159,154,943    72,343,156     33,862,754    15,915,494    7,234,316    3,386,275
11   11    1,446,863,119     657,665,054     307,843,217    144,686,312    65,766,505     30,784,322    14,468,631    6,576,651    3,078,432
12   12    1,326,291,192    602,859,633     282,189,615    132,629,119    60,285,963     28,218,962    13,262,912    6,028,596    2,821,896
13   13    1,224,268,793    556,485,815     260,482,722    122,426,879    55,648,582     26,048,272    12,242,688    5,564,858    2,604,827
15   15    1,061,032,954    482,287,706     225,751,692    106,103,295    48,228,771     22,575,169    10,610,330    4,822,877    2,257,517

FIGURE 5. Here's a portion of a design table that makes designing reactive circuits — like filters — a snap. Actually, the resistors shown here are way too small for a practical circuit, so you'd want to use values further along in the list. Just scroll the table until you locate some reasonable numbers.

For this spreadsheet, you’ll need resistor values down the left-hand column, and capacitor values along the top row. The resistors we’ve already dealt with; you can simply use your template from before, keeping the column on the left but deleting the top row. Then punch in standard values for the capacitors. I used decade multiples of 2.2, 4.7, and 10, since these are the caps I usually have on hand. But feel free to employ any other intermediate ones you desire.

Since capacitance is typically quite small, there are lots of zeros and decimal places running around, which could make the table a bit hard on the eyes. So, as before, I created a row which gives the human equivalent as boldface labels (e.g., 100 pF), and immediately below this is the actul number to be used in the calculations (e.g., 1.00E-10).

If you’ve got your resistor and capacitor values punched in, then save this as a template, too. It’ll come in handy for all sorts of reactive circuits.

The formula you need for Figure 5 is =1/(2*PI()*$B18*C$17). The numbers and letters may vary, depending on what row and column you use to start your sheet. As usual, watch out for the dollar signs, since we want to grab resistor values from the column only, and capacitor values from the row only.

Here’s something new. The ubiquitous constant π (PI) shows up in the formula. Just about all spreadsheet programs have this built in, and by using it, you can get a good 15 decimal places of accuracy, much better than the lousy approximation 3.14 you used in junior high.

As an example of how quickly a problem can be dispatched with this sheet, imagine you wish to roll off the high end of an audio circuit at 100 kHz, say, to avoid spurious oscillations. Scrolling through the table you’ll instantly locate values of 75 KΩ and 22 pF for R and C, respectively. These will give a -3 dB point of 96,458 Hz, which is close enough. And you can look for other suitable combinations if you wish to raise or lower the resistor value to change the overall gain of the circuit. It’s a snap now!

And That’s Just the Start

I’ll confess that I only stumbled onto the notion of using spreadsheets for electronic design recently. But now I’m hooked — and I hope the examples presented in this article have piqued your interest and that you’ll consider creating and sharing your own spreadsheets for electronic design. Judging by the popularity of the Electronics Q & A and Tech Forum columns, the readers of Nuts & Volts comprise an active, generous community. Just imagine what kind of database we could come up with if everyone contributed a design sheet or two ...  NV


Here are several resources if you don’t have a spreadsheet program on your computer — all three are fully-functional freeware!



Sphygmic Software Spreadsheet

About the Author

Thomas Henry is the author of over 130 articles and six books on the subjects of electronic design, microcomputers, music synthesizers, astronomy, caves, and magic. He is a mathematics instructor at South Central College in North Mankato, MN.


Spreadsheet 2006-10 (analog design spreadsheets)