Using 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 AddIns - 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.

Revisiting the Excel Interface

A second look at the Excel interface. Examining the 'Getting Started' pane. Manipulating toolbars. Problems occurring when the standard and formatting toolbars are combined. A look at the functions available on the various Excel toolbars - standard, formatting, borders, chart, control box, drawing, exit design mode, external data, forms, formula auditing, list, pivot table, visual basic, word art, picture, reviewing and web toolbars. Creating a new custom toolbar; populating toolbars with icons. Adjusting the properties of a toolbar item.

Naming sheets, switching between worksheets, copying and moving worksheets around in a workbook. The value of input sheets to improve spreadsheet design. The importance of mouse pointer. Manipulating toolbars. Splitting and freezing the screen.

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. 

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. 

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: - from a user perspective

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.

Performing Calculations

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 drop down lists & the LOOKUP function

Experimenting with and using this great time saver in formulae. Examining a sheet which automatically grades student results. Using VSVLOOKUP to create drop down lists of allowed input values.

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.

Solver

The companion approach to goal seek. Using Excel to find the 'solution' to a problem in which there are multiple variables.

Scenarios

Using the scenarios tools in Excel.

Grouping

Using the group option to 'pull' data from different workbooks as a summary. Grouping and outlining. hiding details, promoting and demoting groups.

Managing Stocks & Shares 

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.

Pivot tables

Using the pivot table wizard, building pivot tables. Using the pivot chart wizard. Pivot table reports.

Mail Merge

Mail merge is not a simple process. However, when Excel is used in combination with Microsoft Word, merging can be set up to proceed smoothly. In this module we using Excel as a database to hold details of names and addresses, create a form letter in Word, review the data source information, add merge fields and merge this data with the form letter. The filtering data functions in Excel/Word are used to produce letters for specific sub groups.

Some Preliminaries

Exchanging spreadsheet data with different versions of Excel. Scrubbing diskettes for safe export of data. Different methods of starting Excel. Creating a suitable folder structure to hold Excel workbooks. Changing the user preferences - adjusting the AutoSave time and back up type. Changing the International settings.  Installing 'add-on' applications such as Microsoft Map and statistical packages.

Export/Import

Understanding Excel's data format. File types supported in Excel. Text files; *.prn, *.txt, *.CSV, Backup files; *.xlk, *.bak, Data Interchange Format; *.dif, SYLK files; *.slk, Toolbars; *.xlb, Add-Ins; *.xla, xll, Templates; *.xlt, Workspaces; */xlw, dBase files; *.dbf, Works 2.0; *.wks, Quattro Pro; */wq1, Lotus 1-2-3; *.wk?, Web Pages; *.htm etc.

Importing data by opening CSV files. Exporting data from Excel - space delimited & comma delimited files. Linking Excel to Microsoft Access - two methods of sharing data. Sharing Excel sheets among different users. Exchanging workbooks with other users.

Installing Add-Ins to the Excel environment

Installing additional functions in Excel. Developing a spreadsheet for calculating the interest rate on an investment using one of these extra functions - the INTRATE function. We develop a model for calculating the 'proper' rate of return on an investment. Installing tools for statistical analysis.

Using Excel Filters

A case study updating and enhancing a spreadsheet used to manage attendance of teams of workers working rotating shifts. Using the COUNT function, a practical example of the database functions in Excel.

Fundamentals

A review of spreadsheet fundamentals. Displaying formula in a sheet. Static (constant) and dynamic data. AutoFill. Creating series of different types.

Making Excel perform calculations

Reviewing your knowledge of building formulae. Entering formulae, editing formulae, functions, AVERAGE, MAX, MIN, COUNT, mathematical operators, sequence of operations, logic operators, range checking. Exposing formulae in a sheet. The mechanism for locking cells containing with formulae. Protecting worksheets.

Cell references

A review of relative and absolute cell references. Copying cells with references, switching between absolute and relative references. Using Excel as a database, using filters.

Macros and Visual Basic for Applications

The first step in automating tedious tasks in Excel is to learn to create macros. We create and edit various macros. Groups of macros can be combined using the Visual Basic for Applications programming language.

Designing & building spreadsheet models

Designing a model on paper before moving to the computer, specifying the problem in pseudo - language, identifying the variables needed to solve the problem, gathering together any algorithms that we may need to solve the problem, build the sheet.

Data lists

Database applications in Excel. Using Auto filters, advanced filtering, creating a custom filter, finding records, sorting sub-totals etc.

Analyzing scientific data

XY charting, Chi square test, t test, linear regressions, etc.

Validating spreadsheets

Making sure that your spreadsheet gives the correct answers. Testing your spreadsheet on a regular basis for compliance.

Graphic elements in spreadsheets

Using the graphics toolbar, types of graphic files suitable for use in the Excel environment, formatting objects, resizing graphics.

MYOB to Excel

Importing data from accounting packages such as MYOB and QuickBooks. Using Excel to present accounting data in a palatable form.

Excel functions

An overview of the use of functions in formulae using some case studies.