Implementing a Neural Network Classifier Entirely in Excel: Complete Hands-On Tutorial

## Why Build a Neural Network in Excel? Neural networks power modern machine learning, from image recognition to predictive analytics. Traditionally, they require Python libraries like TensorFlow or PyTorch. However, for educational purposes or quick prototyping, Excel offers a surprisingly capable environment. This approach demystifies the "black box" of neural networks by exposing every calculation in spreadsheet cells. We'll use the classic Iris dataset to classify flowers into three species based on four measurements: sepal length, sepal width, petal length, and petal width. Compared to coding in Python, Excel's method is more visual and interactive. You see weights update in real-time and tweak parameters manually. It's slower for large datasets but ideal for learning core concepts like forward propagation, activation functions, and backpropagation approximation via optimization. Real-world applications include business analysts embedding simple ML models in spreadsheets for sales forecasting or customer segmentation without IT dependency. ## Prerequisites and Setup To replicate this, ensure Microsoft Excel with the Solver add-in enabled: - Go to **File > Options > Add-ins**. - Select **Excel Add-ins** and check **Solver Add-in**. Prepare the Iris dataset: - Download the standardized version (features scaled to 0-1) from reliable sources like UCI repository. - In Excel, place data in columns A:D (features) and E (one-hot encoded labels: 1 for true class, 0 otherwise) for rows 2:151 (150 samples). We'll create a feedforward neural network: - **Input layer**: 4 neurons (one per feature). - **Hidden layer**: 3 neurons with sigmoid activation. - **Output layer**: 3 neurons (one per class) with softmax-like probabilities, trained via cross-entropy loss. This architecture is compact yet sufficient for Iris classification, achieving ~95% accuracy post-training. ## Designing the Network Architecture ### Input and Hidden Layer Start by designating cells for weights. Use sheets or ranges: - Hidden weights (4x3 matrix): Say, F1:H4. - Hidden biases: F5:H5. For each sample (e.g., row 2), compute hidden activations: - Pre-activation for hidden neuron 1: `=SUMPRODUCT($A2:$D2, $F$1:$I$1) + $F$5` (adjust ranges). - Activation: Sigmoid function `=1/(1+EXP(-pre_activation))`. Copy formulas across hidden neurons. Sigmoid squashes values to (0,1), introducing non-linearity essential for learning complex patterns. **Pro Tip**: Sigmoid formula is `=1/(1+EXP(-X))`. For stability, cap inputs to avoid overflow: `=IF(ABS(X)>50, IF(X>0,1,0), 1/(1+EXP(-X)))`. ### Output Layer - Output weights (3x3 matrix from hidden to output): Say, J1:L3. - Output biases: J4:L4. Pre-activation for output neuron 1: `=SUMPRODUCT(hidden_activations, output_weights_col1) + bias`. Apply softmax for probabilities: `=EXP(pre_act)/SUM(EXP(pre_acts))`. Excel's `EXP` handles this well for small networks. ## Forward Propagation: Computing Predictions For every row, chain inputs through layers: 1. Hidden pre-acts: Matrix multiplication via `SUMPRODUCT`. 2. Hidden acts: Sigmoid each. 3. Output pre-acts: Another `SUMPRODUCT`. 4. Output probs: Softmax normalization. Example for one sample: ```excel Hidden1: =1/(1+EXP(- (A2*F1 + B2*G1 + C2*H1 + D2*I1 + F5))) Output1_prob: =EXP(Output_pre1) / (EXP(Output_pre1)+EXP(Output_pre2)+EXP(Output_pre3)) ``` Drag formulas down for all 150 samples. This creates a prediction matrix—your network's output without training. **Adding Value**: Visualize with charts. Plot predicted vs. actual classes using conditional formatting or scatter plots. Initially, predictions are random (~33% accuracy), highlighting the need for training. ## Training the Model with Solver Neural networks learn by adjusting weights to minimize loss. Here, use cross-entropy loss: For class k: `-LOG(pred_prob_k)` if true label is k, else 0. Total loss per sample: `= -SUMPRODUCT(one_hot_labels * LOG(pred_probs))`. Grand loss: Average over all samples. **Solver Setup**: 1. Select the loss cell. 2. **Data > Solver**. 3. Set **Objective**: That cell, **Min**. 4. **By Changing Variable Cells**: All weights and biases (e.g., F1:L5). 5. **Constraints**: None initially (weights free to vary). 6. **Solving Method**: GRG Nonlinear (handles non-convexity). 7. Options: Increase iterations to 1000+, precision 0.0001. 8. Solve! Solver uses gradient-based optimization (quasi-Newton), approximating backpropagation. Training takes seconds to minutes on modern hardware. **Comparison to Python**: Excel Solver is like `scipy.optimize.minimize`, but visual. Python scales better; Excel caps at ~1000 samples. ## Evaluating Performance Post-training: - **Accuracy**: `=IF(MAX(pred_probs_row)=true_class_prob,1,0)`, average ~0.96. - Confusion matrix: Pivot table on predictions vs. labels. - Loss plot: Track pre/post-training loss. Real-world tweak: Add L2 regularization by penalizing large weights in loss: `+ lambda * SUMSQ(weights)`. ## Complete Example and Resources To skip setup, download the fully implemented workbook: [Neural Network in Excel for Iris Dataset](https://github.com/bartoszmikulski/neural-network-excel/raw/master/iris-nn.xlsx) Open it, examine formulas, run Solver, and experiment. Change hidden neurons to 5—watch accuracy/ training time trade-off. **Extensions for Practitioners**: - **Multi-sheet**: One for data, one for model, one for viz. - **Batch training**: Solver handles all data at once. - **Hyperparameters**: Tune via multiple Solvers or manual search. - **Applications**: Credit scoring (binary output), demand forecasting. **Limitations**: No convolutions/recurrence; slow for big data. Bridge to tools like Google Sheets ML add-ons or Power BI. ## Key Takeaways and Next Steps - Excel neural nets teach fundamentals accessibly. - Core: Forward pass (formulas), optimization (Solver). - Scale up: Export weights to Python for production. Experiment with Wine or MNIST subsets. This hands-on method builds intuition faster than theory alone, empowering non-coders in data roles. (Word count: ~1150) --- <div style="text-align: center; margin-top: 2rem;"> <a href="https://towardsdatascience.com/the-machine-learning-advent-calendar-day-18-neural-network-classifier-in-excel/" target="_blank" rel="noopener noreferrer" class="view-full-resource-btn" style="display: inline-block; background-color: #f97316; color: white; padding: 12px 24px; border-radius: 8px; text-decoration: none; font-weight: 600; transition: background-color 0.2s;">View Full Resource</a> </div>