|
Microsoft Excel is
the spreadsheet application of choice for
most desk top computers running the Windows operating system and is also
available on Macintosh computers.
As you know from our
earlier course, cells in spreadsheets can hold both numbers and text data. Excel
is capable of manipulating
this data in the twinkling of an
eye - adding, subtracting, multiplying and applying formulae to
extremely large numbers. Excel can also manipulate textual information
very efficiently - finding text information in vary
large arrays of data and efficiently sorting large lists of text is part
of Excel's strength.
Our
earlier course covered the
basics of spreadsheet use. This course builds on that
knowledge and
explores some of
the more sophisticated features of Excel.
We will begin by discussing
spreadsheet design - how can a builder of spreadsheets organize the
operations of a sheet to make it easier for the user? Then we will show you how to create and edit
macros to speed up repetitive
tasks, add buttons to toolbars with links to those macros, set up error
checking in data entry, scan data for variables, import data from other
applications, create name ranges, use some advanced functions,
set up sophisticated scientific charts, introduce some
statistical analysis (Chi squared tests, Student's T-Tests), validate
spreadsheets, introduce the Visual Basic for
Applications programming language, work with ranges
and group sheets etc..
 |
Click
here to play a movie which
summarizes the earlier course. The movie is quite large
(5Mb), but if you are contemplating taking this course with
us, we would encourage you to download and play it. |
A sample from the course - using the
LOOKUP function.
|
|
Click
here to play a movie which
illustrates the use of the VLOOKUP function. |
Excel
is a very
sophisticated application indeed. Even in this so-called 'Advanced' Excel course, we will not be
able to cover all aspects of the use of the software.
However, after
you finish, you
will be certainly be able to create some quite sophisticated
spreadsheets.
We offer three different pricing models covering
the same material:
1.
group or one-on-one courses (Adelaide or Canberra - in our offices or
yours)
Each course takes one day and
costs $AUD750.00. Participants are given access to the same
materials on our broadband learning web site. Here you can
complete additional exercises, join various forums, take
multiple choice tests and submit work for comment.
2. Online learning via broadband - fast Internet connection required
This
course is available by online
learning at $AUD250.00 - these courses feature rapid enrolment,
online forums, multi choice reviews questionnaires etc. Click
here to visit our online
learning site. A reasonably fast broadband Internet connection is
required. Dial up access is not suitable.
Experience over many years has taught us that adults learn best by
progressing through a course of study at their own pace and at times
that suit them. Wherever possible, they like to learn by working on
their own materials, rather than work through long practice exercises. Our courses are specifically designed so that this can
occur- we introduce a topic, provide a movie which shows you how to do
it and then give detailed step-by-step instructions for you to follow.
Courses may be taken at Thebarton in Adelaide, South Australia, in
Canberra in hired facilities, or by distance learning or
broadband learning.
If a course is taken 'in-house' at Thebarton, it normally runs for a
whole day
with a break for lunch. Starting time is 9.15 am and finish time around
4.15 - 4.30 pm. We usually work group or one-on-one, but can take up to 3 students
in our training room. **We only run groups if all students come
from the same organization.**
We
use
PayPal, an organization which takes all credit cards
using a secure Internet facility. PayPal is a partner
within the eBay system. We have chosen PayPal because it
is independent of us, allows us to hold money in trust
until we have delivered the course and is now
operational in Australia. We will provide a tax invoice on
receipt of payment.
If you cannot attend after booking a
course, there is no penalty, we will simply re-schedule
another session.
If this is not possible, we will refund payment in full.
Here is a link to
background material on the philosophy behind our course
design.
If your company or
organization is paying for the course, all we need is a
purchase order and an address to send the course
materials.
An
understanding of basic
computer operation including the ability to create, save and locate files. We
would expect that you have used a computer with
the Windows operating system for some
time and perhaps developed reasonable facility
with a word processor
such as Microsoft Word.
In this course we want take you on a journey
where we work with examples of the use of Excel in a number of different fields.
In some instances we will conduct 'case studies' where we
work from an
initial idea of how to solve a problem, to the generation of a
finished
'professional' spreadsheet which does the job. We encourage you to
drive your learning, either by brining along your particular Excel
problem or by selecting modules from the list below which are
appropriate to your needs.
We
hope that you will return to us and show us what you have been able to
achieve as a result of your study. We would be pleased to receive
example spreadsheets from different disciplines as teaching aids.
The course consists of a series of
modules which even though we have said that there is no particular order
of importance, is perhaps best taken in sequence. [There
is of course, the opportunity to revisit modules in any sequence at a
later stage.]
 As in
our introductory
and Intermediate Excel courses, we will
work though a series of modules . Unlike the earlier courses, this time no importance should be attached to the
order in which we cover various topics. We encourage you to examine the
list of topics below and then indicate those of most interest to you in
an email to us. This will help us prepare the ground for your visit with
us.
|
|
|
Advanced Excel |
|
This course follows
the earlier courses - 'An Introduction to Excel' and 'Intermediate Excel'.
It provides you
with an introduction to some of the more advanced features of Excel. As much as possible, we show how the
software works using real world examples, expanding your knowledge of
the use of functions, the IF statement, pivot tables, relative and
absolute cell references, advanced charting, the database functionality
of Excel, goal seeking, scenarios, grouping and more.
 Examples are taken from both Excel 2003 and Excel
2007 as the principles are the same, no matter what version of Excel is
used. Please
note that there will be occasional overlap of material as a concept may
fit logically in more than one place. |
|
Select the modules below, preferably in
the order shown. After you have completed a module, click the back
button in your browser to return and select the next
module. You may need to allow Active X controls to run (just click the
yellow bar at the top of the screen and Allow Blocked Content. When
returning to this menu of modules, you may have to click the back button
twice. |
|
|
|
Introduction |
Our learning system. Revising
material from our Intermediate Excel course. Surveying some
spreadsheets using more advanced features - charting options, statistical
simulations, a spreadsheet where a syndicate is investigating buying a
commercial property, linking Excel sheets to MS Access and so on.
Surveying tools new to Excel 2007 - spreadsheet size,
enhancements to conditional formatting, AutoFiltering etc. Downloading sample files. |
|
Building Spreadsheet Models |
Designing and building spreadsheets -
adopting an organized approach. Getting a simple financial spreadsheet model working
and working toward a more complex and versatile solution to a business
problem. |
|
Ideas for business solutions |
Some general directions for business
applications. How many titles should a book shop stock? The payoff spreadsheet and the importance of
creating an informative user guide
for each of your spreadsheet models. |
|
Using Help |
Using Excel's help system. The MMULT
function and the help file. Copying
samples from the help file to better understand the use of functions.
The value of properly targeted Internet searches. The usefulness of the
F2 key. The magic of CSE functions. Subscribing to video podcasts. |
|
Functions |
Some revision of the role of
functions. Using names in function arguments. Categorization of
functions. The insert function dialog box. Types of arguments. The need
for some more advanced mathematics. Using the LARGE function. Inserting functions in a formula.
Changing the range of cells over which the formula applies.
The RAND random number function, the Chi squared test. Installing
'Add Ins' to Excel. The INTRATE
function. Creating a spreadsheet to calculate loan repayments.
Calculating the future
value of an investment. |
|
Using named ranges |
Why use named ranges in
Excel? Using ranges effectively. Examining the various methods of creating named ranges.
Using named ranges across different sheets in a workbook. Using ranges in
a formula which draws on data in different sheets and calculates average values
on a summary sheet. Ranges live in memory
- creating a named range which reports year in a cell. The value
of named ranges when using drop down lists and Data Validation. Case study: creating
range names in a spreadsheet of your own. |
|
Using the IF
statement |
Using the IF statement to set up
conditional statements. An example where we set up a formula to check
if values
above and below a nominated number. Using the IF statement to
creating a formula which calculates the payment of bonuses to sales staff
based on performance. using the IF statement to automatically
grade student's work. |
|
Finding and fixing Errors |
Most of us make errors when developing spreadsheet
models. In this module, we discuss most of the common error messages ad
how to deal with them. |
|
Using drop down lists & the LOOKUP function |
Types of data validation. Creating
lists and tables in Excel 2003 and 2007. Using a list for data
validation when the list is on the same and different worksheets. Using VLOOKUP to create drop down
lists of allowed input values. Experimenting with
and using this great time saver in formulae: a sheet which automatically
grades student results, a sheet which uses lookup to extract tax rates,
a sheet to grade grade performance of
packing shed lines and so on. |
|
The Excel Interface |
Examining the user interface
- the appearance and arrangement of elements in the Excel screen. The
role of title bar, locating cells, columns and rows, cell identifiers,
toolbars, naming sheets and saving workbooks. The differences in the
interface between earlier versions of Excel and Excel 2007. The editing
box (or formula bar) and its role. The importance of watching the shape
of the mouse pointer as you work with Excel. Adjusting row and column
widths and heights both by dragging and auto fit. Selecting and copying
cells from one location to another. Splitting the screen and hiding rows
and columns to make viewing large spreadsheets easier and facilitating
the comparison of columns of data which are widely separated. Toolbars &
tool tips. Manipulating toolbars. Formatting cells in a
spreadsheet, both numbers and text. Entering and correcting data in
cells. Inserting rows, columns, cells and comments. Inserting and
deleting worksheets in a workbook. Moving, copying and renaming
worksheets. Selecting some or all worksheets within a workbook in order
to cut down on data entry time. |
|
Visual Basic for Applications |
Programming in the
Excel environment - Visual Basic for Applications. Using VBA to modify code created by
recording macros. Planning your application. The VBA
editor - adding a module. Adding a procedure to add some numbers using
input boxes. A VBA routine to add names to a sheet. |
|
Pivot tables |
What is a pivot table. When should
you use one? Examples of pivot tables in business and medical research.
Creating a pivot table using the pivot table
wizard, building pivot tables.
Using the pivot chart wizard. Examples of pivot table reports. |
|
Using Forms |
Forms provide a convenient
mechanism for entering data into spreadsheets. We use a
spreadsheet which contains a form used to scroll through a large
data list. We use a find facility built into the form and then
show how to build your own form and populate it with action buttons from
the toolbox. We show that there are two different ways to create forms -
the 'auto' worksheet form and the forms using Visual Basic programming
to connect text boxes to data contained within the sheet. Adding a
form control to the Quick Access toolbar in the Excel 2007 environment.
Using a form in Excel 2007
|
|
Relative & Absolute Cell References |
REVISION. A focus on using
relative and absolute references skillfully in spreadsheets. Switching
between relative and absolute references. Combining all your skills by
building a payroll spreadsheet. |
|
Charting |
This module
covers the generation of charts in Excel more comprehensively
than was done in the earlier course. We
survey chart types, creating various charts using the
chart wizard, show how to graph non-contiguous data, select data ranges,
add to data ranges etc. Emphasis is placed on formatting
charts for ease of data interpretation. Instruction is provided
on moving and re-sizing a chart, changing chart data,
3D charts, adding extra data, placing charts on separate sheets, adding
a trend line to a graph, linking charts to other
applications using both DDE and OLE links. |
|
Using Excel as a Database |
REVISION. Using Excel as a database.
Tips for database design. Managing a stock list for a small business.
Using AutoFilters to display only the required information from a larger
set of data. Complex filtering enquires. |
|
Spreadsheet design |
REVISION.
In this module, we
consider some of the mechanisms which can be used to increase
the functionality and usefulness of spreadsheets. We emphasize
that the 'look and feel' of an Excel application is important to
the user.
Advice is provided on the value of dividing a workbook into a logical
set of sheets. The value of an input sheet. Formatting cells for
input and output. A brief look at error checking, cell
validation, naming ranges etc. |
|
Goal Seeking |
Using Excel to find
the 'solution' to a problem. We change a single input in a cell
and
have Excel calculate a desired target result - the sales that
one department of our company needs to make in order to reach
25% of total sales. Other examples involve a calculation of the
number of cups of a certain beverage type we need to sell to
maximize profit. |
|
Using the SOLVER function |
The companion approach to goal seek. Using Excel to find the
'solution' to a problem in which there are multiple variables. |
|
Scenarios |
Excel's Scenario Manager enables you
to keep track of different scenarios - a worst case scenario and a best
case scenario. Creating and using scenarios. Developing a scenario
report. Editing a scenario. Saving a scenario. Preventing changes to a
scenario |
|
Grouping |
REVISION. Using the group option
to 'pull' data from different workbooks as a summary. Grouping
and outlining. hiding details, promoting and demoting groups. |
|
Case study - filter&count |
A case study - filtering and counting
in a shift attendance worksheet. Creating a new toolbar. VBA example,
assigning buttons. |
|
Managing Stocks & Shares
with Excel |
Using Excel to keep track of a share
portfolio. Setting up a share analysis worksheet, buy and
sell spreadsheets covering various stock market sectors.
Advanced sorting techniques are illustrated and links from Excel
to Access databases are established. |
|
Access/Excel interactions |
Using Excel to hold
database information. Excel as the best 'root' container for
your Access applications. |
|
Other
Office Applications |
Using Excel to mimic MS Project -
Gantt chart. |
|
Performing Calculations |
REVISION.
Using static values in a worksheet. Entering formulae in cells. Using Excel to perform calculations.
The value of the F9 key to recalculate values in a sheet -
generating a set
of random numbers. Displaying formulae in a sheet. Printing
a master copy of your formulae for audit purposes. A general
discussion regarding functions in formulae. Some common
functions - minimum cost, average cost, total costs, number of
costs and the count function. Using operators in formulae.
Using constant values in formulae. Displaying
formulae in a spreadsheet. Order of calculation - operators.
Using logic operators - IF, NOT, AND in formulae. Locking data (and formulae) in cells.
The importance of correctly formatting data in cells. |
|
|
|
|
