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, ATAPU 1.5, ATAOP 1.5, ATAAA 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:02:47
- Creating A Proper Excel Table 00:06:40
- Finding The Table Design Toolbar 00:20:40
- The Dashboard - Displaying The Data 00:21:18
- Setting A Grey Sheet Background 00:32:19
- How To Display The Dollar Sign 00:34:03
- Entering A Formula Into A TextBox 00:35:09
- Creating Additional Text Boxes 00:39:20
- Creating A Dashboard From Other File Data 00:40:59
- Building A Chart 00:42:11
- Building A Pivot Table 00:43:34
- Building A Chart From A Pivot Table 00:46:58
- Creating A Chart That Shows The Number Of Orders 00:52:57
- What Happens When New Data Is Added? 00:52:36
- How To Get Rid Of Chart Buttons 01:01:37
- Building Another Pivot Table 01:06:21
- Applying A Filter 01:12:29
- Pivot Charts 01:14:59
- Slicers 01:16:28
- Linking Slicer To The Pivot Table 01:21:45
- How To Get The Slicer To Update All Charts 01:25:22
- Selecting More Than One State 01:26:04
- Other Things Slicers Can Do 01:26:57
- Reconnecting A Pivot Table To The Slicer 01:31:12
- Slicers And Shapes 01:32:59
- Speaker Email Address 01:39:20
- Presentation Closing 01:40:24
- .CSV 00:41:15
- Analyze 01:20:46, 01:31:54
- Bar Chart 00:05:05
- Cell 00:07:57, 00:57:04, 01:35:10
- Chart 00:05:02, 00:06:25, 01:14:01
- Column 00:05:21, 00:14:40, 00:55:10, 01:24:19
- Column Chart 00:47:22
- Column Headings 01:18:25
- Dashboard 00:01:33, 00:04:20, 00:06:09, 00:27:17, 0:39:11, 00:43:43, 01:30:42
- Filter 01:11:50, 01:13:42, 01:21:07
- Format 00:34:38, 00:37:59
- Formula 00:04:47, 00:06:23, 00:12:24, 00:35:12, 00:38:41, 00:57:28, 01:33:50
- Line Chart 01:09:32
- Number Formatting 00:34:14
- PDF 00:41:17
- Pie Chart 00:05:06, 01:15:01
- Pivot Chart 01:11:35
- Pivot Table 00:06:25, 00:11:57, 00:43:09, 00:54:37, 00:57:14, 01:05:21, 01:16:49, 01:25:55, 01:35:36
- Power Query 00:41:27
- Refresh 00:56:51, 01:05:58
- Row 00:03:48, 00:08:33
- Slicer 01:16:28, 01:20:49, 01:39:22
- SUM 00:15:54
- Table 00:07:03, 00:08:08, 00:57:50
- TextBox 00:38:37
- TEXT Function 00:36:46, 00:37:15, 00:39:20
- Total Row 00:20:20
- VBA - Visual Basic for Applications 01:00:49
Analyze: The ANALYZE tab has several commands that will enable you to explore the data in the PivotTable.
Bar Chart: A bar chart (also called a bar graph) is a great way to visually display certain types of information, such as changes over time or differences in size, volume, or amount. Bar charts can be horizontal or vertical; in Excel, the vertical version is referred to as a column chart.
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.
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.
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.
Number Formatting: Number formats are used to control the display of cell values that contain numeric data. This numeric data can include things like dates, times, costs, percentages, and anything else expressed as a number. To apply a number format, just select one or more cells and choose a format.
PDF: Portable Document Format, a universal document format created by Adobe that allows cross-platform compatibility of documents.
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.
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.
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
TEXT Function: The TEXT function enables you to convert a number in Excel to any number of text formats. For instance, the format code mmmm d, yyyy would transform the date 1/1/2018 into January 1, 2018.
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.
Total Row: A Total row appears below the data where each column has access to several automatic formulas. The default selection for the Total Row is none, meaning no function is selected when you first turn on the Total Row on your Table.
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.
