course | Mastering MS Excel for Reporting

Data Massaging: Tools And Techniques
 

• Consolidating data from separate files and sheets
• Advanced data validation using lists, dates and custom validation
• Cell management tools: left, right, mid, concatenate, value
• Naming, editing, and managing cells and ranges
• Subtotal, Sumif, Sumifs, Sumproduct, Count, Countif, Countifs
• Looking-up data, texts, and values using Vlookup
• The incredible table-tools technique
• Slicing dates into day names, weeks, week numbers, month names, years and quarters
• Text to columns and dynamic trimming using Trim, Len
• Managing texts and numbers using replace, find, and substitute
• Text change functions
   

Reporting, Analysis And Reconciliations Using Pivot Tables
 

• The 20 must learn rules • Creating and copying 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 and % of
• Pivot table options
• Drill down option
• Showing report filter pages
• Inserting formulas
• Date analysis

Creating pivot charts
 

• Dynamic chart labeling
• Mastering the slicer
• Linking pivot tables and pivot graphs with PowerPoint
• Conditional formatting with pivot tables
• Designing reports using GetPivotData

HR Reports
 

• Listing reports with custom views
• Preparing reports using pivot tables
• Consolidation of data

Cells and Name Ranges
 

• Creating name ranges for data
• Using name ranges for value calculations
• Creating names from the selection command
• Creating a 3-D formula
• Updating name ranges
• Editing name ranges
   

HR Data Formatting
 

• Formatting numbers
• Formatting dates
• Conditional formatting
• Avoiding duplication of employee records
• Data bars for performance appraisal
• Icon sets for job level
• Color scale for employees’ grades
• Greater or less than for earnings
• Based on a formula
• Setting warnings on the residency expiry date

Automate HR Repetitive Tasks
 

• Creating macros for HR reports
• Using macros for monthly reports
• Saving macros in the personnel macros workbook
• Assigning macros to the Quick Access toolbar
   

Data Calculations
 

• Date and time functions for attendance
• Working days
• Years of service
• Logical and Statistical Functions
• Employee pay increase and bonus (paste special)
• Implement HR policies
• Statistical functions for HR data
• Sumif / sumifs / count / countif / countifs / subtotal
• Average / averageif / averageifs
• Lookup Functions
• Vlookup and Hlookup
• Approximate and exact match
• Index and match functions 

Creating Masters With Data Validation
 

• Employee status, locations, and grades
• Employee designations, nationalities, and departments
• Date and number validation
• Custom validation
• Leave Records And Calculations
• Is the employee eligible for leave or a bonus? If function
• Accrued leave
• Leave taken records
• Leave balance
• Payroll And Benefits
• Payroll sheet and salary slips
• Using attendance data to calculate payroll
   

End Of Service Calculations
 

• Computing gratuity
• Computing cashable leave
• HR Charts And Graphs
• Speedometer chart
• Performance against range
• Spinner
• Comprehensive Exercises
• Calculating gratuity
• Salary increment calculator
• Performance review scores