028 3824 5819 traincert@tdt-tanduc.com
Tiếng ViệtEnglish

Excel Dashboard & Reports Advanced Skills

In this course, we offer an in-depth look at some of the key dashboarding concepts you can leverage to create a cutting–edge dashboard presentation. Shows you how to build an effective data model that provides the foundation upon which your dashboard or report is built and you discover the impact of poorly organized data and how to set up the source data for the most positive outcome. Illustrates how interactive controls can provide your clients with a simple interface, allowing them to easily navigate through and interact with your dashboard or report. And also provides a clear understanding of how you can leverage macros to automate your reporting systems.

Besides, you find out how pivot tables can enhance your analytical and reporting capabilities, as well as your dashboards. This course introduce you to pivot tables and explore how this Excel feature can play an integral role in Excel-based presentations. Provides a primer on building pivot charts, giving you a solid understanding of how Excel pivot charts work with pivot tables. Shows you how pivot slicers can add interactive filtering capabilities to your pivot reporting. Finally, introduces you to the new internal Data Model and Power View features of Excel 2013.

Part I: Advanced Dashboarding Concepts (3 chapters)

Chapter 1: Developing Your Data Model

Module 1: Building a Data Model

  • Separating the data, analysis, and presentation layers

Module 2: Data Model Best Practices

  • Avoid storing excess data
  • Use tabs to document and organize your data model
  • Test your data model before building presentation components

Module 3: Excel Functions for Your Data Model

  • Understanding lookup tables
  • The VLOOKUP function
  • The HLookup function
  • The SUMPRODUCT function
  • The Choose function

Module 4: Working with Excel Tables

  • Converting a range to an Excel table
  • Converting an Excel table back to a range

Chapter 2: Adding Interactive Controls to Your Dashboard

Module 1: Getting Started with Form Controls

  • Finding Form controls
  • Adding a control to a worksheet

Module 2: Using the Button Control

Module 3: Using the Check Box Control

  • Check box example: Toggling a chart series on and off

Module 4: Using the Option Button Control

  • Option button example: Showing many views through one chart

Module 5: Using the Combo Box Control

  • Combo box example: Changing chart data with a drop-down selector

Module 6: Using the List Box Control

  • List box example: Controlling multiple charts with one selector

Chapter 3: Macro-Charged Reporting

Module 1: Why Use a Macro?

  • Recording Your First Macro
  • Running your macros
  • Assigning a macro to a button

Module 2: Enabling Macros in Excel 2013

  • Viewing the new Excel security message
  • Setting up trusted locations

Module 3: Excel Macro Examples

  • Building navigation buttons
  • Dynamically rearranging pivot table data
  • Offering one-touch reporting options

Part II: Pivot Table Driven Dashboards (4 chapters)

Chapter 4: Using Pivot Tables

Module 1: Introducing the Pivot Table

  • Anatomy of a pivot table
  • Creating the basic pivot table

Module 2: Customizing Your Pivot Table

  • Changing the pivot table layout
  • Renaming the fields
  • Formatting numbers
  • Changing summary calculations
  • Suppressing subtotals
  • Removing all subtotals at one time
  • Removing the subtotals for only one field
  • Removing grand totals
  • Hiding and showing data items
  • Hiding or showing items without data
  • Sorting your pivot table

Module 3: Examples of Filtering Your Data

  • Producing top and bottom views
  • Creating views by month, quarter, and year
  • Creating a percent distribution view
  • Creating a YTD totals view
  • Creating a month-over-month variance view

Chapter 5: Using Pivot Charts

Module 1: Getting Started with Pivot Charts

  • Creating a pivot chart
  • Understanding the link between pivot charts and pivot tables
  • Limitations of pivot charts
  • Using conditional formatting with pivot tables
  • Customizing conditional formatting

Module 2: Alternatives to Pivot Charts

  • Disconnecting charts from pivot tables
  • Create standalone charts that are connected to your pivot table

Chapter 6: Adding Interactivity with Slicers

Module 1: Understanding Slicers

Module 2: Creating a Standard Slicer

  • Formatting slicers
  • Controlling multiple pivot tables

Module 3: Creating a Timeline Slicer

Module 4: Using Slicers as Form Controls

Chapter 7: Using the Internal Data Model and Power View

Module 1: Understanding the Internal Data Model

  • Building out your first data model
  • Using your Data Model in a pivot table
  • Using external data sources in your internal Data Model

Module 2: Creating a Power View Dashboard

  • Creating and working with Power View charts
  • Visualizing data in a Power View map
  • Changing the look of your Power View dashboard

Thời lượng: 02 ngày
Giảng viên: Microsoft Certified Trainer, MOS Master
Chứng nhận hoàn thành khóa học của Microsoft

KH 2
KH 3
KH 5