Skip to main content

Keep Lab Data in Sheets

Sheets is the spreadsheet inside Conspecta, where your project's numbers are kept. Type them in, import them, or pull them from your samples and inventory, then summarize, fit, and chart them, and add the result to a figure.

Every image analysis run and every set of flow statistics you send onward also becomes a sheet here.

Creating a Sheet

New sheet opens a picker with a blank sheet, ten starters, any templates your team has saved, and a tile for importing a file. Choose one, set whether the sheet is private to you or shared with the project, and give it a name. Nothing is saved until you've named it.

Saving a sheet as a template puts it in the same library as your notes and protocols, which is capped at 200 of your own and 500 shared with the team. See Templates.

Each starter is an empty sheet with the columns the job needs.

StarterColumns
Dose-responseConcentration (µM), Response A, B, C
TimecourseTime (h), Condition, Measurement
Sample measurementsSample, Replicate, Value, Units, Notes
Buffer recipeComponent, Stock, Final concentration, Volume
qPCR resultsSample, Target, Replicate, Cq
Standard curveStandard, Known concentration, Reading 1, Reading 2
Plate readingsWell, Sample, Condition, Reading
Cell countsDate, Sample, Live cells, Dead cells, Viability (%)
Band quantificationLane, Sample, Target signal, Loading control, Normalized
Experiment conditionsCondition, Variable, Level, Replicates, Notes

You can also paste a range into the grid, and the sheet grows to fit it.

A sheet a protocol run made for one of its steps, or one made on a note, keeps that link. Connections in the sheet's ⋯ menu shows the run, the note the sheet was made on, and any other notes the sheet sits on.

In a sheet a protocol run made, a column that records a field from your team's data dictionary keeps that field's choices and expected range, the same as a column you give a field yourself.

Importing

Import, under the ⋯ in the header, takes CSV, TSV, and XLSX, one sheet per file, named from the filename. New sheet's picker offers the same thing as an Import a file tile, and sheets imported that way are private or shared to match the picker's choice. An empty workspace also takes a drag onto the page.

Spreadsheet formulas come across with the import. A sheet that arrived with formulas shows a banner explaining what happened to them, because Conspecta recomputes only the functions listed under Formulas and keeps the rest as written.

Canceling an import partway deletes the half-written sheet.

Microbiome Tables

Import also takes microbiome tables as BIOM (.biom) or QIIME2 visualizations (.qzv). Both arrive with a column per taxon and a row per sample, and the sample IDs run down the left of the sheet as row labels, where charts and sample linking can use them. A QIIME2 taxa bar plot comes in at the most detailed level the file has.

A sheet holds up to 512 columns. A raw feature-level BIOM file is usually much wider, so it can't be imported, and the message names its column count. Collapse it to a higher taxonomic level and import again.

Adding Rows From a File

Add rows from a file, in an open sheet's ⋯ menu, appends a file of readings, such as a plate reader's export, to the bottom of the sheet you have open. It takes the same file types, plus the tab- or comma-separated .txt files many instruments save.

The file's columns are matched to the sheet's by name, and you can change any match or skip a column. Derived and linked columns fill themselves and take nothing from the file. The rows already in the sheet stay as they are, and the sheet's activity records the new rows like any others.

Filling Readings From a Plate Reader

Fill readings from a file takes a plate reader's grid export and puts each well's reading on the row whose Well cell names that well. The export is laid out like the plate, with 1 to 12 across the top and A to H down the side (A to P and 1 to 24 for a 384-well plate). The sheet needs a column headed Well. A grid you drop into Add rows from a file on such a sheet opens it too.

  • Pick the column under Put values in.
  • When the file holds several reads, such as two wavelengths, each gets its own column or Skip.
  • A cell that already has a value is left as it is and counted.
  • Wells the sheet has no row for are skipped unless you check Also add rows for the wells this sheet doesn't have.

Finding a Sheet

