TECHNOLOGY
Moving From Spreadsheets to Tech Stacks: What to Keep in the Sheet
Moving from spreadsheets to tech stacks starts with a raw table that still matches after a plain-text export, then you move one job at a time.
A workbook that started as a quick log has become the place your team checks the numbers, and someone has told you to leave it. Moving from spreadsheets to tech stacks starts with the raw table, the file you still trust after a plain-text save.
Save that table as a plain text file and open it again. If every value you care about is still there, you can move one repeated job into code and leave the sheet for the people who read the result.
This page walks through the layout to fix and the signals that a job should leave the grid. You also get a path that moves one job at a time, with the sheet still open for everyone else.
What Spreadsheets Still Do Well
A spreadsheet is still the right place to type observations, scan a small extract, and hand someone a table they already know how to filter. Research teams use it for collection. Operations teams use it because the workbook is the process: the budget, the tracker, the list a colleague will edit before lunch.
People who only need to read a figure, or to fill a template and send it back, are served by the grid. People who want to join your rows to another file, or rerun the same cleanup next month, need a file their code can open without repair. Those are different readers of the same workbook, and both of them show up.
The sheet also wins when the math is short and someone must audit it by clicking. A total that points at the cells above it can be a clearer record than a script nobody on the team can run. The trouble starts when that clarity is doing the work of a database, a cleaning pipeline, and a report at the same time.
That split only helps after the sheet is tidy enough to leave. The layout rules below are the part to finish before you pick Python, R, or a database.
How to Organize a Spreadsheet You May Leave
Fix the storage rules in Data Organization in Spreadsheets, from Karl Broman and Kara Woo in 2018, before you pick a language. Be consistent, write dates as YYYY-MM-DD, put one thing in a cell, keep a single rectangle with one header row, and keep calculations out of the raw file.
Build One Rectangle With a Header Row
Computers read a table as rows of observations and columns of variables. A title block, a color legend, and a second header that groups countries under a continent name all look helpful on screen, and they all break the moment another tool reads the file.
- Use one header row, and use the same column name for the same fact on every tab.
- Put one observation on each row, and repeat a grouping value on every row instead of merging the cell above a list.
- Split combined codes, so a plate position written as 13-A01 becomes a plate column and a well column.
- Keep a data dictionary, on its own tab or in a text file, that names each column, the unit, and the codes you allow.
- Put formulas on another sheet, or in a script, and leave the raw cells as values you typed or imported.
The continent example is the one people argue with, because the grouped layout looks neater. Europe written once above Slovakia, Germany, and Italy does not attach Europe to those rows once the sheet is exported. Europe on every row does.
Dates, Blank Cells, and One Value Per Cell
A date written as 02-03 means 3 February in the United States and 2 March in much of Europe. YYYY-MM-DD removes that argument. If the program rewrites dates on import anyway, store year, month, and day in three columns and build the date again in the analysis tool. Time written as hours, minutes, and seconds usually survives the trip.
Blanks are a second trap. In a ten-minute flower watch, typing 600 when no pollinator arrived makes that row look like a pollinator at the last second. A blank could mean the watch never happened, the value was lost, or the value was the same as the row above. Add a second column for whether a pollinator was seen, and fill both columns on every row.
WRITE THE VALUE, NOT THE COLOR
If a red cell means failed and a blank means same as the row above, a CSV export keeps neither meaning. Add a status column, fill every row, and say in the dictionary what NA means.
Units belong in the dictionary or in the column name, not beside the number. A cell that says 45 g is two facts. A cell that says 45, with grams recorded once for that column, is one fact a script can sum.
Run the CSV Export Test
The Data Organisation in Spreadsheets chapter in The Turing Way treats a CSV round trip as the test of research data. What survives as text is the data. Font, fill color, borders, merged cells, and formulas are not.
- Export the data sheet to CSV, or follow publishing your data as a CSV when the file is for analysis rather than reading.
- Close the original workbook so you are not still looking at colors and merged titles.
- Open the CSV in a plain editor or in a new sheet.
- Check that every header, status, and note you meant to keep is still a value in a cell.
- If something vanished, add a column for it, fill it, and export again.
CSV keeps one sheet at a time. For a workbook with several data tabs, export each tab and store the set together with the dictionary. Once that bundle matches the workbook, you can ask which job still belongs in the grid.
When Should You Move Work Out of a Spreadsheet?
Leave a job in the sheet when a person must see it, click it, or type a small correction. Move it when the same steps will run again, when the row count is near a hard stop, or when the logic only exists inside formulas one person understands.
| Signal in the workbook | Stay or move | Why |
|---|---|---|
| You repeat the same cleanup on a schedule | Move the steps | A script or a recorded cleaning tool can run those steps the next time the file arrives |
| The sheet is near a row cap, or it is saved as.xls | Move the raw rows | A current worksheet holds 1,048,576 rows by 16,384 columns; Excel 97-2003 and the older.xls format stop around 65,000 rows |
| Only one person can explain the formulas | Move the logic, keep a view | Commented code is easier to reread than a wide block of cell formulas |
| Color, a blank, or a merged cell carries meaning | Fix the sheet first | A CSV export keeps cell text and drops the formatting |
| A colleague must audit a short sum by clicking it | Stay | A visible formula can be the right record when the math is short |
| You must join files, fit a model, or run on a schedule | Move | Those jobs depend on tools outside a single grid |
Match a large import against the Excel specifications and limits before you trust it. Those limits apply to Excel for Microsoft 365 and to Excel 2016 through Excel 2024. The guidance on creating and sharing spreadsheets, first published on 3 June 2021 and updated on 5 October 2023, tells you to check the cap before you publish, because rows the tool cannot show can distort the result. If the file is an official statistics release, that guidance points you on to the Government Analysis Function standard for statistics workbooks.
In England, 15,841 positive tests were left off the daily figures from 25 September to 2 October 2020 after an import used the old.xls format and the rows past the smaller cap never arrived. The grid did what its format allowed. The process had no separate check that every incoming row had landed.
The next choice is which tool holds the job you decided to move.
Where Each Job Goes After the Sheet
A small stack is enough for most teams. Raw rows live in CSV or in one database table. Cleaning that you will repeat lives in a script, or in a tool that writes the steps down. The number a manager must see can stay in a sheet. You do not need a new platform for every layer.
Keep the Sheet for What People Must See
For a file you update over time, such as a budget, formulas and cell references keep totals in step with the inputs. For a statistical snapshot taken at one moment, hard-coded values are appropriate, because those figures are not supposed to change when someone opens the file later.
- A first look at a new extract, before you know which columns matter.
- A short formula a colleague has to audit by clicking the cells.
- The small summary you hand to someone who will not open a script.
- A column of manual overrides, written as values, that the next run is allowed to read.
If your first instinct is the pivot table, have the script write that summary back into a small sheet. People can filter it without reading code, and the heavy join does not have to live in the workbook.
Choose Python or R for the Repeated Work
Python is a general-purpose language, which is why it fits when the same person must also move files, call another system, or run the job on a schedule. R grew up around statistical work, which is why it fits when the deliverable is a model, an interval, and a plot for a paper, especially if the lab already shares R scripts. Either one can read the CSV you just proved.
PICK FROM THE JOB
- The cleanup: OpenRefine records cleaning steps in a browser while the work stays on your machine, which suits a messy export you are not ready to rewrite as code.
- The statistics: R is a natural fit when tests and publication plots are the whole job and colleagues already review R files.
- The pipeline: Python is a natural fit when the steps also have to read other systems or hand data onward.
The language is the wrong argument while the export test still fails. A tidy CSV can be read by both, and the sheet can remain the face of the result.
How to Move One Spreadsheet Job Into Code
Do not copy the workbook cell by cell into a new language. Separate inputs, the steps that change them, and the outputs people read. Then move one repeated step and check it against the old result.
- Save an untouched copy of the workbook, and put a version number in the filename or on a cover tab.
- Make the raw data a rectangle, export each data sheet to CSV, and run the export test.
- Pick one job you repeat, such as the monthly cleanup or one summary, and rebuild only that job in code or in OpenRefine.
- Compare the new result with the old sheet before you trust it, and write the summary back to a small workbook if that is what people open.
- Move the next job only after this one matches. Keep one-off sums in the sheet until a second person has to run them.
Simon Hettrick, then a deputy director at the Software Sustainability Institute, hit this wall on a 2014 survey of UK researchers. In a post on 6 September 2017 he described coming back to his own Excel analysis about three years later and finding the steps impossible to reconstruct, including a missing letter a in a sheet of 3,763 cells. A search for the word taught matched the wrong answers, because the unique phrase was a taught. One subdivision moved. The headline result did not. He had learned Python with pandas from January 2017 and rebuilt the analysis in commented scripts rather than excavating every formula.
I can conclusively say that whilst it’s not impossible to reconstruct the logic behind 40 columns of arcane transformations, it is so, so much simpler to review a couple of lines of well-commented code.
Simon Hettrick, Deputy Director, Software Sustainability Institute, 6 September 2017
He also noted that spreadsheets stay in research because they are accessible, quick, and strong, and that a new postdoc is under real pressure to use the tool colleagues already share. The practical path is the one he ended up on: learn enough code to rerun the job you will regret losing, and keep a record of the steps beside the data.
Spreadsheet Mistakes That Break the Next Tool
The export test fails for habits that feel harmless while you are still inside the grid. Each one removes a meaning your later script will never see.
- Merged titles: A label that spans several columns does not attach to the rows under it after export.
- Color as data: Red for fail, or a highlight for checked, disappears in CSV, so the status needs its own column.
- Hidden rows and tabs: Cosmetic hiding still publishes. Unhide everything before you share, and remember that a CSV of the active sheet includes hidden rows.
- Formulas pasted over raw data: The next analysis cannot tell an imported value from a result you typed on top of it.
- Mixed free text: One column of comma-separated answers arrives with different separators and different spellings for the same tool, and both analyses then disagree.
- Personal data left in the file: Names, addresses, dates of birth, and comment fields travel with the sheet, including in document properties and hidden tabs.
Before you publish, search the grid for column names such as address, postcode, name, dob, and email, and read free-text comment fields. Mark a file that must keep sensitive data, and limit who can open it. A peer check, then a second person, catches more of these leftovers than another pass by the author.
Date coercion belongs on that same list. Identifiers that look like dates can be rewritten on import, which is a known failure with some gene symbols and with ID codes that resemble calendar dates. Three columns for year, month, and day, or a column stored as text, keep the original characters. After the layout survives the export, the repeated cleanup is the job to move, and the one-off sum can wait in the workbook.
Frequently Asked Questions
What Should a Spreadsheet Cover Page Include?
A cover page should name the publisher or department, the publication date, what the file contains, and the version number. Add it even when you think the file is only for you, because spreadsheets often end up with a wider audience than the person who built them. If your cloud drive keeps old copies on its own, learn how that versioning works so you can still point to the copy you shared.
How Should You Name Spreadsheet Files and Headers?
Use a descriptive filename, add a date or date range when the sheet is a snapshot, and include the version number in the name. Stick to hyphens or underscores, and skip acronyms that only your team knows. Match column names across tabs, and keep spaces out of headers, so a column called start_date is not also called Start Date or enddt somewhere else.
Why Do Long ID Numbers Change in Excel?
Excel stores numbers with 15 digits of precision, so a longer ID can change when the cell is numeric. Keep account numbers, sample IDs, and other long codes as text, then check a few values after every import. The same import can rewrite a text code that looks like a date, so do not leave those codes in a column formatted as a date.
What Happens to Macros When You Save a CSV?
Macros do not come along when you save a CSV, and neither do the buttons that ran them. Before you archive the workbook, write down what the macro changes and which process breaks if that change stops. For a file other people will open, ask your IT or security team to review the macro, which is the check the UK government spreadsheet guidance recommends.
Can People Still Use the Sheet After You Add Code?
Yes. Colleagues can keep opening the sheet to read the result and to type a small override, as long as that override lives in its own column of the raw table. The script reads the column on the next run. The edit to retire is the one typed into the middle of a formula, where the next person cannot see it.
Once the plain-text export matches the sheet, pick the cleanup you are about to repeat and move that job first. Leave the one-off sum in the workbook until a second person has to run it again.
-
NEWS4 months agoMicrosoft’s 96% Cyber Score Sits at 86.3% on the Board
-
BUSINESS6 months agoMusk’s $914 Billion Lead Is a Public SpaceX Bet
-
ENTERTAINMENT1 year agoSkrilla’s 6-7 Became Word of the Year by Meaning Nothing
-
SPORTS4 months agoFree Live Sports Streaming Without Cable: An Antenna and App Guide
-
GAMING2 months agoXbox Puts 15-Hour Caps on Game Pass Cloud Play
-
ENTERTAINMENT5 months agoEndgame Encore’s $86 Million Trial for Infinity Vision
-
BUSINESS2 years agoRetail Banking vs Commercial Banking: Who Each Side Serves
-
ENTERTAINMENT2 years agoAndrew Garfield’s Spider-Man Films Are No Longer Free
