Instructor for this course
more

Excel expert David Ringstrom, CPA, covers the basics of creating and utilizing Excel dashboards in this comprehensive course. Dashboards empower users to quickly assimilate large amounts of data into spreadsheets by way of pivot tables, charts, and other Excel features. David shows you how to streamline data analysis, expand or collapse rows and/or columns, create dashboards that look less like Excel spreadsheets, filter pivot table data based on date ranges, and more.

David demonstrates every technique at least twice: first, on a PowerPoint slide with numbered steps, and second, in Excel 2016. He draws your attention to any differences in Excel 2013, 2010, or 2007 during the presentation as well as in his detailed handouts. David also provides an Excel workbook that includes most of the examples he uses during the webcast.  

Topics Covered:

  • Creating self-updating titles for charts and pivot charts.
  • Exploring the Recommended Charts feature in Excel 2013 and later.
  • Discovering how Microsoft Query allows you to create self-updating links to databases, spreadsheets, text files, and other data sources.
  • Seeing how the Sparkline feature empowers you to create tiny, in-cell charts for showing trends of data.
  • Using the Group and Ungroup feature to expand or collapse rows and/or columns.
  • Jump-starting data visualization with the Quick Analysis feature.
  • Filtering pivot table data based on date ranges by way of the Timeline feature in Excel 2013 and later.
  • Learning how to hide or unhide Excel’s Ribbon interface using a simple macro technique.
  • Creating dynamic and interactive graphs with Excel’s PivotChart feature.
  • Using the Linked Picture feature to place pivot tables in close proximity to each other without posing conflicts.
  • Understanding how to use the Slicer feature to streamline data analysis within tables and pivot tables.
  • Creating a dashboard that looks less like an Excel spreadsheet by hiding screen elements.

 

Learning Objectives

  • Discover how to use the Quick Analysis feature for data visualization.
  • Recognize how to use Microsoft Query to create persistent connections to data you wish to present in a dashboard.
  • Explore how to create a dashboard that looks less like an Excel spreadsheet. 
Last updated/reviewed: November 8, 2019

11 Reviews (33 ratings)Reviews

5
Member's Profile
Dashboard creation the right way! Intro to Microsoft Query! Best practices for Pivot Tables! This is a great course for those in accounting which are Controllers, Senior Accountants, and many more roles. Would have liked to see more options with software integration with the query tool, but this video keeps it simple as a framework to expand upon! Awesome job!
5
Anonymous Author
I have used pivot tables in the past, however, this program showed me new functions that I was unaware of. I will be using the dashboard concept for my role in the next couple of days, and I plan to continue on and take part two of this course later today.
5
Member's Profile
This was an interesting course. Allot of it I had already learned, but there were some tidbits in there that I found useful, such as "refresh on file opening" and ctrl-drag for adding a new sheet in excel (who knew?).
4
Anonymous Author
There are several tips that I learned from this lecture that I can start using on my day to day jobs. David was very good on providing lectures that are easy to follow.
5
Member's Profile
I have been using pivot tables for many years but this was a new level of use that I am excited to try. Easy to follow instructions.
4
Anonymous Author
Excellent course. I've used pivot tables for many years and learned new tricks - especially the link picture.
5
Member's Profile
I like seeing the screenshots of actions to be performed and then performing the actual actions live in Excel.
5
Anonymous Author
Great course. Good step by step numbered instructions followed by actual video showing the process.
5
Member's Profile
This course covers a lot of ground. I will definitely use the new knowledge I now possess
5
Anonymous Author
The instructor provides good examples and explanations as he walks thru the steps.
5
Anonymous Author
The course was very informative and easy to follow.

Prerequisites

Course Complexity: Intermediate

Program Prerequisites: Experience with Excel

Advance Preparation: None

Education Provider Information

Company:
Illumeo, Inc., 75 East Santa Clara St., Suite 1215, San Jose, CA 95113
Contact:
For more information regarding this course, including complaint and cancellation policies, please contact our offices at (408) 400- 3993 or send an e-mail to .
Course Syllabus
INTRODUCTION AND OVERVIEW
  11:21Introduction to Excel Efficiency: Dashboards Part 1
  9:08Microsoft Query: New Data Source and Text Files
  10:18Microsoft Query with Text Files and Amending the Query
  8:52Data Security Prompts, Recommended Pivot Tables and Charts (Excel 2013+) and Summarize Pivote Tables
  7:59Applying Number Formats to Pivot Tables, Hiding Worksheet Gridlines and Autofit Column Widths
  7:33Renaming Value Fields in Pivot Tables and Replicate the Pivot Tables
  9:59Top 10 Pivot Tables and Silicing Pivot Tables (Excel 2010+)
  10:04Slicing Pivot Tables (Excel 2010+), Formatting Slicer (Excel 2010+) and Slicing Multiple Pivot Tables
  6:48Incorrect Way to Assemble the Dashboard, Pivot Table Conflicts and Pasting Linked Pictures of Pivot Tables
  10:21Assembling the Dashboard and Automatic Pivot Table Refresh
  9:55Refreshing All Pivot Tables, Pivot Tables Drill-Down and Blank Cell Within Data
  5:19Filling Blank Cells with Zeros for Pivot Tables, Pivot Table Enable Show Detail Option and Conclusion
CONTINUOUS PLAY
  1:47:36Excel Efficiency: Dashboards Part 1
SUPPORTING MATERIALS
  PDFSlides: Excel Efficiency: Dashboards Part 1
  PDFExcel Efficiency: Dashboards Part 1 Glossary/Index
  XLSXWorkbook: Excel Efficiency Dashboards Part 1
  CSVFruit Sales
REVIEW AND TEST
  quizREVIEW QUESTIONS
 examFINAL EXAM