To get started on something that might conceivably be of some use to any of you, I want to list the features of a good list.
You have probably already spotted this, but one of our fundamental skills as knowledge workers is creating and working with lists of things. This ought to be considered one of the basic skills - a transferrable skill - a building block in the skills that combine to enable a knowledge worker to do knowledge work.
It’s no surprise to find that, along with everyone else, accountants use lists all of the time: lists of department codes, lists of staff on the payroll, lists of capitalised assets, lists of debtors who still haven’t paid… the list is endless!
So, how hard can ‘listing’ be? Anyone can write out a list, and an accountant is surely as good as the next person at putting a list into a column of consecutive cells in Excel.
Incredibly, as simple as this sounds, poor technique can waste alot of your time. If you find that an update to a list is required, or simply that the list needs different presentation, then flexibility will be required. Now, if your customers are anything like mine, the ‘updates and changes’ scenario is as common as Wimbledon. For efficient progress, you need to be able to re-use your list without any further reorganisation - and definitely without re-entry of data.
The key to list efficiency is being able to pass…
The Data Menu Test
You probably haven’t heard of the Data Menu Test before, because I’ve just invented it to sum up my key message here. And what does it mean? It’s simple. Passing the Data Menu Test is done by ensuring you can use the commands under the word ‘Data’ in your Excel menu.
I’ll revist the data menu commands and running a practical Data Menu Test in a future post, but for now I’ve put the practicalities of passing the test into… a list:
- Use a single Excel sheet for a list. Not keeping all of your list in one place is simply bad. There’s no doubt that for some later requirements you will want to report some list-extracts on separate pages, but we’re talking about capture at this stage, not reporting. Sometimes when combining related lists from separate sheets into one, a new column is required to distinguish rows according to which sheet they came from. Using this new column, ensure that the originating sheet of each row can be identified. This way, no information is lost.
- Use only one row for each ‘record’. Often a report from some other system will ‘fold’ the information of one list-item onto multiple lines, but this is a formatting option and should not be a part of your list compilation.
- Use one column for each ‘field’. The meaning of data in a column, from the top of the list to the bottom, should be clear and consistent. This generally means that the type of information in each cell of a column is uniform. Certainly, you should not change the meaning of a column part way down. Use a separate column wherever necessary.
- Use a single header row. Lists are much easier to use when each column is given a title. A problem arises, however, when the column title is longer than will fit comfortably in a single cell. In these circumstances, beginners often use two cells for the header text, one on top of the other. This is a mistake. If necessary, widen the column, abbreviate the header text or format the header cell to ‘wrap text’.
- Leave no blank rows or columns in the list. All cells forming any part of the list should be contiguous.* In particular, do not leave a blank row between the header row and the remaining rows.
- Do not include any summary information. Lists are frequently constructed in order to derive some sort of summary information, such as a sum total. However, these should not contaminate your basic list or else any subsequent calculations will need to be elaborately constructed to avoid summing the sums.
* - The inverse should also be true - any cells that do not form part of the list should be separated from the list by one or more completely blank rows or columns.
Armed with this list, you’ll have grasped the key to the data menu and be on the way to huge gains in productivity. Banish for good those time-wasting, energy-draining spreadsheets of unstructured data!
Comments and feedback are welcome!
0 Response to “Lists of things (in Excel)”