Excel Dashboard Fundamentals – Practice Questions with Solutions

Introduction

An Excel Dashboard brings important data, calculations, KPIs, charts, and filters together in one easy-to-read view. Instead of checking a large worksheet manually, a dashboard helps users understand key information quickly. In this chapter, you will practice the fundamentals of creating Excel dashboards, including preparing dashboard data, creating KPI cards, building summary tables, adding charts, using slicers, and designing a simple interactive dashboard. Excel Dashboard Fundamentals Practice Questions with Solutions to help you build concepts.

Question 1: Prepare an Excel Table for a Sales Dashboard

Problem Statement

A company wants to create a sales dashboard. First, convert the following raw sales data into a structured Excel Table that can later be used for formulas, charts, and filters.

Excel Data

Excel Solution

1. Enter the complete dataset in Excel.
2. Select the complete range.
3. Press Ctrl + T.
4. Enable My table has headers.
5. Click OK.
6. Rename the table to:

SalesData

Expected Result

The raw sales data becomes a structured Excel Table that can be used as the main source for the dashboard.

Concepts Covered

• Excel Tables
• Structured data
• Dashboard source data
• Table naming

Question 2: Create KPI Cards for a Dashboard

Problem Statement

Create four important KPI values from the following sales data:

• Total Sales
• Total Units
• Total Orders
• Average Sales per Order

Excel Data

Excel Formulas

Total Sales:

=SUM(D2:D11)

Total Units:

=SUM(C2:C11)

Total Orders:

=COUNTA(A2:A11)

Average Sales per Order:

=AVERAGE(D2:D11)

Excel Solution

1. Enter the sales data.
2. Create four separate KPI cells.
3. Apply the formulas.
4. Add labels such as Total Sales, Total Units, Total Orders, and Average Order Value.
5. Increase the font size of the KPI values.
6. Place the KPI cells at the top of the dashboard.

Expected Result

Concepts Covered

• KPI cards
• SUM
• COUNTA
• AVERAGE
• Dashboard metrics

Question 3: Create a Monthly Sales Trend Chart

Problem Statement

Create a dashboard chart that shows how sales changed during the year.

Excel Data

Excel Solution

1. Enter the monthly sales data.
2. Select both columns.
3. Go to Insert → Line Chart.
4. Add the chart title:

Monthly Sales Trend

5. Format the vertical axis as currency or numbers.
6. Add data labels if they improve readability.
7. Place the chart in the dashboard.

Expected Result

A line chart displays the monthly sales trend and makes changes in sales easy to identify.

Concepts Covered

• Line charts
• Sales trends
• Chart titles
• Dashboard visualization

Question 4: Create a Product Performance Chart

Problem Statement

A company wants to compare sales generated by different products.

Create a suitable dashboard chart.

Excel Data

Excel Solution

1. Enter the product data.
2. Select Product and Sales.
3. Go to Insert → Bar Chart.
4. Select a horizontal bar chart.
5. Add the title:

Sales by Product

6. Sort the data by Sales if required.
7. Add data labels.
8. Place the chart on the dashboard.

Expected Result

The dashboard displays sales performance for each product in a visually comparable format.

Concepts Covered

• Bar charts
• Product comparison
• Data labels
• Dashboard charts

Question 5: Create a Regional Sales Summary

Problem Statement

Create a summary table showing total sales and total orders for each region.

Excel Data

Excel Solution

1. Enter the regional data.
2. Create a summary table.
3. Select the Region and Sales columns.
4. Insert a column chart.
5. Add the title:

Sales by Region

6. Format the Sales values appropriately.
7. Place the summary table and chart together on the dashboard.

Expected Result

Concepts Covered

• Regional analysis
• Summary tables
• Column charts
• Dashboard sections

Question 6: Calculate Profit and Profit Margin for Dashboard KPIs

Problem Statement

A company wants to display Total Revenue, Total Cost, Total Profit, and Profit Margin as dashboard KPIs.

Excel Data

Excel Formulas

Total Revenue:

=SUM(B2:B7)

Total Cost:

=SUM(C2:C7)

Total Profit:

=SUM(B2:B7)-SUM(C2:C7)

Profit Margin:

=B10/B8

Assuming Total Profit is in B10 and Total Revenue is in B8.

Excel Solution

1. Enter the revenue and cost data.
2. Calculate Total Revenue.
3. Calculate Total Cost.
4. Calculate Total Profit.
5. Calculate Profit Margin.
6. Format Profit Margin as Percentage.
7. Display these values as KPI cards.

Expected Result

Concepts Covered

• Dashboard KPIs
• Profit calculation
• Profit Margin
• Percentage formatting

Question 7: Add an Interactive Region Slicer

Problem Statement

Create an interactive dashboard filter that allows users to display sales for a selected region.

Excel Data

Excel Solution

1. Select the complete dataset.
2. Press Ctrl + T.
3. Confirm the table.
4. Select a cell inside the table.
5. Go to Table Design → Insert Slicer.
6. Select Region.
7. Click OK.
8. Click different region buttons in the slicer.
9. Observe how the table responds to the selected filter.

Expected Result

A Region slicer is available for interactive filtering.

Concepts Covered

• Slicers
• Interactive filtering
• Excel Tables
• Dashboard interaction

Question 8: Create a Sales vs Target KPI Section

Problem Statement

A company has a monthly sales target. Create a dashboard section that shows Actual Sales, Target Sales, Variance, and Achievement Percentage.

Excel Data

Excel Formulas

Total Target:

=SUM(B2:B7)

Total Actual Sales:

=SUM(C2:C7)

Variance:

=C10-B10

Achievement Percentage:

=C10/B10

Excel Solution

1. Calculate Total Target.
2. Calculate Total Actual Sales.
3. Calculate Variance.
4. Calculate Achievement Percentage.
5. Format Achievement Percentage as %.
6. Display the four results as dashboard KPIs.
7. Create a column chart comparing Target Sales and Actual Sales.

Expected Result

Concepts Covered

• KPI calculations
• Sales target
• Variance
• Achievement percentage
• Target vs Actual chart

Question 9: Create a One-Page Dashboard Layout

Problem Statement

Create a simple one-page dashboard using the following business data.

The dashboard should contain:

• KPI cards
• Monthly Sales chart
• Regional Sales chart
• Product Sales chart

Excel Data

Excel Solution

Create the dashboard approximately in this structure:

------------------------------------------------------------
                    SALES DASHBOARD
------------------------------------------------------------

   TOTAL SALES       TOTAL PROFIT       TOTAL ORDERS
   ₹1,077,000          ₹330,000              2,215

------------------------------------------------------------

                  MONTHLY SALES
                    [Chart]

------------------------------------------------------------

       SALES BY REGION       SALES BY PRODUCT
          [Chart]                [Chart]

------------------------------------------------------------

Dashboard Design Guidelines

• Keep the dashboard on one worksheet.
• Keep KPI cards at the top.
• Place the most important chart below the KPIs.
• Keep charts aligned.
• Use consistent number formatting.
• Avoid unnecessary decorations.
• Keep enough white space between sections.
• Do not overcrowd the dashboard with too many charts.

Expected Result

A clean one-page dashboard containing KPI cards and multiple charts.

Concepts Covered

• Dashboard layout
• KPI cards
• Chart placement
• Visual hierarchy
• One-page dashboard design

Question 10: Build a Complete Interactive Sales Dashboard

Problem Statement

Create a basic interactive dashboard using the following complete dataset.

The dashboard should allow users to analyze:

• Sales
• Expenses
• Profit
• Orders
• Region
• Product
• Month

Excel Data

Excel Formulas

Total Sales:

=SUM(D2:D17)

Total Expenses:

=SUM(E2:E17)

Total Profit:

=SUM(D2:D17)-SUM(E2:E17)

Total Orders:

=SUM(F2:F17)

Profit Margin:

=(SUM(D2:D17)-SUM(E2:E17))/SUM(D2:D17)

Excel Solution

1. Create the Excel Table

Select the complete dataset and press:

Ctrl + T

Name the table:

DashboardData

2. Create KPI Cards

Create KPI cards for:

• Total Sales
• Total Expenses
• Total Profit
• Total Orders
• Profit Margin

3. Create Dashboard Charts

Create charts for:

• Sales by Month
• Sales by Region
• Sales by Product
• Profit by Month

4. Add Slicers

Add slicers for:

• Region
• Product
• Month

5. Arrange the Dashboard

Use a layout similar to:

------------------------------------------------------------
                    SALES DASHBOARD
------------------------------------------------------------

 TOTAL SALES    TOTAL EXPENSES    TOTAL PROFIT    ORDERS
 ₹________      ₹________         ₹________       _______

                    PROFIT MARGIN
                       ____%

------------------------------------------------------------

                   SALES BY MONTH
                       [Chart]

------------------------------------------------------------

      SALES BY REGION       SALES BY PRODUCT
          [Chart]                [Chart]

------------------------------------------------------------

                   PROFIT BY MONTH
                       [Chart]

------------------------------------------------------------

 Region          Product          Month
 [Slicer]        [Slicer]         [Slicer]

------------------------------------------------------------

6. Format the Dashboard

Apply consistent formatting to:

• KPI values
• Chart titles
• Currency values
• Percentage values
• Slicers
• Section headings
• Table headings

Keep the dashboard clean and easy to read.

Expected Result

A complete interactive sales dashboard is created with KPI cards, charts, and slicers that allow users to analyze the business data from different perspectives.

Concepts Covered

• Complete dashboard creation
• KPI cards
• Excel Tables
• Charts
• Slicers
• Interactive filtering
• Dashboard layout
• Data visualization
• Dashboard formatting

Key Takeaways

• An Excel Dashboard combines important information into one visual report.
• Clean and structured data is the foundation of a useful dashboard.
• Excel Tables make dashboard data easier to manage.
• KPI cards highlight important business metrics.
• Line charts are useful for showing trends over time.
• Bar and column charts are useful for comparing categories.
• Slicers allow users to interactively filter dashboard data.
• Target vs Actual analysis can be displayed using KPIs and charts.
• A dashboard should focus on important information instead of displaying every available field.
• Consistent formatting makes dashboards easier to understand.
• A one-page dashboard should have a clear visual hierarchy.
• Charts should be selected according to the type of information being presented.
• Interactive dashboards allow users to explore data without manually changing the source data.

FAQs

1. What is an Excel Dashboard?

An Excel Dashboard is a visual report that combines KPIs, charts, tables, and filters into a single view.

2. What is the purpose of an Excel Dashboard?

An Excel Dashboard helps users quickly understand important information and analyze data without manually reviewing a large dataset.

3. What should an Excel Dashboard contain?

A basic dashboard can contain KPI cards, charts, summary tables, and interactive filters such as slicers.

4. What are KPI cards?

KPI cards are dashboard elements that display important measurements such as Total Sales, Total Profit, Total Orders, or Profit Margin.

5. Why should dashboard data be converted into an Excel Table?

An Excel Table provides a structured data source and makes it easier to manage formulas, filters, charts, and additional records.

6. What are slicers used for?

Slicers provide clickable filtering options for fields such as Region, Product, Month, Department, or Salesperson.

7. Which charts are commonly used in Excel Dashboards?

Line charts, column charts, bar charts, doughnut charts, and combo charts are commonly used depending on the type of information being displayed.

8. Should an Excel Dashboard contain many charts?

Not necessarily. A dashboard should contain the charts and KPIs that are useful for the intended analysis. Too many visual elements can make a dashboard difficult to read.

9. Can an Excel Dashboard be interactive?

Yes. Slicers, filters, PivotTables, PivotCharts, formulas, and other Excel features can be combined to create interactive dashboards.

10. Can Excel Dashboards be used for business reporting?

Yes. Excel Dashboards can be used to summarize sales, expenses, profits, targets, orders, customers, inventory, and other business data.

Written by Shubhranshu Shekhar, who has trained 20000+ students in coding.

Rishabh Kumar