Learn with Yasir

Share Your Feedback

Mini Project: Excel Formatting Dashboard


Mini Project: Excel Formatting Dashboard

Objective:

Create a visually appealing and well-organized Excel dashboard using various formatting techniques to display sales data for a fictional company.

Project Requirements:

  1. Dataset Preparation:
    • Create a dataset with the following columns:
      • Product Name
      • Sales Region
      • Salesperson
      • Units Sold
      • Revenue
      • Profit Margin (%)
    • Populate the dataset with at least 20 rows of sample data.
  2. Formatting Tasks:
    • Font Styles:
      • Use bold font for column headers.
      • Apply a sans-serif font (e.g., Arial) for the entire dataset.
    • Cell Borders:
      • Add borders around the dataset to separate rows and columns.
    • Fill Color:
      • Use alternating row colors (e.g., light gray and white) for better readability.
    • Number Formatting:
      • Format the “Revenue” column as currency.
      • Format the “Profit Margin (%)” column as percentages with two decimal places.
    • Alignment:
      • Center-align the “Sales Region” and “Salesperson” columns.
      • Right-align the “Units Sold” and “Revenue” columns.
  3. Dashboard Creation:
    • Create a summary section at the top of the sheet with the following metrics:
      • Total Revenue
      • Average Profit Margin
      • Top Salesperson (based on Revenue)
    • Use Merge & Center to create a title for the dashboard.
    • Add a chart (e.g., bar chart or pie chart) to visualize sales by region.
  4. Additional Features:
    • Add a filter to the dataset to allow users to sort and filter data by region, salesperson, or product.
    • Add a legend to explain the conditional formatting.

Deliverables:

  • A single Excel file containing:
    • The formatted dataset.
    • The summary section and chart.
    • Properly applied formatting and conditional rules.

Learning Outcomes:

  • Understand and apply various Excel formatting techniques.
  • Learn to use conditional formatting for data visualization.
  • Practice creating a professional-looking dashboard for data analysis.

🧠 Practice & Progress