I found a question here about tracking a stock portfolio. He would like to automatically create an overview table with a unique stock symbol per row. He also wants the range extended down to include new rows as they become valid.


Sheet1 Symbol Type Shares Price F Buy 100 12 MSFT Buy 100 25 MSFT Buy 50 28 F Buy 100 16 GOOG Buy 25 550 Sheet2 Symbol Quantity F 200 MSFT 150 GOOG 25




Create named ranges

  1. Select tab "Formulas" on the ribbon
  2. Click "New..." button
  3. Type

    in Name: bar

  4. Type formula:
    =Sheet1!$A$2:INDEX(Sheet1!$A:$A, COUNTA(Sheet1!$A:$A))

    in "Refers to:" bar

  5. Click Close button

Repeat above instructions with following names and formulas:

Type - =Sheet1!$B$2:INDEX(Sheet1!$B:$B, COUNTA(Sheet1!$B:$B))
Shares - =Sheet1!$C$2:INDEX(Sheet1!$C:$C, COUNTA(Sheet1!$C:$C))
Price - =Sheet1!$D$2:INDEX(Sheet1!$D:$D, COUNTA(Sheet1!$D:$D))

Array formulas

Array formula in cell A2, sheet 2:

=IFERROR(INDEX(Symbol, MATCH(0, COUNTIF($A$1:A1, Symbol), 0)), "") + CTRL + SHIFT + ENTER

Copy cell A2 and paste down as far as needed. This formula creates uniue distinct symbols. Read this post: How to extract a unique distinct list from a column for a formula explanation.

Array formula in cell B2, sheet 2:

=IF(A2<>"", SUMPRODUCT((A2=Symbol)*(Type="Buy")*Shares)-SUMPRODUCT((A2=Symbol)*(Type="Sell")*Shares), "") + Enter

Copy cell B2 and paste down as far as needed.

Download excel file

Tracking a stock portfolio.xlsx
(Excel 2007 -2010 Workbook *.xlsx)

Pivot table - Semi auto update

You can also convert the range in sheet1 to a table and then create a pivot table with data from table. The table includes new rows automatically. Unfortunately the pivot table does not. Right click anywhere on pivot table and click Refresh when new rows are added.

The question is how to subtract sold shares from bought shares in a pivot table?