Version 1.0 (latest)

Working with Spreadsheets in Reveal

Below are a couple of helpful tips and tricks for working with spreadsheets within Reveal.

excel.png csv.png

Reveal expects your data to be organized in columns and will name fields after the spreadsheet’s first row. If your data is organized in rows instead, you can transpose the fields in Reveal.

LEARN MORE

csv.png

When working with CSV files, avoid formatting the spreadsheet. Instead, apply any necessary formatting (1000 comma separators, currency signs, etc.) through Reveal.

LEARN MORE

excel.png

For 2 or more series charts, the order in which you drop fields in "Columns", "Rows" and "Values" will determine how the chart is plotted, regardless of the column order in your Excel file.

excel.png csv.png

If columns have different types of data combined (strings, numbers, etc.), Reveal will treat them as a column with strings only. This will affect, among other things, formatting.

p-lock.png

Password-protected files and hidden ranges are not supported in Reveal.

Data Ranges

excel.png csv.png

Excel files allow you to select a custom range of data within your spreadsheet, while CSV do not. If you need to select specific data, make sure you always work with an Excel spreadsheet.

excel-columns.png

If you have a spreadsheet with a named range, you can choose that specific Named Range as a data source.

excel-columns.png

If your Excel data is organized in non-adjacent columns, Reveal will pick up the column with most data.

Dates, Numbers and Percentages

reveal-logo.png

By default, numbers will have 2 fraction digits. To change this, access the Formatting menu for the field.

LEARN MORE

top-1000.png

The 1000 separator is disabled by default, even for large numbers. To turn it on, access the Formatting menu.

LEARN MORE

percentage.png

If you need to show percentages or currencies, you will need to select that option from the Formatting menu.

LEARN MORE

excel.png

If you want a date as a column header, make sure you include a ' so Excel will respect your format. The default date format for headers is the full-length format (YYYY-MM-DD 00:00:00 +0000).

percentage.png

Percentages in Excel sheets will be shown in a 0 to 1 scale unless the format is changed to "Percentage". For example, 39% will be displayed as 0.39.

Transposing Columns to Rows

When working with Microsoft Excel and Google Sheets, you can use the transpose functionality to switch columns from rows.

Note
NOTE

Only the first 200 rows in the spreadsheet will be transposed.

You can access the transpose menu in the Visualization Data menu when expanding the sheet in your data source.

Spreadsheets Transpose Data Source Menu

You will see a transpose icon in the top right-hand corner next to the cross button. Once you press it, Reveal will switch columns to rows and vice-versa.

Transposing Action Spreadsheet

In the example above, the information in the spreadsheet will be organized in columns expressing the EmployeeID for each employee in the company.

Transposing Action Final Spreadsheet

Once you are ready, select the tick icon. This will take you back to the Visualization Data menu, where you will now see the name of the sheet with a new icon to its left indicating that the sheet has been modified.

Transposed Spreadsheet Visualization Data

See Also

A common use case is building dashboards with spreadsheets located in a cloud file sharing service. To learn how to successfully share those dashboards with other users, please read Sharing Dashboards with Cloud Files as a Data Source.