Calculus-Free PID (Almost) In A Spreadsheet | Hackaday
Skip to content
PID controllers are everywhere. They regulate temperature, motor speed, power supplies, positioning systems, process equipment, and probably a dozen things within arm’s reach of you right now.
They’re also frequently explained with enough calculus to make them seem more mysterious than they really are. Granted, the I and D in PID stand for calculus terms, but they are easy enough to build into a spreadsheet. Grab a copy and keep it open while you read this post.
The Google Sheet implements a simple simulated PID controller along with a simulated process — the thing we’re trying to control. You can change the controller gains, alter the process, introduce disturbances, and watch what happens without compiling anything or wiring up a heater that might accidentally become a toaster.
The three letters in PID stand for Proportional, Integral, and Derivative. If your calculus is rusty, integral is just how much is building up over time, and the derivative is how much changed just now. Each operates on the error:
error = setpoint - process value<br>The setpoint is where we’d like the system to be, the process value (PV) is where it actually is, and we would obviously like the error to be zero. Proportional is the most obvious method of control. The more we are off, the more we adjust. The closer we are to the setpoint, the less proportional output we need.
Integral, on the other hand, looks at a running tally of errors. Finally, derivative measures how much things have changed from the last time we looked. The basic cycle time for the spreadsheet is set by dt, which, by default, is 0.1 seconds. Therefore, it takes ten spreadsheet rows to cover an entire second.
Suppose we’re controlling temperature and want it to be 20 degrees. If the temperature is 15, the error is +5. If it’s 22, the error is -2. Our controller’s job is to turn that error into an output. That output affects something — a heater or a motor speed or whatever — that can change the process value. So for a temperature example, the output might drive a heating resistor, and the process value is measured by a thermistor.
The PID tries to drive the heater so that the process value is as close as possible to the setpoint. To the PID algorithm, the actual units of the output and the process values are immaterial. The spreadsheet limits output from 0 to 100 and, presumably, that would be a percentage of voltage or a PWM duty cycle. The setpoint and process value might be in degrees C or F. But the algorithm doesn’t really care.
First, Just P
Make sure the Model drop-down is set to DEFAULT. We’ll begin by setting:
Kp = 4<br>Ki = 0<br>Kd = 0<br>Set the initial process value to 0, the setpoint to 20, the process gain to 1, and the time constant to 2 seconds. With only the proportional term operating, the controller is particularly easy to understand:
output = Kp × error<br>At the beginning, the error is 20, so the controller asks for an output of 80 (that is, 4 times 20). However, the process doesn’t instantly jump to 80. Our simulated plant is a first-order system implemented essentially as:
PVnew = PVold + dt/tau × (Kprocess × output - PVold)<br>The actual spreadsheet has extra terms for a bias and disturbance, but you’ll usually leave those at zero. That’s a useful generic model for a surprising number of real things. Turn up a heater, and the temperature approaches a new value gradually. Apply voltage to a motor and its speed doesn’t change instantaneously. Charge a capacitor through a resistor, and you’ve seen exactly this sort of exponential behavior before.
As the process value rises, the error gets smaller. Because the error gets smaller, the proportional controller reduces its output. This works. At least, mostly.
Proportional can’t quite get there.<br>" data-large-file="https://hackaday.com/wp-content/uploads/2026/08/first.png?w=427" class="size-medium wp-image-1134393" src="https://hackaday.com/wp-content/uploads/2026/08/first.png?w=274" alt="" width="274" height="400" srcset="https://hackaday.com/wp-content/uploads/2026/08/first.png 935w, https://hackaday.com/wp-content/uploads/2026/08/first.png?resize=171,250 171w, https://hackaday.com/wp-content/uploads/2026/08/first.png?resize=274,400 274w, https://hackaday.com/wp-content/uploads/2026/08/first.png?resize=427,625 427w" sizes="(max-width: 274px) 100vw, 274px" />Proportional can’t quite get there.<br>Watch where it eventually settles. With the suggested values, the process value winds up around 16 even though our setpoint is 20. Why? At a process value of 20, the error would be zero. A proportional controller presented with zero error produces zero output. But this particular process needs an output of 20 to remain at 20. Therefore, it can’t ever quite get there.
This is the classic steady-state error of proportional-only control. We could crank Kp upward. Try Kp=8. The process gets much closer to the setpoint. But continually...