Advanced: Google Sheets and Excel tricks

Most of these tricks come from the Silk Data Handbook. Look for the Structuring Data for Silk section. Once you ended up with a suitable spreadsheet, you can import the spreadsheet into your Silk site.

Adding data

If your dataset contains, say, countries, you can add some relevant data (like the continent they are located in) Likewise, dates can be used to add separate columns only containing the month or the year. All this will give you more options to filter and customize your data in Silk.

Clean up text and formats

This command makes sure your cells with text are formatted appropriately (check capitalization, weird symbols):

[=proper(A2)], [Find & Replace], [=concatenate(A2," ",A3)]

Ensure unique titles and column headers

Your dataset will need unique titles for all the pages you will import into Silk: this means the first row in your spreadsheet need to contain unique texts. The first row will also need to contain unique headers for the columns.

Create new data from existing values

Many datasets contain values that can be used to calculate or infer relevant additional data. For instance, a city’s population number can used to calculate its percentage of the national population; or an event’s starting and end date can be used to calculate the duration.

Using multiple values in a cell (seperators and splitting)

If your dataset contains cells with mulitple values in them make sure to use a unique separator between the values, like a comma. During the import into Silk you can properly separate these values by selecting “Split on commas" or “Split on other characters"

Convert a CSV file into a workable document by splitting the comma separated values in separate columns: [=split(A2,", ",false)]. Also, see the Google Sheets help article on this.

Summarize data with a pivot table report

Large datasets can usually provide several relevant collections of data that focus on distinct variables: using the pivot table report lets you summarize data, for instance per country or year.

In Google Sheets, go to [Data->Pivot Table Report]. See this article.