• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Using Azure Machine Learning in Excel

You are here: Home / Excel / Using Azure Machine Learning in Excel
using azure machine learning in excel
July 13, 2018 by Philip Treacy

In this post I'm going to look at how you can create an Azure Machine Learning experiment to predict diamond prices. Then set up the experiment as a web service, connect Excel to it, and use it to estimate the prices of diamonds.

If I had to explain what machine learning meant, in this context, I'd say it's using algorithms to analyse data for features that will allow us to estimate a particular property of something given some new data.

Or in English, if my machine learning algorithm examines the weight, colour, clarity and dimensions of a large number of diamonds of known value, it can then estimate the value of another diamond if I can feed it that diamond's weight, colour, clarity and dimensions.

I'm assuming here that the weight of a diamond is a good marker for the price, but obviously there are other things that affect the price. But for the sake of simplicity for this example, I'm going to look at the relationship of weight vs price.

What Algorithm To Use for ML

There are many different algorithms that you can use for machine learning. You don't need to know the nitty-gritty of how the algorithm works though.

Of course a decent knowledge won't hurt you, but I don't need to know the details of how the linear regression algorithm works. I just know that I can use it to create a model of diamond prices if I feed it suitable data.

Linear Regression

Using a linear regression model means I need to have data that when plotted, forms a nice, straight line, and that what I am trying to estimate is a number.

I'm using the diamonds data set from Kaggle. If we look at a plot of the price of the diamonds against the weight we get this, which isn't exactly a straight line.

Full Dataset Plot Diamond Price vs Weight

So I'm going to split the data into separate parts. I found through examining the data that internally flawless diamonds less than 0.82 carats, with color H, I or J, and with Very Good, Premium or Ideal cut, have a pretty good linear relationship for their weight v price.

Plot of Internally Flawless Diamonds Price vs Weight

It may not be the best straight line plot in the world, but remember this is only for the purposes of learning how to use ML in Excel.

Creating the Machine Learning Experiment

Microsoft provides Azure ML Studio (AMLS) for you to create your machine learning experiments. You can get your hands dirty for free, and there are also paid subscriptions options available.

The language used in this arena is to refer to what we create in AMLS as an experiment. There are two parts to this, the training experiment, and the predictive experiment. For predictive experiment, think predictive model.

It's in the training experiment that we provide data for the algorithm to analyse, or learn, what attributes of a diamond may be important in predicting it's value. You can re-run this part of the experiment many times until you are happy that you have an accurate, or as accurate as you can get, model.

Once you are happy with your model you can turn this into a predictive experiment and make it available as a web service. Which basically means you can connect to it via Excel, a web browser, or programmatically using something like Python.

By giving the web service some data relating to a diamond it will return it's estimation of the price of that diamond.

Setting up the Experiment

Microsoft provide a pretty good guide for setting up your first ML experiment which you should read.

Once you've got yourself set up in AMLS, the first thing to do is import the internally flawless diamond dataset, which you can download at the bottom of this post.

Import dataset to Azure machine learning studio

To teach, or train, our experiment we need to provide it with some sample data to analyze so we will use the Split Data module to split the data. We'll use 75% of the data to train the model and then use the remainder to evaluate how well our experiment estimates diamond prices.

split dataset for training

We can now add the Linear Regression module and train the model.

Train the model

We need to tell the experiment what it is we want it to predict, so by clicking on the Train Model module, we can select the column in our dataset that we want the linear regression algorithm to predict.

Select column for training

After training the model we connect a Score Model module which tries to predict the value of the 25% of the dataset we didn't use for training.

Score the model

The results are provided like so

scored labels

Finally we use an Evaluate Model module to see how well our experiment is at predicting the diamond prices

evaluate model

These results are provided as a series of metrics

evaluate model metrics

A measure of how good the model is at predicting prices is the coefficient of determination, also known as R2. You'll see that the model achieves slightly better than Excel does (0.9095) for the trend line in the plot shown earlier in the post. The closer R2 is to 1 the better, so I'm quite happy with this as a rough model.

Creating the Predictive Experiment and Web Service

After saving the training experiment you can then set it up as a web service.

set up predictive web service

AMLS creates the service for you but you can modify certain aspects of how it works.

For example, I have added an Edit Metadata module to change the name of the output from the service from the bland 'Output' to 'Price'.

I have also added a Select Columns in Dataset module so that the service returns only the Price. By default it will return all the attributes of the diamond i.e. color, clarity etc even though we aren't interested in these.

azure machine learning predictive experiment

With all of this set up you can test your web service from within AMLS

test web service

Using the Web Service In Excel

Microsoft provide an AML add-in for Excel that allows you to use web services. You need to know URL and API key for the service to connect to it though.

An easier way is to download the workbook direct from AMLS when you look at your web services

download workbook from aml

You can download the workbook appropriate to your version of Excel.

When you open it on the right hand side of your screen you'll see the AML app starting and then see the name of the web service 'Diamond Price Prediction'.

aml app

Click on the web service name and it will be started ready for you to use.

aml app ready to use

Use the range selection icon to use your mouse to select the input data, rather than manually typing the range. When you've done this the app just says 'Range selected' rather than actually telling you what range you have selected. Check the box if your data has headers, but there's no need to supply headers for this.

You can type the cell reference into the box for the output (the predicted price returned by the web service). Click the Predict button when you are ready and you should get a result for your diamond.

My input range is A2:H2 and the web service returns a value of 2088.61 in cell I2.

aml web service predicting price of single diamond

You can supply data for several diamonds at once and the web service will return its estimate of their values. If my data starts in A1 (including the header row) then I just need to specify a single cell as the output (I2) and the diamond prices will be inserted into the appropriate cells in column I.

