Overview:
This course shows you how to work more efficiently with workbooks and worksheets, enhance the appearance of data and worksheets, perform analysis using functions, and to use the sorting and filtering features to manage and analyse data.
Learning Method:
This course uses a combination of instructor presentation, and student hands-on practical exercises. Participants will have the opportunity to apply what they have learned in the classroom throughout the course.
Who will benefit from this course?
This course is intended for current Microsoft Excel users who wish to extend their knowledge and skills beyond building simple workbooks.
Pre-requisites
This course is designed for current Microsoft Excel users who can build basic workbooks. It is assumed that the user will know how to create and save a workbook, enter simple formulas, and print and chart data.
What can you expect to gain from this course?
After completing this course, you will be able to:
- work with one or more workbooks at once
- use a range of techniques to work with worksheets
- adjust the sizing of columns and rows in a workbook
- use a variety of viewing techniques to view worksheets
- format cells in a workbook
- understand and use absolute referencing in a workbook
- use the fill technique to enter data into cells
- use names and labels
- use a range of logical functions
- use the various Lookup functions
- sort data in lists in a workbook
- use Autofilter to display data in lists selectively.
Course Content:
Module 1:Working with Workbooks
- The open dialog box
- Opening a workbook
- Closing a workbook
- Opening multiple workbooks
- Working with multiple workbooks
- Opening recently used workbooks
Module 2:Working with Worksheets
- Copying a worksheet
- Renaming worksheets
- Moving worksheets
- Inserting and deleting worksheets
- Grouping worksheets
Module 3:Adjusting a Worksheet
- Inserting cells
- Deleting cells
- Inserting columns
- Inserting rows
- Deleting rows and columns
- Resizing columns
- Resizing rows
- Hiding grid lines
Module 4:Viewing Worksheets
- Freezing columns and rows
- Splitting windows
- Using zoom
- Hiding rows and columns
Module 5:Formatting Cells
- Using the font tools
- Using the alignment tools
- Using the number tools
- The format cells dialog box
- Formatting fonts using the menu
- Superscript subscript and strikethrough
- Changing horizontal alignment
- Centring across a selection
- Wrapping text
- Changing text orientation
- Changing vertical alignment
- Formatting dates
- Formatting numbers
- Formatting currency
- Creating custom formats
- Borders and shading
- Clearing cell formats
Module 6:Absolute Referencing
- Absolute and relative referencing
- Problems with relative formulas
- Tracing precedents
- Creating absolute references
- Creating mixed references
- Circular references
- Filling a series
- Filling a growth series
- Filling a series backwards
- Filling using options
- Creating a custom series
- Modifying and deleting
Module 8:Custom Series Labels and Names
- Labels and names explained
- Accepting labels in formulas
- Using text labels
- Creating labels
- Creating names using the name box
- Using names to select cells
- Using names in formulas
- Creating names between workbooks
- Creating names for constants
- Creating a list of names
- Changing the range for names
- Deleting names
Module 9: Logical Functions
- Logical function concepts
- Displaying information using IF
- Displaying values using IF
- Nesting an IF function
- Using the AND function
- Using the OR function
- Using the NOT function
Module 10: Lookup Functions
- Requirements of lookup functions
- The CHOOSE function
- The ISERROR function
- The VLOOKUP function
- Testing lookup function s
- Using VLOOKUP for exact matches
- The INDEX function
- Performing a simple sort
- Sorting on more than one column
- Sorting numbers
- Sorting by rows
- Multiple value criteria
- Using wildcards in custom criteria