The goal of this Course is to show you how to leverage Excel functionality to build and manage better reporting mechanisms. Each Module in this Course provides a comprehensive review of the technical and analytical concepts that help you create better reporting components - components that can be used for both dashboards and reports.
30% of the course is theory built. 70% uses MS Excel as a powerful tool to design and prepare dynamic business reports, dashboards, and scorecards.
- Analyze large amounts of data and report that data in a meaningful way.
- Get a better understanding of data by viewing it from diﬀerent perspectives.
- Use Excel in data slicing and dicing, data massaging, data aggregation, data integration with Access, web, text, SQL, and other databases using pivot tables.
- Quickly slice data into various views on the ﬂy.
- Automate redundant reporting and analyses.
- Create interactive reporting processes.
- Perform advanced and dynamic data validations.
- Design outstanding visualization charts, dashboards, scorecards, and ﬂash 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.
Business professionals, accountants, ﬁnance analysts, senior and junior accountants, business analysts, accounting and ﬁnance professionals, research professionals, marketing and sales, administrative staﬀ, supervisors, general staﬀ from any function who need to learn and apply state-of-the-art techniques to their daily business reporting, reconciliations, and analysis.
- Reporting, analyzing, and reconciling
- Data modeling
- Integration with external data sources
- Report structuring techniques
- Automation and macros
- Charting and visualization techniques
- Utilizing Excel 2013 / 2016
- Practicing pivot tables
Knowledge of Excel 2010+
Getting in the Dashboard State of Mind
- Defining Dashboards and Reports
- Establish the audience and purpose for the dashboard
- Delineate the measures for the dashboard
- Catalog the required data sources
- Define the dimensions and filters for the dashboard
- Determine the need for drill-down features
- Establish the refresh schedule
- A Quick Look at Dashboard Design Principles
- Rule number 1: Keep it simple
- Use layout and placement to draw focus
- Format numbers effectively
- Use titles and labels effectively
Building A Super Model
- Data Modeling Best Practices
- Separating data, analysis, and presentation
- Starting with appropriately structured data
- Avoiding turning your data model into a database
- Using tabs to document and organize your data model
- Testing your data model before building reporting components on top of it
- Excel Functions That Really Deliver
- The SUMPRODUCT function
- The CHOOSE function
- Using Smart Tables That Expand with Data
- Converting a range to an Excel table
- Converting an Excel table back to a range
The Pivotal Pivot Table
- Customizing Your Pivot Table Reports
- Changing the pivot table layout
- Customizing field names
- Applying numeric formats to data fields
- Changing summary calculations
- Suppressing subtotals
- Showing and hiding data items
- Hiding or showing items without data
- Sorting your pivot table
- Creating Useful Pivot-Driven Views
- 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
- Advanced Pivot Tables Techniques
- Creating Pivots and getting Percentage
- Creating Calculated Columns
- Viewing Top/Bottom 10%Creating Weekly/Monthly,Quarterly, Yearly Report
- Use a conditional Formatting in Pivots
- Creating a Pivot Charts
- Using Slicers
- Converting cross Data in to a Data for Pivot
Excel Charts for the Uninitiated
- Chart Building Basics
- A review of the most-commonly-used chart types
- Preparing data for different chart types
- Creating a chart from scratch
- Charting disparate data
- Common Chart Tasks
- Resizing and moving charts
- Changing chart type
- Creating a combination chart
- Selecting and formatting chart elements
- Working with Pivot Charts
- Pivot chart fundamentals
- Pivot charts and the x and y axes
- Pivot charts formatting limitations
The New World of Conditional Formatting
- Applying Basic Conditional Formatting
- Highlight Cells Rules
- Top/Bottom Rules
- Data Bars, Color Scales, and Icon Sets
- Getting Fancy with Conditional Formatting
- Adding your own formatting rules manually
- Showing only one icon
- Showing Data Bars and icons outside cells
- Representing trends with Icon Sets
- Building a legend for your conditional formatting
- Using conditional formatting with pivot tables
The Art of Dynamic Labeling
- Creating a Basic Dynamic Label
- Adding Layers of Analysis with Dynamic Labels
- Excel’s Mysterious Camera Tool
- Finding the Camera tool
- The basics of using the Camera tool
- Cool uses for the Camera tool
- Formula-Driven Visualizations
- In-cell charting without charts or conditional formatting
- Creating visualizations with Wingdings and things
Components that Display Performance Against a Target
- Showing Performance with Variances
- Showing Performance against Organizational Trends
- Using Thermometer-Style Charts to Display Performance
- An Introduction to the Bullet Graph
- Creating your first bullet graph
- Adding data to your bullet graph
- Final thoughts on formatting bullet graphs
- Showing Performance against a Target Range
- Why Use a Macro?
- Introducing the Macro Recorder
- The Macro Recorder user interface
- Recording macros with absolute references
- Recording macros with relative references
- Assigning a macro to a button
- Macro Security in Excel
- The short-term solution to disabled macros
- The long-term solution to disabled macros
- Excel Macro Examples
- Building navigation buttons
- Dynamically rearranging pivot table data
- Offering one-touch reporting options
Giving Users an Interactive Interface
- Introducing Form Controls
- Adding and Configuring Controls
- Using the Button Control
- Using the Check Box Control
- Check Box Example: Toggling a Chart Series On and Off
- Using Option Button Controls
- Option Button Example: Showing Many Views through One Chart
- Using the Combo Box Control
- Combo Box Example: Controlling Multiple Pivot Tables with One Combo Box
- Using the List Box Control
- List Box Example: Controlling Multiple Charts with One Selector
Sharing your Work with the Outside World
- Protecting Your Dashboards and Reports
- Securing the entire workbook using file protection options
- Protecting worksheets
- Protecting the workbook structure
- Linking Your Excel Dashboards into PowerPoint
- Creating the link between Excel and PowerPoint
- Manually refreshing links to capture updates
- Automatically refreshing links to capture updates
- Distributing Your Dashboards via PDF
Using External Data for your Dashboards and Reports
- Importing Data from Microsoft Access
- Importing Data from SQL Server
- Importing Data from SAP
Ten Questions to Ask Before Distributing your Dashboard
- Does My Dashboard Present the Right Information?
- Does Everything on My Dashboard Have a Purpose?
- Does My Dashboard Prominently Display the Key Message?
- Can I Maintain This Dashboard?
- Does My Dashboard Clearly Display Its Scope and Shelf Life?
- Is My Dashboard Well Documented?
- Is My Dashboard Overwhelmed with Formatting and Graphics?
- Does My Dashboard Overuse Charts When Tables Will Do?
- Is My Dashboard User-Friendly?
- Is My Dashboard Accurate?