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
- 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.
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.
Available functions
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. See the full list of available date and time functions in Canva Sheets.
Engineering functions
Engineering functions help you convert between number systems and perform bitwise operations. These are useful for data encoding, binary and hexadecimal conversions, and other technical calculations. See the full list of available engineering functions in Canva Sheets.
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. |
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) |
Informational functions
Informational functions help you check the type or status of a value, such as whether it's text, a number, or an error. These are useful for data validation and error handling. See the full list of available informational functions in Canva Sheets.
Logical functions
Logical functions help evaluate conditions and return results based on specific criteria. These are useful for decision-making within spreadsheets. See the full list of available logical functions in Canva Sheets.
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) |
Math and Statistical functions
Math and statistical functions help perform calculations such as addition, averages, rounding, and statistical analysis. See the full list of available math and statistical functions in Canva Sheets.
Text functions
Text functions help manipulate, format, and analyze text data. These are useful for combining text, extracting specific characters, and converting cases. See the full list of available text functions in Canva Sheets.
Was this helpful?
Helpful
Unhelpful