Lesson 1Linking and exporting visuals: copying charts as images vs linking to source, exporting tables for Word and PowerPointShare visuals and tables good across documents. You go compare copying charts as static images versus linked objects, export tables for Word and PowerPoint, and choose methods dat balance fidelity, file size, and update needs.
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 realistic sample dataset dat reflect office operations. You go research typical activity volumes, define at least ten varied activities, assign departments and units consistent, and avoid contradictions dat could distort 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 outliersConfigure validation and formatting so entries accurate, consistent, and easy to scan. You go apply dropdown lists, restrict invalid values, standardise numbers, currency, and time, and highlight outliers wid 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 totalsCreate formulas dat transform raw entries into actionable metrics. You go calculate totals and averages per activity, derive cost per hour, and aggregate results by department to support workload, efficiency, and cost analysis across de 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 considerationsRefine chart formatting so insights immediately clear and accessible. You go adjust titles, axes, labels, legends, and colours, apply data labels where helpful, and consider accessibility, including contrast and non-colour cues.
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 rangesLeverage Excel Tables and named ranges to make formulas and visuals more robust. You go convert ranges to Tables, use structured references, and define named ranges dat simplify formulas, charts, and links across de 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 tablesApply core aggregate formulas to analyse your dataset. You go use SUM, SUMIF, COUNT, COUNTIF, AVERAGE, and AVERAGEIF, den combine dem wid structured references in Tables to keep calculations readable and resilient to 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 dat clearly communicate key findings. You go build column, bar, pie, and combo charts to compare quantity and cost, match chart types to messages, and prepare visuals dat support decisions without misleading de 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 clean Data sheet layout wid essential and optional fields, ensuring consistency for analysis. You go plan column order, data types, and documentation so later formulas, PivotTables, and charts remain reliable and easy to maintain.
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 summarise large datasets quick. You go build PivotTables dat show quantity and cost by activity and department, apply grouping and filters, refresh wid new data, and format layouts for clear, repeatable reporting views.
Creating a PivotTable from the dataArranging rows, columns, and valuesSummarizing quantity and cost fieldsGrouping, filtering, and slicing dataRefreshing and preserving Pivot layouts
