Dynamic Business Reports and Dashboards Using Excel
- القسم البرامج الإدارية
- الكود C925
Course Objectives
By the end of the course, participants will be able to:
- Use their Excel expertise in data slicing and dicing, data massaging, data aggregation, data integration with Access, web, text, SQL, and other databases using pivot tables
- Perform advanced and dynamic data validations
- 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
Course Outline
- Essential reporting requirement skills
- The 20 rules of pivot tables and pivot charts
- Slicer techniques
- Advanced pivot charts techniques
- Multiple consolidation ranges
- Importing text files
- Connecting to access databases
- Connecting to SQL databases
- Customizing connections properties
- Advanced data structuring techniques
- Custom and advanced data validation
- Creating and managing innovative conditional formatting
- Dependent drop down list
- Data validation with conditional formatting
- Data entry form
- Charting and visualization techniques
- Creating dynamic labels
- Using the camera tool
- Working with formula-driven visualizations
- Using fancy fonts
- Working with sparklines
- Fancy thermometer charts
- Thermometer chart
- Performance against chart range
- 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 modeling
- List-box modeling
- Spinner
- Option-button modeling
- Check-box data models
- Combo box modeling
- Macro charged reporting
- Recording, editing, testing VBA macros
- Building a macro driven reconciliation program
- Building budget variance reporting program
- Building a vendor and invoice analysis report