The search box matches part of a sheet or folder name, or a sheet's description, as you type, across the folder you have open and everything inside it. View on the toolbar holds sorting and filtering. Its Filter section narrows what's left, and the choices combine.

FilterWhat it holds
StatusDraft, In progress, Ready, Archived
SourceLinked for a sheet that refreshes from a project resource, Snapshot for a one time copy of a run, Typed or imported for everything else
Created byOne person on the project. It appears once more than one person has made a sheet here
ReviewReview requested, Changes requested, Approved, Rejected, No review

Folders stay visible whichever filter you pick, because the sheet you're after may be inside one.

Review Status

Once any sheet in the list has been signed off or sent for review, the list adds a Review column. It spells the status out, reading Review requested, Changes requested, Rejected, Approved, or Approved · edited since. A sign-off covers the sheet's columns and rows, so renaming the sheet or resizing a column leaves it approved.

A card and the editor's toolbar each show the same thing as a small colored mark.

MarkOn a cardIn the editor's toolbar
GreenApprovedSigned off
AmberA review still owed, changes requested or an edit sinceA review still owed, changes requested or a sheet edited since it was signed
RedRejectedRejected

Hover a card's mark to read the status and who approved it, rejected it or asked for changes. Hover the toolbar's mark to read it in words, or click it to open Activity.

Moving a Sheet

Check a sheet's row and the bar shows Move, which offers this project's folders and, below them, the other projects you can edit. A sheet you have open moves as well, through Move in its three dot menu.

Either way the sheet takes its rows with it, and the pictures on them arrive in the new project too. A sheet with more than a thousand pictures brings the first thousand, and a message says the rest stayed in the old project, where deleting that project would remove them. See Moving work between projects.

Folder and Sheet Menus

A folder has a three dot menu of its own, on its card and on its row in the list, holding Rename, Move to..., Favorite, Download, Share with project or Make private, and Delete. Some of these act on everything the folder holds.

  • Download opens the export dialog box on that folder and everything in it.
  • Share with project and Make private change the sheets inside the folder. They appear only on a folder whose sheets you made, because only a sheet's creator can change its sharing.
  • Delete trashes the folder along with everything inside it, and the confirmation lists every sheet that goes with it.

In the list, each sheet's row has a three dot menu too, with Rename, Move to..., Favorite, Download, Share, Duplicate, Save as template, and Delete, each acting on that one sheet. Share opens the dialog box that sets who can open the sheet, and appears for the person who made it.

To rename the sheet you have open, click its name in the top bar or choose Rename sheet in its three dot menu. Both open the Rename sheet dialog box.

Duplicating a Sheet

Duplicate in a sheet's three dot menu in the list or on the bulk bar with one sheet selected, or Duplicate sheet at the right of the toolbar inside an open sheet, makes a copy in the same folder and opens it. The copy is named <name> (Copy).

It keeps every row, the columns and their formatting, the description, the charts, and the original's sharing. The pictures on its rows are copied with it and count toward the project's storage, and a sheet with more than 200 pictures is too large to duplicate.

A linked column stays linked in the copy and refreshes on its own, from the same source as the original. A copy of a results table written by an image analysis or a cytometry analysis is an ordinary sheet that you can edit, and its toolbar reads Snapshot from the sheet it came from.

The copy starts its own history. It belongs to you and reads as Draft, with no published versions or sign-offs. Its activity opens with a line naming the sheet it was duplicated from, and the original's activity gains a line naming the copy. Duplicating needs edit access to the project.

The Grid

Everything saves as you type, and the status bar shows whether your changes have saved. When a save doesn't go through, it reads Not saved with a Retry button.

Columns come in three kinds, and the header color shows which.

  • Raw columns are yours, typed or imported, and fully editable.
  • Derived columns were produced by an Analyze action. Their cells are read-only and recompute when the values they depend on change.
  • Linked columns come from elsewhere in the project, refresh from their source, and can't be edited here. An amber dot means the source has changed and a refresh is available.

