Essential Excel For Business Statistics and Data Analytics

Read external data into Excel by importing a delimited text file, selecting a delimiter like comma or tab, and placing the data in an existing sheet or a new one.

Import messy comma-separated csv data into Excel and begin formatting to reveal structure. Align column names and transform flat files into a coherent dataset for business statistics and data analytics.

Learn to read external data into Excel using web queries, converting dynamic web tables (IQY files) into updatable datasets instead of static copy-paste.

Extract a web page table into Excel using a web query, save as IQY, and tidy area, location, latitude, and longitude for Mumbai neighborhoods.

Explore how Excel handles a slightly complicated table with one cell and multiple sub cells, saving as plainText MS-DOS while testing data extraction and web scraping.

Learn to import dynamic data from a non-wikipedia HTML table into Excel using an external data query with a .iqy file, then refresh to reflect source changes.

Learn how to import XML data into Excel, exploring file import options for XML and HTML variants, and understand performance considerations when working with large XML files.

Explore how Excel uses logical operators to compare two string columns and return true or false, enabling quick checks of item equality across lists.

Learn how to filter data quickly in Excel using sort and filter, copy filtered data to another workbook, and expand selections to preserve row integrity during sort.

Learn how to apply conditional filtering in Excel by using operators like greater than or equal to, combining criteria, and identifying top ten and bottom ten values to refine datasets.

Explore using Excel if logic to classify budget outcomes and create an amount check column with conditions such as over 50 is over budget and under 25 two less.

Learn to apply and and or conditions in Excel to evaluate two criteria, such as B2 > 20 and C2 >= 50, and see how results change when using or.

Explore pivot tables to switch from sum to average values, adjust field settings, and display data as percentages to compare cereals, poultry, beef, and fish across European countries.

Explore pivot tables to summarize multi-country, multi-year data by country, food item, and year, computing sums and averages of tonnes per hectare, with filters and column layouts for clear insights.

Learn how brackets determine the order of operations in Excel to avoid miscalculating tax and totals, illustrated by bracketed versus unbracketed formulas.

Explore how brackets control the order of operations in Excel, solving expressions step by step using bodmas and nested divisions.

Learn to remove dollar signs from Excel data to enable basic computations, using format cells to set currency symbol to none, or find and replace across a column.

Learn to use Excel to perform descriptive statistics, computing mean, median, variance, and confidence intervals for height and weight by selecting input ranges and grouping by columns.

Understand central tendency by comparing mean, median, and mode, and learn how outliers and skew influence which measure to use in Excel.

Explore summarisation by a categorical variable in Excel by calculating the average units sold for each country. Specify the dataset range and criteria to compute descriptive statistics across country groupings.

Learn to perform subgroup statistics in Excel by sorting and filtering data, then applying subtotal to compute per-country totals, averages, and other metrics, culminating in a grand total.

Master data visualization as a tool for science, using bar, pie, histogram, line, and scatter plots to reveal patterns and trends. Learn to choose visualization techniques for exploratory analysis.

Use line charts to visualize time series data, such as stock prices by date, displaying open and high values, with options to customize colors and legend.

Plot a scatterplot to compare height and weight as continuous numerical variables, revealing their relationship. Adjust axes and add a trend line, and discuss linear regression later.

Explore how to visualize data distribution with histograms in Excel, using automatic or custom bin ranges to show frequencies and understand the quantitative data set.

Explore bar chart basics in Excel, visualizing discrete values like tonnes per hectare for Belgium foods with bar plots, columns, data labels, and axis titles to compare items.

Explains how to create and customize bar plots and stacked bar plots to compare math and reading scores across parental education categories.

Explore hypothesis testing by formulating the null hypothesis, computing a test statistic and p value, and assessing significance with alpha levels and type I and type II errors.

Learn how to compare two groups using t tests, including one-sample, two-sample, and paired designs, to determine if mean differences are statistically significant.

Check variances to choose between equal and unequal variances in a t-test, using the variance ratio for mustard and chocolate sauce, and interpret p-values greater than 0.05 as not significant.

Understand how ANOVA compares means across three or more independent groups to determine if a continuous outcome differs significantly among them. See how it generalizes the t-test for multiple groups.

Carry out a one-way ANOVA in Excel using data analysis, grouped by columns for three categories, and interpret a p value below 0.05 as evidence of significant differences among means.

Interpret one-way ANOVA results by comparing group means, reject the null when p<0.05, and examine F values and F critical values to determine significance.

Run a two-way ANOVA in Excel to examine how subject and gender influence starting salaries, assess interaction effects, and consider replication and alpha 0.05.

Explore how a two-way anova assesses gender and major on starting salaries, showing significant main effects and a non-significant interaction.

Explore how two-way ANOVA analyzes salary data by gender and major in business statistics and data analytics, revealing significant main effects but a non-significant interaction, with replication guiding interpretation.

Explore the theory of confidence intervals, including 95% intervals, margin of error, and the switch from z to t distribution for small samples, to quantify uncertainty in sample means.

Use chi-square tests to assess whether two categorical variables are associated, by comparing observed and expected counts, evaluating p-values and the null hypothesis of independence.

Compute the chi-square statistic to test gender and pastime independence using observed and expected values, with hands-on Excel steps for gym, cooking, and Netflix.

Compute the chi-square statistic across all cases by summing squared values. With p > 0.05, accept the null hypothesis that gender and past time are independent.

Compute correlations in Excel to explore relationships between stock attributes. Build a correlation matrix for open, high, low, and close prices and understand results like a 0.042 or 0.94 correlation.

Compute the correlation between opening price and high using Excel, by entering ranges, using the correlation function, and via the data analysis tool to create a correlation matrix.

Master the importance of identifying y and x before performing linear regression in Excel data analysis, using examples like median home prices and interest rates to ensure correct model interpretation.

Explore how ordinary least squares regression links height as the response to weight as the predictor. Interpret r-squared, adjusted r-squared, coefficients, and p-values to assess variation explained and model significance.

Learn how to use dummy variables to encode categorical predictors in regression, establish a baseline, and code categories like marital status and gender for accurate income analysis.

Create dummy variables for marital status by coding married and divorced as 1/0 with single as the baseline. Use these dummy variables in regression analysis to interpret results.

Regression with dummy variables for marital status shows income explained by age and marital dummies, with an adjusted r-squared of 0.67; age is significant, while married and divorced are not.

Learn to assemble a regression equation predicting income from age and marital status using dummy variables, interpret the intercept, and drop non-significant predictors based on p-values.

Explain how adding independent variables inflates the coefficient of determination, R-squared, in multiple regression, and how adjusted R-squared accounts for features and degrees of freedom to reveal variable importance.

Learn how to choose the right database by comparing SQL and NoSQL, and apply a quick rule for SQL data to support business statistics and analytics.