Below are some commonly used Excel functions to help map and normalize data into the Data Template for TotalRewards Builder. These functions are useful for cleaning, formatting, and preparing data for accurate uploads.
🪄Common Excel Functions:
1. DOLLAR – Converts a number to text in currency format, with decimals rounded to the specified number of places.
- Common use: Benefits coverage amounts
- Formula: =DOLLAR(number, [decimals])
- Meaning: =DOLLAR(cell_reference, [number of decimal places])
- Arguments:
- number (Required) – A number, a reference to a cell containing a number, or a formula that evaluates to a number.
- decimals (Optional) – Digits to display to the right of the decimal point. Negative values round to the left of the decimal point. Default is 2.
2. TRIM – Removes all spaces from text except single spaces between words.
- Common use: Cleaning irregular spacing in Names, Email Addresses, and Addresses
- Formula: =TRIM(text)
- Meaning: =TRIM(cell_reference)
- Arguments:
- text – The text from which to remove extra spaces.
3. PROPER – Capitalizes the first letter of each word, converting all other letters to lowercase.
- Common use: Formatting Names and Addresses
- Formula: =PROPER(text)
- Meaning: =PROPER(cell_reference)
- Arguments:
- text (Required) – Text in quotation marks, a formula returning text, or a reference to a cell containing the text to convert.
4. IFERROR / IFNA – Handles formula errors by returning a specified value if an error occurs; otherwise, returns the formula result.
- Common use: Preventing upload issues by replacing errors with blanks or default values
- Formula:
- =IFERROR(value, value_if_error)
- =IFNA(value, value_if_na)
- Arguments:
- value (Required) – The value to check for errors.
- value_if_error or value_if_na (Required) – The value to return if an error is detected (e.g., #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL!).
📌 Note: Always test your formulas on a small data set before applying them to the full template to avoid introducing formatting errors into your upload.