Advanced Excel

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 courses, 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.

What will we cover in this course?

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  Excel 2007 and Excel 2010 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. We do not encourage studenst to take this course if they have not taken an earlier Excel course with us.

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.

Cost of courses

  • Online learning via broadband

The most popular option is to take a course as an online learning student. Our online broadband learning courses features rapid enrolment, many multi media instructional movies, online forums, multi-choice reviews, questionnaires, submission of assignments, email support etc. This is an enhanced learning experience at an affordable price. Visit http://www.softwaretutor.designcad.com.au now and enrol. The course cost is $AUD75.00 for >30 hours of high quality training.You can pay by PayPal which accepts direct deposit and credit cards and start immediately or send us an email requesting an enrolment. We will enrol you and post out an invoice. Payment can then be made by direct deposit. Our bank details will be on the invoice. We do offer reduced rates for students from the same organisation.

  • One-on-one training (Adelaide or Canberra - in our offices or yours).

We can also provide one-on-one face-to face training. Each course takes one day and costs $AUD550.00. We do require some information about what it is that you or your organisation hopes to achieve by training so that we can prepare appropriate materials in advance of the course.

Each participant in one-on-one full day courses is automatically enrolled at no extra cost in the broadband learning version of the matching course. This would normally cost an additional $75.00 per person. In this way, students get a double benefit - personal tuition and the ability to access any of the written instructional materials online, replay any of the demonstration movies, download sample files, log questions, take quizzes and submit assignments for review.

Some revision

Movie

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.

Movie

Click here to play a movie which illustrates the use of the VLOOKUP function.

Cost of courses

This course is only offered on a one-one basis. Please call for details of costs and locations.

About our courses & methodology

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 unrelated practice exercises. Our courses are specifically designed so that this can occur. We introduce a topic, provide a movie showing you how to do the task with your software and then give detailed step-by-step instructions for you to follow.

When you have finished this course

Please remember to build on your knowledge of Illustrator after you complete this course. We strongly recommend that Illustrator be used quite intensively after these early stages of learning. You will lose a lot if you let new-found skills ‘wither on the vine’. Practice is the key to learning sophisticated software. The key to success is to complete your first real project as soon as possible.

What you will achieve by taking this course

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.

Running the course

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.]

Tip  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.

  Module Title

  Description

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.