DATA VISUALIZATION LAB

1.​ Create pie chart for sales and sales % by country (sorted in descending order).

Aim:
To create pie chart for sales and sales % by country sorted in descending order.

Steps and Visualizations:

Step 1: Prepare the data

Ensure your data has at least these columns:
●​ Country
●​ Sales (amount)

Step 2: Create a Pivot Table

1.​ Select the entire dataset.
2.​ Go to Insert → PivotTable.
3.​ Choose New Worksheet.
4.​ Click OK.
Step 3: Add fields to Pivot Table
In the PivotTable Fields pane:
●​ Drag Country → Rows
●​ Drag Sales → Values

Step 4: Change Count to Sum

1.​ Click the dropdown beside Count of Sales.
2.​ Select Value Field Settings.
3.​ Choose Sum.
4.​ Click OK.
Step 5: Sort Sales in Descending Order
1.​ Click any value in Sum of Sales column.
2.​ Right-click → Sort
3.​ Choose Sort Largest to Smallest.

Step 6: Calculate SALES %

1.​ Drag Sales into Values again.
2.​ Click dropdown → Value Field Settings.
3.​ Go to Show Values As tab.
4.​ Select % of Grand Total.
5.​ Click OK.

Step 7: Insert Pie Chart

1.​ Select Country and Sales % columns in the Pivot Table.
2.​ Go to Insert → Pie Chart → 2-D Pie.
Step 8: Format Pie Chart
1.​ Click the chart title → Rename to:​
“Sales % by Country”
2.​ Click Chart Elements (+):
Enable Data Labels
Choose Percentage
2.​ Create Bar chart for sales by country and by year (rounded to nearest
thousands and sorted by grand total)

Aim:
To create Bar chart for sales by country and by year (rounded to nearest thousands and sorted by
grand total).

Steps and Visualizations:

Step 1: Prepare the data

Ensure your dataset has these columns:
●​ Country
●​ Year
●​ Sales

Step 2: Create a Pivot Table

1.​ Select the entire dataset.
2.​ Go to Insert → PivotTable.
3.​ Choose New Worksheet.
4.​ Click OK.
Step 3: Add fields to Pivot Table
In the PivotTable Fields pane:
●​ Drag Country → Rows
●​ Drag Year → Columns
●​ Drag Sales → Values

Step 4: Change Sales calculation to SUM

1.​ Click the dropdown beside Count of Sales.
 2.​ Select Value Field Settings.
3.​ Choose Sum.
4.​ Click OK.

Step 5: Round Sales to Nearest Thousand

1.​ Right-click any value in the Pivot Table.
2.​ Select Number Format.
3.​ Choose Number.
4.​ Set Decimal Places = 0.
5.​ Enable Use 1000 Separator (,).
6.​ Click OK.
Step 6: Sort Countries by Grand Total
1.​ Click any value in the Grand Total column.
2.​ Right-click → Sort.
3.​ Select Sort Largest to Smallest.
Step 7: Insert Bar Chart
1.​ Select the entire Pivot Table.
2.​ Go to Insert → Bar Chart.
3.​ Choose Clustered Bar (or Clustered Column).

3. Create a line chart for sales by ship mode (first class, same day, second class and
standard class).

Aim:
 To create a line chart for sales by ship mode (first class, same day, second class and standard
class)

Step 1: Prepare the data

Ensure your dataset contains these columns:
●​ Ship Mode
●​ Sales

Step 2: Create a Pivot Table

1.​ Select the complete dataset.
2.​ Go to Insert → PivotTable.
3.​ Choose New Worksheet.
4.​ Click OK.

Step 3: Add fields to Pivot Table

●​ Drag Ship Mode → Rows
 ●​ Drag Sales → Values

Step 4: Change Sales calculation to SUM

1.​ Click the dropdown beside Count of Sales.
2.​ Select Value Field Settings.
3.​ Choose count
4.​ Click OK.

Step 5: Sort Ship Mode by Sales (Descending)

 1.​ Click any value in count of Sales.
2.​ Right-click → Sort.
3.​ Select Sort Largest to Smallest.

Step 6: Insert Line Chart

1.​ Select the Ship Mode and Count of Sales columns.
2.​ Go to Insert → Line Chart.
3.​ Choose 2-D Line Chart.
4.​ Change chart title to:​
“Sales by Ship Mode”
5.​ Add Axis Titles:
X-Axis → Ship Mode
Y-Axis → Sales
5. Create a heatmap for sales by category by sub-category (in thousands and sorted
by sales value in descending order).

Aim:
To Create a heatmap for sales by category by sub-category (in thousands and sorted by sales
value in descending order)

Steps and Visualizations:

STEP 1: Create a Pivot Table

1.​ Select your entire data range
2.​ Go to Insert → PivotTable
3.​ Choose New Worksheet
4.​ Click OK

STEP 2: Set Pivot Table Fields

●​ Rows → Sub-Category
●​ Columns → Category
●​ Values → Sales
STEP 3: Convert Sales to Thousands
1.​ Right-click any value in the pivot table
2.​ Click Value Field Settings
3.​ Click Number Format
4.​ Choose Number
5.​ Set:
Decimal places: 0
Use 1000 separator (,)
6.​ Click OK

STEP 5: Sort by Sales (Descending)

1.​ Click any Sub-Category name
2.​ Right-click → Sort
3.​ Choose Sort Largest to Smallest
4.​ Sort by Grand Total (Sum of Sales)

STEP 6: Apply Heat Map (Conditional Formatting)

1.​ Select only the sales values (not row/column headers)
2.​ Go to Home → Conditional Formatting
3.​ Choose Color Scales
4.​ Pick a 3-color scale
Low = Light color
High = Dark color
6. Design and Create label for vendor list.

Aim:
​ To Design and Create label for vendor list

Steps and Visualizations:

Step 1: Start Excel

Open Microsoft Excel and create a new workbook.

Step 2: Enter Vendor Data

1.​ Go to Sheet1
2.​ Create the following columns in Row 1:
Vendor Name
Address
City
State
PIN

Step 3: Create Label Design Sheet

1.​ Insert a new worksheet
2.​ Rename it as Labels

Step 4: Create Label Formula

1.​ Select cell A1 in the Labels sheet
2.​ Enter the following formula in formula bar

=Sheet1!A2 & CHAR(10) &

Sheet1!B2 & CHAR(10) &
Sheet1!C2 & ", " & Sheet1!D2 & " - " & Sheet1!E2 & CHAR(10) &
Sheet1!F2
Step 5: Format the Table
1.​ Enable Wrap Text
2.​ Set appropriate row height and column width
3.​ Apply borders to the label cell
4.​ Make the vendor’s name bold
7.​ Create and design the dashboard

Aim:
​ To create and design the dashboard

Steps:

STEP 1: Data Import: Load the sales dataset into Excel

STEP 2: Data Cleaning: Remove duplicates and fix missing values
STEP 3: KPI Identification: Define total sales, profit, and units sold
STEP 4: Pivot Table Creation: Summarize data using pivot tables
STEP 5: Chart Generation: Create bar, line, and donut charts
STEP 6: Interactivity Addition: Insert slicers for dynamic filtering
STEP 7: Dashboard Formatting: Arrange visuals and apply styling

Dashboard: