Taking the time to format spreadsheet data before using it as a data source will make it faster and easier to create Klips. We recommend you adhere to the following best practices to prepare your spreadsheet data before using it in Klipfolio Klips.
Data format
- Make sure numerical data uses a standard decimal format, for example, 1000000.29.
- Remove non-numeric characters from numerical data, for example, currency ($) symbols.
- Ensure all numeric data columns are set as a numeric data type and all non-numeric data columns are set as a text data type.
- Use consistent spelling and spacing for values in dimension columns to prevent unwanted unique values.
Data structure
- Remove all external references, for example, vlookup and pivot tables.
- Use a consistent 2-digit date format for all date columns, for example, DD-MM-YYYY.
- When including columns that add dimension to your data, ensure that they are listed in their own columns. For example, if your data is segmented by country, all countries should be in the same column.
- If possible,
- Format the data in columns rather than rows. This is especially important for date values. Organizing data in columns ensures that new data is added to visualizations when the data source is refreshed.
- Remove total rows.
- Ensure each column has the same number of rows.
- Fill blank cells with text, for example, use N/A or NULL.
File format
- Remove trailing spaces from Excel worksheet names. Otherwise you will not be able to select your data within the Klip Editor.
- A file can have multiple sheets. In the Klip Editor, you can select the sheet you want data from using the Sheet drop-down menu.
- To avoid slow performance, due to limits imposed by your web browser, the maximum file size for any data source type is 10 MB. Learn how to manage file size.
- Modelling your data source cleans up your data. You should keep both the underlying (raw) data source and the modelled data source in your account. The underlying data source is the data source that refreshes the data for your modelled data source. Learn more about data sources.