SALES DATA CLEANING WITH EXCEL

Data Source

Youtube @Kenji Explains (https://shorturl.at/8H53Z)

About the Data

This project utilizes a random dataset consisting of 33 rows and 9 columns, featuring variables. The data represents a sales data set which contains information on Date, clients, contact, Department, Payment, Revenue, Profit, Profit Margin.

Objective

The primary aim of this project is to clean, transform, and prepare the existing sales data for analysis, ensuring accuracy, consistency, and reliability, to inform business decisions and optimize sales strategies

Expected Outcomes

Cleaned and validated sales data.
Enhanced data accuracy and consistency.
Improved data structure and organization.
Reduced data errors and inconsistencies.

Cleaning Process

Raw Data

The raw dataset contains data with incorrect formatting, narrow column widths, inconsistent name arrangements, combined department and city information, and unwanted data formats.

Autofit Rows and Columns
The VBA code was used to autofit the rows and columns by going to the Worksheet tab, right-clicking and scrolling to view code and in the interface that shows general, changed it to worksheet and entered the code Cells.EntireColumn.Autofit and went back to the Excel worksheet.

After Autofitting Rows and Columns

Finding and Removing Duplicates

Duplicates in the dataset were removed by selecting the range of cells , Going to Data tab, then to Data Tools group, clicking Remove Duplicates and Select All.

Finding and Replacing

To remove some unwanted texts on the client column, I used the Find and Replace shortcut on excel (CLT + F) then input (*) to find all the texts in brackets and replaced them.

After replacing

Changing The Case

I modified the text case in Excel by creating a new column and naming the column header “client” and entered the excel function =lower() which changed the Upper case to Lower Case in the new column

After Changing Case

Using Trim and Proper Function

To make the data in the contact column in the right format; erasing the space before the data and inserting correct case where necessary, i applied the Trim and Proper function In excel by creating a new column, titled it contact and entering the formular =Trim(Proper())

After Using Trim and Proper Function

Text To Column
I used Excel’s ‘Text to Columns’ feature to split text into separate columns. I Selected the cell range containing the text, Went to Data tab > Data Tools group and Clicked ‘Text to Columns’ I Chose ‘Delimited’ option and Selected delimiter (_), then finnaly clicked finish.

After Using Text to Column Feature

Filling Empty Cells
The empty cells were filled by Utilizing Excel shortcuts to efficiently fill blank cells. First, the cell range was selected and i entered a short cut key Ctrl + G (Go To Special), selected Blanks as option, Entered the desire value to fill on the formular bar and pressed Ctrl + Enter

If Error Formular
To resolve errors using during the data cleaning, i used the If Error Formular by entering “=IFERROR(I3/H3, “NA”)”

Before

After