Article:2: Observations on Creating Dynamic Excel Dashboards

I have been learning and working on various methods for data analysis and data visualization to manage large amounts of data and create actionable reports. To simplify a complex database, I created a Dynamic Dashboard in Microsoft Excel to display the annual report of an organization with approximately 7K+ data points, to identify trends in the fiscal year and the profit trends of top sales contributors.

Tools that I used to make a simple dashboard for a beginner who wants to delve into Excel for a better understanding and mastery.

Tools that I used for this dynamic dashboard:

A. Slicer

In MS Excel, a slicer is a visual tool that allows you to filter data in a PivotTable, PivotChart, or even in a regular table or range. Slicers provide a user-friendly way to interact with and analyze your data.

When you create a slicer, it typically appears as a separate window containing buttons or tabs representing different filter options. Each button corresponds to a unique value or category in the data, and clicking on a button filters the data to display only the information related to that particular value or category.

Slicers are often used in combination with PivotTables and PivotCharts, allowing users to easily explore and analyze large sets of data by selecting specific criteria or categories. They are particularly useful for creating interactive and dynamic dashboards in Excel. Slicers enhance the user experience by providing a visually intuitive way to filter and navigate data without the need to directly manipulate the underlying tables or charts.

B. Line with Marker:

In Microsoft Excel, a line chart with markers is a graphical representation of data that combines a line connecting data points with individual markers placed on those points. The line visually represents the trend or progression of data, while the markers highlight specific data points. This combination makes it easier to interpret and analyze the information presented in the chart.

C. Subtotal Function:

the SUBTOTAL function is used to perform calculations on a range of data and display the result in a specified location. The key feature of SUBTOTAL is its ability to work with filtered data. It ignores any rows in the range that are hidden due to filtering, making it useful for calculating subtotals in filtered data sets.

Common Function Numbers:

Here are some common function numbers used with the SUBTOTAL function:

• 1: AVERAGE
• 2: COUNT
• 3: COUNTA (counts non-empty cells)
• 9: SUM
• 10: PRODUCT
• 101: AVERAGE (ignores hidden values)
• 102: COUNT (ignores hidden values)
• 103: COUNTA (ignores hidden values)
• 109: SUM (ignores hidden values)
• 110: PRODUCT (ignores hidden values)

These function numbers allow you to choose the type of calculation you want to perform while accounting for filtered data in Excel.

I used the Subtotal function and included the necessary parameters within the parentheses:

Sub total Sum( for total profit):

Sub total Max (For maximum Sale value ):

Sub total Min ( For Minimum Sale value):

Sub total Average (For Average sale value or ASPU(Avg. Sales Per unit ):

The final dashboard was created after all functions and tools were inserted:

Thank you for checking out this article and let me know your thoughts on this and the areas of improvement for me!

Follow me for more analytics content.

#businessanalyst #MSExcel #dashboard #ExcelBeginner