> ## Documentation Index
> Fetch the complete documentation index at: https://sofiedocs.usetransfer.ai/llms.txt
> Use this file to discover all available pages before exploring further.

# Use formulas, pivots, and charts in CoSheet

> Import data, write formulas, ask Sofie for calculations, build pivot tables, create charts, and export CoSheets to Excel.

Use this guide when you need to work inside a CoSheet, not only ask Sofie about a spreadsheet. CoSheet supports direct spreadsheet editing, import, formulas, pivot tables, charts, collaboration, and Excel export.

## Import spreadsheet files

You can import Excel or CSV data from chat, from a Workspace, or from the CoSheet toolbar.

<Steps>
  <Step title="Open the CoSheet">
    Open the CoSheet you want to update or create a new one.
  </Step>

  <Step title="Click Import">
    Use **Import** in the toolbar or the **More** menu.
  </Step>

  <Step title="Choose the file">
    Select the Excel or CSV file.
  </Step>

  <Step title="Select source sheets">
    Choose the sheets you want to import.
  </Step>

  <Step title="Choose where to import">
    Select **Add as new sheet** or **Replace existing sheet**.
  </Step>

  <Step title="Handle name conflicts">
    If sheet names conflict, choose **Rename with suffix (2)**, **Replace existing sheet**, or **Skip conflicting sheets**.
  </Step>

  <Step title="Confirm import">
    Review the selected sheets and click the import confirmation button.
  </Step>
</Steps>

<Warning>
  **Replace existing sheet** overwrites the selected sheet. Use it only after you confirm the existing sheet is no longer needed.
</Warning>

After import, check:

* Header rows.
* Units and date formats.
* Hidden rows or filtered rows in the source file.
* Merged cells.
* Blank columns.
* Formulas that may have imported as values.

## Enter formulas directly

Click a cell and type formulas like you would in a spreadsheet.

Examples:

```text theme={null}
=SUM(B2:B20)
=AVERAGE(C2:C20)
=COUNTIF(E2:E100,"Open")
=IF(D2="Pass","Ready","Review")
=STDEV.S(B2:B20)
```

Use direct formulas when you already know the calculation. Use Sofie when you know the rule but do not want to translate it into spreadsheet syntax.

## Ask Sofie for formulas

Ask Sofie in chat or Sofie Everywhere to help you create formulas for the CoSheet.

Good formula requests include:

* The sheet name.
* The column names.
* The rule.
* The output column name.
* How missing values should be handled.
* Whether the formula should be filled down.

Example:

```text theme={null}
In this CoSheet, add a column called Status. Use Result, Lower Limit, and Upper Limit. Mark Fail when Result is below Lower Limit or above Upper Limit. Mark Review when a limit is missing. Otherwise mark Pass. Show me the formula before applying it.
```

Another example:

```text theme={null}
Create a formula for Days Open using Created Date and Closed Date. If Closed Date is blank, use today's date and mark the row as still open in a separate Status column.
```

<Tip>
  Ask Sofie to show the formula first when the calculation affects a decision, risk score, or review conclusion.
</Tip>

## Fill formulas across rows

<Steps>
  <Step title="Create the first formula">
    Enter the formula in the first data row below the header.
  </Step>

  <Step title="Check the result">
    Compare the result with a manual calculation for one or two rows.
  </Step>

  <Step title="Fill down">
    Copy or fill the formula through the rest of the table.
  </Step>

  <Step title="Spot-check edge cases">
    Check blank cells, missing limits, text values, and dates.
  </Step>
</Steps>

Use helper columns when a formula gets hard to read. For example, calculate `Days Open`, `Limit Status`, and `Owner Status` separately before creating one final review status.

## Use CoSheet for life sciences tables

Common CoSheet patterns:

| Workflow                 | Useful columns                                                                              |
| ------------------------ | ------------------------------------------------------------------------------------------- |
| Deviation tracker        | Deviation ID, product, batch, event, category, owner, due date, status, open question.      |
| CAPA effectiveness check | CAPA ID, metric, baseline, target, post-implementation value, window, evidence, conclusion. |
| Batch record review      | Batch, section, observation, impact, missing evidence, owner, disposition question.         |
| Risk assessment          | Hazard, cause, control, severity, occurrence, detectability, rationale, action.             |
| Tech transfer action log | Workstream, action, sending site owner, receiving site owner, due date, blocker, decision.  |

