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. 🙂
5 thoughts on “Essential Tips For Creating Re-Usable Spreadsheets”
Interesting, If I had to do the same thing I'm not sure I would cover the same stuff...
Layout - yes!
Styles - I don’t use these myself, so I don’t thing I would teach them to others.
Formulas - well it's a tricky one, where to start/stop? but yes defo something on formulas.
Data validation - the inbuilt excel functionality is IMHO very poor. If I have to validate I will use my own formulas of code. - so I think I would miss this out.
Controls – yeah, pointing out that for most user it’s a good idea to avoid using Active-X controls on the worksheet
Protection – Yeah, I have done some vids which might be useful for you – it will at least brake up your seminar a bit!
Can’t remember the last time used a template, but I think that’s just me!
I would also include topics of:
Conditional formatting. – Ties in with validation.
Macro Recorder, How to use, What it does, Why it does this, what it does not do, what you can us it for, what not to use it for, How to link it to your worksheet.
Working with large volumes of data, SS v’s DB, how to work better in Excel with big data, when you really need to use a DB.
Pivot tables – how to link to DB?, what there good for, what they are not good for, gotchas, how to update the PT from the data sources,
Maybe a bit on charting/infograpics?
Spread sheet design – how to make SS readable, look and work professionally.
I guess it all depends on your users to some extent
Cheers
Ross
Hi Ken
Ive got some design slides on the codematic web site, you're welcome to use them.
Agree with most of your above list, and also:
I think n-tier inputs>>calcs>>reports is fundamental for any non trival ss.
avoid protection it breaks auditing functionality and lulls you into a false sense of security, any compenent user can bypass it, no vba skills needed (copy cells to new ws delete protected one, rename unprotected one, job done (usually))
I'm not convinved about styles, I've seen a few workbooks recently with several hundred in, I'm guessing its from copying full worksheets from one workbook to another, but it looks a bit flaky to me.
Agree with the rest of yours and Ross's list
cheers
Simon
Hey guys,
Thanks for the comments. 🙂
Since you both brought up styles... the reason I was thinking of including them was because of the ease of updating cells later. Typically, I'll create one or two custom styles, specifically for data entry. This allows me to update the colours, protection, fonts, etc... very quickly if someone doesn't like it.
On protection (and styles too), I think it comes with a caveat. We all know Excel is not a secure platform, and that will be made very clear. In my work, I don't even use a password on protected sheets. Protection from intentional destruction is not the point. It's protection from accidental destruction that I'm planning on selling.
I'd forgotten about Ross's videos, and will definitely be checking out your site, Simon. 🙂
Conditional formatting would be a good add, I think, too...
Ross, you seriously don't use templates? I've tons at work, and the drive many of the inputs for our database systems. (From coding invoices for A/P entry to requesting cheques to acting as the front end to a reporting database.) Anywhere I can use one I will.
On protection:
Like ken I was thinking from a working POV not a security one really, you can tab forms if worksheet protection is on too, which is good for a lot of form type things.
No I don't use templates Ken. Although only yesterday some one asked another guy in the department for a solution that was perfect for a template - I don’t think he used one either! Most of my work are models or applications so I either use a file system or the SS is just one thing which gets versioned as a whole file. - I think it's just useage - Maybe i could make some of those models in to template files, I've just never really though about it or seen the benefits?
Sorry Ken - I never really use templates either. I know I should, but the only ones I aways do are to set up my Excel environment to print out properly.
I have done the odd one for clients, but generally they are happy enough opening a normal file and saving it with a new name (I often do these as readonly recommended)
cheers
Simon