Saturday, May 25, 2024

Building a Neural Network in Excel: A 6 Step How-To Guide

Datamation content and product recommendations are editorially independent. We may make money when you click on links to our partners. Learn More.

Creating a neural network in Excel is a good way to build a simple version of the advanced mathematical models that power today’s deep learning algorithms and most of the recent artificial intelligence/machine learning (AI/ML) advances. While the neural networks that form the backbone of leading AI offerings like OpenAI’s GPT, Google DeepMind, and Anthropic Claude require powerful processing and are generally built using machine learning tools, a simple neural network built in Excel can demonstrate how these models work.

We’ll show you how to create a neural network in Excel in six steps to give you a foundational understanding of neural networks and their basic implementation.

Creating the Neural Network in Excel

Neural networks are mathematical models that use algorithms inspired by the human brain to process data. Used in machine learning, they create adaptive systems that provide a framework for computers to learn from mistakes and improve continuously. Well-designed neural networks enable computers to make decisions with limited human intervention, making it possible for them to “comprehend” unstructured data and perform such tasks as image recognition, document summarization, and predictive analysis.

For this example, you’ll create a simple neural network in Microsoft Excel that will serve as a model to show how they work. Your neural network will make predictions based on three inputs and one output in your training dataset, as shown in the table below.

Input 1 Input 2 Input 3 Output
1 0 1 1
1 1 0 1
0 0 0 0
1 1 1 1
1 0 0 1
0 1 0 0
1 0 1 1
0 1 1 0

 

Step 1: Set Up Training Data

Start by creating a new Excel spreadsheet and entering your data. This will serve as your neural network’s training data—you will use it for training your model to make predictions.

  1. In a new sheet, create four columns (A to D) and label them “Input 1,” “Input 2,” “Input 3,” and “Output.”
  2. Insert the data values from the table into your spreadsheet. It should now look like Figure 1.
Neural network training data in Excel.
Figure 1: Neural network training data in Excel.

Step 2: Set Up Weights and Biases

The next step is to set up the weights and biases for your predictive model.

  • Weights: Values that the model adjusts during training to give different levels of importance to different features.
  • Biases: A baseline or extra bit of information added to the prediction.

Together, weights and biases help your neural network tune its predictions based on the importance of different factors and the overall tendencies in the data. You can think of them as the knobs your model adjusts during its learning process to make better predictions.

  1. Starting in the next empty column, create weight columns for each of the three inputs: “Weight 1,” “Weight 2,” and “Weight 3.”
  2. Create a “Bias” column.
  1. Calculate the weights and bias for the first record in your spreadsheet. To get started with this example, you can input random values for your weights: 72 percent, 77 percent, and 85 percent.
Weights and biases in the training data.
Figure 2: Weights entered in training data.
  1. To determine the Bias column values, calculate the weighted sum of all your inputs by multiplying each input by its corresponding weight and adding up the results. You can use the following Excel formula to do this:

=(E3*A3) + (F3*B3) + (G3*C3)

Insert the formula into the first row’s Bias field. Your spreadsheet should look like Figure 3.

First row bias calculated.
Figure 3: First row bias calculated.

Step 3: Calculate Your Output

The next step is to calculate the output value of your neural network. To do this, you’ll use the Sigmoid Function to normalize the bias for use in your model. The Sigmoid Function enables your neural network to make predictions in a more balanced and interpretable way, leveraging the non-linear properties of the Sigmoid to capture complex relationships in the data. It’s represented by the following equation:

The Sigmoid Function.

  1. To the right of the Bias column, create a column to hold your predictions and label it “Output.”
  2. Use the following Excel formula to calculate your output:

=1/(1+(EXP(-H3)))

  1. Insert this formula into your first record’s Output field. Your spreadsheet should resemble Figure 4.
First output calculated.
Figure 4: First output calculated.

As you can see, the first row’s output (prediction) value of 0.827783608 doesn’t match the actual value of 1 in your historical training data—your neural network requires fine tuning for accuracy.

Step 4: Fine Tune Your Weights

In this step, you’ll tweak your weights for more accurate results—remember, weights are like fine-tuning knobs that you can adjust to make better predictions.

  1. Add three new columns labeled “Weight 1 Δ,” Weight 2 Δ,” and “Weight 3 Δ.” These will hold the change values for applying to your initial weights.
Change value columns.
Figure 5: Change value columns.

To determine the adjustments required for each of your weights, you’ll use the error weighted derivative formula in a process called backpropagation, which simply means that the weight values are updated from right to left.

You can use the following Excel formula to calculate the weight tuning value for “Weight 1 Δ”:

=($D3-$I3)*A3*$I3*(1-$I3)

  1. Paste the formula in the “Weight 1 Δ” cell.
  2. After calculating the value for “Weight 1 Δ,” drag the formula across to “Weight 2 Δ” and “Weight 3 Δ” to calculate their values as well. Your spreadsheet should now look like Figure 6.
