Excel - Business Intelligence - Creating a Dashboard
Please see below for additional instructions and information regarding this program.
In this training session, you'll learn how to create a stunning, interactive professional-looking dashboard using Excel. This training will provide you with a solid foundation that you can use to build your own dashboards and reports.
This training focuses on what you need to know to create an interactive professional-looking dashboard using Excel. You'll learn how to: make the dashboard maintenance-free when new data becomes available; create the pivot tables needed to drive the dashboard; create great looking visuals; add interactivity using slicers; automate elements of the dashboard with a macro and protect the cells containing critical formulas.
No matter what business you are in, a dashboard is a critical tool in your communication armory and as with any communication tool, presentation is everything. Although there are many tools that can be used to create a dashboard, Excel is commonly used due to it’s power, it’s flexibility and the fact that most people have it installed on their computer.
- Best practice for setting up data sources
- Using Pivot Tables to summarize data
- Visual communication using charts
- Creating a rolling 30-day summary
- Using formulas to create KPI summaries
- Creating interactive filters with Slicers
- Automating the dashboard with a simple macro
- Using protection to prevent accidental changes
Who should attend
This webinar is for any user of Excel who wants to learn how to create Excel dashboards. You should have an intermediate level of Excel knowledge and preferably know how to create a basic pivot table. The training will be delivered using Excel 2016 for Windows. However, much of the functionality is available in Excel 2013 and 2010.
- AutoFill 00:25:58
- Column 00:10:49, 00:14:18
- Dashboard 00:01:53
- Filter 00:04:15
- Format 00:04:15
- Formula 00:04:15
- Key Performance Indicator (KPI) 00:27:35
- Pivot Chart 00:04:15
- Pivot Table 00:04:15, 00:31:37, 00:31:41, 01:09:04
- Ribbon 00:15:25
- Row 00:10:49, 00:28:21
- Slicer 00:04:15, 01:05:30
- Table 00:04:15, 00:10:41, 00:25:48
- VBA - Visual Basic for Applications 01:07:31
- Worksheet 00:19:51
- To Create a Dashboard You Need To Know About 00:04:15
- Ice Cream Dashboard Demo 00:05:43
- How to Create the Dashboard Tab 00:19:48
- Resizing the Table 00:27
- Multiple Tables in the Dashboard 00:21:34
- How to Get the List of Available Columns 00:22:13
- Entering a Formula in the New Text Column and Autofill 00:23:10
- How To Quickly Get To The Bottom of the Sheet in a Column 00:24:08
- Data Preparation 00:25:42
- Table Formatting 00:29:23
- Name a Table 00:31:10
- Pivot Tables 00:31:37
- How To Remove The Text Heading In The Chart 00:54:33
- How To Sort In The Pivot Table 01:00:03
- How To Insert a Line Graph 01:03:37
- Slicers 01:05:30
- Giving a Pivot Table a Name 01:13:40
- What If Two Flavors Have The Same Value? 01:20:54
- What Else Can I Do With The Slicer? 01:23:28
- Refreshing Pivot Tables 01:29:20
- Presentation Closing 01:40:54
AutoFill: Use the Auto Fill feature to fill cells with data that follows a pattern or are based on data in other cells.
Column: A column is a vertical series of cells in a chart, table, or spreadsheet in Excel.
Dashboard: An Excel dashboard is a one-pager (mostly, but not always necessary) that helps managers and business leaders in tracking key KPIs or metrics and take a decision based on them. It contains charts/tables/views that are backed by data. A dashboard is often called a report, however, not all reports are dashboards.
Filter: The Filter feature in Excel allows you to show or hide rows within a list of data by making selections from drop-down lists. The Filter feature is available on the Data tab of all versions of Excel as well under the Sort & Filter command on the Home menu.
Format: When we format cells in Excel, we change the appearance of a number without changing the number itself. We can apply a number format (0.8, $0.80, 80%, etc) or other formatting (alignment, font, border, etc). By default, Excel uses the General format (no specific number format) for numbers.
Formula: A formula is an expression which calculates the value of a cell.
Key Performance Indicator (KPI) : A Key Performance Indicator is a measurable value that demonstrates how effectively a company is achieving key business objectives. Organizations use KPIs at multiple levels to evaluate their success at reaching targets.
Pivot Chart: Pivot charts are an adjunct to Excel’s pivot table feature, which allows you to summarize data by dragging and dropping data with your mouse. Pivot charts are much more interactive than the traditional Excel charts.
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.
Ribbon: The "ribbon" is the strip of buttons and icons located above the work area that was first introduced in Excel 2007. The ribbon replaces the menus and toolbars found in earlier versions of Excel. Above the ribbon are a number of tabs, such as Home, Insert, and Page Layout.
Row: A row is the range of cells that go across (horizontal) the spreadsheet/worksheet. Rows are identified by numbers e.g. row 1, row 5. Examples of use. A row might contain the headings of a table e.g. product ID, product name, price, number sold.
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
Table: A table is an arrangement of data in rows and columns, or possibly in a more complex structure. Tables are widely used in communication, research, and data analysis. Tables appear in print media, handwritten notes, computer software, architectural ornamentation, traffic signs, and many other places.
VBA - Visual Basic for Applications : Visual Basic for Applications is a computer programming language developed and owned by Microsoft. With VBA you can create macros to automate repetitive word- and data-processing functions, and generate custom forms, graphs, and reports. VBA functions within MS Office applications; it is not a stand-alone product.
Worksheets: A worksheet is a collection of cells where you keep and manipulate the data. Each Excel workbook can contain multiple worksheets.
Mike Thomas has worked in the IT training business for 26 years. His expertise and experience covers designing and delivering training courses, creating written training materials (Quick Reference Guides and step-by-step tutorials), recording and editing video-based tutorials and providing support to users. He is a subject matter expert in a range of Microsoft technologies including Microsoft Office, Lync/Skype for Business and SharePoint. He has extensive experience of working with Cloud-based ... 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.