Interactive-Sales-Dashboard-Using-Excel

The objective was to create an annual sales report of a Super Store for last year. So that, the owner can understand their customers and grow more sales in the next year.

We had to create a dashboard to provide the following KPIs as per the demand:

Compare Sales and Orders using a single chart

Which month got the highest sales and orders?

Who purchased more- Men or Women?

What are the different order statuses?

List the top 5 states contributing to the sales.

Relation between Age and Gender based on the number of orders.

Which channel is contributing to maximum sales?

Highest selling category?

After understanding the objective and the problem statement, we went forward to our first step i.e. Data Cleaning. For data cleaning, we followed the following steps:

Removing Duplicates: We had to remove duplicates so that they didn't cause any errors in the calculations or the result outcomes. The easiest but less reliable way to remove duplicates is to select all data and click Remove duplicates. The other way is to apply filters check for duplicate values and delete them.

Removing Null Values: The null values have to be removed for the same reason as stated above they should not cause any error in the Pivot Table calculations. To do so, we apply the filters, select rows with blank values, and delete them.

Setting datatype: Setting the correct datatype is a must. For example, the age column datatype must be a number. It cannot be text and so on for other columns as well.

Correcting Row Values: In the gender column, in certain rows, Men were shown as 'M' and in others as 'Men' and the same for women. So, we need to make them equal to process the data. Either we should indicate Men as 'M' or 'Men' only.

Various other data cleaning steps can be applied as per your requirement. Now moving on to the Data Preparation/Processing part, the necessary steps are:

Grouping Data: We can group the data to simplify the category. Suppose, if we need to check orders by age, our data has an age range between 12 and 51. We might need to check sales for each age, which is tedious and useless. Instead, we can classify them as teenagers, adults, and seniors.

Modifying Data: To get monthwise sales, we can extract months from the date column and create a new month column next to it to track sales by month.

Establishing Relationship between Columns: