This article provides a step-by-step tutorial on how to create an interactive dashboard in Excel using the Superstore dataset from Tableau. It covers topics such as creating pivot tables, pivot charts, maps, slicers, and formatting techniques to enhance the aesthetics and readability of the dashboard. The tutorial aims to help users develop their own interactive dashboards in Excel.
Rephrase:
The author provides a tutorial on how to create an interactive dashboard using Excel, which is a widely used data engineering and analytics software. They emphasize that the skills learned in Excel can be applied to any domain or software as long as a strong foundation is built. The tutorial includes step-by-step instructions on creating pivot tables, pivot charts (line charts and bar charts), and a map, as well as consolidating everything into a single dashboard. Additionally, the role of slicers in filtering and connecting the different components of the dashboard is explained. The author also touches on the importance of formatting and aesthetics to capture the attention of the audience. Overall, the tutorial aims to teach readers how to develop their own interactive dashboards in Excel by explaining the main concepts and walking through the necessary steps. By following the instructions, readers can create visualizations using a sample dataset accessible through Tableau’s website.
Action Items:
1. Download the USA Superstore dataset from Tableau’s website and save it in the latest .xlsx format. (All)
2. Create two pivot tables to calculate the total profits and sales. Drag the Profit field to Values for the first pivot table and the Sales field for the second pivot table. (All)
3. Create two pivot charts for the line charts showing the monthly trends of profit and sales. Select the Sales field under Values and the Order Date under Axis. Convert the bar chart to a line chart. Repeat the same steps for the Monthly Profits chart. (All)
4. Create two bar charts for the profit and sales per product segments. Change the chart type to a horizontal bar chart. (All)
5. Create a map by linking the data from a Pivot Table with the row using the States field and values using Profit. Rename the sheet as Map. (All)
6. Create a new sheet called Dashboard and consolidate the pivot tables, pivot charts, and map into this sheet. (All)
7. Create slicers to filter the charts and values on the dashboard. Connect the slicers to the pivot tables and charts using the Region, Year, and Ship Mode fields. Prevent cell width dynamic changes on slicing. (All)
8. Format the dashboard for aesthetics and readability. Modify the map colors to be more intuitive. Increase the font size and change the number format to currency for the sum of profit and sales. Add a vertical text title at the side. Remove unnecessary components from the line chart and format the x-axis. Remove unnecessary components from the bar charts, increase the bar width, add data labels within each bar, and remove the gridlines. Adjust the chart sizes and titles. (All)
