Lesson 1Linking and exporting visuals: copying charts as images vs linking to source, exporting tables for Word and PowerPointShare your charts and tables properly across files. You'll compare pasting charts as fixed pictures against linked ones, export tables for Word and PowerPoint, and pick ways that keep quality, manage file size, and update easily when needed.
Copying charts as static imagesPasting charts linked to the sourceExporting tables to Word documentsUsing Excel content in PowerPointManaging update behavior for linksLesson 2Populating realistic activity data: researching typical office volumes, creating 10+ varied activities, and assigning consistent units and departmentsMake a proper sample dataset that shows real office work. You'll look up usual activity amounts, set up over ten different activities, give them steady departments and units, and dodge mix-ups that mess up later checks.
Researching realistic office workloadsDefining a catalog of activitiesAssigning departments and ownershipChoosing consistent units and scalesSpot-checking data for realismLesson 3Data validation and formatting: using dropdown lists, number formats, currency, time formats, and conditional formatting for outliersSet up checks and styles so data goes in right, stays uniform, and easy to read. You'll use dropdowns, block wrong entries, standardise numbers, money, and times, and flag odd ones with colour rules.
Creating dropdown lists with lists or tablesValidating numbers, dates, and textStandardizing number and currency formatsApplying time and duration formatsConditional formatting for outliersLesson 4Calculating derived metrics: total quantity per activity, total cost per activity, average time per activity, cost per hour, and per-department totalsMake formulas to turn basic data into useful figures. You'll sum up and average per activity, work out cost per hour, and group by department to check workload, speed, and expenses across the file.
Total quantity and cost per activityAverage time per activity formulasCalculating cost per hour metricsPer-department totals and subtotalsChecking results for calculation errorsLesson 5Formatting charts for clarity: titles, axis labels, data labels, legends, colour choices, and accessibility considerationsPolish your charts so the message jumps out clear and usable for all. Adjust titles, axes, labels, keys, and colours, add data tags where needed, and think about access like good contrast and non-colour hints.
Writing clear, focused chart titlesConfiguring axes and number scalesUsing legends and data labels wiselyChoosing color palettes and contrastDesigning charts for accessibilityLesson 6Using Excel Tables and named ranges: converting ranges to Tables, benefits for formulas and charting, creating and using named rangesUse Excel Tables and named ranges to make your work stronger and easier. Turn ranges into Tables, use smart references, and name ranges to simplify formulas, charts, and connections throughout the workbook.
Converting ranges into Excel TablesUsing structured references in formulasAuto-expanding Tables for new dataCreating and managing named rangesUsing names in charts and summariesLesson 7Formulas for aggregates: SUM, SUMIF, COUNT, COUNTIF, AVERAGE, AVERAGEIF and using structured references with Excel tablesPut key summary formulas to work on your data. Use SUM, SUMIF, COUNT, COUNTIF, AVERAGE, AVERAGEIF, pair with Table references to keep sums clear and steady when data shifts.
Using SUM and COUNT on raw dataApplying SUMIF and COUNTIF criteriaAVERAGE and AVERAGEIF for durationsCombining criteria with table fieldsAuditing aggregate formulas for errorsLesson 8Creating charts: constructing column, bar, pie, and combo charts to compare Quantity and Cost; choosing appropriate chart type for the messageBuild charts that show main points sharp and true. Make column, bar, pie, combo types to match quantity and cost, pick the right type for your point, and avoid confusing viewers.
Choosing the right chart typeBuilding column and bar chartsCreating pie and donut chartsDesigning combo charts with two axesAvoiding misleading visual choicesLesson 9Designing the Data sheet: required columns (Activity, Department, Quantity, Average Time (hours), Cost (USD)) and optional columns (Date, Activity Type, Priority, Notes)Plan a neat Data sheet with must-have and extra fields for steady analysis. Sort columns right, set data types, note everything so formulas, PivotTables, and charts stay solid and simple to update.
Choosing required business fieldsAdding optional context columnsPlanning column order and groupingDocumenting field purpose and unitsDesigning for future data growthLesson 10PivotTables for summaries: creating a PivotTable to summarise Quantity and Cost by Activity and Department, grouping and refreshing dataUse PivotTables to crunch big data fast. Build ones showing quantity and cost by activity and department, group and filter, refresh with fresh data, format for clear reports you can reuse.
Creating a PivotTable from the dataArranging rows, columns, and valuesSummarizing quantity and cost fieldsGrouping, filtering, and slicing dataRefreshing and preserving Pivot layouts
