Course Content

Data Analysis with Excel

## Data Analysis with Excel

# Midterm Exam

**Great work!** You've made it halfway through the course.

Now, let's assess your abilities. **Download** the following data, **import** it into Excel, **complete** the specified tasks, and **answer** the related questions based on your results.

## Task 1: Data Import

**Download and import** the **Products.csv** and **Sales.csv** files into Excel. Use an **inner join** on **Product_ID** to merge these tables into a single dataset that includes all relevant product and sales information. Load this combined dataset into an Excel worksheet for further processing.

## Task 2: Data Highlighting

Apply conditional formatting to the **Transaction_ID** column to **highlight any duplicate IDs**.

## Task 3: Data Transformation

**Add a new column** called **Total_Sales** by multiplying the quantity by the price of each product for every transaction.

## Task 4: Conditional Analysis

Use `SUMIFS`

, `AVERAGEIFS`

, and `COUNTIFS`

to analyze the **Total_Sales** and transaction counts in the Texas and California regions separately to count them.

## Task 5: PivotTable

Create a PivotTable with **Location** as row labels, **Category** as column labels, and the sum of **Total_Sales**. Include slicers for **Product_Name** to facilitate dynamic filtering.

Everything was clear?