Questions? We have answers.

Summary

The following is a list of some of the most useful Excel formulas and functions for data formatting.

Solutions

Lookup

• INDEX() - looks up by row and column number in a grid. Use with MATCH(), which looks up the position of a value in a row or column
  • a better alternative to VLOOKUP() and HLOOKUP(): it does not require the column where you are looking up the value to be to the left of the column where you are pulling the corresponding value, and the formula automatically updates itself even if columns are moved around or additional ones are inserted
• Examples: Data Formatting Guide: Looking up and Retrieving Data from Another File

Text Manipulation

• SEARCH() - search for a string within another string and return the position number of the first instance found
• SUBSTITUTE() - search and replace in a string
• LEFT() - return the leftmost n characters
• RIGHT() - return the rightmost n characters
• MID() - return a substring that is not the leftmost or rightmost (i.e., somewhere in the middle of the string)
• VALUE() - convert a text string representing a number into a number
• ISNUMBER() - checks if a value is a number (of number type, not a text string representing a number)
• CONCAT() - concatenate a list or range of text strings
• TRIM() - remove all spaces from a text string, including leading and trailing spaces, except for single spaces between words
• TEXT() - lets you change the way a number appears. Good for maintaining a set number of digits after decimal places and left padding with 0s when standardizing account numbers, and more.
• LEN() - return the length of a string
• wildcard characters

| ? | matches any single character | | * | matches any number of characters | | ~ | escape character to put before these wildcard characters to match the actual wildcard character; e.g., ~? to match a ? (question mark) character |

• Examples:
  • Data Formatting Guide: Ungroup and split account numbers and descriptions
  • Data Formatting Guide: Ungroup and Split — Extracting complex patterns
  • Data Formatting Guide: Ungrouping and Checking Imbalance
  • Data Formatting Guide: Flipping signs for a range of accounts

Conditional/Logic

• SUMIF() - sum the values in a range whose values in another specified range satisfy a specified condition.
• SUMIFS() - SUMIF() with the ability to specify multiple conditions in multiple ranges
• COUNTIF() - count the number of values in a range that satisfy a specified condition.
• COUNTIFS() - COUNTIF() with the ability to specify multiple conditions in multiple ranges
• IF() - checks if a condition is met; if it is, does one specified thing, and if it is not, does another specified thing
• IFERROR() - if a given expression results in an error, return the other value specified, otherwise returns the given expression; specifies an alternative result if the given expression results in an error
• ISERROR() - checks whether a value is an error, and returns TRUE or FALSE.
• AND() - checks whether all arguments are TRUE, and returns TRUE if all arguments are TRUE.
• OR() - checks whether any of the arguments are TRUE, and returns TRUE or FALSE. Returns FALSE only if all arguments are FALSE.
• XOR() - returns a logical 'Exclusive Or' of all arguments; returns true if the number of TRUE statements is odd, FALSE if the number of TRUE statements is even.
• NOT() - changes FALSE to TRUE, or TRUE to FALSE.

Date

• DATE() - creates a date in Excel date format
• EOMONTH() - returns the last day of the month given

Other

• SUBTOTAL(function_num,ref1,...) — returns a subtotal in a list (ref1,...). Function_num is a number from 1 to 11 that specifies the summary function for the subtotal. 9 for SUM is especially useful to sum up the total of the items showing up in a filter, such as when checking if a GL will still balance when certain filters are applied. Example: Data Formatting Guide: Ungroup and Split — Extracting complex patterns
• COUNTA() — counts the number of cells in a range that are not empty. It is easy to think that this is what COUNT() does, but COUNT() actually counts the number of cells in a range that contain numbers (and a text string representing a number does not count as a number). So often COUNTA() should be used instead of COUNT().
• Pivot tables — Use a pivot table when you want to aggregate or summarize by one or more fields, such as computing total per account. Example: Data Formatting Guide: Ungrouping and Checking Imbalance
• Removing duplicates — Select the range for which you want to remove duplicates, then go to Data -> Remove Duplicates. Example: Data Formatting Guide: Consolidating Duplicate Trial Balance Accounts
• Text to Columns — Can be used to split a single column to multiple columns using a specified delimiter character or fixed width. Can also be used to convert data types.