It is an Excel study for practice. The dataset (.csv) used in this work comes from the following source: Montgomery County Fleet Equipment Inventory
In Part 1,
- Column widths: Sorted out the widths of all columns so that the data is clearly visible in all cells.
- Empty rows: Used the Filter feature to look for blanks and remove all empty rows from the data.
- Duplicate records: Used Remove Duplicates feature to look for and remove duplicated records from the data.
- Spelling: Checked for spelling mistakes in the data and fixed them.
- Whitespace: Used the Find & Replace feature to remove all double-spaces from the data.
- Department names: When the data was converted from its data source, the department names didn’t import correctly and they are split over two columns in the data. Used Flash Fill to reduce the department names to just one column, and then remove any unnecessary columns.
Result:
In Part 2,
- Format the data as a table: Used the Format as Table option to format the data as a table.
- Use AutoSum to calculate values: Used AutoSum to find the following values for column ‘C’ and record each of the values: SUM AVERAGE MIN MAX COUNT
- Create a Pivot Table: Used 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: Used the Sort By Value setting on the pivot table to sort it in descending order by the sum of equipment count.
- Make two more pivot tables: Created two more identical pivot tables.
Result:
