Business Analyst Bootcamp: Excel, Stats, SQL, Tableau

Explore what big data is, its scale from gigabytes to exabytes, why Excel fails, and the six v's—volume, velocity, variability, variety, veracity, value—for guiding secure analysis in the cloud.

Learn how data pipelines capture data via APIs, transform and load it into centralized storage such as a data lake or data warehouse, and enable dashboards and machine learning analyses.

Explore how data is collected, from sampling a representative subset to open data and company data, and use crowdsourcing and static versus dynamic data for real-time insights.

Classify data types from variables to categorical and numerical data, including discrete and continuous, cross-sectional and time series, and structured versus unstructured formats. Explore CSV, JSON, and XML file formats.

Learn why Excel is not a scalable database and explore data storage options like data warehouses, data marts, and data lakes that organize structured, semi-structured, and unstructured data.

Explore the basics of spreadsheets with Microsoft Excel and Google Sheets, covering the layout of columns and rows, cells, autofill, currency formatting, formulas, and operators.

Master core spreadsheet functions for business analysts, including count, counta, min, max, product, sumproduct, and if, and, or, with countifs and sumifs for conditional analysis.

Explore descriptive statistics, from summation notation and central tendency to mean, median, mode, variance, and standard deviation, with population vs sample concepts and Excel applications.

Explore the normal distribution with standard deviation, the 68-95-99.7 rule, z scores, outliers, and IQR, and compare mean, median, and mode as data descriptors.

Explore how to filter and organize data in excel using filters, slicers, conditional formatting, and pivot tables, then master lookup techniques like vlookup, index-match, and xlookup.

Characterize data point relationships using covariance and correlation, noting positive, negative, and zero associations; correlation ranges -1 to 1, while covariance is unbounded.

Learn to model quarterly sales in Excel by building a dynamic calculator with absolute and relative references, using growth rates, the sum function, and copy techniques.

Learn to create and customize charts in Google Sheets by selecting city and traveler data, inserting a chart, choosing chart types, and understanding when to exclude totals for clear visualizations.

Create and customize a histogram from a large data set in a spreadsheet, learn quick navigation shortcuts, and adjust bucket sizes to clarify the distribution and frequency axis.

Apply filters and sorts to a transactional data set and use aggregate functions (sum, average, median, mode, max, min, count, and unique) to derive insights. Explore dispersion via standard deviation.

Learn pivot table basics in Google Sheets or Excel to analyze a company roles dataset by role and gender, using counts and percent of row or percent of column calculations.

Learn to build pivot tables in Sheets/Excel to analyze tutor availability by subject and time zone, visualize results, filter data with slicers, and apply conditional formatting.

Connect orders and customers across two sheets using vlookup and the more robust index match, with absolute references for accuracy. Learn why index match outperforms vlookup.

Explore random variables and probability distributions, including discrete and continuous types, with dice, coin flips, and real-world outcomes, and learn about pmf, pdf, and cdf.

Compute statistics for random variables by weighting each outcome by its probability to obtain the mean (expected value), variance, and standard deviation.

Explore probability distributions defined by formulas, including Gaussian (normal), binomial, Poisson, and Hypergeometric. Understand Bernoulli processes, independent trials, and combinations, with real examples and Excel methods.

Decide using expected value by weighting outcomes with probability and your risk appetite. See roulette, NFT versus cash, and parking fee examples to compare expected values.

Explore binomial, gaussian, Poisson, and hypergeometric distributions and learn to compute probabilities in Google Sheets using trials, successes, and cumulative versus exact options.

Apply Poisson distribution to model customer visits in a time period using the average visit rate, and compute the probability of a given count with examples.

Apply the hypergeometric distribution to compute the probability of exactly one damaged mango in a five-sample draw from a 20-mango box with two damaged, using Excel functions.

Apply probability and statistics to a dealership bonus program by computing the expected bonus, its variance and standard deviation, using sumproduct in Excel to balance payouts and reserves.

Learn how to compute a weighted average for grades by multiplying each assignment by its weight and using sumproduct to sum the results, revealing accurate final grades.