Each column's menu handles renaming, sorting, filtering, formatting, width, freezing, inserting, duplicating, and deleting. Sorting and filtering are view-only. They change what you see and leave the stored sheet as it is, and the footer shows a note saying so. Each row keeps its number in the gutter, the number a formula uses for it, so a sorted sheet shows its row numbers out of order.

Exclude from analysis in a row's menu takes an outlier out of the analysis without deleting it. Give a reason, and the row stays in the sheet, dimmed, with a mark whose tooltip gives the reason. Include again in the same menu brings the row back.

Charts of the sheet, in the drawer, on a note, or in a figure, leave the row out, and so do the fitted curves of dose-response and ELISA sheets. Summarize by group, Percent change vs a baseline, Correlate, and Compare groups leave it out of their counts, means, and tests too, though the row still gets its own cell in a column they add. A chart on a note says how many rows it skipped. Formulas still read every row.

Format changes how a number is displayed and leaves the stored value as it is. Custom… sets your own decimals and unit. Sorting, charts, and exports still read the stored value. Date styles are fixed for every viewer, so a collaborator in another country reads the same column you do.

Using a Field From the Data Dictionary

Data dictionary field…, below Format in a column's menu, gives the column one of the fields in your team's Data dictionary. The list shows each field's type with its unit and range, or its choices. The column keeps its own name, and hovering its header names the field.

The column takes these from the field.

  • Choices become a dropdown while you type, and anything else you type still saves.
  • A range marks a number outside it with an amber fill and a corner mark. Hovering the cell says what is off and where the expectation comes from, such as Outside 10 to 500, the range the data dictionary sets for Concentration, or Not one of the choices the data dictionary lists for Gel result. A sheet a protocol run made can have a range or choices from the protocol itself, and its cells say so the same way. The value stays as you typed it.
  • A unit shows after every number. Format shows it read-only and keeps it, offering plain, decimals and scientific for that column.

While a column uses a field, that menu entry reads Field: and its name, and opens Pick another field… and Unlink from data dictionary, which removes the field from the column and keeps every value and the unit. A sheet saved as a template keeps its columns' fields, and a new sheet made from it has them too. Data dictionary, in the ⋯ menu at the top of the workspace, opens the dictionary to read it, or to add a field for a column to use.

Formulas

Cells take Excel-style formulas. Start with = and the cell computes. Everything that reads a cell (sorting, filtering, charts, exports, copy and paste) reads the computed value.

Selecting a formula cell brings up a formula bar, which is where you edit it. Esc reverts.

Reading Another Sheet

A formula can read another sheet in the project. Write the sheet's name in square brackets before the cell or range, as in =B2/AVERAGE([Plate 1 raw]!B2:B13), or pick it from Reference another sheet in the formula bar, which writes the name at the cursor.

The cell shows the answer from the other sheet's current rows, and when that sheet changes, the formulas reading it follow, while this sheet is open or the next time it opens.

Renaming the other sheet keeps the formula working under its new name. Once it is deleted, the cell shows #REF!, and deleting it first warns that another sheet uses it. Connections in the sheet's ⋯ menu lists the sheets it reads and the sheets that read it.

A shared sheet's formulas can read only sheets shared with the project, so everyone who opens it can open what it reads. A sheet that reads one of your private sheets can be shared once that sheet is shared too.

Supported Functions

The supported functions are IF, AND, OR, NOT, IFERROR, ABS, ROUND, ROUNDUP, ROUNDDOWN, SQRT, POWER, EXP, LN, LOG, LOG10, MOD, SUM, SUMIF, AVERAGE, MEDIAN, MIN, MAX, COUNT, COUNTA, STDEV, ISBLANK, CONCAT, CONCATENATE, LEFT, RIGHT, MID, LEN, TRIM, UPPER, LOWER, and TEXT.

