Build Excel Dashboard for E-Commerce Data

Are you tired of feeling like a clueless driver when it comes to analyzing your data? Well, get ready to buckle up and take control of your data with an Excel dashboard! Just like a car dashboard helps you keep track of your speed, fuel level, and other vital information, an Excel dashboard allows you to monitor key metrics and insights from your data in one central location. Let’s ditch the manual spreadsheet and hop in the driver’s seat of your data with an Excel dashboard to rev up your analysis skills! 🚗

FYI, the dataset for this case study can be obtained from Kaggle. It is the data extracted from the customer database of an e-commerce company that sells electronic products.

Figure 1: Final Result of the Excel Dashboard

Step 1: Identify the purpose and key performance indicators (KPIs)

In this case, we want to track customer satisfaction levels with their online shopping experience. 🐾

Step 2: Clean the data as necessary

Before building the charts, I replaced the 0 in the “Reached.on.Time_Y.N” column with “OnTime” and 1 with “Late”. To do that, you may highlight the values in the “Reached.on.Time_Y.N” column, type CTRL+H, fill in the information as shown in the image below, and select “Replace All”.

Figure 2: Replacing values in the dataset

Step 3: Create pivot tables to analyze and summarize the data.

The final dashboard has 4 pie charts, 3 area charts, 2 doughnut charts, 1 column chart, 1 bar chart, and 4 scorecards. In other words, we need to create 15 pivot tables for this purpose. 🧩

To create a pivot table from the dataset in a new worksheet, you need to select “PivotTable” after clicking the “Insert” tab. Then, click “OK” after selecting “New Worksheet” in the dialog box (see Figure 3).

Figure 3

You may now see a new sheet appear in your workbook and you may rename it as “Pivot Tables” for easy future reference purposes. You can see the PivotTables Fields pane in this new sheet. This pane always appears whenever you select “Field List” in the “PivotTable Analyze” tab. You may move and drag the field to the target area to add a field to your pivot table.

Figure 4 shows a pivot table that classifies the data by customer rating and shows the values as the sum of ID. However, this is not the case we want as this does not represent the truth. To show the number of orders in percentage, we need to change the type of calculation by clicking the arrow on the “sum of ID” field and selecting “Value Field Settings”.

Figure 4

After that, select “Count” in the “Summarize Values By” tab and “% of Grant Total” in the “Show Values As” of the dialog box. You can rename the column by directly typing a new name in the “Custom Name” section (see Figure 5).

Figure 5

We do not show the grand total in the charts; hence, we can remove it by right-clicking the “Grand Total” cell and selecting “Remove Grand Total” (see Figure 6).

Figure 6

To build pivot tables for the remaining categorical variables (“Warehouse_block”, “Mode_of_Shipment”, “Customer_care_calls”, “Prior_purchases”, “Product_importance”, “Reached.on.Time_Y.N”, “Gender”), you can simply copy the pivot table that you created previously, paste it into any cell of the “Pivot Tables” sheet, and replace the “Customer_rating” with other variables.

To build a pivot table for numerical values, we can group the values by placing the cursor in the pivot table, selecting “Group Field” in the “PivotTable Analyze” tab, and inserting the size value. Figure 5 shows that the cost of the product is grouped into intervals of 20 and the values are shown as the count (not percentage) of ID (see Figure 7).

Figure 7

Repeat the same steps and change the size value to create the pivot tables for “Weight_in_gms” and “Discount_offered”. If you do it correctly, you should have 11 pivot tables as shown in Figure 8.

Figure 8

We need to build 4 more pivot tables. Go back to the dataset, create new pivot tables in a new sheet, and rename the sheet as “Averages” for easy future reference. Let’s calculate the averages for the “Customer_rating”, “Customer_care_calls”, “Prior_purchases” and “Discount_offered” by selecting “Average” as the type of calculation (see Figure 9).

Figure 9

Step 4: Create charts and slicers to visualize the data.

It is a good practice to use consistent colors, fonts, and layouts to make the dashboard visually appealing and easy to navigate. I used the following hex codes for different purposes. You may check out more color palettes from Canva for your future project.

Figure 10: Trees During Fall color combination

Also, I used the “Paper” theme color palette in Excel because I think it matches well with the background. In this way, the chart will be automatically created using the colors available in the palette.🎨

Figure 11: Select the color palette in Excel

Next thing is to build a basic framework for the dashboard as shown in Figure 12. Below are the articles that might be useful for you.

1. Insert & edit icons → Search the icon of your preference, change its color, and place it in the respective shape.
2. Add and edit the text in the shapes → Label the shapes with the variable names. Remember to use the same font type and font size.
3. Style shapes → Remove the outline of the shapes and fill it with olive (#787D12) color.
4. Apply transparency to a shape → Set the transparency as 10%.

Figure 12: Dashboard Framework

After building the framework, we need to use Excel’s camera tool to capture the result from the “Averages” worksheet onto the dashboard. The camera tool is a feature that allows users to take a picture of a data set, table, or chart which can be used across multiple sheets in a workbook. Any changes made to the source data set will be reflected in the captured image. The pasted snapshot can be resized and formatted using the camera tools. I recorded the steps (as shown in the GIF below) of how I added and used the camera tool when building this dashboard. 📸

GIF 1: Add and use the camera tool in Excel

Below is the screen recording of how I created a column chart for “Customer_care_calls”. You may refer to this video when creating a bar chart for “Prior_purchases”, given that the features of the bar chart and column chart are almost the same.

Here’s another video showing how I created a pie chart, doughnut chart, and area chart for the dashboard. Also, you can see how I add slicer to filter the data. 🌱

Below is my final dashboard. ✌️

GIF 2: Final dashboard demo

🌟 You may download the Excel dashboard from my GitHub or my Google Drive. Some Excel features cannot be displayed in Google Spreadsheet and will be dropped if you make any changes. Hence, remember to download and save it as a Microsoft Excel file if you were to download it from Google Drive. 🌟

Figure 13: How to download workbook as Microsoft Excel from Google Spreadsheet

Hope you found this article useful. Happy learning! 🏫