Microsoft Excel - Excel Data Analysis Expert (Accredited)

Discover how Excel functions analyze a data file from a holiday gift scenario, comparing a gift voucher and a surprise box to showcase data analysis in Microsoft Excel.

Explore how regional settings in Excel affect formula syntax, including comma versus semicolon separators, and learn to adapt functions like correlation to your locale.

Enable the analysis toolpak in Excel to access data analysis tools like regression and ANOVA. Install the translator option to support language translations and adapt to regional number formats.

Use the and function to determine whether each customer qualifies for special status by meeting two conditions: budget per employee greater than 50 and more than 80 employees.

Discover how to use the and function in Excel to test multiple conditions, including greater than or equal to 50 and text criteria like government customer budget per employee.

Use the and function in Excel to test two conditions—Academy Award equals 1 and ticket sales exceed 50—and identify a double success.

Discover essential Microsoft Excel data analysis techniques, exploring practical methods and insights through content surprise box 1.

Apply the if function in Excel to route orders to teams by a simple test. If the value is less than 100, assign to team A; otherwise, team B.

Apply the if function to calculate a discount for customers with more than 100 employees, multiplying the budget per employee by ten to determine the discount.

Learn to use nested if function in Excel to handle multiple conditions. Create tiered discounts by employee count—over 100, 50–100, or under 50—and edit the formula bar.

Learn nested if functions in Excel by creating a three outcome discount: more than 100 employees yields tenfold; between 50 and 100 yields fivefold; otherwise, no discount, with regional semicolon.

Create a net dvd sales column by subtracting the marketing budget from dvd sales, then use a nested if to assign big bonus, regular bonus, or fired.

Learn to combine the if and and functions in Excel to automate pricing rules. Apply a 50% discount when the dish spoils fast and the sales deficit exceeds 50%.

Learn how the vlookup function looks up an item in a column and returns its shelf number using a chosen table array and exact match option.

Explore content surprise box 2 within the Microsoft Excel data analysis expert course to develop practical data analysis skills in Excel.

Explore data analysis with Excel through Marcus Aurelius’ financial and party-management context, linking stoic principles to practical scenario analysis.

Use goal seek in Microsoft Excel to determine how many hours of music fit a 5.5 denarius budget by modeling expenses as 3 plus 0.63 times hours via what-if analysis.

Use goal seek in Excel to calculate how many people can receive clean water within a 6500 denarius budget, given a fixed 1137 and 0.23 denarius per person variable cost.

Unlock solver add-in in Excel by going to file, options, add-ins, click go, select solver, and confirm; the data tab will show solver for use unless restricted by an administrator.

Use Excel solver to achieve a 10,000 profit by adjusting ticket price, decoration cost, and personnel cost, while keeping tickets fixed at 200 and applying practical constraints.

Excel solver demonstrates setting constraints, like spending at least 300 on decoration and 300 on personnel, then solving to maximize profit.

Examine Ed Wood's life and Plan 9 from Outer Space to show budget constraints shaping creativity, then explore geometric mean, arithmetic mean, mode, and median with a Hollywood dataset.

Explore measurement scales in data analysis, using a pizza analogy to distinguish nominal, ordinal, and parametric scales, with examples like nationality, age, education, location, and income.

Choose the appropriate measurement scale: parametric, ordinal, or nominal, for data analysis, understanding that parametric offers the most freedom and downgrading is always possible, while upgrading is not.

Discover Ed Wood's do-it-yourself movie making, directing, writing, and acting with cheap resources. See how a common household item powered Plan Nine from Outer Space's special effects.

Identify the mode as the most frequent rating in data, illustrated by one and five appearing most often, including bimodal cases, and note its role alongside the mean.

Illustrate how the median, a central-tendency measure, equals the middle value when data are ordered, and how to average the two middle values if there is no single middle.

Compare mean and median to understand when to use each, and demonstrate how extreme values distort the mean, recommending reporting both to reveal central tendency.

Explore a Microsoft Excel file that tracks yearly movie output and average ROI, then compute basic statistics from losses and gains to analyze studio performance.

Learn to calculate mean, mode, min, and max in Excel using the average, mode, min, and max functions. Explore bimodal data and interpret multiple modes in a data set.

Learn to calculate the median in Excel using the median function, select the data range, and interpret the middle value when data is ordered from low to high.

