On Demand Webinar
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, ATAOP 1.5, ATAPU 1.5, ATAAA 1.5, ATAHR 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.
Topics covered:
- 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.
Level: Basic
Format: Group Internet Based
Instructional Method: Live Webcast
NASBA Field of Study: Computer Software & Applications (2 hours)
Program Prerequisites: None
Advance Preparation: None
- Introduction
- What Is a Dashboard? 00:03:28
- Getting Data Set Up 00:06:15
- Converting Data Into a Table 00:08:13
- Naming The Table 00:11:59
- How To Create Cards 00:15:16
- How To Add Numbers To The Shapes 00:28:08
- Referencing a Table In One Workbook From Another Workbook 00:33:34
- Adding More Data To The Table 00:36:40
- How To Get The Data Into The Dashboard 00:37:27
- Changing The Color And Font Size 00:43:40
- Putting Total Row OnOne Card and The Number One Another Card 00:48:10
- How To Display The Number With A Dollar Sign 00:50:12
- Building A Chart From A Pivot Table 00:53:15
- Creating A Second Chart 01:04:30
- Hiding Feilds On The Pivot Chart 01:09:55
- Connecting To Live Data 01:15:11
- Slicers 01:20:32
- Filter Feature 01:23:50
- Slicers Continued 01:25:46
- Connecting A Slicer To A Pivot Table 01:30:32
- Changing The Number of Columns In A Slicer 01:33:46
- Making The Axis Numbers Stay The Same 01:36:17
- How To Change The Name Of A Pivot Chart 01:36:54
- Speaker Email Address 01:40:00
- Presentation Closing 01:40:32
- Cell 00:26:27, 00:52:33, 00:56:23, 01:17:14, 01:26:26
- Cell Reference 00:47:58
- Chart 00:04:53, 00:08:46, 00:53:15, 00:57:17, 01:15:08, 01:23:36, 01:32:11
- Column 00:30:38, 00:45:15, 01:33:54
- Column Chart 00:04:20, 00:53:40, 00:56:37, 01:08:24
- Column Headings 00:14:22, 00:32:13
- Dashboard 00:00:11, 00:03:49, 00:05:24, 00:06:31, 00:15:31, 00:37:12, 00:46:00, 00:56:56, 001:21:59, 01:24:40, 01:32:18
- Filter 01:00:39, 01:21:46, 01:23:53, 01:27:32, 01:33:26
- Format 00:11:24, 00:19:06, 00:58:56
- Formula 00:08:45, 00:15:06, 00:32:08, 00:37:17, 00:41:54, 00:45:10, 01:18:55, 01:39:11
- Formula Bar 00:47:53
- Pie Chart 00:04:22, 00:53:43
- Pivot Chart 01:01:01, 01:09:55
- Pivot Table 00:08:48, 00:53:40, 01:04:37, 01:11:39, 01:18:05, 01:22:31, 01:26:04, 01:37:32
- Power Query 01:15:24
- Refresh 01:17:13
- Row 00:14:40, 00:35:10, 00:55:44
- Scroll Bar 01:33:14
- Slicer 01:20:32, 01:26:16, 01:30:32, 01:33:15
- Spreadsheet 00:01:47
- SUM 00:31:17, 00:43:23
- Table 00:08:13, 00:09:00, 00:11:17, 00:14:38, 00:31:34, 00:45:17, 01:06:08
- TextBox 01:00:04
- Workbook 00:33:37, 01:37:49
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.
Cell Reference: A cell reference refers to a cell or a range of cells on a worksheet and can be used in a formula so that Microsoft Office Excel can find the values or data that you want that formula to calculate. There are three types: Relative, Absolute, and Mixed
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.
Column Chart: A column chart is a graphic representation of data. Column charts display vertical bars going across the chart horizontally, with the values axis being displayed on the left side of the chart.
Column Headings : The column heading or column header is the gray-colored row containing the letters (A, B, C, etc.) used to identify each column in the worksheet. The column header is located above row 1 in the worksheet.
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.
Formula Bar: A toolbar at the top of the Microsoft Excel spreadsheet window that you can use to enter or copy an existing formula into cells or charts. It is labeled with function symbol (fx). By clicking the Formula Bar, or when you type an equal (=) symbol in a cell, the Formula Bar will activate.
Pie Chart: A pie chart is a circular representation that reflects the numbers of a single row or single column of Excel. The individual numbers are called data points (or categories) and a list (row or column) of numbers is called a data series.
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.
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.
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.
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.
SUM: Microsoft Excel defines SUM as a formula that “Adds all the numbers in a range of cells”. This definition clearly points that Sum function has a job to add numbers and the arguments can be supplied using combinations of both numbers and range of cells. =SUM The SUM function is a built-in function in Excel that is categorized as a Math/Trig Function. It can be used as a worksheet function (WS) in Excel. As a worksheet function, the SUM function can be entered as part of a formula in a cell of a worksheet.
Scroll Bar: A vertical or horizontal bar commonly located on the far right or bottom of a window that allows you to move the window viewing area up, down, left, or right. Scrolls through a range of values when you click the scroll arrows or when you drag the scroll box. You can move through a page (a preset interval) of values by clicking the region between the scroll box and either scroll arrow.
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
Spreadsheet: Microsoft Excel is a spreadsheet developed by Microsoft for Windows, macOS, Android and iOS. It features calculation or computation capabilities, graphing tools, pivot tables, and a macro programming language called Visual Basic for Applications. Excel forms part of the Microsoft Office suite of software.
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.
Workbook: In Microsoft Excel a workbook is a collection of one or more spreadsheets, also called worksheets, in a single file.
