Excel - Creating Dashboards

Name: Excel - Creating Dashboards
Rating: 4.5 (8 reviews)

Learn Top Tips For Querying And Displaying Information In Microsoft Excel

Created byBigger Brains

Last updated 11/2023

English

English [Auto],

What you'll learn

Use range names in formulas
Create nested functions
Work with forms, form controls, and data validation
Use lookup functions
Combine multiple functions
Create, modify, and format charts
Use sparklines, trendlines, and dual-axis charts
Create a PivotTable and analyze PivotTable data
Present data with PivotCharts
Filter data with slicers

Course content

1 section • 20 lectures • 2h 49m total length

Creating Range Names10:17
Learn to simplify Excel dashboards by creating and managing named ranges, using create from selection and top row, and adjusting scope to leverage in formulas for clearer data access.
Using Defined Names in a Formula4:17
Use defined names and named ranges in Excel formulas to calculate income earned from number of books sold times sell price, then copy down, format as currency, and save.
Using Specialized Functions, Part 17:18
Explore how to use specialized Excel functions, like countif and its multi-criteria variants, to count authors with five or fewer years and prepare dashboard data.
Using Specialized Functions, Part 211:58
Master averaging with the averageif function, define ranges and criteria (sell price over 5.99), and build dashboards while using the insert function and understanding manual vs automatic calculation.
Applying Data Validation12:28
Enforce data consistency in Excel by using data validation with drop-down lists for genre and state, plus numeric agent code validation, with input messages and stop alerts.
Using a Data Form5:49
Learn to input, edit, locate, and add new records in Excel using the data form, accessed via the Quick Access Toolbar, with fields and criteria.
Adding Form Controls9:26
Learn to add form controls in Excel using the Developer tab to build an author dashboard, including a combo box, data sources, and properties like cell link.
Using Lookup Functions11:39
Master lookup functions to drive an author dashboard in Excel. Create a named range, use index and VLOOKUP, and a combo box to pull income and books sold.
Combining Functions11:10
Learn to build nested functions in Excel to calculate author bonuses on a dashboard, using if, and, vlookup, and sum with income, years under contract, and royalty data.
Creating a Chart7:19
Learn to build clean Excel dashboards by designing simple, goal-driven charts with minimal distractions, highlighting meaningful data like yearly sales and format breakdown.
Formatting and Modifying Charts13:56
Format and customize Excel charts by adding descriptive titles, axis labels, and legends; tailor numbers to display in millions, adjust colors, borders, and chart elements for clear dashboards.
Dual Axis Charts5:38
Create a dual axis combo chart in Excel, placing unit sales on the secondary axis, using a line for sales and columns for earnings, with scales in billions and millions.
Forecasting with Trend Lines8:03
Add trend lines to a dual-axis Excel chart for earnings and unit sales, forecast five years using linear trend lines, and label it actual versus forecasted sales and units.
Creating Chart Templates2:58
Save charts as templates to reuse layouts in files or spreadsheets, then apply the layout to new data using chart templates (crt x) like the 5 year forecast dual axis.
Creating Sparklines7:18
Learn to use sparklines in Excel to compare rows with tiny in-cell charts, choosing line, column, or win/loss types and marking highs and lows.
Inserting Pivot Tables9:47
Build a dynamic pivot table to summarize sales data and answer questions on your dashboard by dragging fields into rows, columns, and values, then compare by author and market.
Analyzing Pivot Table Data, Part 17:42
Master pivot tables to show units sold by market and genre, using filters, sorts, and percentage of column totals for quick insight.
Analyzing Pivot Table Data, Part 28:36
analyze and visualize pivot table data to compare market earnings by genre, explore author and book-level sales, and format electronic versus print earnings with percentage and currency displays.
Presenting Data with Pivot Charts6:47
Create a pivot chart from a pivot table, customize the axis and legend, apply filters like genre and market, and refresh to sync updates with the dashboard.
Filtering Data with Slicers6:53
Learn how to use slicers in Excel to apply permanent filters to pivot tables for faster dashboards. Filter by author, genre, format, and market with easy clear filters.
Knowledge Check

Requirements

A copy of Microsoft Excel is recommended.

Description

Get more from Excel and learn to use Forms, Lookup Functions, Charts, PivotTables, and Slicers to turn data into answers

Crunching numbers is what Microsoft Excel does best – but how do you use those numbers to get the answers you need? This course will show you how to use advanced Excel features to turn massive amounts of data into visual, customizable dashboards.

The ability to easily query and display information from your Excel data is a helpful tool for reporting and decision making, and this course will demonstrate five advanced Excel features (Forms, Lookup Functions, Charts, Pivot Tables, and Slicers) which will do just that.

If you are comfortable with the basics of Excel, let our Microsoft Certified Trainer Barbara Evers walk you through even more useful Excel topics and tools.

Topics covered include:

Using range names in formulas
Creating nested functions
Working with forms, form controls, and data validation
Using lookup functions
Combining multiple functions
Creating, modifying, and formatting charts
Using sparklines, trendlines, and dual-axis charts
Creating a Pivot Table and analyzing Pivot Table data
Presenting data with Pivot Charts
Filtering data with slicers

Over 2 hours of high-quality HD content in the “Uniquely Engaging”TM Bigger Brains Teacher-Learner style!

Objectives. You will be able to:

Use range names in formulas
Create nested functions
Work with forms, form controls, and data validation
Use lookup functions
Combine multiple functions
Create, modify, and format charts
Use sparklines, trendlines, and dual-axis charts
Create a PivotTable and analyze Pivot Table data
Present data with Pivot Charts
Filter data with slicers

Who this course is for:

Microsoft Excel users looking to get more out of their Excel experience.