Spreadsheets for Teachers: A 40-page guide for teachers, written by a maths teacher

The full 40-page guide is for sale on TES, and contains sections on: Data tables and charts Formatting and editing Formulas and referencing Dealing with errors Advanced techniques

The trial version includes a sample of the sections, and a complete list of the contents.   This trial version is available to download here:

About the author

I have been working with spreadsheets for over 10 years, both for fun (yes, you can make games in Excel!) and for work (as a maths teacher).  I'm self-taught, and have designed spreadsheets for a wide variety of purposes.  The more I learn, the more I realise there is to learn, so although I would consider myself an advanced user, even I cannot claim to be using Excel to its full capacity (see disclaimer at the bottom about programming).  

To date, I have generated over 100 custom spreadsheets for use in the Maths classroom, from Pythagoras' Theorem to the A-level Exam Questions Index, in addition to many, many more for such varied applications as record-keeping, budgeting, project management, and even cattle rationing.  The beauty of Excel is that it provides you with a powerful tool while at the same time presenting an elegantly simple interface.  You can use it as little more than a framework for tables of numbers, or you can turn it into a custom program for recording and analysing data that users won't even recognise as a spreadsheet, and practically anything in between.  

For some more examples of spreadsheets I've created, see this sample of my ICT Maths teaching resources.  Note: some of these spreadsheets will require you to enable macros (see: Why and how should I enable macros?) or use the links in the Spreadsheets for Teachers trial version booklet.  

Common topic areas for study:
As you begin to dig deeper into Excel, it may be beneficial to have an overview of the main areas you would most like to work on, so that you get the most out of the time.  For the same reason, it is recommended that you have a blank spreadsheet to mess around with to properly assimilate ideas.  

• Data tables and charts - the best ways to lay out tables in Excel, how to efficiently analyse large sets of data, using the LOOKUP functions, generating and editing graphs and charts to display the data you want in the way you want.  Also includes use of the sort functions and Excel's built-in filter capability (as well as drawbacks to each of these you should be aware of, and work-arounds).  
• Formatting and editing - how to format data the way you want (date formats, currency, custom settings), changing the appearance of a spreadsheet through font and background choices and row and column editing (including how it will look if printed), and conditional formatting (where the appearence changes automatically depending on the data itself).  Also includes Data Validation and protecting cells (for ease of user input - choosing values from drop-down menus and ensuring the nuts and bolts of the spreadsheet are not accidentally tampered with by other users).  
• Formulas and referencing - understanding how Excel interprets cell references (including how to take advantage of absolute and relative referencing, and referencing other sheets within the workbook or even other workbooks), learning the syntax of basic functions for calculating in Excel as well as some of the more useful logic functions such as IF, text functions (for rewriting names, for instance, in the desired format), and some clever statistical analysis functions (averages, measures of spread and correllation, etc).  Also includes use of the RAND function for generating random numbers - useful for statistical simulations, generating an arbitrarily ordered list, etc. 
• When things go wrong - how to deal with errors that inevitably arise while designing or using a spreadsheet, recognising and interpreting common error codes, understanding how Excel deals with different types of data and limitations on certain functions, as well as tracing errors and identifying mistakes in formulas or data input.  Also includes how to minimise errors due to user incompetence ('nothing is foolproof to a sufficiently talented fool') and how to design your spreadsheet to deal intelligently with errors that do occur.  
• Macros and working behind-the-scenes - for the more serious user, a macro is a mini-program that you set up to run inside  a particular spreadsheet - it may simply change certain values, or take the user to a new sheet, or even print something out.  The advantage is that you can make your spreadsheet as accessible and easy-to-use as you like, condensing potentially confusing routines into a single button-push.  All the messy nuts-and-bolts of the operation are done by excel in half a second, and the user experience is that much more slick as a result.  I can provide an introduction to macros, and even get you started on the exciting world of VBA (the programming language used to create macros), but see disclaimer at the bottom of the page.  
  

Click the screenshot below to download an editable Excel version of my Excel topic checklist, or to just have a read through, here's a pdf version.  

Just to clarify...
I am not a computer programmer, I'm a Maths teacher.  This means if you want to go beyond a beginners introduction to VBA (the programming language behind macros), I'm not the right man for the job.  If you want to find out more, I recommend the marvellous resource of Ozgrid, a massive online forum and general repository of knowledge, frequented by people even higher up the Excel ladder than me, where you can usually find answers to questions you might have about Excel.  As a general rule, if you're wondering if it can be done in Excel, the answer is probably yes, and how to is probably on Ozgrid.