Lesson 1Basic data validation and cleaning: removing duplicates, TRIM, CLEAN, handling blanks and inconsistent category namesFocus on cleaning raw sales exports before analysis. You will remove duplicates, fix extra spaces and nonprintable characters, handle blanks, and standardize inconsistent category or channel names using simple Excel tools and functions.
Removing duplicate sales recordsUsing TRIM to remove extra spacesUsing CLEAN to remove bad charactersFinding and handling blank cellsStandardizing category and channel namesUsing Data Validation for allowed valuesLesson 2Creating clear charts: column, line, and stacked column charts for monthly revenue by channel or category, choosing axes and labelsCreate clear, readable charts for sales trends and comparisons. You will build column, line, and stacked column charts for monthly revenue by channel or category, and choose appropriate axes, labels, and legends for BI storytelling.
Choosing the right chart typeBuilding monthly revenue column chartsCreating line charts for trendsStacked column charts by channelFormatting axes, titles, and legendsImproving readability with labelsLesson 3Key functions for BI tasks: SUM, AVERAGE, COUNTIF/COUNTIFS, SUMIF/SUMIFS, AVERAGEIF/AVERAGEIFS explained with examplesLearn how to use core Excel aggregation and conditional functions for BI. You will practice SUM, AVERAGE, COUNTIF(S), SUMIF(S), and AVERAGEIF(S) to summarize sales by product, channel, and period with practical examples.
SUM and AVERAGE for basic sales totalsCOUNT, COUNTA, COUNTBLANK in sales listsCOUNTIF and COUNTIFS for criteria countsSUMIF and SUMIFS for filtered revenueAVERAGEIF and AVERAGEIFS for KPIsCombining functions in nested formulasLesson 4Documenting calculations and sample dataset creation: writing a reproducible sample dataset (10–30 rows) and describing sample rows for submissionDocument your Excel analysis so others can repeat it. You will build a small, realistic sample sales dataset, describe each column, explain sample rows, and record key calculation steps for transparent BI submissions.
Planning a realistic sample datasetCreating 10–30 rows of sample salesDescribing each column and data typeExplaining example rows in commentsDocumenting key formulas and logicSaving a clean version for submissionLesson 5Date handling and grouping: Excel date serials, MONTH/YEAR functions, TEXT, EOMONTH, and grouping data by monthUnderstand how Excel stores and manipulates dates for sales analysis. You will work with date serials, MONTH, YEAR, TEXT, and EOMONTH, and group data by month or year in tables and PivotTables for time-based reporting.
How Excel date serial numbers workUsing MONTH and YEAR for breakdownsUsing TEXT to format dates as labelsUsing EOMONTH for period boundariesGrouping dates by month in PivotTablesHandling mixed or text date valuesLesson 6Annotating charts and exporting: adding data labels, trendlines, chart titles, legends, copying charts to Word/PowerPointEnhance charts with annotations and export them for reports. You will add data labels, trendlines, titles, and legends, then copy charts into Word or PowerPoint while preserving clarity and professional formatting.
Adding and formatting data labelsAdding trendlines for sales trendsEditing chart titles and subtitlesManaging legends and series colorsCopying charts to Word and PowerPointChoosing image vs linked chart optionsLesson 7Simple pivot table use: building a pivot for monthly revenue by channel or category, using filters and value summarizationUse PivotTables to summarize sales without complex formulas. You will build pivots for monthly revenue by channel or category, apply filters and slicers, change value summaries, and format results for BI dashboards.
Creating a PivotTable from sales dataArranging rows, columns, and valuesSummarizing revenue by month and channelUsing filters, slicers, and timelinesChanging value field settings and totalsFormatting PivotTables for readabilityLesson 8Calculated columns and formula design: Revenue/Units sold, margin estimates, and handling divide-by-zero errorsDesign reliable calculated columns for sales metrics. You will build formulas for Revenue per unit, margin estimates, and percentage margins, while safely handling divide-by-zero and missing data using IF, IFERROR, and structured references.
Creating Revenue and Units Sold metricsBuilding gross margin and margin percentUsing IF to avoid divide-by-zero errorsUsing IFERROR for cleaner outputsUsing absolute and relative referencesUsing structured references in tablesLesson 9Designing a clean sales table: required columns (Date, Product category, Channel, Units sold, Revenue), data types, and sample row examplesDesign a clean, analysis-ready sales table structure. You will define required columns, choose correct data types, create example rows, and ensure consistent formats so later formulas, charts, and pivots work reliably.
Defining required sales table columnsSetting correct data types and formatsCreating consistent product categoriesDesigning example rows for testingAvoiding merged cells and gapsAdding a unique transaction ID fieldLesson 10Table formatting and filtering: creating Excel tables, headers, banded rows, freeze panes, and applying filtersTurn raw ranges into well-structured Excel tables for analysis. You will format headers, apply banded rows, use Freeze Panes, and apply filters and sort options to quickly explore sales by date, product, and channel.
Converting ranges into Excel TablesFormatting headers and banded rowsUsing Freeze Panes for large tablesSorting sales data by key fieldsFiltering by date, product, or channelUsing slicers with Excel Tables