Explain the difference between arithmetic mean and geometric mean and show how geometric mean better reflects cumulative percent changes in a four-year capital scenario.

Apply the geometric mean to perceptual changes, better capturing central tendency than the arithmetic mean, by converting percent changes to multipliers, multiplying them, and taking the 1/n root.

Learn how to raise numbers to a power in Excel using the caret operator, demonstrated by eight raised to 0.5 with a simple formula.

Compute the geometric mean to analyze central tendency for perceptual changes and return on investment in Excel. Learn to convert input and output percentages and apply the geometric mean function.

Compute mode and median from the data, compare arithmetic and geometric means for percentages and perceptual changes, and interpret a 47.5% annual visitor decline.

Examine Ed Wood's Plan Nine from Outer Space flying saucers, highlighting budget-friendly effects made from hubcaps or modified paper plates under financial constraints.

Use pivot tables in Microsoft Excel to break down sales totals by product and size, building a dashboard that analyzes large data sets quickly.

Explore pivot charts in Excel using the same data as pivot tables to visualize sales by product type and non allergy versus regular products, including axis titles and data labels.

Create a pivot table in Excel to analyze ticket sales by Academy Award nomination and use of special effects, using the movies dataset.

Choose the right Excel chart for your data, from long time series line charts to bar charts for groups, pie charts for distribution, and Pareto charts and histograms.

Learn to create and customize a two-line chart in Excel to compare Holland and Belgium profits over years, with proper data selection, axes, and axis titles in US dollars.

Demonstrates how a logarithmic scale on the y axis reveals large price changes, using bitcoin and a fictional currency, and shows applying format axis in Excel to highlight business-relevant shifts.

Use Excel 2016 to create a Pareto chart that plots frequency and cumulative percentage, revealing key drivers like expensive hardware and poor game quality for targeted improvements.

Master creating bar charts in Microsoft Excel from scratch using 1980 and 1981 production data for Ahmadabad, New Delhi, and Bangalore, and customize axes, series, and layouts.

Create a pie chart from scratch in Microsoft Excel to show passenger proportions by destination, using data, axis labels, and quick layouts to display percentages or counts.

Identify common chart mistakes, including missing axis labels, choosing the wrong chart type, and overusing 3D effects, to convey data clearly.

Create a line chart for team A, B, and C over the years; then build a pivot chart to compare DVD sales for films with or without special effects.

Explore how Coca-Cola shaped the Santa Claus image through the 1920s campaigns and 1931 Sundblom illustrations, then apply standard deviation theory to an Excel file about Coca-Cola.

Analyze data in Microsoft Excel using standard deviation as a key technique, visualize distributions with histograms, and understand the normal (Gaussian) distribution.

Learn to create histograms in Excel to visually assess normality for plant A, B, and C, tuning bin width to inspect distribution.

Learn how standard deviation measures data dispersion and, with the mean, applies the three standard deviation rules of thumb to bound data within 88–92, 86–94, and 84–96.

Track how Santa's appearance shifted from the early 20th century to 1939 depictions, shaped by Coca-Cola's Christmas campaigns seen by a US traveler returning from Japan.

Excel computes standard deviation for plant A and B, using population vs. sample options, showing plant B has greater spread while means stay near 30 cm.

Understand the normality assumption, where a perfect bell-shaped distribution yields 68%, 95%, and 99% rules of thumb, yet these rules remain rough approximations when data isn't perfectly normal.

Clarify standard deviation through a bottling plant example, highlighting that 95% of sample data lies between mean ± two standard deviations under normal distribution, and not about all bottles.

Inspect an Excel file that records bottle contents in centiliters for three plants A, B, and C, with sample values like 30.5 cl, 29 cl, and 10 cl.

Use excel's norm.dist function to compute the cumulative probability left of 91, with a mean of 90 and a standard deviation of 2, showing 69% below and 31% above.

Apply the norm.dist function in Excel to compute the probability under a normal distribution. Compute mean 90 and sd 2 with x=88.5 to obtain about 23% (area under the curve).

Examine how advertising influences culture as Santa shifts from green to red and white, possibly reflecting Coca-Cola branding in public depictions.

Explore the data file with sold items, customer details, dates, government or non-government status, products, units sold, unit price, revenue, and lead time from order to delivery.

Convert the data to a table, then create pivot tables to analyze lead time by government type and product, and revenue by customer type and product, for a dashboard.