aml web service predicting prices of multiple diamonds

Download the Workbook and Data Set

My workbook with the Azure ML app and the data set I used for the diamonds are provided for download. Enter your email address below to get these.

By submitting your email address you agree that we can email you our Excel newsletter. You can unsubscribe at any time.
Please enter a valid email address.
Predict_Diamond_Prices.xlsx
IF_Diamonds.csv
using azure machine learning in excel

More Machine Learning Posts

More Excel Posts

excel templates

Where to Find Free Excel Templates

Where to find free Excel templates and how to create your own Excel templates. Using templates saves time and effort.
Easily Remove Password Protection from Excel Files

Easily Remove Excel Password Protection

How to remove Excel password protection when you’ve forgotten the password. Works for sheets, workbooks and read only files.
Import data from a picture to Excel

Import Data from a Picture to Excel

Import data from a picture to Excel. Works with pictures from a file or the clipboard and loads it to the spreadsheet.
excel online

5 Excel Online Features Better than Desktop

5 Excel Online Features Better than Desktop including searchable data validation, track changes, single line ribbon and more.

10 Common Excel Mistakes to Avoid

10 common Excel mistakes to avoid, including merge cells, external links, formatting entire rows/columns and more.
new Excel features

Cool New Features in Excel for Microsoft 365

Cool New Features in Excel for Microsoft 365 including the navigation pane, smooth scroling, unhide multiple sheets and more.
dynamic dependent data validation

Dynamic Dependent Data Validation

Dynamic Dependent Data Validation with dynamic array formulas like FILTER make it quick and easy to set up.
QAT

Excel Quick Access Toolbar

The Excel Quick Access Toolbar is not only a handy for your mouse, but it also enables some super easy keyboard shortcuts.

Share and Collaborate in Excel

Share and Collaborate in Excel just like Google Sheets! Show changes, custom views, threaded comments with @ mentions and more.
Workbook Protection

Excel Workbook Protection

Excel Workbook protection can prevent your users from breaking your reports while still allowing interaction with Slicers and refreshing.
Category: ExcelTag: machine learning
Previous Post:calling vba in an add-in from another vba moduleCalling VBA in an Add-In From Other VBA Modules
Next Post:Excel Functions TranslatorExcel Functions Translator

Reader Interactions

Comments

  1. Guilherme Lopes

    July 15, 2018 at 10:11 am

    This post was the best ever!

    Philip, do you think in post more about ML using Azure, merge it working with excel and database?

    Reply
    • Philip Treacy

      July 16, 2018 at 11:01 am

      Hi Guilherme,

      I’m not sure what you mean. In this post I’m using AML as a tool to help me predict the value for something. You can still use Excel for everything else you would in combination with this AML service connection.

      Regards

      Phil

      Reply
      • Guilherme Tarabal Lopes

        August 16, 2018 at 10:59 am

        Hi Phil, sorry if I didn´t make myself clear.

        I mean is, will you show more examples using ML and excel?

        Thanks a lot.

        Reply
        • Philip Treacy

          August 16, 2018 at 11:01 am

          Hi Guilherme,

          I have nothing immediately planned but yes I’m sure I will write some more.

          regards

          Phil

          Reply
  2. Kamran Youni

    July 15, 2018 at 6:24 am

    Dear Philip, This is very helpful. I would love to see a series on ML for advanced statistics users who are trying to learn these tools

    Reply
    • Philip Treacy

      July 16, 2018 at 11:02 am

      Thanks Kamran,

      I’ll consider this for future posts.

      Regards

      Phil

      Reply
  3. luis osorio

    July 14, 2018 at 12:45 am

    Good work

    Reply
    • Philip Treacy

      July 14, 2018 at 4:55 am

      Thanks

      Reply
  4. Julian

    July 13, 2018 at 11:22 pm

    Treacy, you must be a all-round genius. I admire you so much.

    Reply
    • Philip Treacy

      July 14, 2018 at 4:55 am

      Hi Julian,

      I’m not sure about that, but glad you liked the post.

      Phil

      Reply
  5. Alan Lewis

    July 13, 2018 at 10:37 pm

    Many thanks!!

    Is this part of a webinar? Or is it a dead end?

    Reply
    • Philip Treacy

      July 16, 2018 at 11:03 am

      Hi Alan,

      There’s no webinar to go with this but that doesn’t make it a dead-end. This is intended as an intro to AML and if you follow it and the other links in the post, you should be able to set up your own experiments.

      Regards

      Phil

      Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Current ye@r *

Leave this field empty

Sidebar

More results...

Shopping Cart

mynda treacy microsoft mvpHi, I'm Mynda Treacy and I run MOTH with my husband, Phil. Through our blog, webinars, YouTube channel and courses we hope we can help you learn Excel, Power Pivot and DAX, Power Query, Power BI, and Excel Dashboards.

Subscribe to Our Newsletter

Receive weekly tutorials on Excel, Power Query, Power Pivot, Power BI and More.

We respect your email privacy

Guides and Resources

  • Excel Keyboard Shortcuts
  • Excel Functions
  • Excel Formulas
  • Excel Custom Number Formatting
  • ALT Codes
  • Pivot Tables
  • VLOOKUP
  • VBA
  • Excel Userforms
  • Free Downloads

239 Excel Keyboard Shortcuts

Download Free PDF

Free Webinars

Excel Dashboards Webinar

Watch our free webinars and learn to create Interactive Dashboard Reports in Excel or Power BI

Click Here to Watch Now
  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
 
  • About My Online Training Hub
  • Contact
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Copyright © 2023 · My Online Training Hub · All Rights Reserved

Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.

Download A Free Copy of 100 Excel Tips & Tricks

excel tips and tricks ebook

We respect your privacy. We won’t spam you.

x