keshavk21/data_analysis_of_structured-data

Excel project 1

• Raw CSV File

Step follwed for data cleaning-

• Converting csv file to xlsx form.
• Remove empty rows.
• Remove duplicate rows.
• Checking for spelling mistakes and fixing them.
• Finding and replacing double spaces from the data.
• While importing the csv file department name was not imported correctly. It got split into two columns. So we need to merge it.

• Cleaned and Prepared Data

Steps follwed for data analysis-

• Change the format of data to table.
• Calculate SUM, AVERAGE, MIN, MAX and COUNT.
• Creating pivot table. Using the PivotTable feature to create a pivot table that displays the Department field in the Rows section, and the Equipment Count in the Values section, so that the pivot table displays the sum of equipment count by department.
• Sort the pivot table data. Use the Sort By Value setting on the pivot table to sort it in descending order by the sum of equipment count.
• Creating similar 2 more pivot tables.
• Analyzing the data in pivot table -
  • (a) In pivot table 2 add the Equipment Class field below the Department field so that the different vehicle types appear under each department with their respective counts.
  • (b) In pivot table 3 add the Equipment Class field above the Department field so that the different vehicle types appear first, with the different departments listed underneath each vehicle type with their respective counts.

• Analyzed Data