# 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.

### Step 2 – Select Model

• 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.

Read More: How to Create Betting Algorithm in Excel

### 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.

## Related Articles

<< Go Back to Algorithm in ExcelÂ |Â Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Mehedi Hasan

Mehedi Hasan, a BSc graduate in Naval Architecture & Engineering from Bangladesh University of Engineering and Technology, plays a pivotal role as an Excel & VBA Content Developer at ExcelDemy. Fueled by a profound passion for research and innovation, he actively engages with Excel. In his capacity, Mehedi not only adeptly tackles intricate challenges but also showcases enthusiasm and expertise in navigating tough situations with finesse, underscoring his unwavering dedication to consistently delivering exceptional and high-quality content. He... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF