Microsoft Excel – For Human Resource Practitioners
Overview
This is a well-planned module tailored to the needs of Human Resource professionals. The training is based on practical H.R. examples that should enable swift and effective application by practitioners.
Objectives
By the end of this module, participants should be able to create and effectively manage employee databases; produce summarised reports from huge data sets using database tools such as subtotals, pivot tables etc. Participants should also be able to organise personnel records effectively and monitor training schedules using Microsoft Excel’s advanced functions and charting techniques.
-
Beginner
-
18 Hours
GHC 650
Per Participant
Content
Introduction to the Excel Spreadsheet
- Changing the Size of Rows or Columns
- Adjusting Cell Alignment
- Rotating Text
- Creating Custom Number and Date Formats
- Conditional Formatting
- The Format Painter
- Cell Merging and AutoFit
- Find and Replace Formatting
- Adding Patterns and Colours
- Adding Borders
- Working with Styles
- Working with Themes
Doing more with Excel
- Using the IF Function
- Working with Nested Functions
- Breaking up Complex Formulas
- Using Functions and AutoFill to Perform Difficult Calculations
- What are Range Names?
- Defining and Using Range Names
- Defined Names Commands
- Selecting Nonadjacent Ranges
- Using AutoCalculate
- Understanding Relative and Absolute Cell References
- Understanding Basic Mathematical Operators
- Using Formulas with Multiple Cell References
- Understanding the Formula Auditing Buttons
- Understanding VLOOKUP and HLOOKUP
- Using VLOOKUP to Find Data
- How to Find an Exact Match with VLOOKUP
- Finding an Approximate Match with VLOOKUP
- Using VLOOKUP as an Array Formula
- What is an AutoFill List?
- Creating a Custom AutoFill List
- Using a Custom AutoFill List
- Modifying a Custom AutoFill List
- Deleting a Custom AutoFill List
Management of Databases
- What is a Table?
- Creating Tables
- Modifying Tables
- Using the Table Tools – Design Tab
- Adding a Total Row
- What are Records and Fields?
- Adding Fields by Inserting Columns
- Adding Records by Inserting Rows
- Quickly Adding Records to a Data Table
- Deleting Records or Fields
- Cleaning up Duplicate Records
- Sorting Data in a Table
- What is an AutoFilter?
- Creating a Custom AutoFilter
- Using an Advanced Filter
- Using an Advanced Filter with Logical Statements
- Copying Filtered Records
- Filtering with Wildcard Characters
- Validating your Data
- Data Validation using Lists
- Data Validation using Formulas
- Using Database Functions
- Expanding and Collapsing Data
- Filtering Data
- Sorting Data
- Grouping Data
- Refreshing Data
- Editing the Data Source
- Modifying Fields and Labels
- Modifying Values
- Using the Layout Group on the Design Tab
- Applying a Style to a PivotTable
- Changing PivotTable Style Options
- Manually Formatting a PivotTable
- Using the PivotTable Options Dialog
Visual Representation of Data
- Creating a Chart
- Styling Charts with the Design Tab
- Modifying Charts with the Layout Tab
- Additional Styling with the Format Tab
- Manipulating a Chart
- Changing the Type of Chart
- Changing the Source Data
- Working with the Chart Axes and Data Series
- Saving a Chart as a Template
- What are Sparklines?
- Adding Sparklines
- About the Sparkline Tools Tab
- Editing Sparkline Data
- Removing Sparklines
- Showing and Hiding Data
- Changing the Style
- Changing the Sparkline and Marker Colour
- Setting Sparkline Options
File Management
- Using AutoRecover
- Publishing to PDF or XPS
- Protecting an Entire Workbook
- Protecting the Current Sheet
- Opening and Converting Files in XLS Format
- Saving Spreadsheets in XLS Format
- Marking a Workbook as Final
- Encrypting a Workbook
- Managing File Properties
- Managing Versions
- Viewing a Summary of Sharing Issues
- Using the Document Inspector
- Using the Accessibility Checker
- Using the Compatibility Checker
- Setting Compatibility Options
Finishing Touches
- Checking Spelling
- Using the Research Pane
- Using the Thesaurus
- Using the Translator
- Setting your Language
- Using Normal View
- Using Full Screen View
- Using Page Layout View
- Page Break Preview
- Creating a New Window
- Hiding a Window
- Unhiding a Window
- Freezing a Pane
- Splitting a Worksheet
- Switching Between Open Workbooks
- Arranging Workbooks
- Comparing Workbooks Side by Side
- Synchronous Scrolling and Resetting a Window
- Saving a Workspace
- Print Commands
- Print Preview
- Using Basic Print Options
- Other Print Options
- Setting Printer Properties
Course Reviews
Facilitator(s)
Richard Lamptey
Richard Odartey Lamptey has worked in the private sector for a period spanning over eight (8) years. He has consulted...
