The algorithm that performs multiple linear regression calculates (XTX)-1 where X is the design matrix. Ridge and LASSO regression are used when XTX is not invertible or when it is close to not being invertible (such as when there is multicollinearity or when there are more independent variables than data elements.
Sometimes (XTX)-1 can’t be calculated accurately in Excel because of an overflow error. This can occur when there are a large number of independent variables. In such cases, the results from the Multiple Linear Regression data analysis tool will be strange (e.g. R-square value larger than one or a negative value for SSE).
In such cases, you can click on the Use Solver option in the dialog box (see Figure 2 of Real Statistics Capabilities for Multiple Regression) of the Multiple Linear Regression data analysis tool to handle such situations. E.g. for Example 2 of Multiple Regression Analysis in Excel, you would get the output shown in Figure 1.
Figure 1 – Regression using the Solver option
As you can see the coefficients calculated are similar to those shown in 3 of Real Statistics Capabilities for Multiple Regression. For this data, clearly, this approach is unnecessary, but as remarked above, with certain data it can be useful.