Excel Agility: Dashboards Part 1
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
-
If you’re looking for ways to summarize extensive amounts of data in your Excel spreadsheets, this presentation is just what you need. Excel expert David Ringstrom, CPA, introduces you to Excel dashboards. David demonstrates how to quickly assimilate large amounts of data into spreadsheets by way of pivot tables, charts, and other Excel features. After completing the course, you’ll be equipped to summarize and present your data in an efficient manner.
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 tasked with summarizing large amounts of data in Excel spreadsheets.
Your Benefits of Attending:
- 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:
- Describe how to create a dashboard that looks less like an Excel spreadsheet.
- Recall how to use Microsoft Query to create persistent connections to data you wish to present in a dashboard.
- Define how to create dynamic and interactive graphs with Excel’s PivotChart feature.
Level:
Intermediate
Format:
On-Demand Webcast
Instructional Method:
Self-Study
NASBA Field of Study:
Computer Software & Applications (2 hours)
Program Prerequisites:
Experience with Excel
Advance Preparation:
None
Introduction
Example of an Excel Dashboard - 00:01:49
Microsoft Query: New Data Source - 00:06:30
Microsoft Query with Text Files - 00:14:39
Amending the Query - 00:27:27
Data Security Prompts - 00:30:48
Recommended Pivot Tables (Excel 2013+) - 00:33:08
Recommended Charts (Excel 2013+) - 00:35:56
Summarize With Pivot Table - 00:37:43
Applying Number Formats to Pivot Tables - 00:39:39
Hiding Worksheet Gridlines - 00:42:16
Autofit Column Widths - 00:45:14
Renaming Value Fields in Pivot Tables - 00:47:37
Replicate the Pivot Tables - 00:51:41
Top 10 Pivot Table - 00:55:12
Creating a Top 10 Pivot Chart - 00:57:38
Slicing Pivot Tables (Excel 2010+) - 01:01:36
Formatting Slicers (Excel 2010+) - 01:06:21
Slicing Multiple Pivot Tables - 01:12:14
Incorrect Way to Assemble the Dashboard - 01:15:15
Pivot Table Conflicts - 01:17:44
Pasting Linked Pictures of Pivot Tables - 01:19:07
Assembling the Dashboard - 01:22:00
Automatic Pivot Table Refresh - 01:29:22
Refreshing All Pivot Tables - 01:32:21
Pivot Table Drill-Down - 01:34:49
Blank Cell within Data - 01:37:30
Filling Blank Cells with Zeros for Pivot Tables - 01:42:17
Pivot Tables Enable Show Details Option - 01:44:36
Autofit - 00:45:14
Drill-Down - 01:34:49
Micrsoft Query - 00:03:40, 00:06:30, 00:14:39
Pivot Chart - 00:02:40
Recommended Charts - 00:35:56
Recommended Pivot Tables - 00:33:08
Slicer - 00:05:05, 01:01:36
Drill Down: When a user double-clicks on any number within a pivot table, Excel creates a new worksheet that displays the underlying records.
External Data Connections : Connections to data outside of the Excel workbook, such as query that pulls from a database or other spreadsheet, or a connection to a text file.
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.
Recommended Charts Feature: A feature in Excel 2013 and later that enables beginners to get a jump start on creating charts, while also allowing experienced users to view data to be charted in a variety of formats.
Recommended PivotTables : Recommended PivotTables is an artificial intelligence feature in Excel 2013 and later that enables users to jump-start creating a pivot table. Click any cell within a list of data and then choose Recommended PivotTables from the Insert menu. Excel will typically suggest several report formats. Choose a report and then click OK to create the pivot table, which can then be modified in the usual fashion if needed.
Refresh: The Refresh command appears on the Options tab of Excel 2007 and 2010 as well as the Analyze tab of Excel 2013. Pivot tables store a snapshot of the underlying source data, so they don’t immediately reflect changes to said data. You must periodically refresh any pivot table to ensure it reflects any changes to the source data.
Slicer Feature: A visual filtering feature available with pivot tables in Excel 2010 and tables and pivot tables in Excel 2013.
Text Files : Raw data files that often have file extensions such as .TXT or .CSV. TXT files are sometimes tab-delimited (meaning each field is separated by a tab character) while CSV files are comma-delimited.
