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.

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

Refer to your hand out notes.

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

Ideas for business solutions

Some general directions for business applications. The payoff spreadsheet and the importance of a user guide.

Relative & Absolute Cell References

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

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.

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.

MailMerge

The raw data for the generation of form letters can come from a variety of sources - mainframe computers, specialist databases such as the Bureau of Statistics, databases such as Access, FileMaker Pro, SQL Server, Oracle etc. It is not difficult to obtain that information from those sources in the form of ASCII text files. Excel can load those files expertly and you can use the combination of Microsoft Word and Excel to generate form letters, filtering data as you go if that is what is required. the filtering can also be done in the database before outputting the ASCII text file.

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.