Formatting & analyzing data with spreadsheet tabs

JustAsk’s spreadsheet tabs bring the power of traditional Excel spreadsheets into your existing reporting and analysis workflows, with the added benefit of always-on, live data.

Spreadsheet tabs are ideal for:

• Creating richly formatted tables such as financial statements, invoices, or planning sheets
• Performing ad hoc or exploratory analysis

Because these spreadsheets are directly connected to your database via JustAsk queries, you no longer need to export or manually refresh data. You can build and maintain familiar spreadsheet workflows - such as reporting templates or budget trackers - confident that your data is accurate, current, and easy to work with.

How it works

Spreadsheets are a type of tab that, when enabled, can be added to JustAsk workbooks. Other queries in the workbook can be connected to the spreadsheet and used as a data source.

When queries are connected to a spreadsheet tab, a new sheet will be added inside the spreadsheet tab. You can then reference the data in the sheet like you normally would in Excel.

Sheets that correspond to a workbook query will automatically update when the query changes. For example, if a new column is added to a query, the sheet associated with the query will be updated to include the new column.

Limitations

The most important limitation relates to data usability outside of a spreadsheet tab. Data in spreadsheets can’t be used in other queries or promoted to a shared model. While you can work with query data by connecting it to a spreadsheet tab, data created in the spreadsheet - for example, by manual entry or through the use of formulas - can’t be used elsewhere.

Excel differences

• Some formulas may differ slightly from Excel. JustAsk expects the majority of formulas to be the same, but we recommend using the in-sheet formula builder to verify the correct syntax.
• Changes in spreadsheet tabs aren’t autosaved. You’ll need to manually save your work to ensure changes are persisted.
• Keyboard shortcuts are not supported
• References may not reflect structural changes the same way as Excel. Refer to the Handling query changes section for more information and examples.
• Creating pivot tables isn’t supported. If you need to create pivot tables, create a “regular” tab to use JustAsk’s native pivot functionality. Note: You can connect queries containing pivots to spreadsheets as a data source.
• Creating in-sheet charts isn’t supported. If you need to create a visualization, create a “regular” tab to use JustAsk’s native chart functionality.

Adding spreadsheets to workbooks

When spreadsheet tabs are enabled, you’ll see a Spreadsheet option when you create a new workbook or query tab. Click this option to add a Sheet tab to the workbook.

The tab will display a blank sheet similar to what you’d see in an Excel workbook. At this point, you can connect other workbook queries to the spreadsheet to interact with your data.

Adding data to spreadsheets

You can add data to spreadsheets by:

• Connecting workbook queries - Recommended. Creates additional sheets containing query data in the spreadsheet, which are automatically updated when a query or its results change.
• Importing existing CSVs & XLSX files - Recreate existing formatting and formulas in JustAsk
• Manually entering data

JustAsk recommends connecting workbook queries as data sources whenever possible. This ensures that your data is current and, if needed, accessible outside of the spreadsheet tab.

Connecting workbook queries

The recommended way to add data to a spreadsheet tab is to connect an existing workbook query. This allows you to manipulate and format data while taking advantage of your existing data model.

Open the spreadsheet’s settings by clicking the drawer icon (highlighted in the following image) in the left navigation:

When a workbook query is added to a spreadsheet, JustAsk will add a protected sheet with the same name as the query to the spreadsheet.

Data in sheets associated with workbook queries will automatically update when the query’s results or structure changes. For example, if you add a new column to a query, the associated sheet will also be updated to include the new column.

Importing existing CSVs and XLSX files

Note

Data added using this method only lives in the spreadsheet tab where you upload it. If you need to use CSV or XLSX data outside of a spreadsheet tab, consider using data input tables instead.

JustAsk also supports importing existing CSV and XLSX files into spreadsheet tabs! For XLSX files, JustAsk will preserve your formatting, formulas, and references, allowing you to recreate existing workflows and wire them up to live data. CSVs will be parsed into columns.

1. In a workbook, create a new spreadsheet tab or add a blank sheet to an existing spreadsheet tab.
2. Drag the file into the blank sheet. A Drag CSV or XLSX here message should display.
3. Drop (release) the file to begin the import.

After the upload completes, you can edit the spreadsheet like you usually would.

Get started quickly by importing an an Excel template - you can then customize it using your own JustAsk data.

Manually entering data

To manually enter data, click into a cell and type or paste the data. Note: Data added using this method only lives in the spreadsheet tab you enter it into. It will not be accessible outside of the spreadsheet tab.

Referencing query data

Data in other sheets in a spreadsheet tab can be referenced just like you would in Excel. This includes direct references like '<SHEET_NAME>'!A1 or as arguments in formulas.

Creating formulas

Note

The formulas described in this section are not the same as JustAsk’s table calculations. Spreadsheet tabs support additional formulas, and the syntax between JustAsk table calculations and spreadsheet tab formulas may differ slightly.

Formulas in JustAsk spreadsheets generally mirror those in Excel. To view all supported formulas, click the Formulas tab in a spreadsheet and then Insert Function:

Refer to the Excel documentation for detailed information about these formulas.

