Dashboards and Business Reports Using Excel
Why Attend?
Course Methodology
Course Objective
By the end of the course, participants will be able to:
Boost Excel expertise in data slicing and dicing, data massaging, and data aggregation, and data integration with Access, web, text, SQL, and other databases using pivot tables
- Perform advanced and dynamic data validations and master level formatting techniques
- Design outstanding visualization charts, dashboards, scorecards, and flash reports
- Develop master level report solutions using advanced form controls and buttons
- Record, write and edit powerful macros that will perform routine tasks in no time
- Understand the principles of data analysis
- Learn to synthesise and summarise information into a logical framework
- Consider when to use a chart or a table
- Know how to use visual effects to improve their reports and presentations
- Explore how to summarise, present and communicate data clearly and concisely
- Learn the principles of good dashboard design and presenting data graphically
- Discover the tools to analyse data & build reports using your existing Excel tools and skills (and perhaps a few you didn’t know you had!)
YOUR TWO DAY COURSE OUTLINE
Data Analysis Tools
Tool selection
Evaluation of Excel vs. other BI alternatives, including Power Pivot in terms of speed, usability and cost
- Shortcuts
Speed up your data manipulation in Excel with the use of shortcuts - Tables & PivotTables
Analysing data more quickly and efficiently using structured reference table and summarise using PivotTables - Power Pivot
Demonstration of the Microsoft add-in to Excel, PowerPivot
Using Excel as a Data Analysis Tool
- Essential Excel tools & functions
- Creating a robust formula
- Working with text
- Cleaning your data
Overview of must-know tools and functions for data analysis such as LOOKUPS, logical and aggregation functions
Nest functions together to create succinct and robust formulas
Using text and concatenation functions to string numbers and text together to show in a dashboard or chart.
Tools and tricks to clean data before using it to build a report or dashboard
Charting in Excel
- Working with charts in Excel 2013/16
- Creating a Combo or Bubble chart
- Waterfall charts
Overview of new charting features of Excel
Show correlations by charting on different axes and chart types or bubble charts
Comparison of different methods of waterfall charts, create your own template for future use.
Essential reporting requirement skills
- The 19 rules of pivot tables and pivot charts
- Advanced pivot charts techniques
- Multiple consolidation ranges
- Retrieving external data using Microsoft query
- Importing text files using MS query
- Connecting to access databases
- Connecting to SQL databases
- Importing from data connection wizard
- Importing from Microsoft query
- Customizing connections properties
Advanced data structuring techniques
- Custom and advanced data validation
- Creating and managing innovative conditional formatting
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
- Fancy thermometer charts
- Colored chart bars
Building report solutions
- Conceptualizing and understanding report solutions
- Developing a report solution
- Configuring spreadsheet report data options
- Enabling background refresh ,Refreshing data when opening the file
- Combo-box data modeling tool
- List-box data modeling tool
- Form controls data modeling tools
- Option-button modelling, Check-box data models
- Combo and group-box
Macro charged reporting
- Recording, editing, testing VBA macros
- Working with standard VBA code for report design and
- management Building a macro driven reconciliation program
- Building budget variance reporting program
- Building an email based report distribution program
- Building a macro based report printing system
- Building a financial statement preparation system
- Building a vendor and invoice analysis report
Dashboards and Business Reports Using Excel
Delegate Fee: US$ 2 500, 00
Tel: +27 11 066 1931
Email: info@pldsa.co.za
The above registration fee includes all training materials and meals during the training.
Make payment to:
Account Name: Prosperity Leadership Development
Bank: Standard Bank
Account Number: 252714857
Branch Code: 001206
Branch Name: Clearwater
Swift Code: SBZA ZA JJ
*PLEASE NOTE: Payment is required
in full 5 days from date of invoice.
Our Training Courses
Finance:
● Finance for Non-Financial Managers
● Finance for Project Management
● Forensic Accounting and Fraud Investigation
● How to Conduct a Successful Internal Audit
● Debit & Credits: How Accounting Really Works
● The Essentials of Budgeting: From Creation Through Application
Business Report Writing & Proposals:
● Technical Report Writing
● Report Writing Skill
● Advanced Business Report Writing
● Environmental Reporting for Mining
● Operation Workshop
Administrative Skills:
● Filing, Correspondence & Record Management
● 2nd Annual Office Professional & Best Practice In Meeting & Minutes Taking Master Class
● 4-Day Office Executives Combo
● Professional Secretarial & Administration Skill
● Executive PA – for PAs & Executive Secretary
Sales & Marketing:
● Strategies for IT and Technical Professionals
● Successful Telephone Sales Techniques
● Advanced Negotiation Strategies
● Successful Selling Plans
● Successful Importing & Exporting
Mine & Engineering:
● Mine Machine Productivity
● Statistical Analysis & Method Validation for Microbiological Laboratories
● African Woman in Engineering Conference
● Forensic Engineering & Failure Analysis
● High & Medium Voltage Testing
● Procedures Masterclass 2012
● Earthing Systems for Utilities and Industrial Distribution
● Urban Renewal and Sustainable Human Settlements Conference
● Healthcare Waste Management Workshop
● Disaster Risk Management Conference
● Executive PA – for PAs & Executive Secretary
Human Resource:
● Performance Management
● Drafting and Implementing HR Conference
● Occupation Health and Safety in the Construction Sector Workshop
● Labour Relation & Labour Law
● Effective Policy and Procedure
● Development
● Strategy, Design and Implementation
● Organisational Development
● Strategic Business Planning
● Business Contract
● Employment Equity Relations Workshop
● Strategic Facilities Management (SFM)
● Disciplinary Hearing Proceedings
● Documentation & Confidentiality
Leadership & Management:
● Leadership and Management Courses
● Advanced Emotional Intelligence & Leadership Skill Management
● Project Management
● Asset Procurement & Disposal Management
● Mastering Leadership & Management Skills
● Corporate Fraud Control and Investigations
● Managing Discipline Effectively
● Executives Leadership Programme
● Management Empowerment
● Management Skill For New Supervisors
● Managing the IT Department
● IT Compliance Data Security Workshop
● Transforming Government Supply Chain Management
● Fleet Management Workshop
● Stress Management in the Security Sector Workshop
● Knowledge Management in the Public Sector Workshop
● Environmental Management for Sustainable Development Conference
● Water Quality Management Workshop
● 2nd Annual African Woman in Leadership Conference
● Protocol, Diplomacy, Etiquette and Event Coordination
