When you first start using Excel, it seems like a good idea to break your data up as much as possible so that it’s easy to find/read. It’s not uncommon to have lots of sheets containing similar data for different departments / people / products / stores / months etc.
However, as you start to learn the power of Excel, using functions such as pivot tables and filters, you’ll find that it begins to make sense to keep source data in one big table that’s easy to analyse – it’s much easier to summarise a big block of data than it is to consolidate data fragmented across sheets. Answering the question “How many staff have a First Aid Certificate?” is no longer a time-consuming trawl across multiple sheets (or books), but the work of a few clicks of the mouse.
As a standard approach, my workbooks have blocks of data on one sheet, with analysis on one or more others; validation data goes on its own sheet too (“but I have to keep a validation list on the same sheet as the range I’m validating, don’t I…?” No – there is a way…). So, the sheets I have are typically:
- Input_Data – a huge block of data, with only column titles at the top, with a named, self-expanding range
- Analysis_sheet(s) – containing the calculations, pivot tables, etc
- Validation_List(s) – one or more sheets with lists for use in data validation (restricting what people can enter into certain cells – e.g. a list of store names)
- Parameters – those key inputs that I have to put somewhere, e.g. VAT
- Output_Summary – if required, a sheet summarising the key outputs I want people to see
…but with rather more meaningful sheet names, of course….