|
|
|
Intermediate Excel
|
|
|
|
This course follows our earlier course ('An Introduction to Microsoft Excel')
and is organized in much the same way. We aim to take
your further along the path to mastering Excel and
importantly to see how using the program can improve your
efficiency at work. The course contains instructional
material examples created using both Excel 2003 and Excel
2007.
As much as possible, we show how the program works using
real world examples. We cover such things as the use of
functions, handling the Excel interface, the use of conditional
formulae using the IF statement, we take a
closer look at the use of relative and absolute cell
references in formulae, advanced charting, the database functionality of
Excel, goal seeking, scenarios, grouping and more.
|
|
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, downloading sample files. Revising
your understanding of some of Excel's basic capabilities
by developing an expenses spreadsheet. We ensure that you
understand the differences between relative and absolute
cell references by working on a spreadsheet which is used
to calculate payroll. Excel as a database tool, Excel as
a web site tool. Collecting field data, manipulating
lists.
|
|
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.
|
|
Spreadsheet design
|
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.
We emphasize the
value of including diagrams as an aid to the
interpretation of spreadsheets.
|
|
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. Creating new toolbars. 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.
|
|
Using drop down lists & the LOOKUP function
|
Improving the quality of data entry by the use of drop
down lists. Drop lists on the same sheet as data. drop
lists on different sheets. Experimenting with and using
this great time saver in formulae. Examining a sheet
which automatically grades student results. Using VLOOKUP
to create drop down lists of allowed input values.
|
|
Performing Calculations
|
REVISION MODULE:
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.
|
|
Using Forms
|
There are two aspects to form - copies of paper forms and
'smart' forms. We look at sevceral 'paper' forms and then
use a forms to provide a convenient mechanism for
entering data into spreadsheets. 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.
|
|
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.
|
|
Functions
|
A quick revision of the use of functions in Excel.
Inserting functions in a formula, changing the range of
cells over which the formula applies. Creating a
spreadsheet to calculate loan repayments. Installing 'Add
Ins' - the Data Analysis Pack and its use to perform
Student's T Test on two sets of samples.
|
|
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. A case study - some detective work
in a spreadsheet which uses the IF statement.
|
|
Avoiding Excel Errors
|
Methods for ensuring that your spreadsheets are free from
errors. What to do about error messages, intermediate values,
validating your spreadsheets.
|
|
Importing data into Excel
|
Bringing data from main frame computer databases into
Excel.
|
|
Using MailMerge
|
Using Excel to manage your mailing lists in conjunction
with Microsoft Word.
|
|
Conditional Formatting
|
Formatting cells according to different criteria.
|
|
Relative & Absolute Cell References
|
REVISION MODULE. 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.
|
|
Using Macros
|
Creating macros to speed repetitive tasks.
|
|
Downloads
|
Visit the downloads page if you have not already
done so.
|
This course requires an enrolment key