Excel Agility: Dashboards Part 2
Webinar Details $219
- Rated:
- Webinar Length: 100 Minutes
- Guest Speaker: David Ringstrom
- Topic: Business Administration, Business Skills, Finance, Taxation and Accounting, Software
- Credit: ATAAA 1.5, ATATX 1.5, ATAOP 1.5, CPE 2.0
-
In Dashboards Part 2, Excel expert David Ringstrom, CPA, reveals additional techniques you can use to quickly convert large amounts of data into concise summaries. David covers pivot tables, pivot charts, slicers, and other features that allow you to present data in summary form, yet still give you easy access to underlying details. David shows you how to create: dashboards that look less like Excel spreadsheets, dynamic and interactive graphs, self-updating titles for charts and pivot charts, and much 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.
Who Would Be Interested in This Course:
Practitioners who need to transform large amounts of data into concise, self-updating summaries.
Your Benefits of Attending:
- Using linked pictures as a navigation tool to return to the source pivot table.
- Exploring how to amend queries exported from Microsoft Query.
- Understanding the conflict that a linked picture overlaying a slicer can pose.
- Creating self-updating chart titles that change automatically as you filter or slice data related to the chart.
- Getting past the “PivotTable field name already exists” prompt once and for all.
- Jump-starting data visualization with the Quick Analysis feature.
- Learning how to hide or reveal Excel’s Ribbon interface using a simple macro technique.
- Streamlining the filtering of lists in Excel 2013 and later by using the Slicer feature with tables.
- Utilizing the Timeline feature in Excel 2013 and later to filter pivot tables based on date ranges.
- Using the Group command to interactively hide/unhide columns (and/or rows) within Excel worksheets.
Learning Objectives:
- Identify ways Excel dashboards can be used to transform large amounts of data into concise summaries.
- Define how to create dynamic and interactive graphs with Excel’s PivotChart feature.
- State how to apply the Slicer feature in Excel 2010 and later to filter data faster.
Level:
Intermediate
Format:
Self-Study
Instructional Method:
On-Demand Webcast
NASBA Field of Study:
Computer Software & Applications (2 hours)
Program Prerequisites:
Experience with Pivot Tables Is Recommended
Advance Preparation:
None
Table of Contents
Introduction
Excel Versions 00:00:43
Dynamic Pivot Chart Titles 00:03:22
Dynamic Pivot Chart Titles (cont.) 00:11:50
Linked Picture/Slicer Conflict 00:13:18
Linked Picture/Drill Down 00:15:57
Slicers with Tables (Excel 2013+) 00:22:10
Timeline Feature (Excel 2013+) 00:25:58
Protecting Workbooks to Block Drill-Down 00:32:50
Sparklines and Timeline Compatibility Issues 00:34:09
Sparklines Feature (Excel 2010+) 00:35:12
Dashboard Sparklines 00:40:13
Removing Sparklines 00:43:28
Quick Analysis (Excel 2013+) 00:44:33
Dashboard Data Source 00:48:29
Opening Source Files in Excel 00:53:49
Enabling from Text in Office 365 00:56:51
Text Import Wizard 00:58:53
Text Import Wizard Properties 01:00:41
Combine Multiple Worksheets 01:09:40
Combine Multiple Worksheets (cont.) 01:11:19
Combine Multiple Worksheets (cont.) 01:13:09
Combine Multiple Worksheets (cont.) 01:15:49
Set Query to Refresh Automatically 01:19:24
Set Pivot Table to Refresh Automatically 01:19:56
Add Formulas to Query Results 01:21:45
Group and Ungroup Rows and Columns 01:23:00
Custom Views - Multipurpose Worksheets 01:29:35
Custom Views - Multipurpose Worksheets (cont.) 01:32:20
Streamlining Custom Views 01:34:31
Tables Conflict with Custom Views 01:37:10
Controlling Excels Look and Feel 01:38:45
Show/Hide Ribbon 01:41:44
Conclusion 01:47:22
Index
- Concatenation 00:05:18
- Custom Views 01:29:42
- Drill Down 00:16:01
- Linked Pictures 00:13:30
- Microsoft Query 01:12:41, 01:13:36, 01:37:26
- Pivot Table 01:19:57
- Protect Workbook 00:31:59
- Quick Access Toolbar 01:34:48
- Quick Analysis Feature 00:44:47
- Slicer 00:13:35
- Sparkline Feature 00:35:23
- Sparklines 00:00:34:09
- Table Feature 01:37:16
- Text Import Wizard 00:59:36
- Timeline 00:26:08
- Timeline 00:34:18
- Total Row 00:17:42
- Visual Basic Editor 00:41:47
Concatenation: A technique that allows you to join two or more pieces of text together. Although its simplest to use the ampersand (&), you can also use the CONCATENATE function in Excel.
Custom Views: This feature stores a snapshot of the hidden/visible status of columns, rows, and worksheets, along with print settings and filter settings.
Drill Down: When a user double-clicks on any number within a pivot table, Excel creates a new worksheet that displays the underlying records.
Linked Pictures: Provides a live snapshot of a range of cells to you in an image. You can move the image, resize it, position it wherever you want and when the source cells change, the picture gets updated, immediately.
Microsoft Query : A feature on Excel's Data menu that enables you to connect Excel spreadsheets to external data sources such as accounting software, databases, text files, Excel workbooks, and more.
Pivot Table: A report creation tool in Excel that enables you to quickly summarize lists of data into summary reports by clicking checkboxes and dragging fields onscreen.
Protect Workbook: To prevent other users from viewing hidden worksheets, adding, moving, deleting, or hiding worksheets, and renaming worksheets, you can protect the structure of your Excel workbook with a password.
Quick Access Toolbar: A customizable shortcut toolbar that appears above the ribbon in Office 2007 and later.
Quick Analysis Feature : A new feature in Excel 2013 that calls often-overlooked features to your attention, such as pivot tables, recommended charts, totals, tables, and sparklines.
Slicer: You can insert slicers in Excel to quickly and easily filter pivot tables. Slicers were introduced in Excel 2010, and they make it easy to change multiple pivot tables with a single click
Sparkline: A sparkline is a very small line chart, typically drawn without axes or coordinates. It presents the general shape of the variation in some measurement, such as temperature or stock market price, in a simple and highly condensed way. Use sparklines to show trends in a series of values, such as seasonal increases or decreases, economic cycles, or to highlight maximum and minimum values.
Sparkline Feature: A new feature introduced in Excel 2010, sparklines are tiny charts that fit within a single worksheet cell. They’re commonly used for dashboards and other types of spreadsheets that aggregate large amounts of data into a compact space.
Table Feature : The Table feature in Excel 2007 and later is an improvement on the List feature in Excel 2003 and earlier. The Table feature provides enhancements that make it much easier to analyze lists of data.
Text Import Wizard: An Excel feature that allows you to separate data from text files into multiple columns.
Timeline: A visual filtering feature in Excel 2013 that enables you to filter data based on varying date ranges.
Total Row: A Total row appears below the data where each column has access to several automatic formulas. The default selection for the Total Row is none, meaning no function is selected when you first turn on the Total Row on your Table.
Visual Basic Editor : Excel’s development interface that can be used to edit and create macros, user forms, class modules, custom worksheet functions, and other enhancements to Excel’s functionality. The programming interface for Microsoft Excel that can be accessed by way of the Visual Basic command on Excel’s Developer tab or by pressing Alt-F11.