Create and customize pivot tables and pivot charts in Excel to analyze lead time and revenue by customer type and product, then build an interactive dashboard with linked charts.

Learn to build an interactive Excel dashboard by moving pivot charts to a dashboard sheet, adding slicers and a timeline, and linking filters to multiple data views.

Create focused dashboards in Excel with few graphs, balancing final variables like sales and revenue with early warning indicators such as customer satisfaction and delivery time, via cross-department collaboration.

Analyze a Mars mission dataset and forecast the number of passenger volunteers using Excel data analysis to inform terraforming projections.

Learn how the Pearson correlation coefficient (r) measures the strength and direction of a linear relationship, from -1 to 1, with examples of positive, negative, and no linear association.

Explore a Mars data file covering 2030 to 2043, detailing passengers (thousands), average net monthly income, ticket price, living space, health check cost, and training duration.

Visualize data with a scatter plot in Excel to assess the relationship between passenger numbers and monthly income, adjust axes, and identify outliers before computing the correlation coefficient.

Calculate correlation coefficients in Excel, starting with individual correlations and then building a correlation matrix. Use the correlation function to compare data, noting a near -0.99 negative linear correlation.

Learn to create a correlation matrix in Excel using the Data Analysis Toolpak, including labels, selecting input range, and interpreting coefficients like 0.3 and -0.6.

Inspect a correlation matrix in Excel to see which variables relate to passenger numbers, noting negative links with income and positive links with inflation, ticket price, and Mars living space.

Explore the controversial idea of using nuclear bombs to establish a presence on mars and preview how they could provide benefits.

Explore correlation in Excel by analyzing a mock stock price dataset against short-term rates, the S&P 500, unemployment, long-term rates, and crude oil prices.

Unlock the Analysis ToolPak, generate a correlation matrix with labels in the first row, and note strong positive links between unemployment rate, crude oil price, and ABC 123 stock price.

Explore the difference between correlation and causality using a classic ice cream and drownings example, and identify how hidden variables like temperature confound relationships.

Explains that correlation coefficients capture only linear patterns, so non-linear relationships may show weak correlations. A low correlation does not prove the absence of a relationship.

Identify how outliers distort correlation analysis by affecting the R value, and learn to investigate causes before removing data, never excising more than 5%, and evaluating real-world context.

Explore how to respect outliers in data analysis, examining how outliers affect the correlation coefficient between temperature and visitors, and when to remove or keep them based on context.

Explore how time lag affects data relationships by testing delayed correlations, using unemployment rate and revenue as examples, and learn to shift data to reveal stronger insights.

Explore time-lag analysis in Excel by correlating promotional expenses with sales, testing zero to three week lags to identify the strongest lag for sales impact.

Explore how to assess time lag in Microsoft Excel by shifting promotional expenses and sales data, computing correlation coefficients, and finding a two-week lag with strongest impact.

Examine how nuclear explosions on Mars might create craters as reservoirs, melt ice to form water, and release CO2 to thicken the atmosphere for longer liquid water.

Explore single ols regression in Excel, interpreting the equation y = a + b x, including the slope, intercept, R-squared, residuals, and the concepts of homoscedasticity and heteroscedasticity.

Perform an ordinary least squares regression in Excel with sales as the dependent variable and marketing spend (in thousands of euros) as the independent variable, using a scatterplot and trendline.

Perform a single ols regression in Microsoft Excel to practice data analysis techniques and strengthen skills for the Excel Data Analysis Expert accreditation.

Explore non linear regression in Excel by comparing exponential, logarithmic, and power trendlines to a linear fit, and interpret R-squared to reveal diminishing returns in levels versus satisfaction.

Explore the theory of multiple OLS regression and learn how Excel calculates it to isolate the pure effects of experience and training on mistakes, guided by R squared.

Explore the rules of multiple ols regression, including parametric scales for y and preferably x, avoiding high-cardinality nominals and multicollinearity, with Excel-based calculations in part 3.

Perform a practical multiple OLS regression in Excel using the data analysis toolpak, check multicollinearity with correlations, and interpret R-squared, adjusted R-squared, ANOVA significance, and coefficients.

Perform a multiple OLS regression in Excel to analyze how drink sales (in thousands) depend on the number of stands, ticket price, and concert duration, assess model fit and significance.