Excel Dashboard — Coffee Sales
Are you curious about how to create an interactive dashboard using multiple related tables on Excel? I definitely was, and that’s why I decided to follow “Data with Mo’s” exceptional how-to video and create a dashboard for myself! In this article I’ll provide a few snapshots of what this dashboard can do before explaining all the steps I took to create it.
The Coffee Sales dashboard consists of 3 reactive graphs and 5 customizable slicers, which enables the user to filter results and find the sales data based on specific dates, Coffee types, Roast Types, Sizes, and Loyalty. Below I’ll briefly highlight this dashboard’s features.
These are the steps I took to create this interactive dashboard:
1.Downloaded the data and opened it in Excel: I located the xlsx file on the GitHub repository that Mo shared, downloaded it, and opened it in Excel — pretty straightforward!
2. XLOOKUP to fill in several columns from related tables: The xlsx file had three sheets; orders, customers, and products. To create this dashboard I needed all relevant information on one sheet. I started with the orders sheet and used XLOOKUP to fill in data from the customer sheet, including Name, Email and Country.
The syntax for XLOOKUP is as follows: =XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]). This was my input to fill in the names column: =XLOOKUP(C2,customers!$A$2:$A$1001,customers!$B$2:$B$1001,,0).
When I tried this for the emails column, I found that the cells that did not contain email addresses appeared with a zero in them. To avoid this, I created a simple IF statement that returned a blank cell for those cells without emails. Although the input looks complicated all it means is that if the value is 0, then I want nothing (“”), otherwise, I want the value. =IF(XLOOKUP(C2,customers!$A$2:$A$1001,customers!$C$2:$C$1001,,0)=0,“”,XLOOKUP(C2,customers!$A$2:$A$1001,customers!$C$2:$C$1001,,,))
The countries column was very similar to the names column: =XLOOKUP(C2,customers!$A$1:$A$1001,customers!$G$1:$G$1001,,0).
3. INDEX + MATCH to fill in information dynamically: To avoid the hassle of inputting the same function repeatedly again, I used the index match function to fill in data from my products table.
Inside the Index function, I used the MATCH function to match the row and column and row number =INDEX(products!A1:products!$A$1:$G$49,MATCH(orders!$D2,products!$A$1:$A$49,0),MATCH(I$1,products!$A$1:$G$1,0))
4. Multiplication formula for Sales column: =To create a column for sales I simply multiplied price and quantity =L2*E2
5. IF statement to enhance value format:= I wanted to see the full names of the Coffee Types so I started a new column (Coffee Type Name) and created an IF statement as seen here. =IF(I2=”Rob”,”Robusta”,IF(I2=”Exc”,”Excelsa”,IF(I2=”Ara”,”Arabica”,IF(I2=”Lib”,”Liberica”))))
I created a similar column and IF statement for Roast Types: =IF(J2=”M”,”Medium”,IF(J2=”L”,”Light”,IF(J2=”D”,”Dark”)))
6. Formatted Dates and Currency: I changed the dates format to dd-mmm-yyyy, which replaced month number with three letter abbreviations. I also formatted the currency to USD.
7. Checked For Duplicates: While cleaning would usually be a large part of this process, this particular dataset was already in good condition. I did use the remove duplicates feature to check and there were none found.
8. Convert Range to Table: In order to have any changes I make to the dataset populate more easily to my pivot tables, I converted the range into a table.
9. Sale-over-Time Pivot Table + Formatting: To create the first pivot table I clicked insert table and selected the entire orders table.
Since I wanted to measure sales of specific coffee types over time, I dragged “Sales” into the value section, “Coffee Type Name” into columns, and “Date” into the rows section. Then I simplified the dates by grouping them by month and year and the sales by rounding the numbers to the nearest whole.
I inserted a line chart and edited colors and axis titles.
10. Insert Timeline + Formatting: I added a date slider and customized it to my liking as well. Check out the before and after!
11. Insert Slicers + Formatting: I added slicers for the Coffee Roast Name, Size and Loyalty. In order to add the Loyalty slicer I needed to create a column for it in my Orders table using XLOOKUP on the Customer sheet.
To format the sliders with this design I created my own format, by selecting on at the bottom of the slicer format drop-down.
12. Sales per country and Top 5 Customers Pivot Tables: I copied over the sheet from the sales pivot table and modified it by dragging “Country” into rows and leaving “Sales” in the value section. Then I formatted it to match the style of my other graph.
I followed the same steps for my Top 5 Customers pivot table, except I put “Customer Name” in the rows section. I also filtered the values to only show the top 5 names.
13. Building the Dashboard: To create the dashboard I copied over my graphs into a new sheet and snapped them to the grid by holding alt as I moved them. I adjusted the height and width of the cells in-between my dashboard elements and then removed the gridlines for a clean look. The results are displayed at the top of this article!
Thanks for reading, see my profile for more Data Analysis projects!
