Microsoft Excel 2021365 - Beginner to Advanced - Assembling a Dashboard - Part 1

The video tutorial covers the basics of designing an interactive dashboard using Excel. It starts with setting up the dashboard layout using shapes and text boxes, followed by creating a combo box for year selection. The tutorial then explains how to use Excel functions like UNIQUE and INDEX to manage data. It also covers data preparation for charts using SUMIFS and advanced chart data setup using COUNTIFS and SUMIFS.

Why is it important to plan the information to be displayed on a dashboard before starting calculations?

• To avoid unnecessary data collection

• To ensure the dashboard meets the intended purpose

• To ensure the dashboard looks aesthetically pleasing

• To save time during the design process

What is the purpose of the combo box drop-down in the dashboard?

• To change the color scheme of the dashboard

• To select the year for which data is displayed

• To add new data entries

• To switch between different dashboards

How can you enable the Developer ribbon in Excel?

• By right-clicking on the ribbon and selecting 'Show Developer'

• By installing a plugin

• By going to File > Options > Customize Ribbon and checking Developer

• By using a keyboard shortcut

Which Excel function is used to extract a list of unique years from a data source?

• UNIQUE

• VLOOKUP

• FILTER

• SORT

What is the purpose of sorting the list of unique years?

• To make it easier to read

• To prepare for a pivot table

• To ensure the years are in chronological order

• To match the order of another dataset

What properties need to be set for the combo box to function correctly on the dashboard?

• Data validation rules

• Border style and fill color

• Input range and cell link

• Font size and color

How does the SUMIFS formula help in preparing data for the column chart?

• It counts the number of entries

• It sums data based on multiple criteria

• It filters data based on a single criterion

• It averages data across all entries

What additional criterion is used in the SUMIFS formula for the column chart?

• The customer type

• The sales region

• The selected year

• The product category

Which formula is used to count the number of orders by product?

• AVERAGEIFS

• COUNTIFS

• COUNT

• SUMIFS

What is the main difference between the SUMIFS calculations for COGS and Profit?

• The criteria used

• The use of absolute references

• The sum range selected

• The order of operations