Entering data into Excel table

Overview

PortfolioSlicer-Source.xlsx contains multiple data-entry tables, with one main table on each worksheet, where you enter information about your investments.

It is very important that you do not attempt to refresh Portfolio Slicer when tables contain bad data or empty rows. In the worst case, invalid rows can leave the workbook in a state that is difficult to recover from without manually moving data into a new workbook.

Before refreshing, always check for:

  • bad data
  • empty rows in Excel tables

How to Check If an Excel Table Has Bad Data

Portfolio Slicer uses conditional formatting in several source tables to flag obvious problems. In many cases, cells with bad data will be highlighted in red.

The workbook may flag problems such as:

  • Account table Currency values that do not exist in the Report Currency table
  • Account table Active values that are not valid
  • Symbol table Currency values that do not exist in the Report Currency table
  • Symbol table Allocation values that do not exist in the Allocation table
  • Symbol Sector table Symbol values that do not exist in the Symbol table
  • Symbol Alias table Symbol values that do not exist in the Symbol table
  • Transactions table Account values that do not exist in the Account table
  • Transactions table Date values outside the valid range
  • Transactions table TransType values that are not valid
  • Transactions table calculated Symbol values that do not exist in the Symbol table

Here are examples of how bad date data may appear:

Example of bad date data
Example of bad date data

Important Limitation

Conditional formatting does not catch every possible problem.

For example, Excel can still allow text in fields that are supposed to contain numbers. A value like ABC in a numeric field may not look obviously broken in the table, but it can still cause refresh errors later.

Practical Rule

Do not refresh Portfolio Slicer when you see red cells or suspicious values in numeric/date columns.

How to Check If an Excel Table Has Empty Rows

Empty rows inside an Excel table can also cause problems.

A practical way to check is:

  1. First review each column header to make sure filters are cleared.
  2. If a column shows a filter icon, remove the filter before continuing.
Column filter example
  1. Go to the row you believe is the last real row in the table.
  2. Move across to the last column in that same row.
  3. Confirm that this really is the last cell of the table.

If you see the table continue beyond that point, you likely have extra empty rows.

Example of a correct table end:

Correct table end

Example showing an extra empty row:

Table with extra empty row

If you find empty rows, delete them as table rows rather than just clearing the contents.

Delete table row

Good Data Entry Habits

A few habits make Portfolio Slicer safer to maintain:

  • enter data in small batches
  • review red cells immediately
  • validate dates and numeric values carefully
  • remove accidental empty rows before refresh
  • keep backups before major data-entry sessions