Before writing formulas, remember the formula:
Whether you’re a data science student wanting to demystify backpropagation, an Excel power user curious about AI, or a professional who needs a quick prototype without writing code, this guide is for you.
For a deeper understanding of neural networks and MS Excel, check out the following resources:
In this comprehensive guide, I’ll walk you through building a complete neural network from scratch using only MS Excel. You’ll learn how to set up the architecture, implement forward propagation, define a loss function, and even train the network using Excel’s built‑in Solver add‑in. By the end, you’ll have a working Excel model that can learn the XOR logic – a classic non‑linear problem that single‑layer perceptrons cannot solve.
using NumPy to see how formulas map to real code. Share public link build neural network with ms excel full
Excel's Solver engine will run backpropagation iterations behind the scenes, rapidly adjusting your parameters until the Total Error drops near zero. 6. Verifying the Results Once Solver finishes, look back at your training table. Compare your target outputs ( ) to your predictions (
After Solver finishes, the "Total Error" should be very low (e.g.,
Once your network is trained (via Solver or manual descent), you can test it on new inputs. For example, enter 0.5 in x1 and 0.5 in x2 – the XOR of two 0.5 values is ambiguous, and the network should output something around 0.5 as well.
To adjust our weights, we must find how much our error changes relative to each weight ( By the end, you’ll have a working Excel
Open a new Excel workbook and create 6 named worksheets (tabs):
Repeat this logic systematically for every single weight and bias value across your parameter configuration block. 7. Automating the Training Loop (Epochs)
In a dedicated section of your spreadsheet, initialize your parameters: Weights (
This step calculates the network's output by passing inputs through each layer. Towards Data Science Weighted Sum Formula Example: =SUMPRODUCT(Inputs
): For each neuron, multiply each input by its weight and add a bias. In Excel, use the SUMPRODUCT function. Formula Example: =SUMPRODUCT(Inputs, Weights) + Bias Activation Function (
Formula for Z(2)=(A1(1)⋅W11(2))+(A2(1)⋅W21(2))+(A3(1)⋅W31(2))+B1(2)Formula for cap Z raised to the open paren 2 close paren power equals open paren cap A sub 1 raised to the open paren 1 close paren power center dot cap W sub 11 raised to the open paren 2 close paren power close paren plus open paren cap A sub 2 raised to the open paren 1 close paren power center dot cap W sub 21 raised to the open paren 2 close paren power close paren plus open paren cap A sub 3 raised to the open paren 1 close paren power center dot cap W sub 31 raised to the open paren 2 close paren power close paren plus cap B sub 1 raised to the open paren 2 close paren power Apply the Sigmoid function one last time to Z(2)cap Z raised to the open paren 2 close paren power A(2)cap A raised to the open paren 2 close paren power . This final value is your prediction ( Ŷcap Y hat 📉 Step 3: Calculating Error and Backpropagation
Let’s design columns for these calculations (starting at row 2 for the first input row):
| | A | B | C | D | | --- | --- | --- | --- | --- | | 1 | h1 | w21 | b3 | y1 | | 2 | h2 | w22 | b4 | y2 |
If you want to truly understand backpropagation, you can implement gradient descent using Excel’s iterative calculation. This method updates weights step by step using the derivative of the loss with respect to each weight.