Functions whose answer changes on its own are left out. That covers NOW, TODAY, RAND, INDIRECT, OFFSET and their kind, because two people opening the same shared sheet would compute different numbers. An imported formula using one of them is kept as written and shows the value it came in with.

Analyze

Analyze runs a named operation over the sheet. Pick one, fill in which columns it should use, and press Run. The result appends derived columns, a result card, or both, in a single step you can undo.

GroupActionWhat it does
ReshapeSummarize by groupGroups rows by a column and aggregates the rest by mean, sum, count, minimum, maximum, or standard deviation
ReshapeReshapeMoves between wide and long layouts
TransformNormalize to a referenceDivides a column by another column or a fixed value
TransformPercent change vs a baselineExpresses values as a percentage change from a baseline group's mean
CompareCorrelatePearson or Spearman correlation between two columns, with r, r², and n
CompareCompare groupsA t-test or ANOVA of one column across groups, with per-group means and significance

Results that aren't columns appear as cards in the Results panel and are saved with the sheet. Chart on a card opens the chart of that result.

A card loses its Chart button when a column its chart plots is deleted.

Assays

Three guided fits sit in the same menu, under Assays.

  • qPCR ΔΔCt normalizes target Ct values to housekeeping genes and a calibrator group, producing ΔCt, ΔΔCt, and fold-change columns
  • ELISA standard curve (4PL) fits a standard curve from your knowns and interpolates the unknowns into a concentration column
  • Dose-response IC50/EC50 (4PL) fits a dose-response curve and reports the IC50 or EC50

A sheet can also be created as a qPCR assay, in which case it opens on a short setup that maps your columns and previews the ΔΔCt calculation before you commit.

After an ELISA standard curve or a dose-response fit, the result card shows the points with the fitted curve drawn over them.

A sheet can hold several fits, such as two analytes read from one plate. Each has its own card, and the curve drawn on a scatter is the one fit to the two columns it plots.

Charting

Chart opens a panel under the grid. Its four controls set the chart type, the x axis, which columns are values, and what to group by. Leave any of them empty for a sensible default.

The panel is a quick preview. To take it further, use Add to or Export.

Add to

Add to sends the chart somewhere else in the project:

ChooseWhere the chart goes
FigureA panel in an existing figure or a new one, with the full styling controls. Customize a Chart covers that editor.
NotebookThe chart at the end of a note you pick, or of a new one.
ReportThe chart a row wide at the end of a report, as Reports describes.
Slide deckThe chart on a new slide at the end of a deck.

In a note, a report or a deck the chart reads the sheet as it changes. Once a chart from the sheet is in a figure, In 1 figure beside Export lists those figures and opens them. Make Publication Figures covers laying one out.

Export

Export takes the preview itself as PNG, SVG, TIFF, or PDF at a printed width in millimeters and up to 600 DPI, or copies it to the clipboard. The SVG is a vector file, so bars, points and axis ticks each select on their own and the legend stays editable text.

Theme draws it Light or Dark, and starts on Light whichever theme the app is in.

On a phone held upright, tap the line naming what's charted to open the four controls.

Embedding Live Data

Embed data pulls a project resource in as columns that refresh from their source. It adds a row per item and leaves your own columns and rows alone.

There are two kinds of source.

  • Live resources bring in a whole workspace at once, one row each, from Samples, Inventory, Orders, Tasks, Locations, Genetics, or Image analysis. Sample status and health, stock quantities, order totals, and task deadlines all refresh from the workspace that owns them.
  • Assays and plates bring in one specific thing you pick. That is a Plate with a row per well, a Sample set with a row per member, one or more Analysis runs with their per-image results, or a protocol's results. Pick several runs and a Group column names each row's source, so treated and control sit side by side ready to chart.

Protocol results brings in one result table from every run of a protocol, or each run's captured values with a row per run. Each row starts with its run, the day it started, who started it, the protocol version and the run's status. It pulls from run tables shared with the project, and a row excluded in its run's table arrives excluded with the same reason. Refresh after another run finishes to add its rows.