If you prefer a more guided experience when using functions, select a function in this window and click OK. You’ll be prompted to fill in the formula’s arguments.

You can also directly add formulas to sheets. In a cell or the formula editor, enter = to start building the formula. For example:

=AVG(A:A)

Copying with references

JustAsk supports copying a sheet connected to a workbook query to a new, editable sheet in a spreadsheet tab.

Right-click on the protected query sheet and select Copy with references:

A copy of the results sheet will be created. Unlike the original protected sheet, you can directly edit the contents of the copied sheet:

Note: The new sheet uses absolute references to pull in data. This means that if the query’s results set changes, the data in the copied sheet will also change. Refer to the Handling query changes section for more information and examples.

Unlocking spreadsheets for dashboard editing

By default, spreadsheets on dashboards are read-only. If you want dashboard viewers to be able to edit cells - for example, to perform what-if analysis - you can unlock a spreadsheet tab.

When a spreadsheet is unlocked and viewed on a dashboard, viewers can edit all cells in the spreadsheet.

To unlock a spreadsheet:

1. Open the spreadsheet’s settings by clicking the drawer icon in the left navigation.
2. Toggle Unlock spreadsheet to on.

Note

Unlocking a spreadsheet only affects how it behaves on a dashboard. In the workbook editor, all cells remain fully editable regardless of the toggle state.

Handling query changes

When a workbook query is updated, the sheet associated with it in a spreadsheet tab will also be updated to reflect the changes. This includes - but isn’t limited to - structural changes like adding columns, changing a filter value, or adding pivots.

JustAsk spreadsheets differ from Excel in the way that underlying data changes are “absorbed.” For example, if you have a hardcoded reference for columns A:D and a new column (E) is added, only the query sheet will be updated to include the new column. The sheet containing the reference will have to be manually updated to A:E.

Let’s take a look at another example, this time with a query that uses pivots.

1. Workbook query with filters and pivots

The image below displays the results of a query with two filters:

• Category - Only include specified product categories
• Created At - Only include records for the past 3 complete years

The results of the query have been pivoted so that the table has columns for the years 2022, 2023, and 2024.

2. Query results sheet

This image displays the sheet in a spreadsheet tab associated with the Sale Prices by Year workbook query.

Notice that the years are in the B, C, and D columns, which matches the results table in the actual workbook tab.

3. Reference query results data

This image displays a formatted sheet that references cells in the Sale Prices by Year sheet. Just like in the query results, this sheet contains a column for each year (or pivot).

For example, data for 2022 is pulled from the B column in the Sale Prices by Year sheet.

4. Change filter in workbook query

Let’s look at what happens to a sheet when the underlying structure of the query results changes.

In this example, the Created At filter has been changed to include only the past 2 complete years, resulting in the 2022 column being removed from the query results.

5. Updated query results sheet

When query results change, the sheet associated with the query will automatically be updated. This image shows the updated Sale Prices by Year sheet in the spreadsheet tab.

Notice that it now only contains columns for 2023 and 2024, which matches the results in the actual workbook tab.

6. Impact on query data references

In this last image, you can see what happened to the formatted sheet when the query results sheet it references changed. The references and formulas in the highlighted column are now incorrect.

When the query’s filter included the past 3 complete years, the D column in the Sale Prices by Year sheet was populated. Because it was changed to the past 2 complete years, a column was removed and the data shifted to the left, leaving the D column empty.

Removing query data

You can remove workbook queries from a spreadsheet by unchecking them in the spreadsheet’s settings panel.

Note: When a workbook query is removed from a spreadsheet, the sheet referencing the query will also be removed. This doesn’t delete the query from the workbook.

Saving your work

Changes in spreadsheet tabs aren’t autosaved. Periodically save your work by clicking Save Sheet in the spreadsheet’s Home tab:

This button will be highlighted in blue when there are unsaved changes. Additionally, JustAsk will prompt you if you attempt to navigate away from the spreadsheet with unsaved changes.

Displaying spreadsheets on dashboards

Note

Spreadsheets on dashboards are read-only by default. To allow dashboard viewers to edit cells, unlock the spreadsheet in the workbook editor. To edit the full spreadsheet, click File > Edit or the Edit button to open a draft of the document.

Spreadsheets can be included on a dashboard just like any other visualization. By default, JustAsk will only display the first sheet in the spreadsheet on the dashboard.

Including all sheets

To display all sheets on a dashboard, first open the spreadsheet’s settings by clicking the drawer icon in the workbook’s left navigation panel. Then in the Dashboard settings section, toggle Show tab strip to on:

When enabled, all sheets will display as tabs on the dashboard:

Changing sheet display order

By default, JustAsk will display the first sheet in a spreadsheet tab when a dashboard is viewed. You can change the sheet order by dragging sheets in the spreadsheet tab in the workbook. When finished, click Home tab > Save Sheet to ensure the changes are reflected on the dashboard.

Exporting spreadsheets

Spreadsheets can also be exported back to Excel. On a dashboard, hover over the right corner of a spreadsheet tile and click the ⠇ (three dots). Then select Download XLSX.