Formulas in Canva Sheets
Canva Sheets supports various functions to help you calculate values, analyze data, and organize information efficiently.
Copy and paste formulas through Quick Calculations
Select a range of cells and copy formulas from the formula bar and paste them into any cell in your current sheet, making calculations faster and easier.
- Open your Canva Sheet.
- Select a range of cells containing text, numbers, or dates.
- The formula bar shows instant insights based on your selection. Select the dropdown to see other insights such as sum, average, or count.
- Select Copy to copy both the value and formula.
- Right-click on any cell and select Paste. You can also press command + v (Mac) on or ctrl + v (Windows) on your keyboard.
Quick Calculations limitations:
- The formula bar has to be turned on for Quick Calculations to display properly.
- Quick Calculations offers a lean set of high-priority summary insights.
Adding formulas
Canva Sheets formulas and Magic Formulas serve different purposes. Canva Sheets formulas are manually entered using predefined functions, while Magic Formulas use AI to generate formulas from plain language queries.
- Type an equal sign (=) on the cell you want to add a formula to.
- Select the function that you prefer. See the available functions below.
- Fill in the required values by selecting the cells.
Available functions
Math & statistical functions
Math and statistical functions help perform calculations such as addition, averages, rounding, and statistical analysis.
FUNCTION | WHAT IT DOES | EXAMPLE |
|---|---|---|
SUM | Adds up a range of numbers. | =SUM(A1:A10) |
AVERAGE | Calculates the average (mean) of a range of numbers. | =AVERAGE(A1:A10) |
MIN | Returns the smallest value in a range. | =MIN(A1:A10) |
MEDIAN | Returns the middle value in a range. | =MEDIAN(A1:A10) |
MAX | Returns the largest value in a range. | =MAX(A1:A10) |
COUNT | Counts the number of cells in a range that contain numbers. | =COUNT(A1:A10) |
COUNTA | Counts the number of cells that are not empty in a range. | =COUNTA(A1:A10) |
COUNTBLANK | Counts the number of empty cells in a range. | =COUNTBLANK(A1:A10) |
COUNTUNIQUE | Counts the number of unique values in a range. | =COUNTUNIQUE(A1:A10) |
MODE | Returns the most frequently occurring number in a dataset. | =MODE(A1:A10) |
CORREL | Calculates the correlation coefficient between two ranges. | =CORREL(A1:A10, B1:B10) |
PRODUCT | Multiplies the values in a given range. | =PRODUCT(A1:A5) |
SUMPRODUCT | Multiplies corresponding values in two or more arrays and sums the products. | =SUMPRODUCT(A1:A5, B1:B5) |
ROUND | Rounds a number to a specified number of decimal places. | =ROUND(A1, 2) |
ROUNDUP | Rounds a number up to the nearest integer or specified decimal place. | =ROUNDUP(A1, 2) |
ROUNDDOWN | Rounds a number down to the nearest integer or specified decimal place. | =ROUNDDOWN(A1, 2) |
ABS | Returns the absolute (positive) value of a number. | =ABS(A1) |
STDEV | Calculates the standard deviation of a dataset, measuring how spread out the values are. | =STDEV(A1:A10) |
VAR | Calculates the variance of a dataset. | =VAR(A1:A10) |
SQRT | Returns the square root of a number. | =SQRT(A1) |
EXP | Returns e (Euler’s number) raised to a given power. | =EXP(A1) |
LN | Returns the natural logarithm (base-e) of a number. | =LN(A1) |
LOG | Returns the logarithm of a number with a specified base. | =LOG(A1, 10) |
LOG10 | Returns the base-10 logarithm of a number. | =LOG10(A1) |
POWER | Raises a number to the power of another number. | =POWER(A1, 3) |
PI | Returns the value of π (Pi). | =PI() |
Logical functions
Logical functions help evaluate conditions and return results based on specific criteria. These are useful for decision-making within spreadsheets.
FUNCTION | WHAT IT DOES | EXAMPLE |
|---|---|---|
IF | Returns one value if a condition is met, another if not. | =IF(A1>10, "High", "Low") |
IFS | Evaluates multiple conditions and returns the first true result. | =IFS(A1>90, "A", A1>80, "B", A1>70, "C") |
COUNTIF | Counts cells that meet a specific condition. | =COUNTIF(A1:A10, ">50") |
COUNTIFS | Counts cells based on multiple conditions. | =COUNTIFS(A1:A10, ">50", B1:B10, "<100") |
AVERAGEIF | Calculates the average of values that meet a specific condition. | =AVERAGEIF(A1:A10, ">50") |
AVERAGEIFS | Calculates the average based on multiple conditions. | =AVERAGEIFS(A1:A10, B1:B10, ">50", C1:C10, "<100") |
SUMIF | Adds numbers that meet a specific condition. | =SUMIF(A1:A10, ">50") |
SUMIFS | Adds numbers based on multiple conditions. | =SUMIFS(A1:A10, B1:B10, ">50", C1:C10, "<100") |
AND | Returns TRUE if all conditions are met. | =AND(A1>10, B1<100) |
OR | Returns TRUE if at least one condition is met. | =OR(A1>10, B1<100) |
NOT | Returns the opposite of a logical statement. | =NOT(A1>10) |
IFERROR | Returns a default value if an error occurs. | =IFERROR(A1/B1, "Error") |
ISBLANK | Checks if a cell is empty. | =ISBLANK(A1) |
ISERROR | Checks if a value is an error (TRUE for errors, FALSE if not). | =ISERROR(A1/B1) |
Lookup and reference functions
Lookup and reference functions help find specific data within a sheet by searching for values, retrieving corresponding data, and identifying positions. These are useful for data retrieval, matching, and table lookups.
FUNCTION | WHAT IT DOES | EXAMPLE |
|---|---|---|
VLOOKUP | Searches for a value in the first column of a table and returns a value from another column in the same row. | =VLOOKUP(1001, A2:C10, 2, FALSE) |
HLOOKUP | Similar to VLOOKUP but searches horizontally across a row instead of a column. | =HLOOKUP(1001, A1:J2, 2, FALSE) |
XLOOKUP | Searches a range or array for a match and returns a corresponding value. More flexible than VLOOKUP. | =XLOOKUP(1001, A2:A10, B2:B10) |
INDEX | Returns the value of a cell at a specified row and column in a range. | =INDEX(A2:C10, 3, 2) |
MATCH | Searches for a specified value in a range and returns its position instead of its value. | =MATCH(50, A1:A10, 0) |
Text functions
Text functions help manipulate, format, and analyze text data. These are useful for combining text, extracting specific characters, and converting cases.
FUNCTION | WHAT IT DOES | EXAMPLE |
|---|---|---|
TEXT | Converts a value to text with a specific format. | =TEXT(12345, "00000") → 12345 |
VALUE | Converts a text string into a number. | =VALUE("100") → 100 |
CONCAT | Combines text from multiple ranges and/or strings. | =CONCAT(A1:A3) |
CONCATENATE | Joins multiple text values into one (older function, doesn’t support ranges). | =CONCATENATE(A1, " ", B1) |
LEN | Counts the number of characters in a string, including spaces. | =LEN("Hello World") → 11 |
LEFT | Extracts a specified number of characters from the start of a string. | =LEFT("Canva", 3) → Can |
RIGHT | Extracts a specified number of characters from the end of a string. | =RIGHT("Canva", 2) → va |
UPPER | Converts text to uppercase. | =UPPER("hello") → HELLO |
LOWER | Converts text to lowercase. | =LOWER("HELLO") → hello |
Date and time functions
Date and time functions help you work with dates, extract specific components, and calculate time differences. These are useful for tracking events, calculating durations, and formatting dates.
FUNCTION | WHAT IT DOES | EXAMPLE |
|---|---|---|
DATE | Creates a date value from separate year, month, and day inputs. | =DATE(2024, 3, 20) → March 20, 2024 |
DAYS | Calculates the number of days between two dates. | =DAYS(A2, B2) |
YEAR | Extracts the year from a date. | =YEAR(A2) → 2024 |
MONTH | Extracts the month from a date. | =MONTH(A2) → 3 (March) |
DAY | Extracts the day from a date. | =DAY(A2) → 20 |
Financial functions
Financial functions help with investment analysis, loan calculations, and financial forecasting. These functions are useful for managing budgets, calculating loan payments, and analyzing investment returns.
FUNCTION | WHAT IT DOES | EXAMPLE |
|---|---|---|
IRR | Determines the internal rate of return for an investment. | =IRR(A2:A6) |
NPV | Calculates the net present value of an investment. | =NPV(10%, A2:A6) |
PMT | Calculates the loan or investment payment based on fixed payments and interest rates. | =PMT(5%/12, 60, -20000) |
PV | Returns the present value of a loan or investment. | =PV(5%/12, 60, -200) |
FV | Calculates the future value of a loan or investment. | =FV(5%/12, 60, -200, -5000) |
NPER | Calculates the number of periods required to pay off a loan or investment. | =NPER(5%/12, -200, 5000) |
RATE | Determines the interest rate of a loan or investment. | =RATE(60, -200, 5000) |
Error codes
Errors in Canva Sheets occur when a formula has incorrect inputs, invalid references, or calculation issues. Understanding these error messages can help you troubleshoot and fix problems in your spreadsheet.
See suggested fix will appear for syntax errors (e.g., random, incorrect, or incomplete characters). It won’t appear for formula evaluation errors.
ERROR CODE | WHAT IT MEANS | HOW TO FIX IT |
|---|---|---|
#DIV/0! | A number is divided by zero or an empty cell. | Check that the denominator isn’t zero or blank. |
#VALUE! | There’s an issue with the formula or referenced cells (e.g., wrong data type). | Ensure all values are in the correct format (numbers, text, etc.). |
#REF! | The formula refers to an invalid cell (deleted or moved). | Update the formula to reference a valid cell. |
#NAME? | The formula contains an unknown function name or a typo. | Verify function names and ensure all references are spelled correctly. |
#NUM! | The formula contains an invalid number, such as an impossible calculation. | Check for incorrect numerical values or extremely large/small numbers. |
#N/A | The formula can’t find the value it’s looking for. | Ensure the lookup value exists in the referenced range. |
#NULL! | The formula can’t resolve a range correctly. | Make sure the correct range separator is used. |
#SPILL! | The formula results in multiple values, but there isn’t enough space to display them. | Clear adjacent cells to allow the result to expand. |
#ERROR | A general formula error occurred. | Double-check the formula structure and inputs. |
#UNKNOWN! | The error doesn’t match any known category. | Review the formula syntax and ensure all references are valid. |
Was this helpful?
Helpful
Unhelpful