Mastering Data Analysis in Excel

Master the sort and filter tools in Excel to organize data by values and cell color, and filter by client ID, Washington state, or orders over 1000 dollars.

Learn how the text to columns tool splits a single column into multiple columns for data preparation in Excel, using fixed width or delimited options.

Remove duplicates in Excel to clean data for accurate analysis, using the data ribbon, selecting order ID, and validating unique rows before saving.

Apply the data validation tool from the data ribbon to limit cell inputs, using a predefined list of survey answers and input and error messages to guide users.

Master find and replace, go to, and go to special to locate values and replace them, including find all and replace all. See tv to television replacements and review results.

Use the data entry form tool in Excel to add a new customer, filling fields like customer ID, age, gender, education, marital status, and pets, creating a new row.

Master logical functions in Excel with IF, AND, OR to create new columns, categorize ages into groups, and assess reviews using nested IF and OR logic.

Explore advanced formulas in excel by using the subtotal function to calculate filtered totals for 2016, and compare its results with sum and count.

Explore how to extract characters with left and right and merge data using concatenate. Use len to count characters and apply upper, lower, and proper to format text.

Learn to work with dates in excel using YEAR, MONTH, DAY, EOMONTH, and DATE. Extract year, month, and day from orders, and calculate last days or offset dates.

Learn to compute totals in Excel using sumif, sumifs, countif, and countifs, including 2016 revenue, cost of product, operational expenses, California orders, and nested if.

Learn how to calculate averages with Excel's average, averageif, and averageifs functions, using ranges, criteria, and filters to analyze age by gender and location.

Explore how to use Excel's statistical functions—max, min, average, median, mode, var, stddev, and correl—to analyze data like product cost and its relationship with revenue.

Explore Excel's financial functions—PMT, PV, RATE, NPER, NPV, and IRR—applied to loans and investment cash flows to calculate payments, present value, and returns.

Master the pivot table in Excel to analyze data and build reports by year, gender, and product category. Learn to summarize revenue, expenses, and orders with dynamic filters.

Learn to build year-by-year pivot table reports showing orders, revenue, operating expenses, and net profit, and create calculated fields for net profit and net fee with averages and shares.

Group data in pivot tables to create a frequency distribution of product costs by ranges, count orders, and quickly ungroup or regroup as needed.

Learn to use getpivotdata to base net profit growth calculations on pivot table values, enabling accurate results as layout changes and avoiding copy-paste errors.

Apply what-if analysis with goal seek to perform sensitivity analysis and determine the required average fee to reach target net profit.

Explore the data table tool in Excel to perform sensitivity analysis and compare loan options by duration and interest rate, calculating monthly payments and total repayment for informed decision making.

Explore how to use Excel's scenario manager to create base, low, and high scenarios, switch between them, and view a summary of monthly payment and total repayment for sensitivity analysis.

Audit Excel formulas using formula auditing tools to view formulas, trace precedents and dependents, and visualize arrows showing which cells influence or are influenced by a formula.

Master Vlookup with exact match to merge education and marital status into orders in Excel, then use pivot tables to analyze orders and net profit by client characteristics.

Master index and match to replace Vlookup, enabling left and right lookups, two matching values, and faster performance on large data sets.

Learn how to accelerate vlookup on large data sets by using approximate match with a sorted range and an if function, achieving faster merges and accurate results.

Learn how to format data as a table in Excel to enable filters, totals, and automatic formula expansion, and use tables for pivot table analysis and alternatives to VLOOKUP.

Learn to use the data model to create pivot table reports from multiple formatted tables without merging data. Build relationships between orders and customers to analyze profits across datasets.

Create a slicer to filter pivot table data by year and view orders and net profit by age across years in an interactive dashboard.

Master conditional formatting in Excel to visualize data and build dashboards, highlighting results with pivot tables, bars, color scales, icons, and rules such as above average and greater than 20%.

Learn how to use sparklines as mini charts in Excel, creating line, column, and win/lost sparklines from a pivot table to visualize net profit trends for management dashboards.

Learn to create column and bar charts in Excel, build a pivot table, customize chart data, axes, colors, and labels, and explore chart styles for effective data visualizations.

Create and customize a line chart from a pivot table to visualize yearly order counts, adjusting axis, markers, and line styles before moving to a pie chart.

Explore how to create and customize pie and doughnut charts in Excel, use pivot tables to show reviews as shares of orders, and combine visuals for a speedometer chart.

Create and customize a stacked area chart from a pivot table to compare revenue and operational expenses by state, adjust formats, colors, and gridlines, and explore other area chart types.

Master combo charts in Excel by merging a net profit column chart with a ROE line chart, using a pivot table and axis formatting to compare yearly performance.

Master the speedometer gauge in Excel by building a combo chart from pie and doughnut charts, set performance ranges, configure the needle, and visualize 83% performance with color-coded segments.

Create an interactive Excel dashboard by adding slicers to pivot charts, linking charts to slicers for year and state, and display net profit versus planned results for management.

Mastering data analysis in Excel shows how to prepare data, analyze it, create reports and interactive dashboards, and present management with actionable recommendations to boost profit and service quality.

Create a scatter plot in Excel to explore the relationship between property area and prices, revealing a positive correlation and adding a trend line.

activate the data analysis ToolPak add-in from Excel’s standard package for advanced statistical analysis; access it via the data ribbon, revealing a suite of financial and statistical tools.

Use the data analysis toolpak to generate descriptive statistics for property prices in Excel. Retrieve measures like mean, median, mode, standard deviation, variance, range, and more with one click.

Learn to create a histogram in the data analysis toolpak to visualize property price distributions with bins, interpret descriptive statistics, and generate charts.

Explore correlation in Excel by examining how area and price co-vary, from -1 to 1, and learn to compute it using the correl function and to build a correlation matrix.

Master linear regression in Excel to predict price from property area using a regression model, correlation, and trendline. Learn how to run regression via data analysis.

Explore how to interpret regression output in Excel, build a predictive property price model from area, and assess reliability using r square, adjusted r square, f and p values.

Build a multiple linear regression model with several explanatory variables, assess p-values and R-squared, exclude non-significant factors like distance from city municipality, and derive predictive equations.

Create and manage navigable hyperlinks in Excel to connect cells, worksheets, and external files, enabling quick access between dashboards, data sheets, and cover pages.

Protect sheet and protect workbook in Excel by applying a password, locking and unlocking cells, and selecting locked or unlocked ranges to control edits.