Intermediate

---

Intermediate Level

This course is designed for those who have completed the introductory course, or who have a good working knowledge of the software.  By the end of this course, delegates will be able to use some of the more advanced features of Excel, to produce complex financial models.

Pre-Requisite:  Delegates attending this course should be already working with Microsoft Excel and wish to expand their knowledge, or are converting from another Electronic Spreadsheet package

Duration:           1 Day

Data Lists

• Create a data list
• Use a form to view, sort and enter records
• Perform single and multi level sorts
• Filter a list using Autofilter

?

 Borders and Shading

• Apply cell borders and shading
• Apply Autoformat

?

 Viewing a Large Worksheet

• Go To a specific cell or named range
• Split a worksheet into different viewing panes
• Freeze and Unfreeze panes as worksheet titles
• View a workbook in multiple windows
• Compare worksheets side by side
• Hide and unhide rows and columns
• Hide and unhide worksheets and workbooks

?

 Managing Worksheets

• Switch between worksheets
• Enter and edit data on multiple worksheets
• Copy data between worksheets and workbooks
• Insert and delete worksheets
• Move and copy worksheets
• Rename worksheets
• Change the colour of the worksheet tab

?

 Logical and Nested Functions

• Use the IF, AND, OR and NOT functions
• Use nested functions

?

 Financial Functions

• Use financial functions (PMT, FV, PV, NPV)

?

 String, Date and Time Functions

• Extract text from a string (Left, Right, Mid)
• Change case (Upper, Lower, Proper)
• Find and replace text within a string (Replace)
• Convert between text and number values (Text)
• Enter date and time functions (Today, Now)
• Return selected parts of a date (Month, Year)

?

 Statistical Functions

• Use other statistical functions (Min, Max, Large, Median, Mode, Stdev)

?

 Creating and Apply Styles

• Define a style
• Apply a style to worksheet cells
• Remove a style from cells
• Modify an existing style
• Copy styles between workbooks

?

 Proofing Tools

• Use AutoCorrect
• Use Find and Replace
• Check spelling across the worksheet

?

 Adding Comments

• Add a comment
• Review, edit and delete comments
• Print comments

?

 Formatting a Chart

• Add, remove, move, resize chart elements
• Format chart elements
• Format chart axes
• Format data series