User guide

Formulas and computed columns

Write an Excel-style formula to add a new column, including VLOOKUP across open files, date functions, and logic.

Formulas compute values for every row in a file. They always create a new column. Computed columns and lookups are part of PRO; inserting a blank column works in every edition.

Adding a computed column

You can add a computed column from the menu by clicking Data > Add computed column. You can also press Cmd+K (Ctrl+K on Windows and Linux) and type “add column”.

Give the new column a name. Then write a formula starting with =. The formula uses the same syntax as Excel.

A formula runs over the whole column. You cannot type a formula into a single cell. Cells hold single values. If you type a formula into a cell, BigXL notices. It then offers to turn your formula into a computed column instead.

Referencing columns

You can reference a column by its name like full_name or city. You can also reference a column by its letter like B or D.

If a column name has spaces or special characters, you must wrap it in brackets. Use brackets like [Order ID] or [full_name 2].

Text functions

Text functions help you clean and modify strings.

• Join text with &. Example: =full_name & " (" & city & ")"
• UPPER(text) converts text to uppercase.
• LOWER(text) converts text to lowercase.
• TRIM(text) removes leading and trailing spaces.
• LEFT(text, n) returns the first n characters of a string.
• RIGHT(text, n) returns the last n characters of a string.
• MID(text, start, length) returns characters from the middle of a string.
• LEN(text) returns the length of the text.
• FIND(text, within) and SEARCH(text, within) return the position of the text, or 0 when it is not there.
• CONCAT(a, b, …) or CONCATENATE(a, b, …) joins multiple values.
• SUBSTITUTE(text, old, new) replaces all occurrences of old text with new text.
• TEXTJOIN(delimiter, values…) joins values with a delimiter between them.

Number functions

Number functions perform math operations.

• ABS(n) returns the absolute value of a number.
• ROUND(n, digits) rounds a number to a specific number of decimal places.
• SQRT(n) returns the square root of a number.
• POWER(base, exp) calculates an exponent. You can also use the ^ operator. Example: =POWER(2, 3) gives 8.
• MOD(n, divisor) returns the remainder after division.
• CEILING(n) rounds a number up to the nearest integer.
• FLOOR(n) rounds a number down to the nearest integer.
• SUM(a, b, …) adds the values on one row, such as =SUM(q1, q2, q3). It adds across columns of the same row, not down a column.
• AVERAGE(a, b, …) averages the values on one row in the same way.
• INT(n) rounds a number down to a whole number.
• MAX(a, b) returns the larger of two values.
• MIN(a, b) returns the smaller of two values.

Date functions

Date functions help you work with dates and times.

• TODAY() returns the current date.
• NOW() returns the current date and time.
• YEAR(date) extracts the year from a date.
• MONTH(date) extracts the month number from a date.
• DAY(date) extracts the day of the month from a date.

Logic and conditionals

Logic functions help you make decisions based on data.

• IF(test, true_value, false_value) checks a condition. Example: =IF(mrr > 1000, "high value", "standard")
• IFERROR(value, fallback) catches errors. Example: =IFERROR(VLOOKUP(order_id, other.csv, 2, FALSE), "no record")
• AND(a, b, …) checks if all conditions are true. Example: =IF(AND(age > 18, country = "US"), "eligible", "not eligible")
• OR(a, b, …) checks if any condition is true. Example: =IF(OR(status = "active", status = "trial"), "current", "churned")
• NOT(test) reverses a condition. It returns true if the test is false.
• ISBLANK(value) returns true if the cell is empty.
• IFNA(value, fallback) uses the fallback when the value is blank.

Operators

You can use standard operators in your formulas.

• Math operators are + and - and * and / and ^
• Text operator is &
• Comparisons are = and <> and < and > and <= and >=

VLOOKUP

VLOOKUP lets you find data in another file and bring it into your current file.

Example: =VLOOKUP(emp_id, salaries.csv, 2, FALSE)

This formula takes the value of emp_id on the current row. It finds that value in the first column of salaries.csv. Then it brings back the value from the 2nd column of that matched row.

The other file has to be open in another tab. The match is always against the first column of the target file. This works the same as Excel.

Always write FALSE for the last argument. This forces an exact match.

If you need to pull several columns from one file, VLOOKUP might be slow. You might also want to see match counts before committing. In these cases, use Join instead.

What is not supported

BigXL does not support every Excel function. If you need a missing function, use an alternative instead.

• HLOOKUP, INDEX, MATCH are not supported. Use VLOOKUP or Join instead.
• SUMIF, COUNTIF, AVERAGEIF and their plural forms are not supported. Use Pivot instead.
• PROPER is not supported. Use Title Case from the Clean menu.

When you type one of these unsupported functions, BigXL tells you where to go. It does not fail silently.