Data Analytics in Microsoft Excel (A Complete Course)

Learn to import and prepare data in Excel using get and transform data, including text, csv, web data, and data from a picture, with loading and refreshing.

Identify and fix missing data in Excel by converting to a table, locating blank cells, diagnosing if errors are from data collection or calculation, and correcting or removing affected rows.

Explore data validation in Excel to prevent errors: enforce patterns like EMP followed by four digits, date formats, and salary values, with input messages and error alerts.

Learn to clean data in Excel with text functions like trim, clean, substitute, replace, concat, left, and right to standardize customer records, fix IDs, and format currency and phone numbers.

Format and present data effectively in Excel by creating tables, correcting number formats, using text to column and transpose, and grouping data for a clean, navigable presentation.

Clean and prepare a real-world sales data set in Excel by applying data cleaning techniques to correct order IDs, dates, customer IDs, and other fields.

Explore data transformation in Excel using the Power Query editor, launching Power Query and navigating the home, transform, add column, and view menus.

Master data preparation in Microsoft Excel by using home tab tools to clean, organize, and analyze datasets for actionable data analytics.

Master data transformation techniques using the transform tab in Microsoft Excel as part of a complete data analytics course.

Explore Power Query's add column tools to create new columns from patterns, formulas, and conditions, including column from example, custom column, conditional column, index, and invoke custom function.

Explore the view tab icons in Power Query to control query settings, the formula bar, monospace, whitespace visibility, column quality, distribution, and query dependency.

Master advanced data cleaning and transformation in Power Query using messy CSV data, including correcting dates, times, and text and handling nulls in Excel.

Apply data cleaning and transformation techniques to a product sales dataset to create time-based metrics, using the module's project and the edited folder to sharpen your skills.

Learn how formulas and functions power data analysis in Excel, distinguish between formulas and functions, and navigate the function library to clean, transform, and analyze data.

Master key functions for data analysis in Microsoft Excel to enhance your data analytics workflow. Apply practical techniques from this complete course to transform data into insights using Excel.

Master text functions for data cleaning in Excel, including trim, upper, lower, proper, concatenate, left, right, mid, len, substitute, replace, text split, and date formatting.

Explore date and time functions in Excel, including now, today, year, month, day, weekday, and date value, and apply network days to measure working days between dates.

Learn to handle and troubleshoot Excel errors using iferror, iserror, isna, isnumber, isblank, islogical, and the error type function. Diagnose NA, div/0, and other errors with practical, example-driven guidance.

Explore data analysis in Excel using formulas and functions to compute total sales, identify top products, and compare packaging sizes with sum, unique, and sum if functions.

Learn to analyze sales data in Excel using formula and function, including unique and sumif, to identify the branch location with the highest total sales.

Learn how data modeling in Excel uses Power Pivot to create functional relationships between tables such as customers, products, orders, and payments, enabling integrated data analysis.

Explore Power Pivot basics in Excel: activate the Power Pivot add-in, access the data model, create measures and KPIs, and manage relationships through the Power Pivot interface.

Import data from CSV, Excel, or text into Power Pivot, build a data model, and create one-to-many relationships across tables, using diagram and data views for PivotTable analysis.

Connect the Power Pivot data model to PivotTable in Excel to enable cross-table analysis and create PivotTables from the model.

Explore how to use power pivot to build a data model, create pivot tables and pivot charts, and analyze revenue and sales across products, customers, and order status by period.

Create and use measures and KPIs in Excel with Power Pivot to track revenue, sales quantity, and orders, and set KPI targets with color-coded status.

Explore automating analysis with Power Query, Power Pivot, pivot tables, and pivot charts, using DAX and bridge tables to connect product, sales, and region data for insights.

Analyze customer behavior to identify revenue contributors and repeat customers, compare average order size by segment, and calculate churn rate using a Power Pivot data model and Power Query.

Master pivot tables in Excel to analyze data by dragging fields into values, rows, columns, and filters, turning raw data into insightful summaries.

Learn to create and analyze pivot tables in Microsoft Excel to identify top products, branches, and channels, and to track sales trends, returns, and payment methods.

