Overview
The Core Essentials course picks a combination of salient topics designed to introduce participants to MS Excel and get them off to a productive start. The course concentrates on simple and complex data management techniques. The aim is to be able to organize data for a wide range of report types using formulas and several other tools and techniques.
Objectives
At the end of this course, participants should be able to build and organise data; extract reports; create and customise charts as well as monitor trends in their data sets. Participants should also be able to protect worksheets and workbooks and deal effectively with all issues concerning printing of documents in MS Excel.
-
Beginner
-
18 Hours
GHC 600
Per Participant
Content
Advanced File Tasks
- 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
Working with Function and Formulas
- Understanding Relative and Absolute Cell References
- Understanding Basic Mathematical Operators
- Using Formulas with Multiple Cell References
- Understanding the Formula Auditing Buttons
- Fixing Formula Errors
- Modifying Error Checking Options
- Displaying and Printing Formulas
- What are Functions?
- Finding the Right Functions
- Inserting Functions
- Some Useful and Simple Functions
- 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
- What are Array Formulas?
- Defining Basic Array Formulas
- Using Functions within Array Formulas
- Using the IF Function in Array Formulas
Managing Tables
- 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
- Conditional Formatting
- The Format Painter
- Cell Merging and AutoFit
- Find and Replace Formatting
- 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
- Absolute and Relative Cell References
Printing and Viewing your Workbook
- 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
- 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
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...