Excel is used a lot for small to medium data exchange and manipulation tasks. While there are more robust or specialized tools available for many specific tasks, Excel is a tool which gets used because it's widely available and generally adaptable to many situations or purposes. This flexibility has benefits and drawbacks. While Excel can be used for a quick list or some data manipulation, failure to follow good data structuring and management principles in Excel can lead to data which are unweildy, difficult to maintain, and difficult to analyze. This exercise demonstrates how to organize and analyze a dataset using named tables, pivot tables, and pivot charts with common controls (called slicers in Excel).
- Watch the optional preparation tutorials.
- Create the Excel dashboard by following along with the tutorial.
- Turn in your completed Excel dashboard.
- If you're not familiar with general data cleaning and formatting principles necessary to create properly formatted named tables in Excel, you may want to watch these tutorials first.
- Optional data cleaning tutorial (53 min): https://www.youtube.com/watch?time_continue=6&v=EwmuaqnoaKs&feature=emb_logo
- Optional get data ready for pivot tables tutorial (11 min): https://www.youtube.com/watch?v=FQH9VPLpfwE&list=PLG9A6ovzPqX4uTMILULzW_MASdnH8j_1z&index=13&t=280s
- Creating an interactive dashboard tutorial (1 hour 2 min): https://www.youtube.com/watch?v=3qiHGXEWOe4&list=PLG9A6ovzPqX4uTMILULzW_MASdnH8j_1z&index=13&t=1006s
- There is a link below the video to download the data file. You should download and open the Excel file before you start the tutorial. Download Excel file: https://www.simonsezit.com/article/dashboards-in-excel-using-pivot-tables-pivot-charts-and-slicers/
- Submit the file in Canvas.