Lesson 1Linking and exporting visuals: copying charts as images vs linking to source, exporting tables for Word and PowerPointShare visuals and tables smoothly across documents. Compare copying charts as static images against linked objects, export tables for Word and PowerPoint, and pick methods balancing accuracy, file size, and update requirements.
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 lifelike sample dataset mirroring office operations. Research common activity volumes, define over ten varied activities, assign departments and units consistently, and steer clear of contradictions that might skew 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 for accurate, uniform, scannable entries. Apply dropdown lists, block invalid values, standardise numbers, currency, time, and flag outliers 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 totalsCraft formulas turning raw data into useful metrics. Calculate totals and averages per activity, derive cost per hour, and group by department to aid workload, efficiency, and cost reviews 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, colour choices, and accessibility considerationsPolish chart formatting for instant clarity and access. Adjust titles, axes, labels, legends, colours, add data labels where useful, and factor in accessibility like contrast and non-colour indicators.
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 rangesHarness Excel Tables and named ranges for sturdy formulas and visuals. Convert ranges to Tables, use structured references, define named ranges simplifying formulas, charts, and links 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 tablesEmploy key aggregate formulas to dissect your dataset. Use SUM, SUMIF, COUNT, COUNTIF, AVERAGE, AVERAGEIF, pair with Table structured references for readable, adaptable calculations amid 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 clearly conveying findings. Construct column, bar, pie, combo charts comparing quantity and cost, select types fitting the message, prepare visuals aiding decisions without misleading.
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 vital and extra fields for steady analysis. Arrange column order, data types, documentation ensuring formulas, PivotTables, charts stay dependable and simple to upkeep.
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 dataEmploy PivotTables for swift dataset summaries. Create ones showing quantity and cost by activity and department, use grouping, filters, refresh with new data, 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
