Course content
 Download PDF File
Course Objectives
By the end of the Program, Participants will be able to:
-
Boost Excel Business Intelligence (BI) expertise in business and management reporting.
-
Perform automated report writing, analysis and reconciliation.
-
Link their Excel with PowerPoint for dynamic data update.
-
Develop dynamic BI dashboards, scorecards and flash management reports to assist professionals in measuring performance and enhancing decision making.
-
Use advanced report development techniques by linking-up Excel with Access, Web, Text, SQL, Oracle and other databases.
-
Design, prepare and run reports using PowerPivots as a BI tool.
-
Perform data analysis techniques to produce timely and accurate reports.
Business professionals, business analysts, research professionals, marketing and sales, administrative staff, supervisors, general business professionals and staff from any function who need to learn and apply state-of-the-art techniques to their daily business reporting, reconciliations and analysis.
Tools and Techniques
Consolidating Data from Separate Files and Sheets
Advanced Data Validation Using Lists, Dates and Custom Validation
Array Functions
Cell Management Tools: Left, Right, Mid, Concatenate, Value
Naming Cells and Ranges
Subtotal, Sumif, Sumifs, Sumproduct
Looking-Up Data, Texts, and Values Using Vlookup
The Incredible Table Tools Techniques
Slicing Dates into Day Names, Month Names, Years and Quarters
Text to Columns and Dynamic Trimming Using Trim, Len,
Find and Substitute
Text Change Functions
The 19 Must Learn Pivot Tables Tools
Creating Pivot Tables
Number Formatting Techniques
Designing Report Layout
Sorting in Ascending, Descending and More Sort Options
Filtering Labels and Values
Expanding and Collapsing Reports
Summarize Data By Sum, Average, Minimum, Maximum, Count
Show Values as % of Total & % of …
Pivot Table Options
Inserting Formulas
Date Analysis
Copying Pivot Tables
Creating Pivot Charts
Dynamic Chart Labeling
Mastering the Slicer
Showing Report Filter Pages
Linking Pivot Tables and Pivot Graphs with PowerPoint
Conditional Formatting with Pivot Tables
Designing Reports Using the GetPivotData
Report Design and Modeling Techniques Spinner
Check Box Data Modeling with IF function
Option Box Data Modeling with IF function
List Box Data Modeling with CHOOSE function
Linking Excel with Text Files
Linking Excel with Databases (Access)
Linking Excel with SQL
Linking Excel with Internet
Scenario Manager
Charting and Visualization Techniques
Creating Dynamic Labels
Using the Camera Tool
Working with Formula-Driven Visualizations
Using Fancy Fonts
Leveraging Symbols in Formulas
Working with Sparklines
Creating Unconventional Style Charts
Tips and Tricks in Excel
Controlling and Protecting Your Reports, Analysis and
Reconciliations
Data Entry Form
Custom List
Text to Speech
Protecting Worksheets and Workbooks
|