Excel for Data Science and Machine Learning

Learn regression analysis as a powerful prediction tool for causal relationships, from simple to multiple regression, including building, interpreting, and comparing models for data science and supervised machine learning.

Use regression analysis to predict a dependent variable from independent variables with the simple linear model y = beta0 + beta1 x + epsilon, illustrated by house price versus size.

Learn to perform a first regression in Excel by predicting ice cream sales from daily mean temperature, using linest and the data analysis toolpak, and interpret the coefficients.

Learn to read regression statistics, anova, and coefficients to interpret model fit. Compare correlation and regression, assess r, r-squared, adjusted r-squared, and standard error with data examples.

Decompose variability into the total sum of squares (SST), the regression sum of squares (SSR), and the error sum of squares (SSE); SST = SSR + SSE.

Interpret the regression table by examining the analysis of variance, including sum of squares, degrees of freedom, the f statistic, and the p value to assess model significance.

Learn five core OLS assumptions for linear regression, including linearity, zero covariance between errors and predictors, normality and homoscedasticity of errors, no autocorrelation, and no multicollinearity.

Explore OLS assumptions of normality, zero mean, and homoscedasticity, and learn how the central limit theorem and log transformations address heteroscedasticity and yield semi-log models with elasticity.

Explore the no auto correlation assumption and its impact on time series data, and learn to replace linear regression with AR, MA, ARMA, or ARIMA models when errors are correlated.

Learn to use Excel's trend function with least squares to fit a line from known x and y and predict new y, using mean temperature and dummy rain as predictors.

Clean and transform a messy property sales dataset in Excel using Power Query, importing CSV data, cleaning data, and creating numeric features. Create end-minus-one dummies for furnishing status.

Explore how to assess linearity and regression assumptions for price data in Excel, using scatterplots, log transforms, and variance inflation factors.

Standardize data by subtracting the mean and dividing by the standard deviation to achieve a mean of zero and a standard deviation of one, i.e., feature scaling.

Explore logistic regression as a nonlinear extension of linear regression for predicting categorical outcomes, including binary and multi-class cases, to forecast yes or no decisions like customer purchases.

Explore how logistic regression assessment uses pseudo r-squared and the confusion matrix to compare actual versus predicted admissions, evaluating accuracy, precision, and recall across true/false positives and negatives.

Explore how the ROC curve evaluates classification models by plotting true positive rate and false positive rate across cutoffs, and use AUC to measure discrimination.

Explore how cluster analysis groups observations with similar features into clusters, enabling market segmentation, image segmentation, and insights from scatter plots of customers by age and spending.

Discover how k-means clustering uses centroids to assign data points to the nearest cluster, iteratively updating means to reveal two or three data groups in Excel.

Learn how to perform k-means clustering in Excel by grouping six cities using latitude and longitude, calculating Euclidean distances to centroids, and iterating to update means.

Compare flat and hierarchical clustering, where k means is flat and divisive and agglomerative methods build nested clusters; use the elbow method and den diagram to visualize results.

Entropy measures disorder and homogeneity, guiding decision tree splits. Two-class scenarios show entropy ranges from 0 to 1, revealing how information gain reduces uncertainty.

Learn to build a multi-feature decision tree in Excel by calculating entropy and information gain. Use countifs and index to build frequency tables and split first by outlook.

Explore why the cloud drives machine learning with cost efficiency, speed, global scale, performance, and productivity, and learn how Azure Machine Learning Studio enables drag-and-drop model building.

Access Microsoft Machine Learning Studio and sign in to start using the workbench. Explore projects, experiments, web services, data sets, trained models, and settings, including Excel communication with AML Studio.

Build your model in Azure Machine Learning Studio by selecting a two-class boosted decision tree with 100 trees, training on a 90/10 split, and evaluating with AUC and confusion matrix.