Create a custom column
- Thomas Timbul
This guide explains how to create a custom reusable column, which will make use of Excel or other spreadsheet software formulas. For custom historical data columns, please see
Instructions
On the Data / Columns screen, click Add new Custom Column:
This will launch the Custom Column Builder:
The title is what you will see both in the column chooser as well as in the header cell in Excel.
The description is what will show as the description in the column chooser when you hover over the column. It isn’t used elsewhere and could be a reminder to yourself as to what this column is supposed to do (it might not be obvious from the formula)
Format option | Formatted in Excel as |
|---|---|
Unformatted | Generic |
Date | Date with choice of format: (MM/dd/yy, MM/dd/yyyy, dd/MM/yy, dd/MM/yyyy) |
Integer | Number without decimals and with thousands delimiter |
Number with Decimals |
|
Percentage | Percentage with % symbol |
Price |
|
Text | Text |
True/False |
|
Formula:
Arguably the most important part, you can use almost any cell value or formula that is permitted in Excel (or the spreadsheet software of your choice). For example if you are using Google Sheets you can insert an image using “=image(@{132})”.
Formulas that are to be evaluated by Excel need to begin with an equal sign (=). Anything else will just be rendered as typed (but column references will still be resolved).
Column References
You can refer to and use in your calculation any column that exists in PC2, including custom ones. So you can even create building blocks of some formulas and reuse them elsewhere.
To add a column reference simply search for it and click the title.
To add a reference to an input column, use letter references such as @{A}.
Do Columns need to be in the output?
What happens when you remove a column from your output that is used in the formula of another?
If referred columns are missing, Price Checker will transparently embed the formula into any dependent formulas, so the calculations will still work.
If a core column is missing, it’s value will be hardcoded into the formula.
Examples
Let’s assume we’re on row 3:
Custom column formula | References | Rendered in Excel as |
|---|---|---|
@{14} | 14 = AMZ Multi Pack Qty | 2 |
=@{14} | 14 = AMZ Multi Pack Qty (e.g. column P) | \=P3 |
=@{1}+@{85} | 1 = Cost (e.g. column C) | =C3+AQ3 |
@{1}+@{85} | as above | 2.35+0.38 |
=max(@{33}, @{42} | 33 = BuyBox Price New (e.g. column N) | =max(N3,Z3) |
Who want’s some @{11}? | 11 = Amazon Title | Who want’s some 12Pk Dog treat, beef flavour [long expiry]? |
=@{1}+@{85} | 1 = Cost (e.g. column C) | =C3+0.38 |
Two custom formulas:
| 1 = Cost (e.g. column C) 5006 = first formula (assume not on sheet) | =(C3+0.38)*10 |
Allow column to be added multiple times
When this option is selected, it allows you to add this column to the same sheet more than once. Otherwise it will be limited to once per sheet/tab.
Deleting a custom column
You can delete your column just by clicking “delete” and confirming the prompt. A deleted column will also be removed from all sheets. However, PC2 won’t currently check whether you’re using this column in other custom formulas, so be sure to amend those if you need to.
Price Checker does not currently check if your custom formulas contain a column when deleting.