AEDBR
Advance Excel and Dash Board Reporting
Dash Board Reporting
Its time to save your time and gain control over your financial, accounting, auditing, presentations and day to day data calculations, sorting, and storing work, by one of the most intelligent software application .
Advance Excel
- Discover methods to dramatically reduce the time you spend on spread sheets
- Develop practical solutions to your business problems through superior spread sheet design
- Upgrade your spreadsheet skills with advanced data management techniques
- Use macros more efficiently
- Consolidate worksheets from different sources into one workbook
- Develop efficient techniques for using charts
Dash Board Reporting
- Use spreadsheet skills for advanced financial applications
- Create, format and trouble shoot pivot tables
- Analyze data accurately using pivot tables and slicers
- Adopt logical methods to solve complex problems by
analyzing data accurately using pivot tables - Efficient working with related worksheets and workbooks by benefitting from new reporting and presentation techniques
Course Outline,Day One,Excel Overview
Conditional Formatting & Sparklines
- Formatting data on Conditions
- Highlight Duplicate & Unique Values
- Finding top 5 / 10 items in the Data
- Formatting data using Data Bars & Icons
- Clearing & Managing Rules
- Nested loops
- Analyzing only total rows
- Summarizing a data based on single attribute
- Adding data based on two attributes
Summarizing & Consolidating Data
- Creating Sub Totals
- Sorting data with Multiple Level Sorts
- Advance Filtering on more than 1 criteria
- Extracting Unique Values with filtering
- Tips of copying Filtered data to New File
Lookups and Data Table
- Picking data based on row criteria
- Analyzing data based on column criteria
- Match and indexing
- Data Tables single variable
- Data Tables more than one variable
- Data Entry Forms
- Excel speech on data entry
- Creating Grade sheet/Result sheet for performance Creating data list in excel
- Slicer
Pivot Tables & Slicer
- Creating Pivot table & analyzing data
- Refreshing & Changing Pivot Table Range
- Creating sheets from Pivot table
- Calculations & Creating Dynamic Pivot
- Table creation & text to column
- Defining Name & work with tables
- Creating Virtual Fields
- Creating more than one Pivot table on one sheet
- Changing Source
- Slicer
Advance Excel and Dash Board Reporting
- Discover methods to dramatically reduce the time you spend on spread sheets
- Develop practical solutions to your business problems through superior spread sheet design
- Upgrade your spreadsheet skills with advanced data management techniques
- Use macros more efficiently
- Consolidate worksheets from different sources into one workbook
- Develop efficient techniques for using charts
Day Two
Charts & Validation
- Creating Column, Line & Pie Charts
- Shortcuts of Creating Charts
- Changing Chart type
- Adding Target Line in chart
- Method of pasting chart into PowerPoint
- Creating two axis chart & advance charting
- Creating Drop Downs in cell
- Sharing Workbook
- Inserting object
- Hyperlinking
Data Mining
- Auditing spread sheet
- Using Attendance data to calculate payroll
- Identifying source cells
- Changing source data year on year
- Tracing dependent cell
- Rearranging the data in required sequence
- Quick summary of data
Macros
- Record macros for automating tasks
- Creating Button to run a Macro
- Macro with Keyboard & its benefit
- Run / Delete a macro
- File Level Security
- Sheet Level Security
- Making the worksheets invisible
- Preventing Sheets from Deletion Cell / Row / Column Level Security
- Limiting access to cell
Formulas
- Statistical: Count, CountA, CountIF, CountIFs, & CountBlank
- Trim & Conatenate, Sum, SumIF & SumIFs
- Change Case: Upper, Lower & Proper
- Logical: If Condition with Multiple IF’s, Using
- AND & OR in IF Condition
- Lookup: Advance & Multiple Vlookup & Hlookup
