About Adding a Spreadsheet as a Data Source

Data source files from a Microsoft Excel spreadsheet file must have the XLSX extension (signifying a Microsoft Office Open XML Workbook file) and be no larger than 50 MB. You can also add Comma Separated Value files with the .CSV extension.

Before you can upload a Microsoft Excel file as a data source, you must structure the file in a data-oriented way and it mustn‘t contain pivoted data. Note the following rules for Excel tables:

  • Tables must start in Row 1 and Column 1 of the Excel file.

  • Tables must have a regular layout with no gaps or inline headings. An example of an inline heading is one that repeats itself on every page of a printed report.

  • Row 1 must contain the table’s column names. For example, Customer Given Name, Customer Surname, Year, Product Name, Amount Purchased, and so on. In this example:

    • Column 1 has customer given names.

    • Column 2 has customer surnames.

    • Column 3 has year values.

    • Column 4 has product names.

    • Column 5 has the amount each customer purchased for the named product.

  • The names in Row 1 must be unique. Note that if there are two columns that hold year values, then you must add a second word to one or both of the column names to make them unique. For example, if you have two columns named Year Lease, then you can rename the columns to Year Lease Starts and Year Lease Expires.

  • Rows 2 onward are the data for the table, and they can’t contain column names.

  • Data in a column must be of the same kind because it’s often processed together. For example, Amount Purchased must have only numbers (and possibly nulls), enabling it to be summed or averaged. Given Name and Surname must be text as they might be concatenated, and you may need to split dates into their months, quarters, or years.

  • Data must be at the same granularity. A table can’t contain both aggregations and details for those aggregations. For example, if you have a sales table at the granularity of Customer, Product, and Year, and contains the sum of Amount Purchased for each Product by each Customer by Year. In this case, you wouldn’t include Invoice level details or a Daily Summary in the same table, as the sum of Amount Purchased wouldn’t be calculated correctly. If you have to analyze at invoice level, day level, and month level, then you can do either of the following:

    • Have a table of invoice details: Invoice Number, Invoice Date, Customer, Product, and Amount Purchased. You can roll these up to day or month or quarter.

    • Have multiple tables, one at each granular level (invoice, day, month, quarter, and year).