|| Home | Excel | Access | Word | Outlook | VBA | eBooks | Handouts | Site Index | RSS ||
Training Resources for Microsoft Excel
Published: 10 April 2014
This area contains links to information, downloadable handouts and sample files for people who have attended my courses. If you have any questions regarding the material here or the course work please email me at email@example.com. The material here will help you master useful techniques in Microsoft Excel that will help you create professional-looking, efficient and accurate spreadsheets.
Note that all sample files (other than PDFs) are supplied in ZIP format for security. Handouts are in Adobe PDF format which can be read on screen or printed. Unless otherwise stated, Microsoft Office files are supplied in both Office 2007/2010/2013 format (e.g. *.xlsx) and Office 2003 format (e.g. *.xls).
Some material is in the form of video demonstrations. These are usually about 10 minutes in length and are of two types:
You can pause, rewind and replay both kinds of video and watch them as many times as you like. Ideally, you should first watch and listen to the Video Demo then watch the Viewlet whilst carrying out the tame task on your computer, pausing the viewlet as necessary.
Some of these video demos were recorded using Microsoft Excel 2007 but are equally applicable to later versions (2010 and 2013). In many cases, the same techniques can also be applied to Excel 2003.
Excel Spreadsheet Techniques - Part 1
The first demo takes you through the creation of a simple model that forecasts sales over a 10 year period. The techniques used include formatting, writing formulas that include percentages and absolute cell references, and creating a simple drop-down list.
Excel Spreadsheet Techniques - Part 2
The second demo shows you how to add a chart to your spreadsheet. With Excel 2007 the task of creating an eye-catching chart is very easy. This demo show you not only how to build a chart but how to choose which data to include and how to decide what is the best kind of chart for your particular data.
Excel's powerful Subtotals tool provides a quick and easy way to summarise data. After organising your data by sorting you can use the Subtotals tool to insert various kinds of subtotals for each category of data and, if your data allows, add further nested subtotals.
Excel SUMIF Function
This demonstration demystifies Excel's SUMIF function and shows you how to use it creatively to summarise or extract information from your data.
Excel Pivot Tables
Pivot Tables offer the ultimate flexibility for summarising your data. Like the Subtotal tool the Pivot Table tool allows you to analyse and summarise business data. Whilst the Subtotal tool is limited to showing you its results in columns, the Pivot Table displays its results in a grid allowing you to plot one column of information against another. As you will see, it can work with multiple columns and display its results in many different ways. The video demonstration is split into three different sections: Part 1 shows you how to build a basic Pivot Table and how to rearrange the data to suit your needs; Part 2 explains how to use formatting to improve the appearance and usability of the Pivot table; Part 3 introduces the grouping tool so that you can further organize and summarize your data, and also specify how Excel calculates the data it shows.
Sample Data Files
If you are attending one of my Excel classes you will probably use a selection of the files included in this .zip. It contains files in both Excel 2003 and Excel 2007/2010/2013 format:
|©2000-2014 Martin Green firstname.lastname@example.org All rights reserved|