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