Excel 2021 In Practice - Ch 1 Independent Project 1-4

Introduction

excel 2021 in practice offers a practical pathway for learners to deepen their spreadsheet skills by completing a series of independent projects. In Chapter 1, the curriculum is divided into four focused tasks that guide you from setting up a basic worksheet to building a dynamic dashboard. Each project reinforces essential techniques—data entry, formula creation, table manipulation, and visual representation—ensuring that you can apply what you learn immediately in real‑world scenarios. By the end of these exercises, you will have a portfolio of completed files that demonstrate competence in excel 2021 and readiness for more advanced topics.

Project Overview

The first chapter’s independent projects are designed to be completed sequentially, allowing you to build confidence step by step. Below is a brief overview of each task:

1. Project 1 – Setting up the worksheet – Create a clean, organized layout for data entry.
2. Project 2 – Using formulas – Apply common functions to calculate totals, averages, and conditional results.
3. Project 3 – Data analysis with tables – Convert raw data into structured tables, use sorting and filtering, and perform basic statistical analysis.
4. Project 4 – Creating a dashboard – Combine charts, slicers, and conditional formatting into a single, interactive view.

Project 1 – Setting up the worksheet

Preparation

• Open a new workbook in excel 2021.
• Save the file with a descriptive name, such as Chapter1_Project1.xlsx.

Steps

1. Create a header row (Row 1) and label columns: Date, Product, Quantity, Unit Price, Total.
2. Format the header with bold text and a background fill to make it stand out.
3. Apply column widths so all content fits comfortably; use the “AutoFit” feature for precision.
4. Freeze the top row (View → Freeze Panes → Freeze Top Row) to keep headers visible while scrolling.

Tips

• Use Table formatting (Insert → Table) to automatically expand ranges as you add rows.
• Name the table SalesData for easy reference in later formulas.

Project 2 – Using formulas

Core Functions

• SUM (=SUM(range)) for total calculations.
• AVERAGE (=AVERAGE(range)) for mean values.
• IF (=IF(condition, value_if_true, value_if_false)) for conditional logic.
• VLOOKUP (=VLOOKUP(lookup_value, table_array, col_index, FALSE)) for lookup operations.

Implementation

1. In the Total column (E2), enter = [@Quantity] * [@Unit Price] and press Enter; the formula will auto‑fill for the entire table.
2. Below the table, calculate the grand total with =SUM(Table1[Total]).
3. Create a conditional flag in a new column High Value using =IF([@Total] > 500, "Yes", "No").

Emphasis

• Bold the final grand total cell to draw attention.
• Italicize function names when first introduced to aid readability.

Project 3 – Data analysis with tables

Structuring Data

• Convert the raw dataset into a structured table (already done in Project 1).
• Add a Date column if missing, ensuring dates are recognized as actual date values (not text).

Operations

1. Sort the table by Date in ascending order (Table Tools → Sort).
2. Apply a filter on the Product column to view sales for a specific item.
3. Use PivotTable (Insert → PivotTable) to summarize total sales per product.

Statistical Insights

• Insert a calculated field in the PivotTable to compute the average order value: =Sum of Total / Count of Order ID.
• Highlight the top‑selling product with a conditional format that turns the cell green when its percentage exceeds 25 % of total sales.

Project 4 – Creating a dashboard

Components

• Chart: Insert a clustered column chart that displays Total Sales by Product.
• Slicer: Add a slicer for Date to enable dynamic filtering.
• KPIs: Show key performance indicators such as Grand Total and Average Order Value using large, formatted cells.

Assembly Steps

1. Place the column chart on a new sheet named Dashboard.
2. Insert a slicer linked to the Date field; position it beside the chart for easy access.
3. Add a text box that displays the grand total (=GETPIVOTDATA("Total",Dashboard!$A$3)).
4. Apply conditional formatting to the KPI cells: green for growth, red for decline

Project 4 – Creating a dashboard (continued)

Advanced Customization

• Link KPI cells to dynamic PivotTable data using =GETPIVOTDATA to ensure real-time updates when filters change.
• Apply data validation to slicer selections to restrict input to valid date ranges, preventing errors.
• Use form controls (e.g., scroll bars) to adjust parameters like time intervals for trend analysis.

Final Touches

• Freeze the top row of the dashboard for readability during scrolling.
• Add a title and descriptive labels to all visual elements for clarity.
• Protect the dashboard sheet with a password to prevent unintended edits while allowing slicer interactions.

Conclusion

By completing these projects, you’ve built a solid Excel-based sales tracking system that combines structured data management, powerful formulas, and interactive dashboards. The use of tables ensures scalability, while formulas automate calculations and conditional logic. PivotTables and slicers provide dynamic insights, and the dashboard consolidates critical metrics into an intuitive interface. This workflow not only streamlines data analysis but also enhances decision-making through real-time visibility. For further refinement, consider integrating Power Query for automated data imports or Power BI for advanced visualizations. Regular practice with these tools will deepen your proficiency and adaptability in handling complex datasets efficiently.