A linked sheet fills once. Add your own columns beside the pulled ones, annotate rows, and refresh when you want the latest. If an upstream record is deleted, its row keeps the values you last pulled and your notes, with a flag showing the source is gone.

Finding Things

Search sheet filters rows to those matching in any column, and a caret in the search field opens find and replace. Group by folds rows into groups by a column's value. Freeze up to here on a column keeps the identifying columns on screen while you scroll right.

Cmd/Ctrl + Z and Cmd/Ctrl + Shift + Z undo and redo one step at a time, in the order you worked. A step is a cell edit, a paste, a column change, a sort, a filter, or a Group by choice.

Sharing and Collaboration

A sheet is private to you or shared with the project, chosen when you create it and changeable later. A results table written by an image analysis or a cytometry analysis is the exception. It takes that analysis's sharing and follows it whenever the analysis changes.

A shared sheet in a team project supports live co-editing, so everyone on the project can work in the same grid, with presence avatars in the toolbar showing who's in it. Very large sheets (past about 50,000 cells) turn live editing off.

In a live sheet, edits to different cells all stay, including cells typed while offline, which reach everyone once you're back online. A cell keeps one person's whole entry. When you and a teammate type in the same cell at the same time, whoever's entry was replaced sees Some fields were updated by another user. with both versions under Keep mine and Use theirs, named by place, such as Row 3, Concentration, and Apply keeps the one they pick.

Without live editing, any number of people can still work in one sheet. When a teammate saved it after you opened it, your save takes in their changes as follows.

  • Edits to different cells both stay.
  • Rows either of you added at the bottom are kept.
  • If you both changed the same cell, or one of you deleted a row the other changed, a box above the grid shows Some fields were updated by another user. with each version under Keep mine and Use theirs, named by place, such as Row 3, Concentration. Nothing else you change saves until you choose and click Apply.
  • Changing the columns while someone else edits rows asks about the whole sheet.

The same box asks when you and a teammate both renamed a sheet or changed its status, whether or not it is live.

When your AI assistant changes a sheet you have open, its rows appear at the bottom of the grid, its new and renamed columns appear in place, and the cells it changed take its values. Your edits save alongside them, and a cell you both changed keeps yours.

While your AI assistant edits, it is listed with the people in the sheet as Assistant for and your name. An agent is listed under its own name.

Project members with view-only access can read and export any sheet but can't change it.

Sheets have a status from Draft through to Ready or Archived, and an Activity panel with comments and sign-offs.

Exporting

The Export section of an open sheet's ⋯ menu holds two formats. Excel (.xlsx) keeps formulas, number types, and formatting. CSV (.csv) writes values only, plus an Excluded column holding the reason for any row left out of analysis. Both export the sheet as it is now, including your latest edits and the values you last refreshed.

Those two write one open sheet. To take several at once, use Export in the workspace header or, once you check rows, Download on the bar along the bottom. Both open the same dialog box.

What comes first, and it sets the rest of the dialog box.

  • The sheets themselves writes the same Excel or CSV files the two menu items above write, one per sheet, several arriving as a zip. A selection larger than 50,000 cells is refused before anything downloads, with the count named, so pick fewer sheets and run it again.
  • A list of them writes one row per sheet, with its name, folder, status, source, sharing, its column and row counts, and who made it when. This adds Columns and a Format of CSV or Excel.

Scope applies to both and offers these choices.

  • The folder you have open
  • That folder and everything inside it
  • The whole workspace
  • The rows you checked, which is what Download opens on

Checking a folder includes the sheets inside it.

Download on a folder's own three dot menu asks only about that folder and everything in it, or the whole workspace, whatever level the list is showing. On a sheet's row it asks only about that sheet, or the whole workspace.

Every scope applies the search box and the facets you have on screen, so filtering to one source and pressing Export covers only those sheets.