Weight-tuning values calculated.
Figure 6: Weight-tuning values calculated.
  1. Next, you’ll apply the new weight tuning values to the second row of your training data. Insert the following Excel formula into the second row’s “Weight 1” field:

=J3+E3

  1. Drag the formula across to the second row’s “Weight 2” and “Weight 3” fields, as well. Your spreadsheet should look like Figure 7.
Additional weight-tuning values calculated.
Figure 7: Additional weight-tuning values calculated.

Step 5: Calculate Remaining Values

Next, you’ll calculate the bias, output, and weight-tuning values for the remaining rows.

  1. Drag the Bias (cell H3), Output (cell I3), and Weight Tuning (cells J3, K3, L3) calculations down to the last row so your spreadsheet looks like Figure 8.
Weight-tuning calculations in place.
Figure 8: Weight-tuning calculations in place.
  1. Now that you have all your weight tuning calculations in place, calculate the rest of your weight values (e.g., Weight 1, Weight 2, Weight 3) by dragging them down to the last row.
All values calculated.
Figure 9: All values calculated.

Step 6: Add More Training Data

Your output values are still incorrect, so you’ll need to add more historical data to continue training your neural network. For this example, you can add synthetic data.

  1. Copy your existing training data and paste the values after the last record, effectively doubling the size of your training dataset.
  2. Be sure to also propagate the new weights, biases, outputs (predictions), and weight-tuning calculations to your new training data records by dragging them down to the last new record. Your spreadsheet with the newly expanded training data should look like Figure 10.
Additional training data added.
Figure 10: Additional training data added.

See how the Weight 1 column percentages are increasing while the Weight 2 and Weight 3 percentages are decreasing? Even with this limited dataset, the floating point outputs or predictions closer to 1 match your training data outputs with a value of 1. This means your results are tracking in the right direction.

Of course, you’ll need significantly more data to achieve high accuracy predictions—but you’ve successfully created a simple neural network in Microsoft Excel.

This is just a basic example of a neural network, and is unlikely to be powerful enough to tackle complex problems. But it serves as a foundation for understanding the core concepts of neural networks and how they work.

Practical Uses for Neural Networks in Excel

As a spreadsheet application, Microsoft Excel is primarily geared for tasks like data analysis, desktop number crunching and calculations, and basic visualizations—while it’s not the most powerful or ideal tool for creating neural networks, in some scenarios it may be appropriate for accomplishing basic neural network tasks.

Educational Purposes

Excel can provide a simple and visual method for data science learners to understand the basic concepts of neural networks. You can use it to create a small neural network for educational purposes and to grasp the fundamental concepts of how neurons, weights, and activations work.

Small-Scale Prototyping

If you’re working on a small-scale or simple neural network task, and you’re already familiar with Excel, it might be quicker to prototype and experiment in a familiar environment.

Visualization and Interpretation

Excel can be useful for visualizing and interpreting small neural networks. You can create simple charts and graphs to represent the network structure, weights, and activation values, which can be helpful for gaining insights into how the network is functioning.

Frequently Asked Questions (FAQs)

Is this Excel example a real neural network?

Yes, neural networks are fundamentally mathematical constructs—so this Excel example is, for all intents and purposes, a real neural network (albeit a basic one).

Do I need advanced programming skills to make this neural network more powerful?

No, extensive programming skills are not necessary; however, a basic understanding of Excel formulas and functions is beneficial. As is the case with all machine learning projects, the best way to supercharge your model is to provide as much training data as possible.

What are the limitations of creating a neural network in Excel compared to specialized tools?

Excel has limitations in terms of scalability and complexity when compared to dedicated machine learning tools. It may not handle large datasets or intricate neural network architectures as efficiently. For more advanced tasks, we recommend transitioning to specialized tools like TensorFlow or PyTorch.

Can I use Excel to train a neural network on real-world data?

Yes, you can use Excel to train a neural network on real-world data, provided the dataset is not too large or complex. Excel’s capabilities are suitable for educational purposes and small-scale projects.

Are there any specific Excel versions or add-ins required for creating a neural network?

As this example serves to illustrate, a standard version of Excel with basic functions is sufficient for creating a simple neural network. There are no specific add-ins required, although certain versions of Excel may offer enhanced statistical features and solvers.

Bottom Line: Building Neural Networks In Excel Demystifies Machine Learning

While Excel is not the go-to tool for sophisticated machine learning tasks, this exercise provides a tangible introduction to neural network mechanics. As you gain more machine learning proficiency, you’ll likely transition to more specialized tools and programming languages. Nonetheless, the principles you’ve grasped here will serve as a solid foundation for your future endeavors in artificial intelligence and machine learning.

If you’re interested in using Excel for complex processes and statistical analysis, you might like our how-to guides on using it for Monte Carlo simulations, logistical regressions, and linear regressions

Subscribe to Data Insider

Learn the latest news and best practices about data science, big data analytics, artificial intelligence, data security, and more.

Similar articles

Get the Free Newsletter!

Subscribe to Data Insider for top news, trends & analysis

Latest Articles