Excel - Business Intelligence - Creating a Dashboard

1. Introduction
2. What Is a Dashboard? 00:02:39
3. Demo Dashboard File 00:05:53
4. Dashboard Sheet - Gridlines 00:08:07
5. Converting Data Into a Table 00:09:25
6. Naming The Table 00:13:07
7. Creating A Dashboard 00:13:56
8. Displaying Total Revenue In Large Font - Shapes 00:15:01
9. Creating The Shape Overview 00:20:05
10. How To Add Numbers To The Shapes 00:23:00
11. Adding More Data To The Table 00:27:37
12. Entering In A Secondary Formula To Shapes00:28:52
13. Changing The Text, Font, Size, and Color 00:36:11
14. Using The ScratchPad 00:38:14
15. Filling The Card With Information 00:39:25
16. SUMIFS 00:42:23
17. Changing The Format 00:45:45
18. Changing The Table Name 00:46:08
19. TEXT Function 00:50:13
20. Formatting In Currency 00:50:54
21. Adding Another Row To The Data 01:01:03
22. Charts 01:02:14
23. Creating A Column Chart 01:02:50
24. Building A Pivot Table 01:04:35
25. Sorting The Pivot Table 10:08:48
26. Building A Chart From A Pivot Table 01:10:13
27. Moving The Chart To The Dashboard 01:13:13
28. Creating A Second Chart 01:13:54
29. Updating Pivot Tables 01:20:23
30. Removing Gray Boxes From Chart 01:27:48
31. How To Add A Description To The X And Y Axis 01:29:33
32. Slicers 01:31:47
33. Connecting A Slicer To A Second Pivot Table 01:35:40
34. Speaker Email Address 01:38:24
35. Presentation Closing 01:40:21

• 

  Mike Thomas

  Mike Thomas has worked in the IT training business for 26 years. His expertise and experience covers designing and delivering training courses, creating written training materials (Quick Reference Guides and step-by-step tutorials), recording and editing video-based tutorials and providing support t [...]

  View Full Profile

• Cell 00:11:31, 00:24:56, 00:50:56, 01:06:24, 01:11:07
• Chart 00:04:24, 00:10:02, 01:02:14, 01:08:31, 01:24:58, 01:31:25
• Column 00:15:25, 00:24:30, 00:44:47, 00:51:27, 01:04:03
• Column Chart 01:02:50, 01:11:36
• Dashboard 00:03:36, 00:06:07, 00:10:14, 00:14:08, 00:24:01, 00:38:39, 00:59:46, 01:21:19, 01:32:10
• Filter 00:19:35, 01:28:16, 01:33:52
• Format 00:45:49, 00:53:56
• Formula 00:10:01, 00:24:12, 00:28:32, 00:30:04, 01:23:57
• Formula Bar 00:35:00
• Pivot Chart 01:28:45
• Pivot Table 00:10:03, 01:04:45, 01:08:20, 01:20:33, 01:33:47
• Refresh 01:20:25
• Ribbon 00:13:07, 01:33:04
• Row 00:11:54, 00:15:50, 00:22:37, 00:35:51, 00:40:27, 01:08:07, 01:20:55
• Slicer 01:30:54
• Spreadsheet 00:33:25
• SUM 00:25:59
• SUMIFS 00:42:23
• Table 00:09:27, 00:10:08, 00:12:10, 00:19:07, 00:28:19, 01:31:04
• TextBox 01:28:36
• TEXT Function 00:50:17, 00:53:45
• Scroll Bar 01:33:33

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.

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.

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.

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.

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.

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.

SUMIFS: A look-up function in Excel that allows you to add up numbers based upon up to 127 criteria that you specify. Unlike VLOOKUP, the SUMIFS function can add up two or more values and returns zero (instead of #N/A) if no match is found.

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.