The Spreadsheet Widget is a visualization type that lets the user customize and visualize data in an Excel-like spreadsheet. It enables most Excel features, including functions, styling, sorting, and filtering. The Spreadsheet widget allows users to add sheets to work on the dataset. It can also generate shareable reports and clone the spreadsheet for further computations.
Adding and Configuring a Spreadsheet Widget
To add a Spreadsheet Widget to the dashboard, follow the below process.
- Navigate to the three-dots menu on the desired widget on the dashboard.
- Click Settings.
- Click on the Visualization Type dropdown.
- Select Spreadsheet. Notice that the Visualization data changes to a spreadsheet type.
- Click Save
Note: The data from the dataset for the widget is displayed under a Data tab. The data columns and rows are not editable. To perform any computations, create a new sheet and reference the cells from the Data tab. Users can also do it on the Data tab at the end of the last row in the Data tab.
To refer to cells from the Data tab, follow this example: Data!A1. This will refer to the cell A1 from the Data tab.
Spreadsheet Widget Settings
There are multiple collapsable menus on the left side of the window. In the Settings menu:
- Give the Spreadsheet Widget a name in the Visualization title box.
- Choose the widget category under the Category dropdown. (optional)
- The Ignore Fields dropdown lists the fields (column names) from the data. Whichever fields are chosen to ignore don’t show up on the visualization. (optional)
- In the Max Records field, enter the number of data records (rows) that must be displayed on the visualization. (optional)
- The Live Data slider, when enabled, prompts the user to enter the number of seconds. This will refresh the widget data after the number of seconds entered.
- The Description is a free text box that allows the user to enter a description for the widget. (optional)
Note: The description entered here will be displayed via an info icon (i) on the widget.
- The text entered in the Footnote will be displayed at the bottom of the widget. (optional)
- Use the search bar to find the menu options in the Settings collapsable menu.
Display and Formatting
The Display and Formatting settings are under the second collapsable menu.
- There are two options available for the Spreadsheet theme: dark and light. By default, the theme is set to light.
- Enabling the Hide Toolbar checkbox will make the Spreadsheet toolbar disappear.
- Another similar setting is the Read Only checkbox, which hides the toolbar and prevents the user from editing.
Under the Widget menu, settings specific to the Widget are available for configuration.
|Show Title||Unchecking this would make the Widget title disappear.|
|Alerts||Unchecking this would hide the Alerts icon.|
|Drilldowns||Unchecking this would hide the Drilldowns menu.|
|Data/Export||Unchecking this would hide the Data/Export menu.|
|Share||Unchecking this would hide the Share menu.|
|Maximize||Unchecking this would hide the Maximize menu.|
|InstantSights||Unchecking this would hide the InstantSights menu.|
|Analyze||Unchecking this would hide the Analyze menu.|
|Filters||Unchecking this would hide the Filters icon.|
|Query||Unchecking this would hide the Query menu.|
|Data Diagram||Unchecking this would hide the Data Diagram menu.|
|Refresh||Unchecking this would hide the Refresh menu.|
|Remove from Dashboard||
Unchecking this would hide the Remove from Dashboard menu.
Note: All checkboxes are enabled by default.
Under the advanced settings menu, there is an option to change the dataset.
At a given time, only one dataset can be used. In the example below, the Sample-dataset gets replaced with the Customer_data.
Configuring the Spreadsheet Widget Visualization
The Spreadsheet Widget is the visualization tab on the right side of the window. It has a lot of customization options for the dataset.
Understand the Spreadsheet Widget Menu Bars
The Spreadsheet Widget provides many Excel-like features. The menu bar items are no different.
The topmost menu bar in the visualization window is the Spreadsheet Widget Toolbar.
|Allows the user to undo the actions|
|Redo||Allows the user to redo the actions|
|Font||Select a font from this dropdown menu|
|Size||Select a font size from this dropdown menu|
|Bold||Enabling this would Bolden the selected text|
|Italic||Enabling this would Italicize the selected text|
|Underline||Enabling this would underline the selected text|
|Strikethrough||Enabling this would strike the selected text|
|Text Color||Change the text color from here|
|Fill color||Fill the Spreadsheet cells with color from here.|
|Borders||Apply borders to cells|
|Horizontal Alignment||Align text either to the left, right, or center of the cell|
|Vertical Alignment||Align text at either the top or bottom of the cell|
|Text wrap||Wrap the text in the cell or overflow it.|
|Define left indentation|
|Right Indent||Define proper indentation|
|Number||Number formatting dropdown|
The bar below the toolbar is the formula bar. The selected cell is displayed to the left of the formula bar. Users can write custom formulas for computations in the formula bar and edit the cell's contents.
The bottom bar displays the Datasheet and any additional sheets, if any.
To add a new sheet to the widget
- Click the “+” icon.
- Users can also rename the new sheet. To rename:
- Double-click on the default sheet name
- Press delete
- Enter the new sheet name.
- Press enter.
To access the individual sheet settings menu, click on the arrow on the sheet.
|Hide sheet||Hides the Data sheet|
|Rename sheet||Renames the sheet name|
|Remove sheet||Removes the sheet|
|Hide sheet||Hide the sheet|
Click on the three-dots menu to display a listing of all sheets. Click on a sheet name to navigate to and unhide a sheet.
- Click to save the Spreadsheet Widget
- Click to clone the Spreadsheet Widget
- Click to delete the Spreadsheet Widget
- Click to export the Spreadsheet Widget as a PDF
- Click to close the Spreadsheet Widget
Working with the Spreadsheet Widget Visualization
The Spreadsheet widget provides many options to customize data. Custom and built-in formulas can be used to visualize data and draw insights. The first sheet is the Data tab, which is non-editable. To edit the data and perform computations:
- Select a cell
- Type the equal sign =.
- Type in a function, like SUM or MAX. The formula bar also suggests the formula the moment the user starts typing.
- Enter an operator, like colon : or select the cells or range that are part of the function. Remember to close the parenthesis for the formula to work.
- Press Enter for the result. The result is shown in the cell, but the formula is also seen in the Formula bar. Refer to the below SUM example to understand better.
- Many Excel-like operations are available for the users to customize and visualize data. Refer to this guide for more information.
Spreadsheet Data Transformation
On the widget toolbar, the Data Transformation tab allows the user to transform the data before visualizing it on the spreadsheet. For example, filtering the clicks to be less than 123000 in the data transformation tab will be reflected on the spreadsheet.