MEGA TRICK: quickly add multiple columns with proper format

Not sure if this is common knowledge or…

Task: add many (10+) columns to an existing table, formatted as something different than text.

Example: a wide table for filling in accounting in Euro currency, where each column corresponds to a month (aka a matrix of data):

Why: because the team used to fill it like that in spreadsheets, or needs this specific layout to copy-paste into spreadsheets for further processing. You could do this with top grouping, but it’s not preserved when copying it over to Excel.

Typical approach: swear a lot, then manually create X columns, manually name them, manually go over each of them and set up proper format.

The trick:

  1. Prepare a list of X values in the format as you need them, tab-separated. For example, print €0.00 followed by a tab in any text editor, then duplicate this X times. Or prepare it in your favorite spreadsheet app.

  2. Create one column in the target table of Text format, and copy-paste the string into a cell in that column:

This will create X columns and guess column formatting based on inserted values. You can mix value types (numbers, currencies, dates) too.

  1. Also you can assign column names in one go following the similar trick. See more here:
    How to Paste column names?

Congrats, you’ve just saved yourself 30+ minutes of work.

The complete demo:

Shameless plug. I’ll be posting more tricks like this on my upcoming blog codatricks.com — please subscribe :slight_smile:

10 Likes

In case no one noticed. When I pasted months and years in the video, they were briefly formatted as large currency numbers. That’s because all dates in Coda are numbers (or more precisely, each date/time is stored as a number of days since Jan 1, 1900 midnight, with decimal part resolving to time on that day, e.g. .5 meaning noon). So Coda parsed “January 2020” etc as dates, and represented them as numbers with a currency sign. However it still stored those as strings internally, hence they still applied correctly as headers.

Thank you for this tip! I will definitely be adding columns your easy way now.