Excel - Business Intelligence - Creating a Dashboard
Webinar Details $219
- Rated:
- 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, ATAAA 1.5, ATAOP 1.5, ATAPU 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:08
- Creating A Proper Excel Table 00:07:58
- Converting Data Into a Table 00:09:25
- Naming The Table 00:11:15
- The Dashboard - Displaying The Data 00:13:23
- Building A Chart 00:16:00
- Turning Off Gridlines 00:23:00
- Naming The Boxes 00:24:43
- Building A Pivot Table 00:25:30
- Building A Chart By Formula 00:29:29
- Building A Chart From A Pivot Table 00:29
- Creating The Dashboard Tab 00:33:37
- Creating A Chart That Shows The Number Of Orders 00:35:44
- Formatting Numbers With A Comma And Dollar Sign 00:45:11
- Creating A Dashboard A Simpler Way 00:50:52
- Adding A New Row Of Data 00:54:28
- Building A Column Chart 01:00:03
- Building A Line Chart 01:04:49
- Creating A Second Chart 01:06:17
- How To Show The Top Sales Rep 01:09:30
- Creating Revenue By State 01:14:04
- Aligning Charts 01:16:17
- Updating The Data 01:17:07
- Automating Pivot Tables Through A Macro 01:20:35
- Creating A Macro 01:23:36
- Macro Review 01:28:47
- Adding A Filter 01:29:49
- Slicers 01:32:36
- Speaker Email Address 01:39:52
- Presentation Closing 01:41:52
- Analyze 01:31:20
- Cell 00:07:46, 00:16:40, 00:19:50, 00:29:12, 00:37:50, 00:45:52, 00:47:40
- Cell Reference 00:37:35
- Chart 00:08:23, 01:01:17, 01:08:21, 01:19:05
- Column 00:07:31, 00:17:50, 00:52:23
- Column Chart 00:14:41, 01:01:28, 01:04:56, 01:14:58
- Column Headings 00:28:46, 00:38:29, 01:03:52
- COUNT 00:38:52
- Dashboard 00:03:08, 00:06:46, 00:11:48, 00:12:37, 00:24:33, 00:31:37, 01:02:31, 01:04:31, 01:39:31
- Filter 00:10:57, 01:29:51, 01:35:07
- Format 00:42:10, 00:45:38, 00:53:33
- Formula 00:08:23, 00:16:04, 00:29:16, 00:35:06, 00:41:09, 00:49:15, 00:56:00, 01:12:54, 01:37:55
- Formula Bar 00:31:43, 01:13:17
- Key Performance Indicator (KPI) 00:04:05
- Line Chart 01:04:58
- Macro 00:58:37, 01:20:39, 01:27:33
- Pie Chart 00:14:43, 01:06:46
- Pivot Table 00:08:24, 00:10:20, 00:25:30, 00:27:22, 00:36:07, 00:50:05, 00:57:18, 01:04:30, 01:18:29, 01:32:31
- Refresh 00:56:28, 01:19:46, 01:27:01
- Row 00:06:37, 00:07:30, 00:08:17, 00:9:51, 00:17:50, 00:28:13, 00:35:54, 01:27:07
- Slicer 01:32:38, 01:32:33
- SUM 00:38:41
- Table 00:07:00, 00:08:15, 00:11:01, 00:52:26
- Worksheet 00:26:59
Analyze: The ANALYZE tab has several commands that will enable you to explore the data in the PivotTable.
COUNT: Use the COUNT function to get the number of entries in a number field that is in a range or array of numbers.
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.
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.
Line Chart: Line charts can display continuous data over time, set against a common scale, and are therefore ideal for showing trends in data at equal intervals or over time. In a line chart, category data is distributed evenly along the horizontal axis, and all value data is distributed evenly along the vertical axis.
Macro: One or more lines of programming code that automate tasks. The Macro Recorder allows users to automate tasks without seeing the underlying programming code.
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 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.
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.
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.
Worksheets: A worksheet is a collection of cells where you keep and manipulate the data. Each Excel workbook can contain multiple worksheets.
