Challenge: 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.