Lesson 1Linking and exporting visuals: copying charts as images vs linking to source, exporting tables for Word and PowerPointShare visuals and tables well across documents. You will compare copying charts as fixed images versus linked items, export tables for Word and PowerPoint, and pick methods that balance quality, file size, and update needs for local office use.
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 departmentsBuild a real sample dataset that shows office operations. You will look into typical activity levels, define at least ten different activities, assign departments and units steadily, and avoid errors that could mess up later analysis.
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 validation and formatting so entries are correct, steady, and easy to check. You will use dropdown lists, block wrong values, standardise numbers, currency, and time, and mark unusual items with conditional formatting 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 that turn raw entries into useful metrics. You will calculate totals and averages per activity, work out cost per hour, and group results by department to help with workload, efficiency, and cost checks across the workbook.
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, color choices, and accessibility considerationsImprove chart formatting so insights are clear and reachable right away. You will adjust titles, axes, labels, legends, and colours, add data labels where useful, and think about accessibility, including contrast and non-colour signs.
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 formulas and visuals stronger. You will change ranges to Tables, use structured references, and set named ranges that simplify formulas, charts, and links across the workbook for reliable work.
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 tablesApply main aggregate formulas to check your dataset. You will use SUM, SUMIF, COUNT, COUNTIF, AVERAGE, and AVERAGEIF, then mix them with structured references in Tables to keep calculations clear and strong against data changes.
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 messageDesign charts that clearly share key findings. You will build column, bar, pie, and combo charts to compare quantity and cost, match chart types to messages, and prepare visuals that help decisions without confusing the audience.
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)Define a clean Data sheet setup with needed and extra fields, ensuring steadiness for analysis. You will plan column order, data types, and notes so later formulas, PivotTables, and charts stay reliable and easy to keep up.
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 summarize Quantity and Cost by Activity and Department, grouping and refreshing dataUse PivotTables to sum up big datasets fast. You will build PivotTables that show quantity and cost by activity and department, apply grouping and filters, refresh with new data, and format for clear, repeatable reports.
Creating a PivotTable from the dataArranging rows, columns, and valuesSummarizing quantity and cost fieldsGrouping, filtering, and slicing dataRefreshing and preserving Pivot layouts
