How to Perform Machine Learning in Excel (With Easy Steps)

Step 1 – Prepare Training Data

For illustration, we have a sample preprocessed dataset.

You need to preprocess your data by discarding missing data, encoding categorical data, etc.

Training dataset for the machine learning process

Step 2 – Select Model

Enable the Analysis Toolpak.
Go to the Data tab and select Data Analysis.

Data tab after enabling Data Analysis ToolPak

The Data Analysis dialog box will appear. Select Regression and click OK.

Data Analysis dialog box

In the Regression window, insert the Y and X. Enter the salary range (B4:B14) and experience range (C4:C14) in the Input Y Range and Input X Range sections respectively.
Check Line Fit Plots and Labels.
Select B20 as the Output Range.
Click OK.

Regression dialog box

The regression analysis outcome and some regression parameter values will be displayed as shown:

Output of regression analysis

It plots both the Series Data (approximate salaries) and Predicted Data (salaries for modeling) in the graph.
Name your graph. We named this chart Machine Learning Model.

Change Title for the Line Fit plot

Step 3: Predict Data

Create a machine-learning model chart and a modeled salary structure for the employees. The chart analyzes the given data and, in turn, provides a predicted trendline equation which can be used to generate a modeled salary structure.

Right-click on any of the points on this chart and select Add Trendline.

Add Trendline to Chart

The Format Trendline window will appear.
Select Linear and check Display Equation on the Chart.

Format Trendline option

The Machine Learning Model will appear as shown below.

Machine learning model chart with equation

To develop a formula using this equation to model the salaries, create a new column to store the Modeled Salaries.
Enter the following formula in cell D5, and press ENTER. This will provide the salary for an employee with 0 years of experience.

=B5*869.3939394+1025.454545

Use of Regression formula to find the predicted output

Use the Fill Handle to apply the formula to the entire column.

Formula filled to all cells

Step 4 – Evaluate the Model’s Performance

Find the difference between the actual values and the predicted values. The difference values can be used to evaluate the accuracy of the model. If more values are known, the model can be updated.

Enter the following formula in cell E5 to compare with the real data and press Enter.

=C5-D5

Use the Fill Handle to fill the rest of the cells.

Formula to find the error values

The predicted salaries for four more unknown values have been found.
If the actual values are known, you can rerun the regression analysis using these values to get a better model.

Four more predicted outputs are calculated