Now that I've caught you with an alluring title... 😉
This area is actually a great passion to me, as I'm not a big fan of maintenance work. I like to sit down, think my project through, build it, and then never have to worry about it again. A fantasy? Maybe... but I do have some projects at work which are exactly that. One of my databases has been in use for 4 years with very little tuning.
I will, most likely, be teaching a 3 hour course in late March on "Creating Re-Usable Spreadsheets" to an audience of people who work with Excel regularly. They are accountants, and oversee staff members who also use Excel. The point of this seminar is not necessarily to cover cutting edge concepts, but rather to drive home design points to build spreadsheets that can be used, and re-used, without breaking. In addition, I'll throw in some time saving tips throughout the session that can speed up their productivity.
I'm thinking that my approach for the course will probably be based on my Spreadsheet Design Tips article, and will start by:
- Covering the 3 types of spreadsheets
- A discussion on spreadsheet risk
The purpose of the above is to draw attention to how important solid thought out design is, and the risks that can be generated from ad-hoc creation of spreadsheets to solve business issues. I'd like these people to realize that by investing time up front to design their worksheet well, they can significantly reduce training times, error rates, and ensure that worksheets that they pass to their staff come back working, as compared to just looking like they still work.
From there, we'll start focussing on actual design work and tools by way of a case study. This will involve:
- A discussion on layout and efficiency for the data entry clerk
- Usage and implementation of styles
- Using appropriate formulas wherever possible to reduce redundant or unnecessary data entry
- Leveraging Lookup and Index/Match formulas
- Setting up data validation
- Adding form controls
- Applying protection options
- Saving files as templates, and how to determine if it is appropriate to do so
Naturally, it will end off with a question and answer period.
I figure that with 15 minutes per subtopic, (some more, some less,) I should be able to cover off about 2.5 hours, and a Q&A session should draw it out nicely.
So what do you think? Are there any pet pieces that you see that I've missed? Anything that you wonder why I'd cover? I'm curious on your take. 🙂