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.

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

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.

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

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.

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.

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

The Machine Learning Model will appear as shown below.

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 the Fill Handle to apply the formula to the entire column.

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.

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.