Spreadsheet versus Database

Explore

Spreadsheet versus Database

How to switch from spreadsheets to databases

Goodbye, Spreadsheet!

Transforming your Spreadsheet setup

Nina Kastenauer

Surprising differences

You won’t be able to just copy and paste your existing Excel-sheets into Coda and call it a day.

If the two set-ups differ so much that one provides massive advantages over the other, it follows that one tool’s set-up can’t just easily be copy and pasted into the other tool

“What got you here, won’t get you there”.

Instead, you’ll have to transform your spreadsheet data so that it fulfills a database’s data requirements.

Modifying your spreadsheet data

Here’s a link to the demo spreadsheet we’ll be working on:

⁠

Step 1) Insert new sheet to prepare Data for Export

We will insert two new sheets, and will title them “Prepare for Export - Projects” and “Prepare for Export - Tasks”.

In these, we will bring in all corresponding data for Projects and Tasks, respectively, and we will ensure that each of these two sheets fulfill the criteria outlined below.

Step 2) Every row needs to contain all relevant information and all data has to be stored in a table format.

Data cannot be stored in a different sheet, inside a sheet name, or in a different part of the same sheet.

Below are examples of spreadsheet data that will require further transformation before it’s ready to move to a database.

❌

⁠

❌

⁠

❌

⁠

✅

⁠

Step 3) Every cell within each column needs to be same data type.

If Cell A2 contains a date, then A3 cannot be text.

Step 4) Every column can only contain ONE specific formula.

This just might be the biggest surprise for spreadsheet users.

Let’s say in your spreadsheet you have a column for “Invoice Amount USD”. So far, you’ve made these calculations on the fly. Sometimes, you multiply the number of items with the price for each (B2 * C2). Sometimes, you deduct a discount ((B2 * C2) - 20%). Sometimes, you add a surcharge. And so on.

This does not fulfill a database’s requirement for data integrity. The rule can be summed up as “Same rights for everyone”. You’re free to define any formula you wish, but it will be applied to all cells within that column. One work-around would be to generate three column formulas: Column “Regular Invoice Amount”, Column “Discounted Invoice Amount” and Column “Increased Invoice Amount”. You get the idea.

Step 5) The first row shall always contain be the column headers.

Gallery

Want to print your doc?
This is not the way.

Try clicking the ⋯ next to your doc name or using a keyboard shortcut (

CtrlP

) instead.