Practice 6: Customer Tracking with Data Import

← Back to ISMG-2050 Course Hub

Assignment Metadata

Module: Module 2 (Weeks 3 & 4)
Due Date: Sunday, September 21, 2026 at 11:59 PM MT
Starter Input: customer_data.csv
Expected Submission: [your initials]Practice06.xlsx

Skills Covered

Importing and formatting CSV data (Data > Get Data > From Text/CSV)
Icon sets: Arrow indicators for trend visualization
Data bars: Horizontal bar visualization of magnitude
Color scales: Heat-map conditional formatting
Professional table styling and number formatting

Part 1: CSV Data Import (30 pts)

Open a new blank workbook. Save as [your initials]Practice06.xlsx.
Go to Data > Get Data > From Text/CSV. Navigate to and select customer_data.csv.
In the preview dialog, verify columns show: Customer ID, Name, Email, Region, Purchase Date, Product Category, Quantity, Unit Price, Total Revenue.
Click Load to import. The data will appear in Sheet1 starting at A1.
Rename the sheet tab to "CustomerData".
Select all imported data (click any cell), then Ctrl+T to convert to an Excel Table. Check "My table has headers". Name the table Customers in the Table Design tab.
Select column A (Customer ID). Format as Text (right-click > Format Cells > Number > Text) to prevent Excel from dropping leading zeros.

Part 2: Icon Sets on Region Revenue (25 pts)

In a new sheet named "Icons", create a region summary:
A1: type Region, B1 type Total Revenue
In cells A3:A7, enter regions: East, West, North, South, Central.
In cell B3, use SUMIF to aggregate from the CustomerData table: =SUMIF(Customers[Region],A3,Customers[Total Revenue])
Copy B3 down through B7.
Select range A2:B2. Apply a header style (e.g., Gray Fill with Dark Text).
Select range B3:B7.
Home > Conditional Formatting > Icon Sets > 3 Arrows (Colored):

This assigns a Green Up Arrow for highest revenue, Yellow Sideways Arrow for mid-range, Red Down Arrow for lowest.

Part 3: Data Bars on Customer Transactions (25 pts)

In a third sheet named "DataBars":

A1: type Customer ID, B1 type Name, C1 type Total Revenue, D1 type Purchase Count

Select the entire CustomerData table (Ctrl+T created a table), copy the data.

Paste into A2:D2 of this sheet. Then use PivotTable or manual formulas to aggregate:

B2:B6: Enter customer names for 5 customers of your choice from the imported data.

C2:C6: For each selected customer, enter their Total Revenue (sum all rows matching that customer).

D2:D6: Enter Purchase Count (number of rows for each customer).

Select range C2:C6.

Home > Conditional Formatting > Data Bars > Gradient Fill - Blue. The horizontal bars visually represent revenue magnitude.

Select range D2:D6.

Home > Conditional Formatting > Data Bars > Solid Fill - Green.

Part 4: Color Scales on Revenue Distribution (20 pts)

Select range A3:A7 on the CustomerData sheet.

Home > Conditional Formatting > Color Scales > Green-Yellow-Red.

This creates a heat-map across the Region revenue totals from Practice Part 2.

All data ranges should display with consistent professional formatting: Currency for revenue ($#,##0.00), Number format for counts (#,##0).

Submission Criteria

File named exactly [your initials]Practice06.xlsx

"CustomerData" sheet: CSV imported successfully via Data > Get Data, converted to Excel Table

"Icons" sheet: Region summary with SUMIF and Icon Sets (3 Arrows)

"DataBars" sheet: Customer revenue aggregation with Blue data bars on revenue, Green data bars on purchase count

"CustomerData" sheet: Color scale (heat-map) applied to a numeric range

All currency columns formatted as $#,##0.00; all counts formatted as integers

AI Disclosure statement included

→ Back to Week 3\u20134 Homework Portal