Multiple linear regression analysis is a method used when a researcher aims to estimate the effect of independent variables on a dependent variable. In multiple linear regression, the number of independent variables must be at least two.
Generally, when performing data analysis with multiple linear regression, specialized software for statistical data processing is used. Did you know that the Microsoft Excel application, which is widely known as a highly useful tool for office activities, can also be used to conduct multiple linear regression analysis?
Of course, there are additional steps you need to know to use Excel like other statistical software to analyze your research data.
In this tutorial, I will explain how to perform multiple linear regression analysis in Excel using the menu available in the application. In this article, I will also provide a case study example to give readers a deeper understanding.
How to Activate Data Analysis Tools in Excel
In this article, I will first guide you to ensure that the Data Analysis Tools menu is properly installed in your Excel application. To access the Data Analysis Tools in Excel, click on the Data ribbon and check the top-right corner.
Do you see the Data Analysis menu? If the menu is available, it means your Excel application has the Data Analysis Tools ready to use for multiple linear regression analysis. However, if the menu does not appear when you click on the Data ribbon, it means the Data Analysis Toolpak has not been activated in Excel.
For this reason, in this section, I will provide a tutorial for activating the Data Analysis Toolpak in Excel if it is not yet active. Steps to Activate the Data Analysis Toolpak: Look for the Options menu in Excel. Note that the location of this menu may vary slightly depending on the version of Excel.
If you are using Office 365, Click File. Click More, and you will find the Options menu there. After clicking on Options, a new window will appear with several choices. Look for the Add-Ins menu. In the Add-Ins window, scroll to the bottom. There, you will see a dropdown menu labeled Manage. Select Excel Add-ins and click Go.
A new window will appear. In this window, you will find an option to activate the Data Analysis Toolpak. To activate the Data Analysis Toolpak, check or enable the option in the window and click OK. Congratulations, you have successfully activated the Data Analysis Toolpak menu in Excel!
Now, to confirm, return to the Data ribbon in Excel and check the top-right corner. The Data Analysis menu should now be visible. The steps I have outlined above are illustrated in detail in the step-by-step images below:
Case Study Example
To provide a deeper understanding, this article includes a case study for practice. This case study uses multiple linear regression analysis with two independent variables.
In this case study, a researcher collects data on the inflation rate, unemployment rate, and economic growth in a region over the past 30 years. Based on this case study, we know that the data used is annual time series data consisting of 30 observations.
The purpose of this research is to determine the influence of the inflation rate and unemployment rate on economic growth. The detailed data collected can be seen in the table below:
From the table above, we can create a specification for the multiple linear regression equation by dividing the variables into dependent and independent variables. The inflation rate and unemployment rate are considered independent variables because they influence other variables.
Meanwhile, economic growth is considered the dependent variable because it is affected by the independent variables. In this case study, we will estimate the influence of the inflation rate and unemployment rate on economic growth.
This article provides a tutorial on how to perform multiple linear regression analysis. Tutorials on assumption tests, such as residual normality tests, multicollinearity tests, heteroscedasticity tests, and other assumption tests, will be discussed in a separate article.
How to Perform Multiple Linear Regression Analysis in Excel
As promised, in this article, I will provide a tutorial on how to perform multiple linear regression analysis using Excel. The first step is to ensure that you have correctly entered the data in accordance with the order of the variables.
In the case study data table above, I have arranged the data input and tabulation to facilitate data analysis in Excel. In the table, the values of each variable are arranged according to the number of observations, from the first observation to the 30th observation.
After ensuring the data has been entered correctly, the next step is to click the Data ribbon in Excel and then select Data Analysis in the top-right corner. Once you click on the Data Analysis menu, a new window will appear containing various analysis methods you can choose to assist with your data analysis.
From the available options, find and select Regression. Then click OK, and a new window will open, requiring you to follow additional steps.
Input the Y and X variables. For the Y variable, input all the data from the dependent variable (economic growth) along with its label. For the X Variable, input all the data from the independent variables (inflation rate and unemployment rate) along with their labels.
Make sure to check the Labels box and set the Confidence Level to 95%. A confidence level of 95% ensures that the alpha threshold in this study is 0.05. You need to specify where the analysis results will be saved. Excel provides three options: Save in the same Excel sheet, Save in a new sheet in the same Excel file, or Create a new Excel file for the results.
In this tutorial, I will demonstrate saving the output in the same Excel sheet. Select the Excel cell where you want to store the analysis output. The detailed steps mentioned above are illustrated in the images below:
After completing all the steps correctly, click OK, and the results of the multiple linear regression analysis will appear in your Excel sheet:
From the analysis results, as shown in the image above, it is evident that the output of the multiple linear regression analysis is identical to the results obtained using other statistical software.
The output includes the coefficient of determination, the ANOVA regression table, and the T-statistic values, all of which are consistent. Additionally, the output includes p-values, which are extremely useful for drawing conclusions and testing statistical hypotheses in your research.
This concludes the tutorial I have written for this occasion. I hope it is helpful and provides insight for those of you performing regression analysis in Excel for the first time. Thank you for reading this article and stay tuned for updates from Kanda Data in the coming weeks. Thank you!
