Understanding Charts and Chart Tools
Determine which type of chart can best display their data, create chart using Quick Analysis tool, create chart using Recommended Chart Tool, add and remove chart data series quickly, create dynamic chart titles and chart labels that can be updated automatically, create chart with multiple-lines Category Axis, customize and format charts using Chart Elements and Chart Style icons as well as filter chart using Chart Filter icon.
Creating Combo Charts
Create Line-Column chart, create secondary value axis on a chart, reverse the category axis and values axis of a chart, show 3-months forecasting trends on a Line chart using Linear Trendline, add a moving average Trendline to a Line chart, display different levels on a chart as well as compare different targets with a chart.
Using In-Cell Visualizations
Learn how to create in-cell Bar chart using the Data Bar Conditional Formatting features, how to create in-cell line chart, column chart and Win/Loss chart using the Sparklines tool, as well as how to use Icon Sets and symbols to visualize the values on a table.
Presenting Your Reports
Learn how to convert a chart into a picture, use Excel Camera tool and Linked Picture options to take live picture of a range of cell that updates automatically, use Screenshot tool to capture a snapshot of charts or tables, paste link chart or table onto a slide so that it can be updated automatically when source data is changed, as well as insert Excel worksheet object into Microsoft PowerPoint for presentation purpose.
Introducing Excel Macro
Learn how to record macro using Macro Recorder and edit macro using VBA (Microsoft Visual Basic for Application), assign macro to a form control button on the worksheet as well as assign macro to a command button on the Quick Access Toolbar.
Understanding Excel Dashboard
Learn some useful dashboard fundamental knowledge, how to import data source, build an effective data model and design dashboard layout using frames, how to present complex ratios and statistics on a dashboard as well as how to create a dynamic and professional-look dashboard using PivotTable, PivotChart, Slicer and Timeline.
Building PivotTable Driven Dashboard
Learn how to import Microsoft Access data into Excel using Ms Query Wizard, filter pivot data with Report Filter, Slicer and Timeline, sort pivot data by color as well as build a dynamic PivotTable driven BI Dashboard.
Creating Dynamic Charts and Tables
Learn how create dynamic chart using table filter and slicer, define dynamic range names using OFFSET function, create dynamic charts with defined names, INDEX & MATCH functions as well as contr chart data series using Data Validation drop-down list.
Adding Interactive Controls to Dashboard
Learn how to use various form controls such as buttons, combo box, check box, option button and scroll bar to manipulate the dashboard. For example, when a check box is checked, charts and tables will be updated automatically. Participants will also learn how to switch between different dashboards using hyperlink buttons and build a dynamic dashboard using form controls and advanced functions.
The case studies are based on real-life business scenarios and problems encountered by Excel users in their daily jobs. Participants will learn useful skills to provide efficient and practical solutions to overcome the problems and increase productivity.