Ask Sofie to create these tables when you have the source information in chat, a CoMeeting, or a Workspace.

## Create a pivot table

Use a pivot table when you need grouped counts, sums, averages, or comparisons by category.

<Steps>
  <Step title="Prepare the source data">
    Put headers in the first row of the source range. Remove blank header cells.
  </Step>

  <Step title="Open Pivot">
    Click **Pivot** and choose **Create Pivot Table**.
  </Step>

  <Step title="Set the source range">
    Enter the **Source Range** in A1 notation, such as `A1:F100`.
  </Step>

  <Step title="Set the output location">
    Enter the **Output Start Cell**, such as `H1`.
  </Step>

  <Step title="Drag fields">
    Drag available fields into **Rows**, **Columns**, **Values**, or **Filters**.
  </Step>

  <Step title="Choose aggregation">
    For value fields, choose the aggregation that matches the question, such as count, sum, or average.
  </Step>

  <Step title="Set display options">
    Toggle **Row Totals**, **Column Totals**, **Grand Total**, and choose a style.
  </Step>

  <Step title="Create the pivot">
    Review the configuration and create the pivot table.
  </Step>
</Steps>

Use **Auto** in the pivot dialog when you want Sofie to suggest a starting layout from the available fields.

Good pivot questions:

* How many deviations are open by product and category?
* Which CAPA owners have overdue actions?
* What is the average assay result by campaign?
* Which batch record sections have the most review findings?
* How many risks remain high after planned controls?

## Manage existing pivot tables

The **Pivot** menu can show existing pivot tables. Use it to edit, refresh, or delete a pivot.

Refresh a pivot after source data changes. Review any stale pivot before sharing the CoSheet or exporting it.

<Note>
  A pivot table summarizes source data. It does not replace source-row review when individual records matter.
</Note>

## Create a chart

Use charts when you need to see a pattern, compare categories, or explain a trend.

<Steps>
  <Step title="Prepare the data range">
    Put headers in the first row and labels in the first column when possible.
  </Step>

  <Step title="Click Chart">
    Open **Chart** from the toolbar or **Create Chart** from the **More** menu.
  </Step>

  <Step title="Choose chart type">
    Select a chart type such as column, bar, line, area, pie, doughnut, scatter, or radar.
  </Step>

  <Step title="Enter the data range">
    Use A1 notation such as `A1:D10`.
  </Step>

  <Step title="Add a title">
    Use a title that states the metric and grouping.
  </Step>

  <Step title="Set chart options">
    Choose legend position, grid lines, data labels, stacked bars or areas, and smooth curves when available.
  </Step>

  <Step title="Preview">
    Open **Preview** to check the chart before creating it.
  </Step>

  <Step title="Create the chart">
    Create the chart and review labels, units, and outliers.
  </Step>
</Steps>

Chart type guidance:

| Chart type      | Best for                                      |
| --------------- | --------------------------------------------- |
| Column or bar   | Counts or values by category.                 |
| Line or area    | Trends over time or campaigns.                |
| Pie or doughnut | Simple composition with few categories.       |
| Scatter         | Relationship between two numeric measures.    |
| Radar           | Comparing profiles across several dimensions. |

<Warning>
  Do not use a chart to hide data quality issues. Ask Sofie to inspect missing values, units, outliers, and grouping logic before you draw conclusions.
</Warning>

## Export to Excel

Use **Export** or **Export as Excel** to download a `.xlsx` file.

Before exporting:

* Refresh pivot tables.
* Check formulas after imported data changes.
* Confirm chart labels and units.
* Remove scratch sheets or clearly label them.
* Confirm sensitive draft notes should leave Sofie.
* Save or note the version you exported.

Useful export prompt:

```text theme={null}
Review this CoSheet before Excel export. List formula risks, stale pivots, unclear chart labels, missing units, hidden assumptions, and rows that need human review.
```