Skip to content
  • There are no suggestions because the search field is empty.

Build an Impressive Excel Dashboard: Track Bike Sales with Ease

Picture of Michael Foutz

Learn how to create an impressive Excel dashboard to track bike sales with ease. This step-by-step guide will teach you how to clean up your data, create pivot tables, visualize data with charts, and assemble it all into a dashboard with slicers for easy filtering.

 

Step 1: Clean Up Your Data

Before we start creating our dashboard, we need to make sure our data is clean and ready to use. Here’s what to do:

  1. Open your Excel file with the bike sales data. (Download here)
  2. Check for duplicates by selecting your data range, going to the "Data" tab, and clicking on "Remove Duplicates".
    excel_remove_duplicates
  3. Expand abbreviations: If there are any abbreviations in columns like 'Occupation' or 'Region', replace them with full words for clarity.
  4. Group ages: It might be helpful to categorize ages into brackets (e.g., 18-25, 26-35, etc.) for better analysis. You can do this by adding a new column and using the IF function to assign each age to a bracket.

Step 2: Create Pivot Tables

Pivot tables will help us summarize and analyze our cleaned data.

  1. Select your data range, go to the "Insert" tab, and click "Pivot Table".
    Pivot_table
  2. Choose where you want the Pivot Table to be placed (new worksheet is usually best).
  3. Drag and drop fields into the Pivot Table fields area:
    • For Rows, you might add 'Marital Status', 'Gender', or 'Age Bracket'.
    • For Values, add numerical data like 'Income' or 'Number of Cars Owned'. Use "Sum" or "Average" as needed.
    • You can also use 'Bike Purchase Status' to see how other factors relate to bike purchases.

Step 3: Visualize Data with Charts

Now, we'll turn our pivot table data into visual charts.

  1. Select your pivot table and go to the "PivotTable Analyze" tab.
  2. Click on 'PivotChart' to select a chart type. Bar charts are great for comparing categories, and line graphs are perfect for showing trends over time.
  3. Customize your chart: Adjust titles, labels, and colors to make your chart clear and appealing.

pivot_table

Step 4: Assemble the Dashboard

With your charts ready, it's time to create your dashboard.

  1. Decide on the layout: Determine how you want to arrange your charts and tables on a new Excel sheet.
  2. Copy and paste your charts and pivot tables onto this new sheet.
  3. Insert slicers for interactive filtering: Go to the "Insert" tab, click "Slicer", and choose the fields you want to filter by. This could be 'Region', 'Age Bracket', or 'Marital Status'.
  4. Arrange your slicers around your charts to make it user-friendly.

Additional Tips:

  • Experiment with different chart types and pivot table configurations to find what best represents your data.
  • Use conditional formatting in your pivot tables to highlight key data points, like high sales regions or age groups with high bike purchases.
  • Regularly update your dashboard as new data comes in to keep it current.

By following these steps and exploring the features of Excel, you will be able to create a dynamic and informative dashboard tailored to your bike sales data. Keep in mind that practice and experimentation are crucial in mastering Excel dashboards.