IMPLEMENT THE FOLLOWING USING EXCEL
EX No. 1
TO CREATE A PIE CHART FOR SALES AND SALES % BY COUNTRY (SORTED
IN DESCENDING ORDER) USING EXCEL
STEP 1: Prepare Your Data
Data is organized in a table format.
Country Sales Sales %
USA 1000 25%
India 800 20%
China 700 17.50%
Germany 600 15%
UK 500 12.50%
Canada 400 10%
STEP 2: Sort the Data
Sort the table in descending order by the Sales column:
1. Select the range (including headers).
2. Go to the Data tab.
3. Click Sort.
4. In the Sort dialog box, choose "Sales" from the Sort by dropdown and select "Largest
to Smallest".
STEP 3: Create a Pie Chart
1. Select the data range to include in the pie chart (Country and Sales columns).
2. Go to the Insert tab.
3. In the Charts group, click on the Pie Chart icon.
4. Choose the desired pie chart type (e.g., 2-D Pie).
Step 4: Customize Pie Chart
1. Add Titles and Labels:
o Click on the chart to select it.
o Go to the Chart Design tab.
o Use the Add Chart Element dropdown to add data labels, chart title, and
legend as needed.
2. Format the Chart:
o Right-click on different elements of the chart (like the slices, legend, etc.) to
format them. Change colours, fonts, and more.
3. Display Sales Percentages:
o Click on the chart and then on the Chart Elements button (plus icon).
o Check Data Labels and then select More Options.
o Choose to display Percentage.
Step 5: Finalize and Review
1. Review the Chart:
o Make sure the data is accurately represented and easy to read.
o Check for any inconsistencies or errors.
2. Save the Work:
o Save Excel workbook to ensure all changes are preserved.
OUTPUT:
Sales
400
1000
500
600
800
700
USA India China Germany UK Canada
EX No. 2
CREATE BAR CHART FOR SALES BY COUNTRY BY YEAR (ROUNDED TO
NEAREST THOUSAND AND SORTED BY GRAND TOTAL
Step 1: Prepare Data Table
o Arrange data in a table with countries as rows and years as columns.
o Round sales data to the nearest thousand.
Country/Year 2021 2022 2023 Grand Total
Japan 1000 1500 1100 3600
USA 800 1200 800 2800
India 400 1000 500 1900
Step 2: Sort Data by Grand Total
o Highlight the entire table.
o Go to the Data tab and click on Sort.
o Choose to sort by the Grand Total column in descending order.
Step 3: Insert a Bar Chart
o Highlight the data, excluding the Grand Total column.
o Go to the Insert tab.
o Choose Bar Chart from the Charts group (either 2D or 3D bar chart.
Step 4: Adjust Chart Elements
o Title: Click on the chart title to edit "Sales by Country and Year."
o Axis Labels: Ensure the country names are on the vertical axis and sales
figures are on the horizontal axis.
o Data Labels: Add data labels by clicking on the chart, then going to Chart
Elements (the plus sign) > Data Labels.
Step 5: Customize Chart
o Use the Chart Tools to change colors, styles, and other formatting options.
These tools are available under the Format tab when the chart is selected.
Step 6: Final Adjustments
o Ensure that sales figures in the data table are rounded to the nearest thousand
for consistency.
o Double-check that the chart accurately represents data.
OUTPUT:
Sales By Country By Year
GRAND TOTAL 3600 2800 1900
2023 1100 800 500
2022 1500 1200 1000
2021 1000 800 400
Japan USA India
EX No. 3
TO CREATE LINE CHART FOR SALES BY SHIP MODE (FIRST CLASS, SAME
DAY, SECOND CLASS AND STANDARD CLASS) USING EXCEL
Step 1: Prepare Data Table
Data is organized in a table format.
Ship Mode Sales
First Class 800
Same Day 750
Second Class 900
Standard Class 600
Step 2: Select Data
o Click and drag to select the data range to include in chart. This should include the
headers (e.g., Ship Mode, Sales) and all the data points.
Step 3: Insert a Line Chart
o Go to the Insert tab on the Excel ribbon.
o In the Charts group, click on the Line chart icon.
o Choose the desired line chart type from the dropdown menu (e.g., Line, Line with
Markers).
Step 4: Customize Chart
1. Add Titles and Labels:
o Click on the chart to select it.
o Go to the Chart Design tab.
o Use the Add Chart Element dropdown to add axis titles, chart title, and data
labels as needed.
2. Format the Chart:
o Right-click on different elements of the chart (like the line, axes, etc.) to
format them. change colors, line styles, fonts, and more.
Step 5: Finalize and Review
1. Review the Chart:
o Make sure the data is accurately represented and easy to read.
o Check for any inconsistencies or errors.
2. Save Work:
o Don’t forget to save Excel workbook to ensure all changes are preserved.
OUTPUT:
Ship Mode
900
800
750
600
FIRST CLASS SAME DAY SECOND CLASS STANDARD
CLASS
EX No. 4
TO CREATE SCATTER CHART FOR SALES BY SHIP MODE BY COUNTRY (ROUNDED
TO THE NEAREST DOLLAR AND SORTED BY FIRST CLASS) USING EXCEL
Step 1: Prepare Data Table
Data is organized properly in a table format.
Country Ship Mode Sales
Canada Second Class 600
Japan Same Day 700
Germany Standard Class 800
USA Second Class 900
USA First Class 1000
India First Class 1200
Step 2: Sort Data by First Class
o Select the entire data range.
o Go to the Data tab.
o Click on Sort.
o In the Sort dialog box, choose "Ship Mode" from the Sort by dropdown, and sort by
"A to Z" to ensure "First Class" appears first.
Step 3: Insert a Scatter Chart
o Select the data to include in the chart, typically the "Sales" and "Ship Mode" columns.
o Go to the Insert tab.
o In the Charts group, click on the Scatter (X, Y) Chart icon.
o Choose the desired scatter chart type (e.g., "Scatter with only Markers").
Step 4: Customize Scatter Chart
1. Add Titles and Labels:
o Click on the chart to select it.
o Go to the Chart Design tab.
o Use the Add Chart Element dropdown to add axis titles, chart title, and data
labels as needed.
2. Format the Chart:
o Right-click on different elements of the chart (like the markers, axes, etc.) to
format them. change colors, marker styles, fonts, and more.
3. Adjust Axes:
o Ensure the x-axis and y-axis are labeled appropriately. For instance, the x-axis
could represent "Ship Mode", and the y-axis represents "Sales".
Step 5: Finalize and Review
1. Review the Chart:
o Make sure the data is accurately represented and easy to read.
o Check for any inconsistencies or errors.
2. Save Work:
o Save Excel workbook to ensure all changes are preserved.
OUTPUT:
Sales By Ship Mode
1400
1200
1200
1000
1000 900
800
800 700
600
600
400
200
0
0 1 2 3 4 5 6 7
EX No. 5
CREATE A HEAT MAP IN EXCEL FOR SALES BY CATEGORY AND
SUBCATEGORY (IN THOUSANDS AND SORTED BY SALES VALUE IN
DESCENDING ORDER)
Step 1: Prepare Data
Data with categories in one column, subcategories in the next, and sales values in the last.
Ensure sales data is in thousands.
Category Subcategory Sales (in thousands)
1200
Home Goods Furniture
1300
Electronics Mobile Phones
1000
Electronics Laptops
1100
Home Goods Kitchen Appliances
850
Clothing Women's Apparel
700
Clothing Men's Apparel
Step 2: Sort Data by Sales Value
o Highlight the data range.
o Go to the Data tab and click Sort.
o Choose to sort by the Sales (in thousands) column in descending order.
Step 3: Insert Heat Map
o Highlight the sales data range.
o Go to the Home tab, then Conditional Formatting in the Styles group.
o Choose Color Scales and select a color scale (e.g., green-yellow-red).
Step 4: Create a Pivot Table
o Select data range and go to the Insert tab.
o Click on PivotTable.
o Place Category in the Rows area, Subcategory in the Columns area, and
Sales in the Values area.
o Ensure the sales values are summarized by sum.
Step 5: Apply Conditional Formatting
o Highlight the range of sales values in the Pivot Table.
o Go to Conditional Formatting > Color Scales and select the same color scale
used earlier.
Step 6: Format Pivot Table
o Adjust the formatting to make the heat map clear and visually appealing.
o Add data labels, adjust column widths, and customize colors as needed.
OUTPUT:
Sum of Sales (in
Row Labels thousands)
Clothing 1550
Men's Apparel 700
Women's Apparel 850
Electronics 2300
Laptops 1000
Mobile Phones 1300
Home Goods 2300
Furniture 1200
Kitchen
Appliances 1100
Grand Total 6150
EX No. 6
TO DESIGN AND CREATE THE LABEL FOR VENDOR LIST
Step 1: Prepare Data Table
Open Excel and enter vendor data in a table format.
Vendor
Address City State
Name
Vendor 1 1 CMC colony Vellore TN
Vendor 2 2 CMC colony Chennai TN
Vendor 3 3 CMC colony Coimbatore TN
Vendor 4 4 CMC colony Madurai TN
Vendor 5 5 CMC colony Trichy TN
Vendor 6 6 CMC colony Salem TN
Vendor 7 7 CMC colony Tiruvallur TN
Vendor 8 8 CMC colony Kanchipuram TN
Step 2: Open the Mail Merge Wizard in word
1. Go to the Mailings tab on the Word Ribbon.
2. Click on Start Mail Merge and select Labels from the dropdown menu.
Step 3: Select Label Options
1. In the Label Options dialog box, choose the type of label (e.g., Avery).
2. Select the specific label product number that matches labels.
3. Click OK to close the dialog box.
Step 4: Choose Data Source
1. Click on Select Recipients and choose Use an Existing List.
2. Browse to the Excel file that contains vendor data and select it.
3. In the Select Table dialog box, choose the worksheet with data and click OK.
Step 5: Insert Merge Fields
1. Click on Insert Merge Field and select the fields to include in the label (Vendor
Name, Address, City, State).
2. Arrange the fields in the desired order and format.
o «Vendor Name»
o «Address»
o «City»
o «State»
Step 6: Preview Labels
1. Click on Preview Results to see the labels of actual data.
2. Use the Next Record and Previous Record buttons to view each label.
Step 7: Complete the Mail Merge
1. Once finish the label design, click on Finish & Merge.
2. Choose Print Documents to print labels or Edit Individual Documents to create a
new document with the labels.
EX No. 7
TO DESIGN AND CREATE THE DASHBOARD
Step 1: Prepare Data Tabel
Data is clean and structured in a table format.
Date Sales Expenses Profit
01-Jan-25 3000 1000 2000
02-Jan-25 2000 1500 500
03-Jan-25 1000 500 500
04-Jan-25 5000 3500 1500
Step 2: Create a Pivot Table
1. Insert a Pivot Table:
o Select data range.
o Go to the Insert tab and click on PivotTable.
o Choose the PivotTable to be placed (new worksheet or existing worksheet).
2. Arrange Pivot Table:
o Place the necessary fields in the appropriate areas (Rows, Columns, Values,
Filters).
Step 3: Insert Charts and Graphs
1. Insert a Chart:
o Select the data to visualize.
o Go to the Insert tab and choose the type of chart (e.g., Column, Line, Pie).
2. Format the Chart:
o Add titles, labels, and format the chart to make it visually appealing.
Step 4: Create Key Metrics and Summary
1. Add Calculations:
o Use formulas to calculate key metrics (e.g., Total Sales, Total Profit, Average
Expenses).
o Place these calculations in a separate section of the worksheet for easy
reference.
2. Format Key Metrics:
o Use formatting options to highlight the key metrics (e.g., bold, colors).
Step 5: Assemble the Dashboard
1. Arrange the Elements:
o Place the PivotTable, charts, and key metrics in a logical and visually
appealing layout.
2. Add Titles and Labels:
o Add a main title for the dashboard.
o Label each section clearly.
Step 6: Use Slicers and Filters
1. Insert Slicers:
o Select the PivotTable or chart.
o Go to the PivotTable Analyse tab and click on Insert Slicer.
o Choose the fields to use as slicers.
2. Format Slicers:
o Customize the slicers to match the dashboard design.
Step 7: Finalize and Review
1. Review the Dashboard:
o Ensure all data is accurately represented.
o Check for any inconsistencies or errors.
2. Save Work:
o Save Excel workbook to ensure all changes are preserved.
OUTPUT:
Dashboard
04-Jan-25
03-Jan-25
02-Jan-25
01-Jan-25
0 1000 2000 3000 4000 5000 6000
Profit Expenses Sales
IMPLEMENT THE FOLLOWING USING TABLEAU
EX No. 1
SHIP MODE (FIRST CLASS, SAME DAY, SECOND CLASS AND STANDARD
CLASS).
Step 1: Prepare Your Data
Data is organized properly in a table format.
Ship Mode Sales
First Class 800
Same Day 750
Second Class 900
Standard Class 600
Step 2: Connect to Data Table
1. Open Tableau.
2. Click on "Connect to Data" on the start page.
3. Choose the data source you to use excel file.
4. Browse to choose data file and select it.
Step 3: Load Data
1. Once connected, Tableau will automatically detect the data structure.
2. Drag and drop the fields to visualize into the Rows and Columns shelves.
Step 4: Create a Line Chart
1. Drag the "Ship Mode" field to the Rows shelf.
2. Drag the "Sales" field to the Columns shelf.
3. Click on the "Show Me" button and select the Line Chart option.
Step 5: Customize Chart
1. Add Titles and Labels: Click on the chart to select it1.
2. Go to the "Marks" card and add axis titles, chart title, and data labels as needed.
3. Format the Chart: Right-click on different elements of the chart (like the line, axes,
etc.) to format them. change colors, line styles, fonts, and more2.
Step 6: Finalize and Review
1. Review the Chart: Make sure the data is accurately represented and easy to read.
2. Save Work: Save Tableau workbook to ensure all changes are preserved.
OUTPUT:
EX No. 2
SALES BY SHIP MODE BY COUNTRY (ROUNDED TO THE NEAREST DOLLAR
AND SORTED BY FIRST CLASS)
Step 1: Prepare Your Data
Data is organized properly in a table format.
Country Ship Mode Sales
Canada Second Class 600
Japan Same Day 700
Germany Standard Class 800
USA Second Class 900
USA First Class 1000
India First Class 1200
Step 2: Connect to Data Table
1. Open Tableau.
2. Click on "Connect to Data" on the start page.
3. Choose the data source to use Excel file .
4. Browse to data file and select it.
Step 3: Load Data Table
1. Once connected, Tableau will automatically detect the data structure.
2. Drag and drop the relevant fields to visualize into the appropriate shelves (e.g., Rows
and Columns).
Step 4: Create a Scatter Chart
1. Drag the "Ship Mode" field to the Columns shelf.
2. Drag the "Sales" field to the Rows shelf.
3. To differentiate by country, drag the "Country" field to the Detail button on the
Marks card.
4. Click on the "Show Me" button and select the Scatter Plot option.
Step 5: Customize Chart
1. Add Titles and Labels:
o Click on the chart to select it.
o Use the "Marks" card to add axis titles, chart title, and data labels as needed.
2. Format the Chart:
o Right-click on different elements of the chart (like the markers, axes, etc.) to
format them. You can change colors, marker styles, fonts, and more.
3. Adjust Axes:
o Ensure the x-axis and y-axis are labeled appropriately.
Step 6: Finalize and Review
1. Review the Chart:
o Make sure the data is accurately represented and easy to read.
o Check for any inconsistencies or errors.
2. Save Work:
o Save Tableau workbook to ensure all changes are preserved.
OUTPUT:
EX No. 3
SALES BY CATEGORY AND SUBCATEGORY (IN THOUSANDS AND SORTED
BY SALES VALUE IN DESCENDING ORDER)
Step 1: Prepare Data Table
Data is organized properly in a table format.
Sales (in
Category Subcategory
thousands)
1200
Home Goods Furniture
1300
Electronics Mobile Phones
1000
Electronics Laptops
1100
Home Goods Kitchen Appliances
850
Clothing Women's Apparel
700
Clothing Men's Apparel
Step 2: Connect to Data
1. Open Tableau.
2. Click on "Connect to Data" on the start page.
3. Choose the data source you want to use Excel file.
4. Browse to data file and select it.
Step 3: Load Data
1. Once connected, Tableau will automatically detect the data structure.
2. Drag and drop the relevant fields you want to visualize into the appropriate shelves
(e.g., Rows and Columns).
Step 4: Create a Heat Map
1. Drag the "Category" field to the Rows shelf.
2. Drag the "Subcategory" field to the Columns shelf.
3. Drag the "Sales" field to the Color button on the Marks card.
4. Tableau will automatically create a heat map based on the sales values.
Step 5: Customize Chart
1. Add Titles and Labels:
o Click on the chart to select it.
o Use the "Marks" card to add axis titles, chart title, and data labels as needed.
2. Format the Chart:
o Right-click on different elements of the chart (like the color marks, axes, etc.)
to format them. You can change colors, label styles, fonts, and more.
Step 6: Create a Pivot Table (Cross Tab in Tableau)
1. Create a Pivot Table:
o Drag the "Category" field to the Rows shelf.
o Drag the "Subcategory" field to the Columns shelf.
o Drag the "Sales" field to the Text button on the Marks card.
o This will create a cross tabulation of the data.
Step 7: Apply Conditional Formatting
1. Highlight the range of sales values:
o Use the "Color" button on the Marks card to adjust the color scale.
2. Select the same color scale you used earlier for consistency.
Step 8: Format Pivot Table
1. Adjust the formatting:
o Make sure the heat map is clear and visually appealing.
o You can add data labels, adjust column widths, and customize colors as
needed.
OUTPUT: