Excel 2021 In Practice - Ch 1 Guided Project 1-3

Excel 2021 in Practice – Chapter 1, Guided Project 1‑3

Excel 2021 is the latest desktop version of Microsoft’s spreadsheet powerhouse, and Chapter 1 of the “Excel 2021 in Practice” series introduces the first three guided projects that lay the foundation for everyday data work. Whether you are a student, a small‑business owner, or a seasoned analyst transitioning to the new interface, these projects teach core skills—data entry, formatting, basic formulas, and simple data visualisation—through realistic scenarios. By the end of Project 1‑3 you will be able to create a clean, functional workbook, perform essential calculations, and present results with clear charts, all while applying best‑practice techniques that improve accuracy and readability Small thing, real impact. Turns out it matters..

1. Introduction – Why Guided Projects Matter

Traditional Excel tutorials often list functions in isolation, leaving learners unsure how to apply them to real problems. Guided projects bridge that gap by embedding tasks in a narrative context: you are asked to track monthly sales, analyse employee attendance, or compare budget versus actual expenses. This approach mirrors the way professionals use Excel on the job, reinforcing muscle memory and encouraging critical thinking. In Chapter 1 the three projects share a common workbook, allowing you to see how data structures evolve from a simple table to a mini‑dashboard.

2. Project 1 – Building a Monthly Sales Tracker

2.1 Goal

Create a workbook that records sales for five products across twelve months, calculates totals, growth rates, and highlights outliers.

2.2 Step‑by‑Step Process

1. Set up the worksheet

   • Rename the first sheet to “Sales_Tracker”.
   • In cell A1 type Month, and in B1–F1 enter product names (e.g., Product A…Product E).
   • Fill A2–A13 with the month names (January to December).

2. Enter sample data

   • Populate the table with realistic sales figures (e.g., 4,500; 6,200). Use the Ctrl+Enter trick to paste a column of numbers quickly.

3. Apply Table formatting

   • Select the range A1:F13, press Ctrl+T, confirm My table has headers.
   • Choose a built‑in style such as Table Style Light 9. Tables automatically add filter arrows, improve formula readability, and expand when new rows are added.

