Lesson 1Linkin' an' exportin' visuals: copyin' charts as images vs linkin' to source, exportin' tables fi Word an' PowerPointShare visuals an' tables good across documents. Yuh guh compare copyin' charts as static images versus linked objects, export tables fi Word an' PowerPoint, an' choose methods dat balance fidelity, file size, an' update needs.
Copying charts as static imagesPasting charts linked to the sourceExporting tables to Word documentsUsing Excel content in PowerPointManaging update behavior for linksLesson 2Populatin' realistic activity data: researchin' typical office volumes, creatin' 10+ varied activities, an' assignin' consistent units an' departmentsBuild a realistic sample dataset dat reflect office operations. Yuh guh research typical activity volumes, define at least ten varied activities, assign departments an' units consistent, an' 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 an' formatin': usin' dropdown lists, number formats, currency, time formats, an' conditional formatin' fi outliersConfigure validation an' formatin' so entries accurate, consistent, an' easy to scan. Yuh guh apply dropdown lists, restrict invalid values, standardize numbers, currency, an' time, an' highlight outliers wid conditional formatin' rules.
Creating dropdown lists with lists or tablesValidating numbers, dates, and textStandardizing number and currency formatsApplying time and duration formatsConditional formatting for outliersLesson 4Calculatin' derived metrics: total quantity per activity, total cost per activity, average time per activity, cost per hour, an' per-department totalsCreate formulas dat transform raw entries into actionable metrics. Yuh guh calculate totals an' averages per activity, derive cost per hour, an' aggregate results by department to support workload, efficiency, an' cost analysis across di workbook.
Total quantity and cost per activityAverage time per activity formulasCalculating cost per hour metricsPer-department totals and subtotalsChecking results for calculation errorsLesson 5Formatin' charts fi clarity: titles, axis labels, data labels, legends, colour choices, an' accessibility considerationsRefine chart formatin' so insights immediately clear an' accessible. Yuh guh adjust titles, axes, labels, legends, an' colours, apply data labels where helpful, an' consider accessibility, includin' contrast an' non-colour cues.
Writing clear, focused chart titlesConfiguring axes and number scalesUsing legends and data labels wiselyChoosing color palettes and contrastDesigning charts for accessibilityLesson 6Usin' Excel Tables an' named ranges: convertin' ranges to Tables, benefits fi formulas an' chartin', creatin' an' usin' named rangesLeverage Excel Tables an' named ranges to make formulas an' visuals more robust. Yuh guh convert ranges to Tables, use structured references, an' define named ranges dat simplify formulas, charts, an' links across di 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 fi aggregates: SUM, SUMIF, COUNT, COUNTIF, AVERAGE, AVERAGEIF an' usin' structured references wid Excel tablesApply core aggregate formulas to analyse yuh dataset. Yuh guh use SUM, SUMIF, COUNT, COUNTIF, AVERAGE, an' AVERAGEIF, den combine dem wid structured references in Tables to keep calculations readable an' 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 8Creatin' charts: constructin' column, bar, pie, an' combo charts to compare Quantity an' Cost; choosin' appropriate chart type fi di messageDesign charts dat clearly communicate key findings. Yuh guh build column, bar, pie, an' combo charts to compare quantity an' cost, match chart types to messages, an' prepare visuals dat support decisions widout misleading di audience.
Choosing the right chart typeBuilding column and bar chartsCreating pie and donut chartsDesigning combo charts with two axesAvoiding misleading visual choicesLesson 9Designin' di Data sheet: required columns (Activity, Department, Quantity, Average Time (hours), Cost (USD)) an' optional columns (Date, Activity Type, Priority, Notes)Define a clean Data sheet layout wid essential an' optional fields, ensurin' consistency fi analysis. Yuh guh plan column order, data types, an' documentation so later formulas, PivotTables, an' charts remain reliable an' easy to maintain.
Choosing required business fieldsAdding optional context columnsPlanning column order and groupingDocumenting field purpose and unitsDesigning for future data growthLesson 10PivotTables fi summaries: creatin' a PivotTable to summarize Quantity an' Cost by Activity an' Department, groupin' an' refreshin' dataUse PivotTables to summarize large datasets quickly. Yuh guh build PivotTables dat show quantity an' cost by activity an' department, apply grouping an' filters, refresh wid new data, an' format layouts fi 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
