Formulas for Labels with Values

In your Excel linking document, you can create formulas for your labels (such as the balance sheet parentheticals) that include values from elsewhere in Excel.

Creating Formulas for Labels with Values

Setting Up Values

  1. Determine where the values are located in Excel, whether in the same or different worksheet or workbook. In this example, the values are entered on the same sheet, below the table.
  2. Use Excel formulas to populate the cell values.

Using the CONCATENATE Formula

  1. Set up the desired label as CONCATENATE formula, to combine text strings and cell references. To use the shortened format of this formula, start with =.
  2. Add text strings in quotation marks and cell references to values, using ampersands in between each one.

Using the TEXT Formula

  1. Use the TEXT formula to modify the cell references, in order to include punctuation (commas), dollar signs, decimals, or parentheses around negative values. The first argument of the formula ("value") is the cell reference or value. The second argument is the desired cell format, in quotation marks. You can use the same types of custom cell formats used on for the table values, or a simplified version.

Sample Cell Formats

Example Custom Cell Format
$1,524

"$#,##0"

$2.4

"$#.#"

$(528)

"$#,##0;$(#,##0)"

 

Example

Shortened CONCATENATE formula, using &:

TEXT formula used to specify the cell format for the values:

Results of the formula:

If you prefer, you can use Excel formulas to tie the values in your balance sheet parentheticals, or other labels, to information elsewhere in Excel. In this case, we'd like to make a formula for the allowance for doubtful accounts. For now we'll place those values at the bottom of the balance sheet. We'll use the CONCATENATE formula to combine strings of text with the values from those cells. Here we're using the shorthand for CONCATENATE, which is to use an ampersand between each piece of the text. We'll first start by adding the text strings. Start with equals, and then each of the text strings below in quotes. I'll leave aside those variables for now. So I have a text string here, "Accounts receivable net of allowances of," then I want one that is spaces and the word "and", and this comma and the word "respectively." Next we'll put in the variables. I need to put an ampersand between things we're connecting, and then now click on the cell we want to link. Delete that old value, and put another ampersand there. So you can see first is the text string, then that cell value, the next text string, ampersand, click that other value, ampersand, then the last text string. Press Enter. Here, if you look at the results, notice it doesn't have any punctuation -- no dollar signs or commas. Even if I put the commas into this cell, it won't show up. I could put a dollar sign, or decimals, or anything, and it won't come through up here. That's because you actually need to specify the format that you desire in the formula. I'm going to use the Excel TEXT formula to modify that to a different format. So, TEXT, open parenthesis, the value is the cell reference, and then a comma. Now in quotes, we'll put the cell format we want to use. In this case I want to use, first a quote, a dollar sign, and then I'm going to use #,##0. If you wanted to do a decimal, you can do that as well. You can customize this cell format, just like you do the other cell formats that we use elsewhere in Excel. Close the parentheses. I'll do the same for this one. Now you can see it shows the dollar signs and the commas. Now I can use Excel formulas to populate the values from wherever they come from. If you're sharing this Excel document, you might also like to leave a cell comment, making note of where they're linked to.