Excel Data Visualization & Dashboards: Excel Reporting

Think creatively to solve Excel problems beyond rote methods, using boolean logic, nested ifs, and alternative approaches like the int function to map values to multipliers.

learn why to develop differently in Excel, adopting scalable formulas over legacy VBA, reducing errors, speeding development, and embracing data science principles for robust dashboards.

Learn how to insert and add the camera tool to the Quick Access Toolbar in Excel, including steps to access options, choose all commands, and add the camera tool.

Explains using the camera tool in Excel to snapshot a cell range, produce live infographics and charts, and keep visuals updated as data changes.

this lecture covers development principles for excel dashboards: transparency, developmental memory, and efficacy, emphasizing clear error handling, readable formulas, and goal-driven design to inspire confidence.

Learn practical formula editing tips to work faster, read formulas more easily, and write semantically meaningful Excel formulas using named ranges, whitespace, and evaluate formula tools.

Explore how Excel builds the calculation chain and performs full recalculation, why volatile functions slow workbooks, and how to avoid slowdowns using efficient functions instead of pivot tables or VBA.

Design for readability by structuring formulas and using natural fits. Prefer lookup functions over nested ifs, use named ranges, and document your approach.

Explore how lookup functions identify and retrieve related data in Excel, using leftmost keys, and use index and match to locate and return precise values.

Demonstrate how vlookup retrieves a price from a coffee menu by selecting the table array, choosing the column index, and using false for an exact match.

Demonstrates how to use index match in excel reporting to retrieve prices from coffee menu data table, locking references and creating a lookup for items like latte, decaf, and sizes.

Combine VLOOKUP and MATCH to build a dynamic, automatically updating dashboard from raw data, with exact matches, left-side lookups, and no VBA or pivot tables.

Array formulas perform calculations across ranges and can return multiple values, activated by ctrl-shift-enter. Unlike regular formulas, they use a reverse funnel logic to output several results from one input.

Learn to build a dynamic dashboard component in Excel using array formulas with index/match to pull monthly tornado counts for a selected year via a dropdown, with an updating chart.

Learn to use array formulas with a practical four-step approach: determine the return range, place the formula in the top-left cell, drag to size, and activate with control+shift+enter.

Explore how Excel tables differ from pivot tables, data tables, and auto filters, and learn how automatic sizing and structured references use semantic names for formulas.

Insert an Excel table from your data, assign a meaningful name, and use structured references for easy counting and dynamic updates as you add records.

Learn to use Excel tables to manage datasets, set headers, apply 16-digit length tests, create boolean columns like incorrect credit card, and auto-fill calculations across the table.

Explore how Excel tables enable dynamic charts that auto-update as you add data, avoiding manual updates and enabling responsive dashboards.

Change the Excel table name to something meaningful to avoid confusion, keep column names reasonable, and keep the formats in check to prevent slowdowns with large tables and structured references.

Discover sorting in Excel using small and large in tables, then apply index and match to pull the top ten and bottom ten tornado years by total tornadoes for insights.

Demonstrate filtering with boolean functions and aggregating via the dot product to drive dashboards; for example, select years 1950–1955 by setting them true and multiplying by the target values.

Learn to filter on Excel tables and build a dynamic dashboard with year-based filters, a data validation dropdown, and conditional formatting that creates a heat map and an interactive view.

Learn to perform aggregation with Excel tables using the sumproduct function to sum totals across filtered years and quarters, mirroring booleans and conditional logic for dashboards.

Build interactive dashboards in Excel using tables and functions, with data validation dropdowns that auto-update results and rely on five or six formulas.

Enable the developer tab to access form controls and VBA macros for interactivity in Excel, with steps to customize the ribbon and add the tab in Office 2010 and newer.

Explore form controls in Excel to build lightweight, cell-linked interfaces, focusing on combo box, list box, checkbox, spinner, and option button while avoiding ActiveX controls due to complexity and bugs.

Explore Excel form controls—combo box, list box, scroll bar, spinner, and checkbox—via the developer tab, format controls, and cell links, with no VBA.

Explore form controls like scrollbar, spinner, listbox, combo box, and checkbox. Connect them to cells to drive interactivity without macros, and access properties via format control or the developer tab.

Learn to build a no-vba, scrollable table in Excel using a form control scroll bar linked to a parameter cell and index data to pull year and total tornadoes.

Learn formula driven development in Excel, preferring formulas over VBA, avoiding disconnected data and run buttons, and building lightweight, dynamic information transformation and presentation.

Explore the information transformation presentation (ITP) framework that turns backend data into transformed calculations and finally into dashboards and charts using Excel tables, formulas, and interactive controls.

Learn to build an interactive legend in Excel using checkboxes to filter a line chart of country populations, and explore setup, formatting, and maintenance across three acts.

Learn to build an interactive legend in Excel by creating a transformation table and checkboxes to toggle country series on a line chart, enabling dynamic show or hide of data.

Build an interactive legend in Excel dashboards by moving charts to a presentation layer, linking checkboxes to data with named ranges and IF formulas, and refining axes and titles.

Learn how to maintain an interactive legend by naming checkboxes, using the selection pane, and formatting to track and edit dashboard elements. Reuse dynamic setups across dashboards by copying and pasting, and confidently manage toggle labels and on/off states with selection controls.

Create an interactive chart toggle to switch between two related visual narratives on a single chart, revealing an underlying business process to inform better decisions.

Learn to build an interactive chart toggle in Excel that controls savings data with a checkbox, showing delta between original costs and new costs in a stacked area chart.

Format an interactive toggle in Excel to create cohesive visuals with color, borders, and axis changes. Link the chart title to a cell via an if formula to reflect savings.

Maintain the interactive toggle to adjust chart colors and series order, using select data and the name manager to update the show savings range without touching raw data.