Overview of Our Mini Dashboard

Before we start, here’s what our mini dashboard will look like:

• A clean and visually appealing interface.

• Interactive elements like filters.

• Summarized insights with charts and data analysis.

We’ll cover concepts like Power Query, data cleaning, and basic dashboard creation. By the end, you’ll also know how to use Excel for practical data analysis projects.

Step 1: Prepare Your Dataset

Start by opening your dataset. For this tutorial, our dataset contains the following columns:

• Date

• Salesperson

• Region

• Product

• Units Sold

• Sales Amount

Let’s align and clean the data:

1. Select All Data: Click the top-left corner box to highlight the entire dataset.

2. Auto-Adjust Columns: Double-click any column boundary to auto-fit the content.

3. Freeze Top Row: Go to the View tab and select Freeze Top Row for easy navigation.

4. Add Filters: In the Data tab, enable filters to sort and analyze the data easily.

Download a sample dataset here and practice these steps alongside this guide.

Step 2: Remove Duplicates

Duplicate data can lead to errors in analysis. Let’s clean it:

1. Select the Dataset: Highlight your entire dataset.

2. Go to Data Tab: Click on Remove Duplicates.

3. Configure Settings: Check the box for “My Data Has Headers” and click OK.

4. Result: Excel will display how many duplicates were removed and how many unique rows remain.

Step 3: Handle Missing Values

Handling missing data is crucial for accurate analysis. Here’s how to address blank cells:

For Text Columns (e.g., Salesperson, Region, Product):

• Replace missing values with “Unknown”:

  1. Select the relevant columns.

  2. Go to Find & Select > Replace.

  3. Leave “Find what” blank and type “Unknown” in “Replace with”.

  4. Click Replace All.

For Numeric Columns (e.g., Units Sold):

• Replace missing values with the average:

  1. Use the AVERAGE formula to calculate the average for the column. Example: =AVERAGE(B2:B200).

  2. Replace blank cells with this value by using Find & Replace.

For Entire Rows with Multiple Missing Values:

If a row has critical missing data (e.g., blank Date, Salesperson, and Region), delete it:

• Filter rows with blank values and delete them by selecting Delete Row.

Step 4: Summarize Data

Now, let’s calculate some key metrics:

1. Total Sales Amount:

   • Use the formula =SUM(F2:F200) to calculate the total sales.

   • Highlight the result for clarity.

2. Total Units Sold:

   • Use =SUM(E2:E200) to calculate total units sold.

3. Sales by Region:

   • Use SUMIF to calculate sales for each region: Example: =SUMIF(C2:C200, "North", F2:F200).

4. Product-Wise Units Sold:

   • Use SUMIF for each product: Example: =SUMIF(D2:D200, "Product A", E2:E200).

Step 5: Create Visualizations

Dashboards are incomplete without charts. Let’s create the following:

1. Bar Chart for Region vs. Sales:

• Select the summarized region data.

• Go to Insert > Bar Chart and choose a suitable style.

2. Pie Chart for Product vs. Units Sold:

• Select the summarized product data.

• Go to Insert > Pie Chart.

3. Line Chart for Date vs. Sales Amount:

• Select the Date and Sales Amount columns.

• Go to Insert > Line Chart.

Step 6: Add Interactive Features

To make the dashboard user-friendly:

• Add slicers for filtering data.

  • Go to Insert > Slicer and choose fields like Region or Product.

• Use conditional formatting to highlight trends.

Step 7: Final Touches

• Arrange your charts and tables neatly.

• Add titles and labels for clarity.

• Save your work and test the interactivity.

Conclusion

Congratulations! You’ve successfully created a mini dashboard in Excel. This is a simplified version, but the same principles can be used for larger, more complex projects. Don’t forget to subscribe to my YouTube channel for more tutorials, download the dataset linked above to practice, and let me know in the comments how your dashboard turned out!