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 values calculations
• Creating names from 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 residency expiry date

• AUTOMATE HR REPETITIVE TASKS
• Creating macros for HR reports
• Using macros for monthly reports
• Saving macros in personnel macros workbook
• Assigning macros to 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 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