Data Analysis Project-Excel Dashboard
In this project, we will be using a fictional dataset from KAGGLE. The first step in analyzing data is to clean and transform it. Excel provides flexible functionality to achieve this, starting from transforming your data with power query and loading it into your spreadsheet. Let me walk you through the process of loading, cleaning, performing summary statistics, and most importantly building charts and interactive dashboards in excel.
Downloading your dataset
This is the first step to take whenever you want to work with a dataset. My dataset is a fictional sales dataset ‘Supermart Grocery Sales’. It consists of 9994 rows and 11 columns which include ‘order Id’, ‘customer name’, ‘category’, ‘sub category’, ‘city’, ‘order date’, ‘region’, ‘sales’, ‘discount’, ‘profit’, ‘state’. Here is the link to download it Supermart Grocery Sales
Loading your dataset into Excel Power Query
After downloading your dataset, go to excel, load it and click on the ‘Get data’ option in the data toolbar menu. Select your file option and proceed to load into power query. I love to clean my data in the spreadsheet rather than in the power query editor. I basically use Power query editor to merge queries if I have common column(s) I would be joining on. My data was not dirty except that I had to reformat the date column. I loaded my data directly into the spreadsheet and I used shortcut tips (ALT+H+O+I and ALT H+O+A) to automatically adjust the column width to fit the size of my data and auto adjust the data row heights and columns. I checked all my columns to be sure they had the correct data types.
Performing Summary Statistics Using Data Analysis Tool Pack And Predefined Excel Formulas
I have some numerical columns like profit, sales, and discount that needed summary statistics. I used the data analysis tool pack in the data toolbar menu. This can be activated by navigating to file>options>add-ins if you do not have it automatically on your data toolbar menu. I performed descriptive statistics(sum, mean, median, mode, standard deviation, maximum, minimum). I got $1120.95 as the maximum profit. I used the LOOKUP formula to find out the region and it was the western region. I used the VLOOKUP formula to keep track of the details of each customer, using the order id as the lookup value. I used the SUMIF formula to find out the sum of sales greater than $1000 and sales lesser than $1000. I also used the COUNTIF formula to get the number of each region.
Building Charts Using Pivot Table And Building Dashboard
Building charts is one of the most important parts of analyzing data as it is the building block of dashboards for data visualization. We create pivot charts by going to the insert option and clicking on pivot table or selecting a range of data and using quick analysis. I built 4 pivot tables consisting of 3 column charts and 1 pie chart. The first column chart visualized the sum of profit each region made. West made the highest profit close to $1200000 while north made the least. The second column chart visualized the sales of each category. The egg, meat, and fish category made the highest sales while Oil and Masala made the least sales. The pie chart visualized the percentage of sales each region made. West made 32%, North 0%, South 16%, East 29%, and Central 23%. I created slicers for easy filtering of the data in the charts and report connections between all the pivot tables. I formatted my charts, copied them to a new worksheet, arrange them and remove the gridlines using ALT+W+V+G. I labeled my axis and titles. I named my dashboard and I'm done with building the dashboard in excel.
Important Links
.png)
Comments
Post a Comment