Data and spreadsheets
Choose useful columns before building a spreadsheet
List the decisions the table must support, then define what one row stands for. Add a column only when a decision, a filter or a calculation needs it. Give each column one type and one fact per cell, and write a sample value for it before you build anything.
A text to start with
I want to build a spreadsheet to help me decide [decision]. One row should represent [one thing]. The information I have today is [sources and fields]. Suggest columns for this table. For each one, give a name, a data type and an example value, and say which decision it supports. Mark any column that would hold several facts in one cell and propose how to split it.
Example request. Change the details to fit your situation.Column design worksheet
Decision this table supports: [ ] One row represents: [ ] Source of the data: [ ] Column name: [ ] Type (text, number, date, choice): [ ] Unit or allowed values: [ ] Example value: [ ] Decision or calculation it supports: [ ] May be blank? [ ] Columns removed because no decision needs them: [ ] Repeat the item block as needed.
Columns that hide trouble
A column named Notes or Details often holds several facts that later need sorting. A column called Amount may mix currencies, or hold both a price and a quantity. Test the row definition against every record: if some rows are orders and others are customers, the table needs a different shape. For text fields you will filter often, such as status, fix the allowed values. If you cannot name what a column will be used for, leave it out.
Ask another agent to check the result
Review this column plan. Flag any column that mixes different units or puts more than one fact in a single cell, such as a number with its unit typed in. For each, quote the example value, explain what breaks, and propose a split. Add no columns beyond the splits.
Test with real rows
Type five to ten real rows into the proposed layout, including awkward ones with missing or unusual values. Note each value that does not fit its column and adjust the columns. Then try the filter or total you will need most. When the layout holds up, keep the column definitions in a short list beside the file.