Microsoft Excel: Master Data Analysis, Cleaning & Dashboards

Identify and remove duplicates in Excel using the Remove Duplicates dialog, selecting columns to check (such as email addresses) and optionally highlight duplicates with conditional formatting.

Identify missing data in Excel by highlighting blanks with conditional formatting, then delete rows or fill blanks with defaults using find and replace or the average formula.

Standardize data formats in Excel to clean dates, numbers, and text for reliable analysis. Apply trimming, casing, and left, right, and mid extractions to produce clean, analysis-ready datasets.

Learn Excel text manipulation using concatenate to combine names, left and right to extract start and end characters, and mid to capture middle text for data cleaning and dashboards.

Master data cleaning in Excel by applying trim to remove extra spaces, clean to strip non-printable characters, and substitute to replace text, improving data readiness for analysis and dashboards.

Master sorting and filtering in Excel to organize large data sets, including sorting numbers, text, and dates in ascending or descending order, and applying multiple filters with clear criteria.

Master conditional formatting in Excel to highlight key data, apply color scales and icon sets, show data bars, and manage or clear rules for cleaner, more insightful dashboards.

Master four essential Excel functions—sum, average, count, and counta—to analyze data quickly by summing ranges, calculating averages, counting numbers, and counting non-empty cells.

Explore Excel's logical functions, if, and, or, to test conditions, return values for true or false, and nest or combine for smarter data analysis.

Explore Vlookup, Hlookup, and Xlookup in Excel to quickly find data in tables, understand vertical and horizontal lookups, and return exact or approximate results with clear syntax.

Learn to create and format pivot tables in Excel to analyze large data sets by region and product, using from scratch setup, filters, styles, and custom number formats.

Group and summarize data in Excel using pivot tables to analyze sales by date, salespersons, and products, with options to total, average, or other summaries.

Learn to create calculated fields and calculated items in Excel pivot tables to perform custom calculations, like discounted sales. Build pivot tables from sales data to analyze totals across products.

Explore Excel’s what-if analysis tools—goal seek, data tables, and scenario manager—to solve problems, forecast budgets, and test strategies by varying inputs and seeing outcomes.

Create bar, line, and pie charts in Excel to visualize sales data and trends; organize data with labeled rows and columns, then customize with titles and colors.

Master customizing Excel charts by styling, layout, and data labels, adjusting titles, axis labels, and colors, and save your preferred designs as templates for future use.

Choose the right chart for your data in Excel by understanding its purpose and type, then use bar, column, line, pie, donut, or scatter charts to reveal trends.

Create combo charts in Excel by combining a line and column chart, display monthly sales with profit percentages on a secondary axis, and customize titles and axes.

Link charts to dynamic data by turning data into tables so visuals auto-update with new rows; use offset-based dynamic named ranges for extra control.

Master descriptive statistics by calculating the mean, median, and mode in Excel to summarize and understand data efficiently.

Master variance and standard deviation as measures of data spread, with Excel steps for population and sample data. Learn to compute the mean, squared differences, and interpret the results.

Learn to enable Analysis ToolPak in Excel and perform descriptive statistics, regression, histograms, and t-tests to analyze data sets.

Explore Power Query, Excel's data transformation tool that connects, cleans, and shapes data without writing complex formulas, import csv files, apply transforms, and load results back to Excel for analysis.

Transform and clean data in Excel to speed analysis by trimming spaces, removing duplicates, reshaping with text to columns, and using Power Query, pivot tables, and slicers.

Discover how to merge columns into a full name using text join and concatenate, and how to append data from multiple sheets into a single table with Power Query.

Learn to create data models in Excel by connecting multiple tables, building relationships, and using pivot tables to analyze data across sales, products, and customers.

Explore relationships between tables in Excel using a data model and customer ID to connect data. Leverage pivot tables and Power Pivot for deeper analysis.

Learn to record and run macros in Excel to automate repetitive tasks, save workbooks as macro-enabled, and use the developer tab to assign macros to buttons.

Learn to edit Excel macros with VBA, access the Visual Basic editor, and modify code in modules to format ranges, apply bold fonts and colors, then test and save.

Explore essential Excel keyboard shortcuts that speed up navigation, data selection, formatting, and formula work, including shortcuts for bold, italics, format cells, sums, undo/redo, saving, and filtering.

Customize the quick access toolbar to streamline Excel workflows by adding formula tools and print options, dragging to reorder, and exporting settings for cross-device use.