This data analysis session focuses on utilizing Microsoft Excel to perform data cleaning, create pivot tables, and build a functional dashboard. The session follows a structured analytical process, beginning with data preparation and concluding with visualization and reporting.

Session Overview and Data Cleaning

The instructor emphasizes that real-world data is rarely ready for immediate analysis and requires systematic cleaning. The following steps were covered:

• Initial Cleanup: Removing extraneous rows to ensure the data starts from the header row.
• Data Formatting:
  • Correcting date formats that appear as numbers.
  • Setting currency formats for financial columns.
• Data Transformation:
  • Using Find and Replace (Ctrl+H) to standardize abbreviated region names (e.g., SW to Southwest).
  • Using Text to Columns to separate combined strings, such as salesperson names and IDs.
  • Calculating a new “Total Cost” column by multiplying unit cost by quantity.

Analysis and Pivot Tables

Once the data is organized, the session transitions to using pivot tables to summarize large datasets without complex formulas.

• Creation: Pivot tables are generated by selecting data within the range and choosing the “Insert Pivot Table” function.
• Insights: The group analyzes performance trends, such as identifying underperforming salespersons and regional performance disparities.
• Refinement: Features like sorting (smallest to largest) and filtering (top/bottom N) are used to isolate specific metrics.

Dashboard Development

The final portion of the session focuses on creating a dashboard to visualize the findings.

• Visual Elements: Participants are guided to insert charts (such as clustered columns) based on the pivot tables.
• Interactivity: Slicers are introduced as a filter mechanism. The instructor demonstrates “Report Connections” to link multiple pivot charts to a single slicer, ensuring the entire dashboard updates dynamically based on user selections.
• Presentation: The instructor stresses that a key part of the analyst’s role is not just preparing the data, but communicating insights through organized dashboards and reports.

The session concludes with an assignment for the next class, where participants are expected to perform a similar end-to-end analysis on a new dataset and present their findings.