Excel - Business Intelligence - Creating a Dashboard
Webinar Details $219
- Webinar Length: 100 Minutes
- Guest Speaker: Mike Thomas
- Topic: Business Administration, Business Skills, Finance, Software, Taxation and Accounting
- Credit: CPE 2.0, ATATX 1.5
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 its power, its flexibility, and the fact that most people have it installed on their computers.
- 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.
Format: Group Internet Based
Instructional Method: Live Webcast
NASBA Field of Study: Computer Software & Applications (2 hours)
Program Prerequisites: None
Advance Preparation: None
- What Is a Dashboard? 00:02:48
- Sales Data Demo File 00:04:41
- Ice Cream Dashboard Demo - Getting Data 00:09:28
- How To Automate A Refresh 00:21:38
- Building The Dashboard 00:34:32
- Creating A Pivot Table 00:37:28
- Resizing the TextBox 00:58:32
- Entering A Formula Into A TextBox 01:03:14
- Creating a Chart 01:04:21
- Building A Chart From A Pivot Table 01:08:21
- Removing Value Field Buttons 01:15:10
- Slicers 01:17:36
- Linking Slicer To The Pivot Table 01:24:48
- Benefit Of Using A Slicer Over A Filter 01:37:32
- Attendee Questions 01:32:26
- Presentation Closing 01:45:29
- .CSV 00:08:58
- Cell 00:34:58, 00:38:16, 00:58:17
- Column 00:34:46
- Dashboard 00:02:46, 00:34:32, 01:02:52, 01:19:04
- Data Model 00:32:40
- Filter 00:02:42, ,01:19:10 01:15:40, 01:37:54
- Formula 00:03:35, 00:37:05, 00:59:19, 01:03:17
- Pivot Chart 00:03:34
- Pivot Table 00:03:33, 00:38:20, 00:41:26, 00:50:14, 01:08:11, 01:25:50
- Power Pivot 00:32:52
- Power Query 00:26:26
- Query 00:22:25
- Refresh 01:13:04, 01:33:12
- Ribbon 01:25:56
- Row 00:05:36, 00:32:05
- Slicer 00:02:42, 01:17:36, 01:24:04, 01:32:55
- Table 00:02:42, 00:16:33, 00:20:34, 00:39:09
- TextBox 00:44:00, 00:55:40, 01:03:26
- Text Files 00:06:31, 00:08:56
- VBA - Visual Basic for Applications 01:18:14
Cell: In spreadsheet applications, a cell is a box in which you can enter a single piece of data. The data is usually text, a numeric value, or a formula. The entire spreadsheet is composed of rows and columns of cells.
Chart: In Microsoft Excel, a chart is often called a graph. It is a visual representation of data from a worksheet that can bring more understanding to the data than just looking at the numbers. A chart is a powerful tool that allows you to visually display data in a variety of different chart formats such as Bar, Column, Pie, Line, Area, Doughnut, Scatter, Surface, or Radar charts.
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.
Data Model: A Data Model allows you to integrate data from multiple tables, effectively building a relational data source inside an Excel workbook. Within Excel, Data Models are used transparently, providing tabular data used in PivotTables and PivotCharts.
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.
Formula: A formula is an expression which calculates the value of a cell.
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.
Power Pivot: Power Pivot is an Excel add-in you can use to perform powerful data analysis and create sophisticated data models. With Power Pivot, you can mash up large volumes of data from various sources, perform information analysis rapidly, and share insights easily.
Power Query: Power Query is a data connection technology that enables you to discover, connect, combine, and refine data sources to meet your analysis needs. Features in Power Query are available in Excel and Power BI Desktop. Power Query is one of three data analysis tools available in Excel: Power Pivot.
Query: A database query extracts data from a database and formats it in a readable form. A query must be written in the language the database requires; usually, that language is Structured Query Language (SQL). For example, when you want data from a database, you use a query to request that specific information.
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.
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.
TextBox : Textboxes are used in worksheets or userforms to display information or to allow the user to input information.
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.