Excel Advanced Extra – 1 Day

Text is straight forward to work with in Excel, however by learning to use the Text functions in Excel you will learn how to manipulate and manage strings of text data in your spreadsheets.

The key topics covered are:

• Understanding Text Functions
• Using the PROPER Function
• Using UPPER and LOWER Functions
• Using the CONCATENATE Function
• Using the LEFT and RIGHT Functions
• Using the MID Function
• Using the LEN Function
• Using the SUBSTITUTE Function
• Using the T Function
• Using the TEXT Function
• Using the VALUE Function

Excel’s information functions give you information about the data contained with in a cell or or a range of cells. This can be extremely useful when working with lists of mixed data.

The key topics covered are:

• Understanding Information Functions
• Using the CELL Function
• Using the ISBLANK Function
• Using the ISERR Function
• Using the ISODD and ISEVEN Functions
• Using the ISNUMBER and ISTEXTFunctions
• Using the TYPE Function

This session will show you the maths functions that Excel contains. These can be extremely useful when manipulating numbers. For example, the ability to round a number that is a currency figure is very important to keep the figures sensible in a financial spreadsheet.

The key topics covered are:

• Understanding Maths Functions
• Using SQRT
• Using ABS
• Using INT
• Using TRUNC
• Using ROUND
• Using ROUNDDOWN and ROUNDUP
• Using ODD and EVEN
• Using CEILING
• Using MROUND
• Using PRODUCT
• USING SUMPRODUCT

This session introduces the statistical functions that Excel can easily calculate. Excel is not designed as a specialist statistical package but does offer some very good functionality for relatively straight forward statistical analysis.

The key topics covered include:

• Understanding Statistical Functions
• Status Bar Statistics
• Using COUNTBLANK
• Using MODE
• Using MEDIAN
• Using LARGE and SMALL
• Using STDV
• Using VAR

Excel is very widely used to build financial forecasts for businesses. This session runs through the financial functions that Excel offers to automate analysis of financial data.

The key topics covered include:

• Understanding Financial Functions
• Using PMT
• Using FV
• Using NPV
• Using RATE
• Using EFFECT
• Using NOMINAL

This session focuses on how Excel handles and manipulates dates. It shows you how to use Excel to calculate future days and dates and also how to work with calendar functions.

This key topics covered include:

• Understanding Date and Time Functions
• Using NOW
• Using HOUR and MINUTE
• Using TODAY
• Calculate Future Dates
• Using DATE
• Using Calendaring Functions
• Using WEEKDAY
• Using WEEKNUM
• Using WORKDAY
• Using EOMONTH

Given that Excel is the standard spreadsheet package used in offices worldwide it is very often used for data management and manipulation. Where data is held in another software program and needs manipulating and presenting it is very likely that it will be imported into Excel before being worked on. This session leads you through the tools that Excel contains to help make data imports as quick and simple as possible.

The key topics covered include:

• Understanding Data Importing
• Importing from an Earlier Version
• Understanding Text File Formats
• Importing Tab Delimited Text
• Importing Comma Delimited Text
• Importing Space Delimited Text
• Importing Access Data
• Working with Connected Data
• Unlinking Connections
• Exporting to Microsoft Word
• Exporting Data as Text

This session covers some of the more advanced techniques used when working with macros. For example how to test your macro and also how to create a button which when clicked will trigger your macro to run.

The key topics covered include:

• Preparing Data for an Application
• Recording a Summation Macro
• Recording Consolidations
• Recording Divisional Macros
• Testing Macros
• Creating Objects to Run Macros
• Assigning a Macro to an object