Now that you have been working with the advanced features in Microsoft Excel, you areOn Request Only - Contact DPA
ready to take your skills to the next level. Please note that you should know how to use the
commands shown in the DPA Excel Intermediate and Advanced course before attending this
course. This course will focus especially on Excel formulas and functions and how to create
an efficient and well designed spreadsheet.
Duration: Two-Day Course
Platform: Windows Only
Fee: Call for information
We recommend one to eight people for this course.
Create a Well Designed Spreadsheet
- Create efficient formulas using formula
operators, order of precedence, relative,
absolute and mixed cell references.
- Use range name in formulas.
- Combine the Conditional Formatting command
with the ISBLANK AND ISERROR functions.
- Nest or combine the AND and OR function in the
- Convert the case of text from lowercase to
uppercase or proper case, join several strings of
text from two or more cells and extract data from
a cell. Also, use the Text to Columns command
to separate data into multiple columns.
Date and Time Functions
- Subtract dates using the DATEDIF function and
calculate the difference between two times. Also
learn how to use the YEAR, MONTH and DAY
functions and quickly generate many different
- Learn when to use the AVERAGEA and COUNTA
Lookup and Reference Functions
- Learn when to use the VLOOKUP, HLOOKUP,
CHOOSE, LOOKUP, MATCH and OFFSET
function to look up the data.
- Use the database functions to extract information
from your database without rearranging the data.
For example use, DSUM, DAVERAGE,
DCOUNT, DMAX and DMIN.
Math & Trig Functions
- Learn when to use the ROUND, ROUNDUP,
ROUNDDOWN, CEILING or FLOOR function.
- In Excel 2007, use the new SUMIFS function.
- Use the PMT function to calculate the payment
of a loan.
- Learn how to use data tables to test different
input values without having to retype or copy the
formula for each value.
- Restrict the data that may be entered into a cell
by combining the Data Validation command with
- Add your own calculated fields to the pivot table
and use a dynamic range and that will expand or
collapse as required with a pivot table.
- Use array functions to ensure consistency in
your formulas, For example, use the
SUMPRODUCT function in an array.
What If Analysis (If time Permits)
- Learn how to use the Goal Seek and Scenario
Application Overview: Microsoft Excel 2003
Microsoft Excel is a powerful tool to create and format spreadsheets and to analyze, evaluate, and calculate data. You can use advanced features to manage, track, and chart critical business data to create rich data visualization, PivotTable views, and professional-looking charts. If you perform a task repeatedly in Microsoft Excel, you can automate the task with a macro. A macro is a series of commands and functions that are stored in a Microsoft Visual Basic module and can be run whenever you need to perform the task. For example, if you often enter long text strings in cells, you can create a macro to format those cells so that the text wraps.
IT, Database, Engineering, Finance, Legal, Marketing and Admin Professionals as well as anyone who needs create and edit spreedsheets that can be searched, sorted, analysed, reported, updated and shared with other users.