Master Machine Learning Basics Using Google Sheets: A Practical Guide

Google Sheets is a powerful tool for analysis, you can perform basic machine learning tasks in Google Sheets. Google Sheets offers built-in functions, add-ons, and integrations to perform machine learning tasks. In this article, we will show how to use machine learning with Google Sheets.

Let’s consider a sales dataset to apply machine learning concepts such as regression analysis, classification, and trend prediction using Google Sheets.

1. Linear Regression for Sales Prediction

Linear regression is used to predict a target variable based on one or more input variables You can use the LINEST function in Google Sheets to predict total sales based on Units Sold.

• Select the Units Sold and Total Sales columns, which will be the input and output variables.
• Select a cell and insert the following formula.

Formula:

=LINEST(G2:G71, E2:E71, TRUE, TRUE)

This will return a set of regression statistics based on the Units Sold and Total Sales column.

Set of Regression Statistics:

• The first value 27.02405783 is the slope (m).
• The second value -42.01697274 is the intercept (b).

Interpret the Results: The regression equation is:

Total Sales = m * Units Sold + b

Formula:

This formula predicts the future sales value based on the unit values and the regressions.

2. Trend Analysis – Predicting Future Sales

Trend analysis involves identifying patterns in data over time. In this case, we can predict future sales based on historical sales data.

Insert Line Chart

• Select your Date and Total Sales columns.
• Go to the Insert tab >> select Chart >> from Setup >> select a Line Chart to visualize the sales trend over time.

Add a Trendline to Trend Sales:

• Click on the Chart >> go to Chart Editor.
• Under Customize tab >> select Series, and then check the Trend line box.
• Format the Line Color and Line Opacity.
• A trendline will be added to the chart.

Use the TREND Function:

If you want to predict future sales, use the TREND function. For example, you could use it to predict sales for the next day.

Formula:

=TREND(G2:G70, E2:E70, E71)

This formula predicts the total sales for the next value in the Units Sold column (E71).

3. Use Simple ML for Sheets Add-On

You can use Simple ML to build a regression model that predicts Total Sales based on features like Units Sold, Region, or Product. This helps you forecast sales trends or identify key drivers.

Install the Add-on:

• Go to the Extensions tab >> from Add-ons >> select Get add-ons.

• In the search bar search for Simple ML.
• Select and Install from the add-on store.

• Authorize the necessary permissions,

Use the Simple ML in Your Sheet:

Let’s assume you have some missing values in your dataset. You can predict the missing values by using this add-on.

• Go to the Extensions tab >> select Simple ML for Sheets >> click on Start.

• From Task Pane >> select Predict missing values.

• Select the column of your missing values (e.g.; Total Sales) >> click on Predict.

Missing values will be added to the sheet in a new column.

Now you can use all available predictions based on your requirements from Simple ML.

4. Install the BigML Add-on for Google Sheets

• Navigate to the Extensions tab >> from Add-ons >> select Get add-ons.
• Search for BigML for Sheets >> click Install and follow the prompts to authorize the add-on.

Connect Google Sheets to BigML:

• Go to the Extensions tab >> select BigML >> select Start.

• BigML will appear in the task pane.
• Log in using your BigML credentials or create an account.
• Select the sheet in your Google Sheet and click Send Data to BigML.

Create and Train a Model in BigML:

• Once your data is uploaded to BigML, open the BigML dashboard.
• Go to the Sources tab >>Select 1-CLICK DATASET to create a dataset.

• To train your model select LINEAR SPLIT. It will select 80% data to train your model.

• Now you can perform available predictions.
  • Linear Regression for predicting continuous values (e.g., Total Sales).

Output:

Now, you can use the trained model to predict outcomes for new data points directly in Google Sheets from the available options.

Conclusion

Though Google Sheets is not known for machine learning use, it can handle machine learning concepts, especially for beginners. From linear regression with built-in functions to advanced classification with add-ons, you can perform essential tasks without needing specialized software. You can leverage third-party add-ons like BigML, and Simple ML Sheets to gain insights into your sales data by following some steps. Explore these techniques to expand your analysis by integrating Google Sheets with more advanced machine learning tools.