What can we help you with?

e.g. Getting Started, Build Dashboards

Top

Spreadsheet

Follow

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 process below.

  1. Navigate to the three-dots menu on the desired widget on the dashboard. 
  2. Click Settings.
  3. Click on the Visualization Type dropdown. 
  4. Select Spreadsheet. Notice that the Visualization data changes to a spreadsheet type.
  5. 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:

  1. Give the Spreadsheet Widget a name in the Visualization title box.
  2. Choose the widget category under the Category dropdown. (optional)
  3. 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)
  4. In the Max Records field, enter the number of data records (rows) that must be displayed on the visualization. (optional)
  5. 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.
  6. 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.
  7. The text entered in the Footnote will be displayed at the bottom of the widget. (optional)
  8. 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.

  1. There are two options available for the Spreadsheet theme: dark and light. By default, the theme is set to light.
  2. Enabling the Hide Toolbar checkbox will make the Spreadsheet toolbar disappear. 
  3. Another similar setting is the Read Only checkbox, which hides the toolbar and prevents the user from editing.
  4. Users can also specify the Maximum number of rows and columns to display by entering the value in the Max Rows and Max Columns box.

Widget Menu

Under the Widget menu, settings specific to the Widget are available for configuration.

Menu Interpretation
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.

Advanced Configuration

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.

Toolbar

The topmost menu bar in the visualization window is the Spreadsheet Widget Toolbar.

Menu Interpretation

1. Undo

 Allows the user to undo the actions
2. Redo Allows the user to redo the actions
3. Font Select a font from this dropdown menu 
4. Size Select a font size from this dropdown menu 
5. Bold Enabling this would Bolden the selected text
6. Italic Enabling this would Italicize the selected text
7. Underline Enabling this would underline the selected text
8. Strikethrough Enabling this would strike the selected text
9. Text Color Change the text color from here
10. Fill color Fill the Spreadsheet cells with color from here.
11. Borders Apply borders to cells
12. Horizontal Alignment Align text either to the left, right, or center of the cell
13. Vertical Alignment Align text at either the top or bottom of the cell
14. Text wrap Wrap the text in the cell or overflow it.

Merge Merge text

15. Merge

 Merge the selection

16. Left Indent

 Define left indentation
17. Right Indent Define proper indentation
18. Number Number formatting dropdown
19. Edit The edit option allows you to create or remove filters on the selected columns. Read more.
20. Insert The Insert option allows you to add charts at a cell level for a range of data. Read more.

21. Freeze/Unfreeze Rows Pins the selected rows so that its visible on scroll
22. Freeze/Unfreeze Columns Pins the selected columns so that its visible on scroll

Add/Remove Filters

Users can add filters to columns to filter out data.

To add a filter:

  1. Select a column(s)
  2. Click on the funnel icon in the Edit bar.
  3. Go back to the column to find the filter options.

The filter options available are:

  • contains
  • not contains
  • equal
  • not equal
  • begins with
  • not begins with
  • ends with
  • not ends with

Choose the filter and specify the condition in the right box and click apply for the results.

Note: Filters don't work on the locked columns.

To remove a filter, click on the column and click the filter button.

Insert Graphs

Users can add basic graphs within the spreadsheet to have insights at row/cell level. You can add several kinds of graphs: Line, Spline, Spline Area, Area, Bar, Radar, Pie, Donut, and Scatter. You can also add Sparklines to the spreadsheet.

To add a graph:

  1. Click on the cell you want a graph
  2. Click Graph dropdown. You can choose to add at the cell or above it
  3. Choose the type of graph
  4. Choose the range of data
  5. Choose the color
  6. Click Save

Formula Bar

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 formula bar supports standard formulas like: SUM, AVG, UNIQUE, etc. To apply formula, click on the f(x) button, select the data and hit enter.

Sheets Bar

The bottom bar displays the Datasheet and any additional sheets, if any.

 

To add a new sheet to the widget

  1. Click the “+” icon.
  2. Users can also rename the new sheet. To rename:
    1. Double-click on the default sheet name
    2. Press delete
    3. Enter the new sheet name.
    4. Press enter.

Sheet Settings

To access the individual sheet settings menu, click on the arrow on the sheet.

Menu Interpretation
Hide sheet Hides the Data sheet

 

Menu Interpretation
Rename sheet Renames the sheet name
Remove sheet Removes the sheet
Hide sheet Hide the sheet

 

Other Functionalities

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.

 

 

  1. Click to save the Spreadsheet Widget
  2. Click to clone the Spreadsheet Widget
  3. Click to delete the Spreadsheet Widget
  4. Click to export the Spreadsheet Widget as a PDF
  5. 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:

  1. Select a cell
  2. Type the equal sign =. 
  3. Type in a function, like SUM or MAX. The formula bar also suggests the formula the moment the user starts typing.
  4. 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.
  5. 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.
  6. 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.

 

Was this article helpful?
0 out of 0 found this helpful
Return to top

Related articles