Explore how to customize pivot table appearance with the design tab, including selecting styles, creating pivot table styles, managing row headers and banded rows, subtotals, grand totals, and report formats.

Learn to create slicers and timelines to filter pivot table data, connect multiple slicers to pivot tables, and control which fields they affect.

Explore creating and editing calculated fields in pivot tables to compute percentages, totals, and custom metrics within Excel, using show value as, calculated field, and solve order options.

Explore how pivot charts connect to pivot tables, create and customize charts, switch chart types, use slicers, and resolve limitations by moving data outside the pivot table.

Analyze sales data by building a data model with Power Pivot, importing tables, establishing relationships, creating pivot tables and pivot charts, and cleaning data with Power Query.

Learn to create effective visuals in Excel by building charts from ranges or pivot tables, mastering data visualization, and designing dashboards to communicate insights clearly.

Create and customize pivot charts and a variety of bar, line, and pie charts in Excel, refine dashboards with data labels, formatting, and storytelling.

Learn to create and customize combo charts in Excel by combining column and line charts, adding data series, managing axis priority, and switching chart types for product versus location.

Learn to apply conditional formatting to pivot tables for visual insights using data bars, color scales, and icon sets, with rules like greater than to highlight high values.

Create dynamic charts in Excel using tables that auto expand and named ranges for locations and values. Explore pivot table and named ranges to keep charts updated as data grows.

Visualize trends in Excel with sparklines by inserting line or column sparklines, selecting data ranges, and editing styles to highlight highs and lows for dashboards.

Learn to visualize company sales performance in Excel by building pivot charts from a prepared pivot table, reuse the same data across modules, and create visuals for a dashboard.

Learn to create an Excel dashboard that summarizes analysis on a single page with metrics and KPIs. Explore layout and color choices, and how filtering enhances insights.

Design an effective Excel dashboard by configuring a distraction-free 16:9 layout, creating a background with shapes and shadows, and a heading such as Cardinal Coca Cola sales dashboard.

Create an Excel dashboard with pivot tables and pivot charts to answer key questions on total sales, top products, and regional performance using slicers.

Learn to arrange pivot charts and visuals on an Excel dashboard by cutting, pasting, aligning, formatting, and adding descriptive text boxes and data labels.

Apply a dominant color with complementary shades and a restrained two-color palette to create simple, readable dashboards with focused visuals and minimal distractions.

Learn how to present insight on a dashboard by interpreting analysis findings, crafting concise messages, and placing insights on the dashboard alongside visuals for clear business context.

Build a sales performance dashboard by leveraging a pivot table and pivot chart, replicate instructor examples, and design a report-ready dashboard in Excel with Word integration for executive reporting.

Learn to create efficient reports and dashboards that convey findings to stakeholders, using structured reports that back up analysis and guide decision making.

Outline the complete report blueprint, from front page to findings and conclusions, including the problem statement, company overview, executive summary, and recommendations.

Draft a first report from the dashboard, then refine into a second and final version, presenting findings and recommendations from dashboard insights and pivot-table questions.

Learn to create dynamic links between Excel pivot tables and Word reports. Use paste options like link and keep source formatting to ensure changes in Excel reflect in Word dashboards.

Finalize your report in Excel by expanding charts and refining headers, footers, and backgrounds using the sales report template, then present insights and recommendations clearly.

Prepare a sales performance report by leveraging your dashboard, using the provided template or your own structure, and include the relevant graphs to answer the objective.

Activate and use the data analysis tool pack in Excel to generate descriptive statistics, then explore diagnostic and predictive analytics with pivot tables, dashboards, and reports.

Learn to perform regression analysis in Excel to quantify how quantity and price drive total sales, using the data analysis tool pack and interpreting R squared and F statistics.

Explore time series forecasting in Excel by transforming quarterly sales data, building pivot tables, and using Forecast.ETS or forecast sheet to predict future quarterly sales.

Explore what-if analysis in Excel, mastering goal seek, data tables, and solver add-in to model revenue, costs, and profit scenarios like Coca-Cola, and optimize constraints.

Learn to conduct a forecasting analysis in Excel with regression, goal seek, data tables, and solver. Use real GDP as the Y variable with four X variables and interpret results.