4. Add calculated columns

   • In column G (header Total), enter the formula =SUM([@ [Product A]:[@[Product E]]).
   • Excel’s structured references make the formula read like natural language, reducing errors when columns are moved.

5. Insert a growth‑rate column

   • In H1 type Growth %.
   • In H2 enter =IF([@Month]="January",0,([@Total]-OFFSET([@Total],-1,0))/OFFSET([@Total],-1,0)).
   • Format the column as Percentage with one decimal place.

6. Conditional formatting for outliers

   • Highlight G2:G13, go to Home → Conditional Formatting → Data Bars and select a blue gradient.
   • Add another rule: Greater Than the 90th percentile (=PERCENTILE.INC(Table1[Total],0.9)) and set a bright fill to draw attention to peak months.

7. Create a summary chart

   • Select the Month column and the Total column, then choose Insert → Line Chart.
   • Add data labels for the highest and lowest points only (right‑click the series → Add Data Labels → Format Data Labels → Value From Cells).

2.3 Best Practices Highlighted

• Structured references keep formulas readable and automatically adjust when the table expands.
• Named ranges are unnecessary inside a table; the table name itself (e.g., Table1) is sufficient.
• Conditional formatting should be limited to a few rules to avoid performance lag on larger workbooks.

3. Project 2 – Employee Attendance Dashboard

3.1 Goal

Design a dashboard that records daily attendance for 20 staff members, flags late arrivals, and summarises monthly absenteeism.

3.2 Workflow

1. Create the raw data sheet

   • Add a new sheet named “Attendance_Raw”.
   • Columns: Date, Employee ID, Employee Name, Check‑In Time, Check‑Out Time.
   • Use Data Validation (Data → Data Validation) on Employee ID to restrict entries to a predefined list stored in a hidden sheet “Lookup”.

2. Standardise time entries

   • Format Check‑In and Check‑Out as hh:mm AM/PM.
   • Insert a helper column Hours Worked with =([@Check‑Out]-[@Check‑In])*24. Multiply by 24 to convert the time difference to decimal hours.

3. Identify late arrivals

   • Add a column Late? with the formula =IF([@Check‑In]>TIME(9,0,0),"Yes","No").
   • Apply a Cell Icon Set (green check, red exclamation) via Conditional Formatting to make the status instantly recognizable.

4. PivotTable summary

   • Insert a PivotTable on a new sheet “Attendance_Summary”.
   • Drag Employee Name to Rows, Date to Columns (grouped by month), and Late? to Values (set to Count).
   • Add Hours Worked to Values as Average to see typical daily hours per employee.

5. Slicer for interactive filtering

   • With the PivotTable selected, go to PivotTable Analyze → Insert Slicer, choose Employee Name and Month.
   • Position the slicers at the top of the sheet; they provide a quick way for managers to focus on a single employee or month.

6. Dashboard visualisation

   • Insert a Clustered Column Chart based on the Late? count per employee.
   • Add a Gauge chart (via a doughnut + pie combination) to display overall punctuality percentage: =1-GETPIVOTDATA("Count of Late?",$A$3,"Late?","Yes")/GETPIVOTDATA("Count of Late?",$A$3).

3.3 Key Learning Points

• Data validation prevents entry errors, a crucial step for maintaining data integrity.
• PivotTables provide powerful aggregation without writing complex formulas.
• Slicers turn static reports into interactive dashboards, enhancing user experience.

4. Project 3 – Budget vs. Actual Financial Comparison

4.1 Goal

Construct a comparative analysis that juxtaposes planned budget figures against actual expenditures, calculates variances, and visualises the results with a waterfall chart Worth knowing..

4.2 Detailed Execution

1. Layout the comparison table

   • In a new sheet called “Budget_Analysis”, create headers: Category, Budgeted, Actual, Variance, % Variance.
   • Populate Category with common cost items (e.g., Rent, Salaries, Utilities, Marketing, Miscellaneous).

2. Enter budget and actual numbers

   • Use realistic amounts; for illustration, budget Rent = 12,000, actual = 12,500.

3. Calculate variance

   • In D2 (Variance) type =C2-B2. Drag down.
   • In E2 (% Variance) type =IF(B2=0,0,D2/B2). Format as Percentage with one decimal place.

4. Conditional formatting for variance

   • Highlight D2:D6, set a rule: Less than 0 → green fill (under‑budget), Greater than 0 → red fill (over‑budget).
   • Add data bars to the % Variance column for quick visual scanning.

5. Create a waterfall chart

   • Select the range A1:C6, go to Insert → Waterfall Chart.
   • Manually set the Budgeted column as Start, Actual as Total, and the intermediate rows as Increase/Decrease based on the sign of the variance.
   • Enable Data Labels and format the axis to show currency.

6. Dynamic scenario analysis with a dropdown

   • In cell G1, insert a Data Validation list: Base, Optimistic, Pessimistic.
   • In a hidden column, define multiplier factors (e.g., 0.95, 1.00, 1.10).
   • Adjust the Budgeted column using =B2*VLOOKUP($G$1,$J$2:$K$4,2,FALSE). Changing the dropdown instantly updates the waterfall, illustrating how scenario planning works.

4.3 Takeaways

• Waterfall charts are ideal for visualising step‑by‑step financial changes, a feature newly refined in Excel 2021.
• Scenario analysis via dropdowns encourages “what‑if” thinking without requiring separate worksheets.
• Keeping raw data separate from calculations (using a hidden “Data” sheet) simplifies auditing and version control.

5. Scientific Explanation – How Excel Handles Calculations Under the Hood

Excel stores numbers as double‑precision floating‑point values (IEEE 754 standard). This explains why seemingly simple calculations (e.That said, 1+0. That said, , 0. When you perform arithmetic—such as the *Growth %* formula in Project 1—Excel first converts the displayed values to their binary representation, executes the operation, and then rounds the result according to the cell’s formatting. g.2) can produce tiny rounding errors.

• Use ROUND or ROUNDUP functions when exact financial cents are required.
• Avoid equality checks on floating‑point numbers; instead, test if the absolute difference is below a small tolerance (ABS(A1-B1)<1E-9).

Excel 2021 also introduces dynamic arrays, allowing formulas like =SORT(FILTER(Table1[Total],Table1[Total]>1000)) to spill results into adjacent cells automatically. While the guided projects stick to classic formulas for compatibility, you can later replace them with dynamic array equivalents for cleaner worksheets Took long enough..

6. Frequently Asked Questions

Q1: Can I protect the workbook so users can only edit the raw data sheet?
A: Yes. Use Review → Protect Sheet on each sheet, allowing edits only in the cells you specify (e.g., the data entry range). Combine this with a Workbook Password to prevent structural changes Simple, but easy to overlook. Turns out it matters..

Q2: How do I update the charts automatically when new months are added?
A: Because the data resides in an Excel Table, any new row automatically expands the chart’s source range. Ensure the chart is linked to the table columns, not a fixed range Simple as that..

Q3: What is the best way to share this workbook with colleagues who use older Excel versions?
A: Save the file as .xlsx (compatible with Excel 2010 onward). Avoid features exclusive to Excel 2021, such as LET or XLOOKUP, unless you know recipients have the same version But it adds up..

Q4: My conditional formatting slows down the workbook; what can I do?
A: Limit the applied range to the actual data (e.g., =$G$2:$G$1000 instead of whole column). Consolidate multiple rules into a single rule using Formula Is when possible.

Q5: How can I export the dashboard to a PDF while keeping the slicer selections?
A: Before printing, set the slicer to the desired view, then go to File → Export → Create PDF/XPS Document. The PDF captures the current visual state, including filtered data.

7. Conclusion – Turning Guided Practice into Real‑World Mastery

Chapter 1’s three guided projects lay a solid groundwork for everyday Excel 2021 tasks. By building a structured sales tracker, designing an interactive attendance dashboard, and visualising budget variances with a waterfall chart, you acquire a toolkit that spans data entry, calculation, analysis, and presentation. The underlying principles—using tables, structured references, PivotTables, and conditional formatting—are portable to any industry or dataset Which is the point..

Most guides skip this. Don't.

Remember that mastery comes from repetition: recreate the projects with your own data, experiment with the new dynamic array functions, and gradually replace manual steps with automated ones (e.So , Power Query for data cleansing). Because of that, g. As you progress through the subsequent chapters, you’ll see how these foundational skills integrate with advanced topics such as Power Pivot, VBA macros, and collaborative cloud features That's the whole idea..

Start today by opening a fresh workbook, naming the sheets as described, and following the step‑by‑step instructions. The moment you see the line chart rise with your sales totals or the slicer instantly filter attendance records, you’ll experience the confidence and efficiency that Excel 2021 brings to every professional’s toolkit.