Entering data into Excel table

Portfolio Slicer has multiple Excel tables in "src*" worksheets where you should enter data about your investments.
It is very important that you do not attempt to refresh Portfolio Slicer workbook with tables that have bad data or that have empty rows. . These bad records can corrupt Portfolio Slicer data model and only recovery from that is moving your data manually from corrupted workbook to new workbook - and that could be time consuming.

How to check if Excel table has bad data

Same Portfolio Slicer source Excel tables have conditional formatting rules that check if data makes sense. If all cases, when these rules identify that data is not right, it will change cell background to red. Here is the list of conditional formatting rules that might change your cell background to red:

• Table “Account” column “Currency” - checks if currency value is in the “Currency” table.
• Table “Account” column “Active” - check if value is “Yes” or “No”.
• Table “Symbol” column “Currency” - checks if currency value is in the “Currency” table.
• Table “Symbol” column “Allocation” - checks if allocation value is in the “Allocation” table.
• Table “Symbol Sector” column “Symbol” - checks if symbol value is in “Symbol” table.
• Table “Symbol Alias” column “Symbol” - checks if symbol value is in “Symbol” table.
• Table “Transactions” column “Account” - checks if account value is in “Account” table.
• Table “Transactions” column “Date” - checks if date value is between MinDate (defined in “Config” table) and current date. If you will enter date is not right format, this will trigger this rule too, examples:
  
  


• Table “Transactions” column “TransType” - checks if transaction type value is in system “TransType” table.
• Table “Transactions” calculated (!) column “Symbol” - checks if account value is in “Symbol” table.

But these rules do not capture all “bad data” scenarios. Excel does not have “type enforcement” rules, so there is nothing that stops you from entering alphanumeric values in the fields that expect numbers. For example if you will enter “ABC” in the “Price” field of “Transactions” table, Excel will accept that value without any problems. But when you will load that data into PowerPivot data model, it will not be able to handle such data and it might report error, or in same rare cases can even corrupt data model.

As a user you should be aware of what data you enter in what cells and again, you should never refresh data model when you have “bad data” (red cells) in your Excel tables.

How to check if Excel table has empty rows

1. For Excel table first check each column header to see if there are any filters applied to that column values. When column has no filter, then you will see arrow down next to the column name. If column has filter then you will see cone-shaped filter icon next to smaller arrow down.
   
   In above example column “Account” has no filter and column “Portfolio” has filter. If filter is applied, then you must remove it before proceeding.
2. One the first column of the table go to very bottom cell - what you believe should be last record in this table.
3. On the same row now move right to the last column of this table. If in the last cell bottom right corner you see “corner” tiny icon, that means you are really at the very last cell of this table and your table has no empty rows:
   
   But if you see that cell in other row has this “corner” icon, that means you have extra empty row:
   
4. If you see empty rows in your table, you need to delete them. To do so select all cells in that tables row, do right mouse click, then choose “Delete” and “Table Row”: