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, ATAAA 1.5, ATATX 1.5, ATAOP 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 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.
- Ice Cream Dashboard Demo 00:02:54
- How to Create the Dashboard Tab 00:05:55
- Data and Tables 00:16:28
- Entering KPI’s 00:21:52
- Creating a Metric in the 00:23:19
- Refresh Function 00:33:27
- Finding The Name Of A Table 00:33:59
- SUM E:E 00:35:19
- Merging Text Boxes Into One 00:36:26
- How To Name A Table 00:38:35
- How To Get The Formula Into The Text Box 00:39:12
- How To Create Charts 00:48:46
- How To Build A Pivot Table 00:52:48
- Examples Of Using Dashboards That Don’t Have An Auto-Generated Data Source 01:03:17
- Creating A Revenue Per Month Chart 01:19:20
- Creating A Line Chart 01:12:50
- Filtering in A Chart 01:14:58
- Slicers 01:17:50
- Recruitment Dashboard 01:32:50
- Ice Cream Dashboard 01:35:23
- Speaker’s Email Address 01:41:41
- Presentation Closing 01:42:19
- Cell 00:23:28, 00:23:43
- Chart 00:48:56, 00:57:18, 01:17:43
- Column 00:16:50, 00:26:50
- Dashboard 00:00:13, 00:02:41, 00:42:31, 00:56:42
- Filter 01:14:59
- Formula 00:20:52, 00:23:31
- Key Performance Indicator (KPI) 00:21:52
- Pivot Table 00:52:47, 00:58:18, 01:10:02, 01:17:39m, 01:26:25
- Power Query 00:09:21
- Query 00:11:58
- Ribbon 00:16:35
- Row 00:13:48, 00:16:50, 00:26:36, 00:42:26
- Slicer 01:17:50, 01:24:08, 01:35:37
- Table 00:16:28, 00:26:46, 00:39:09
- Text Box 00:06:48,00:24:32, 00:36:29
- VBA - Visual Basic for Applications 01:35:48
- Workbook 00:18:23
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.
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.
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 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 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.
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.