Explore inferential statistics by using representative samples to infer population conclusions, test hypotheses, and apply the central limit theorem to understand how sample means mirror the population.

Explore how to form null and alternative hypotheses, their mutual exclusivity, and how right tail, left tail, and two-tail tests define rejection regions in hypothesis testing.

Explore the two error types in hypothesis testing, type I and type II errors, and learn how sample data influence rejecting or failing to reject the null hypothesis.

Explore p values, z scores, and the standard normal distribution to test hypotheses. Learn manual calculation and Excel-based methods, left and right tails, and symmetry.

Compare the p value to alpha to decide whether to reject the null hypothesis. Use the 5% significance level and z-score tails to identify rejection regions.

Master when to use the z test or t test, and apply p-value or critical-value approaches to hypothesis testing. Grasp confidence intervals, standard error, and role of degrees of freedom.

Compare a control and a challenger in AB testing to see which variant improves conversion rates. Set the traffic split and significance level to determine a winner based on data.

Form null and alternative hypotheses and test a left-tailed z test at alpha 0.05; the coffee can example shows rejection with mean 2.92, mu 3, sigma 0.18.

Assess Heathrow's status using a one-tailed t test (n=60, x̄=7.25, s=1.052) to test if mean exceeds 7; with α=0.05, p=0.03 confirms Heathrow as a superior service airport.

Explore AB testing on a marketing page to evaluate average order value with a z test, discuss t tests, normal distribution, standard deviation, alpha, and p values.

Perform a two-tailed hypothesis test on golf ball mean distance, comparing 297.6 to 295 with known sigma 12, n=50, alpha 0.05, and use p-values or critical values to decide.

Explore how traditional algorithms differ from machine learning and artificial intelligence, highlighting data-driven predictions, synthetic data, deep learning, and supervised, unsupervised, and reinforcement learning.

clean data is essential for reliable machine learning; address data entry errors, mislabels, and missing values, then handle outliers and plan training/testing splits, drops, imputing, or synthetic data.

Explore linear regression from single-factor to multi-factor models, identifying independent and dependent variables, interpreting coefficients, F and t tests, R-squared, and the importance of reasonable relationships.

Learn to model a dependent variable with multiple factors using multifactor linear regression, evaluating weight and horsepower, addressing multicollinearity, and refining models with F tests and p-values.

Compare models using distance measures such as Euclidean distance with normalization, and assess performance with RMSE and relative RMSE, precision, recall, and F1.

Investigate nonlinear regression forms, including polynomials, exponents, and logs, to model non-linear relationships like BMI versus body fat. Use R squared to choose fit while addressing outliers and correlated features.

Explore how to run a linear regression in Excel (Mac and Windows) using the analysis toolpak, interpret r-squared, f-test, and t-tests, and refine models by trimming insignificant intercepts.

Use a histogram to spot outliers and dirty data, then compute q1, q3, and the iqr to set bounds and decide removals.

Clean the baby height data by removing outliers with the interquartile range in Excel. Then build a regression of height on age in months and interpret coefficients, r-squared, and significance.

Explore a regression with four X variables in Excel, perform F and t tests, remove non-significant predictors, and build a refined model to predict Y.

Build a regression model from training data to predict admission chance using GRE, TOEFL, GPA, SOP, and LOR; clean outliers with interquartile range, apply filters, and validate on testing data.

Explore classification, a supervised learning approach that splits data into binary or multi-class groups using support vector machines with kernels and optimal hyperplanes, contrasting with regression's discrete or continuous outputs.

Explore clustering, an unsupervised learning method that groups data into meaningful clusters, revealing hidden patterns for market segmentation and product testing. Understand how clustering guides customer insights and strategy.

Explore how k-means clustering creates centroids, assigns data by distance, and converges progressively. Compare hierarchical clustering's agglomerative and divisive methods, noting when to use numerical or categorical data and standardization.

Explore how to optimize clustering by balancing error and computation using the elbow method and silhouette score, and watch out for Simpson's paradox in multi-group data.

Explore reinforcement learning, where an AI agent learns through trial and error in an environment, maximizing rewards across iterations, with examples like alpha go, stockfish, alpha zero, and alpha star.

