Introduction to Data Analytics with Microsoft Excel

Learn to view and enter data in Excel, use autofill for months and quarters, edit and delete cells, copy and paste as values, move data with the crosshair drag.

Learn how to format cells in Excel, apply alignment, fonts, colors, and merge cells, and understand data types—text, numbers, dates, and formulas—to improve readability and analysis.

Learn to write Excel formulas starting with equals, use cell references like B3 and B4, and copy them across with the fill handle to keep results dynamic.

Learn fast data calculations in Excel with Autosum and Alt+Equal for sum and average, and master XLOOKUP for retrieving names and salaries from large datasets.

Master cell references and absolute vs relative in Excel formulas by using sums, ranges, and copy-paste techniques to compute revenues and headcount growth.

Use XLOOKUP to find employee IDs and departments from a name, then fix a growing investment formula by correcting cell references and locking with F4.

Master data quality in Excel by cleaning data, removing empty rows and columns, ensuring single-line headers, and standardizing formats to enable reliable sorting, filtering, and pivot table use.

Learn to import csv data into Excel using file open or from text csv, with comma delimiters and basic data loading. Link the workbook to the csv for updates.

Learn to clean data in Excel by using the data tab to filter distinct values and remove duplicates, understanding exact-match criteria across all columns.

Identify data attributes and duplicates by sorting and using if and and formulas on key fields, such as the ID field, then apply unique and count functions to summarize departments.

Learn to clean messy excel data by standardizing names, emails, and salary formats using find and replace and formulas such as upper, proper, trim, and lower.

Practice using the sort and unique formulas in Excel to count 18 unique subcategories from a long list, demonstrating the array approach and the counta with unique combination.

Sort and filter data in Excel to analyze a dataset by category and cost price, using boundaries and custom sort levels to reveal item highlights by markup.

Learn to use the concatenate function to merge customer id and name with spacing and a hyphen, then tally revenue by status using sumif and static references.

Learn to handle complex or criteria in Excel by using the advanced filter, building a criteria range, and separating lines to implement and or queries on data.

Learn how to summarize data with pivot tables in Excel, including creating a pivot table, placing fields, and using slicers and filters to analyze quantity, revenue, and contract duration.

Create and customize pivot charts from pivot tables, using clustered column, line, and pie charts with secondary axes and data labels to visualize revenue, quantity, and total contract value.

Learn to create and customize Excel charts from sales by month and by country using line, clustered column, funnel, Pareto, and scatter charts, with data range and axis adjustments.

Master vertical, horizontal, and trend analysis in Excel using a fictional income statement to interpret net sales, cost of sales, gross profit, and net income, plus variance analysis.

Create an Excel waterfall chart to tell the story behind numbers, linking budgeted gross profit to actual net income via variance analysis and items like SGA, taxes, and interest.

Highlight data insights with conditional formatting in Excel, using data bars, color scales, and icon sets to spot trends and top items at a glance.

Explore database functions in Excel, such as D sum, D average, and D count, to compute totals, averages, max and min by using separate criteria that update dynamically.

Learn to evade formula errors in Excel by using formula text to reveal faulty formulas and the aggregate function to compute sum, average, median, min, and max while ignoring errors.

Create a pivot table from the item and total cost data in Excel, then insert a clustered column chart to visualize total cost by item.

Master how to use the scenario manager in Excel's what-if analysis to create, switch between, and update multiple scenarios. Learn to summarize results with scenario summary and pivot table reports.

Explore data tables in the what-if analysis section of Excel to quickly model sales scenarios by varying one or two variables, using row and column input cells.

Learn to use the goal seek function in Excel's what-if analysis to solve for a target value, adjusting inputs like loan payments, budgets, and growth rates.

Load and activate the Analysis ToolPak in Excel to access tools like ANOVA, correlation, and descriptive statistics, and run analyses on one worksheet at a time with output tables.

Learn to use the Analysis ToolPak to compute correlation and covariance between data variables, interpret the results, and understand how two variables move together in data sets.

Learn to use Excel's analysis toolpak to compute descriptive statistics, moving average, and exponential smoothing with a real sales dataset, and generate charts to visualize forecasts.

Explore the analysis toolpak to compute rank and percentile, build a salary histogram, and perform regression to relate net income to expenses, sales, and cost of goods.

Utilize the scenario manager to create cost increase and cost decrease scenarios for cost per unit, then generate a scenario summary report showing original, increased, and decreased totals.

Apply data analytics in Excel by gathering and cleaning data, using What-If analysis and the Analysis ToolPak, and analyzing and visualizing data across departments.