Excel Agility: Dashboards Part 1
Please see below for additional instructions and information regarding this program.
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.
- 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.
NASBA Field of Study:
Computer Software & Applications (2 hours)
Experience with Excel
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.
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.
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.
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.
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.
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.
David H. Ringstrom, CPA, is an author and nationally recognized instructor who teaches scores of webinars each year. His Excel courses are based on over 25 years of consulting and teaching experience. David’s mantra is “Either you work Excel, or it works you,” so he focuses on what he sees users don’t, but should, know about Microsoft Excel. His goal is to empower you to use Excel more effectively. To learn more about David, you can view his LinkedIn profile and follow him on ... View Full Profile
Aurora Training Advantage is registered with the National Association of State Boards of Accountancy (NASBA) as a sponsor of continuing professional education on the National Registry of CPE Sponsors. State boards of accountancy have final authority on the acceptance of individual courses for CPE credit. Complaints regarding registered sponsors may be submitted to the National Registry of CPE Sponsors through its website: www.nasbaregistry.org.
For more information regarding administrative policies such as complaint and refund, and cancellation please contact our offices at 407-542-4317 or firstname.lastname@example.org.
You must answer all questions during the webinar, view the recording completely and pass the test at the end with 70% correct answers to receive CPE credit.