Explore model fit by weighing underfitting and overfitting, using r-squared and root mean squared error, and balancing training accuracy with reliable testing, production predictions, and cautious interpolation and extrapolation.

Explore forecasting with time series, including qualitative and quantitative approaches, and learn how historical patterns through the trend, seasonality, and irregularities drive future predictions.

Learn to predict with time series by separating trend, seasonality, and irregularities, smooth and deseasonalize to reveal trend, forecast with regression, and evaluate errors using MAE, MAPE, MSE, and RMSE.

Analyze time series by decomposing data into trend, seasonal, and irregular components, then smooth using moving averages and deseasonalize to forecast future values.

Learn to deseasonalize quarterly sales data in Excel using a three-day moving average to reveal the trend and quarter-specific seasonality for forecasting.

Learn to smooth a time series with moving averages, isolate seasonality, and forecast with regression in Excel, then evaluate mae and mape.

Learn how to model a Shopify e-commerce store's 3-year profitability by projecting monthly revenue and costs, incorporating seasonality, growth, fixed and variable costs, and net revenue per customer.

Explore how relational and no sql databases store data, using primary and foreign keys, star and snowflake schemas, and joins to connect tables like employees, departments, and sales fact table.

Master the basics of reading data from tables with SQL, including select, where, order by, distinct, join, and aggregate functions, using MySQL Workbench to explore relational databases.

Learn how to modify table data using insert into to add multiple rows, update to change specific records with a where clause, and delete from to remove rows safely.

Learn to join multiple tables in SQL using inner, left, right, and full joins, with dot notation and key relationships, to combine orders and customers.

Explore non-SQL data structures like columnar parquet, graphs including directed acyclic graphs, and time series. See how NoSQL stores JSON blobs and how data engineers ingest and store data.

Install MySQL server and workbench on Mac, select the correct chip, handle security prompts, set a password, and connect to a local instance.

Learn to install MySQL on Windows by downloading the MySQL Community Server and MySQL Workbench from mysql.com, running a custom installer, configuring a password, and finishing setup.

Explore how relational databases differ from Excel and learn core SQL concepts - tables, keys, schemas - and basic queries using select and where to read and filter data.

Master MySQL workbench basics and write SQL queries to read from store.customers, using select, where, order by, and aggregates like sum and average.

Master joining tables in sql by linking orders and customers on customer_id, using select and on clauses, and compare to Excel lookups like index match and vlookup.

Explore core sql operations: insert, update, delete, and joins in MySQL to combine tables such as customers and orders, using auto increment, nullability, and where clauses to manage data.

Understand what business intelligence is and why visualization turns data into insights and decisions. BI software connects databases and creates visuals to inform senior stakeholders and improve the business.

Explore Tableau, a no-code business intelligence and data visualization suite, from desktop to prep builder, and learn to build dashboards, worksheets, and stories with dimensions and measures.

Identify information types suitable for visualization, from change over time to flow data, and match them with line, bar, heat map, bubble, histogram, scatter, pie, radar, and Sankey diagrams.

Dashboards visualize key metrics and key performance indicators in one place, providing live updates to inform executives about the business. Ensure data governance and standardization to avoid confusion and overcrowding.

Learn to present data effectively by choosing informative, persuasive, or demonstrative formats, delivering with extemporaneous style and storytelling, and building clear, well-structured slides.

Learn how to download and install Tableau, start a trial, and navigate the interface to connect to diverse data sources, including Excel files and databases.

Master Tableau basics: connect to Excel and server data, build table relationships, create visualizations with Show Me, and craft dashboards and stories with calculated fields.

Learn Tableau Analytics on the Analytics tab to create line graphs of sales over time, and use summarize, model, forecast, trend lines, and clustering to gain insights.

Explore Netflix titles data in Tableau by loading a CSV, identifying dimensions and measures, using clustering, scatter plots, and box plots to generate insights and tell a data story.

Live Tableau walkthrough explores FIFA 23 players data from Kaggle, cleaning a CSV, filtering rows, and using clustering and visuals on age, wage, base stats, and nationality to gain insights.