Plan
Spreadsheets
Excel-compatible spreadsheets inside a project: sheets, formulas, formats, tables, validation, named views with filters and sort, live model data, charts, Excel import and export, AI formula help and the API.
On this page
Overview
A spreadsheet is a workbook of sheets with values, formulas and formats, kept in the project next to the model. Use it for the calculations engineers keep beside a model:
- mass, power and thermal budgets;
- link budgets and trade-study scoring;
- schedules, cost estimates and test-data reduction;
- requirement and verification tables.
Formulas use the Excel formula language and Excel's function names. The Holarch Calc Engine calculates them in the page and on the server, with Excel's results as the reference. Excel workbooks import with their formulas live and export back to .xlsx.
A spreadsheet is an item: an Artifact labeled Spreadsheet. It has a name, number, description, labels, comments, history and relationships like any item, and counts as one item toward the item limit. Cells do not count as items; they have their own limits (see Limits).
Spreadsheets work the same in LML and SysML v2 projects, and on every plan.
Concepts
- Spreadsheet: the item that holds the workbook. Listed under Plan → Spreadsheets and in the Database as an Artifact with the Spreadsheet label.
- Sheet: one tab of the workbook. A spreadsheet has at least one sheet. Sheets can be hidden and colored.
- Cell: a constant (number, text, TRUE or FALSE, a date) or a formula starting with
=. A filled cell is a cell with a value or formula; formats and notes alone do not count. - Formula: an Excel-style expression, such as
=SUM(B2:B12)*1.1. Function names are English and arguments are separated by commas in every interface language. - Computed value: the result of a formula. Holarch never stores it: each page calculates it from the inputs, so every viewer sees the current result.
- Named view: how a sheet is shown: filters, sort, hidden and reordered columns, column widths and frozen panes. Views never change cell data. Every sheet has the Default view; shared views are seen by everyone with access, personal views only by their owner.
- Table: a block of cells with a header row and a name, such as
Parts. Formulas refer to its columns by name:=SUM(Parts[Mass]). - Model table: a live, read-only table of model items in a sheet. It changes when the model changes.
- Marked cell: a cell imported from Excel that keeps Excel's last value because Holarch does not calculate its formula yet. It is marked Value only, not recalculating, and its tooltip says why.
- Undo: a single Undo takes back any one change to cells: typing an entry, a paste of 10,000 cells, a fill, an import. Undo does not touch changes to a view (filters, sort), because views are not cell data.
How to use it
When and why
Use a spreadsheet when a calculation belongs to the project but does not fit the model's own attributes. Typical points in the process:
- Concept and architecture: trade studies with weighted scores, first mass and power estimates.
- Design: budgets with contingency and margin, link budgets, sizing calculations with iteration.
- Verification: test-data reduction, statistics and pass/fail tables.
- Program control: cost estimates and schedule arithmetic.
When a value should be part of the model (a requirement's threshold, a component's mass), keep it on the item and read it into the sheet with a model table or HOLARCH.VALUE. The spreadsheet then follows the model, and the model stays the single source.
Create a spreadsheet
- Open Plan → Spreadsheets.
- Click + New Spreadsheet, or press ⌘K / Ctrl+K and choose New Spreadsheet….
- Type a name and press Enter. You see the editor with an empty sheet named Sheet1 and the active cell in A1.
To start from an Excel workbook instead, see Import an Excel workbook.
Enter values and formulas
- Select a cell and type. Press Enter to move down or Tab to move right.
- To enter a formula, type
=first, for example=B2*C2. - While you type a function name, a list of matching functions opens. Press ↑ or ↓ to choose and Tab to insert it. The argument hint under the entry shows the function's arguments.
- To point at a cell for a reference, press an arrow key while typing a formula (Point mode), or click the cell.
- Press F4 (or ⌘T / Ctrl+T) with the cursor in a reference to switch it between relative and absolute (
B2,$B$2,B$2,$B2). - Press Enter to finish, or Esc to cancel. The cell shows the result. The formula bar shows the formula.
A formula that cannot be read stays open with the message "This formula has a problem." and the reason under it, for example "A parenthesis is not closed." Correct it, or press Esc.
Build a budget spreadsheet
This builds a small mass budget with contingency and a margin check.
- Create a spreadsheet named
Mass Budget. - In row 1 type the headings
Subsystem,Mass (kg),Contingency,With contingency. - In rows 2–4 type three subsystems with their mass and contingency, for example
Payload Camera,2.4,10%. - In D2 type
=B2*(1+C2)and press Enter. - Select D2:D4 and press ⌘D / Ctrl+D to fill the formula down. Each row now shows its mass with contingency.
- In A6 type
Total, in D6 type=SUM(D2:D4). - In A7 type
Allocation, in D7 type12. In A8 typeMargin, in D8 type=(D7-D6)/D7. - Select D8 and click % in the toolbar.
- With D8 selected, click Conditional ▾ → Highlight Cells Rules → Less Than, type
0.1and click OK. The margin is highlighted when it falls below 10 %.
Worked example: CubeSat EO-1 mass check
The CubeSat EO-1 demo keeps the subsystem masses on the model items. Read them into a sheet instead of typing them:
- Create a spreadsheet named
EO-1 Mass Check. - In A1 type
Payload Camera mass, in B1 type=HOLARCH.VALUE("Payload Camera","Mass"). B1 shows 2.4, the Mass of 1.1 Payload Camera in kg. - In A2 type
Payload with contingency, in B2 type=B1*1.1(the demo uses 10 % contingency for a calculated mass). B2 shows 2.64. - In A3 type
Payload allocation, in B3 type2.75, the allocation of 1.1 Payload Camera. - In A4 type
Margin, in B4 type=(B3-B2)/B3, then click %. B4 shows 4 %. - Open the Database, change the Mass of Payload Camera to 2.5 kg and come back. B1 shows 2.5, and the margin goes down without any edit to the sheet.
For a list of many items, insert a model table (Model Table… in the toolbar) of the Asset class with the Number, Name and Mass columns, then total it with =SUM(Parts[Mass]), where Parts is the table name you chose.
Filter and sort with views
- Click a cell inside the data. Its first row is the header row.
- Click Sort & Filter ▾ → Filter Buttons, or press ⇧⌘F / Ctrl+Shift+F. Each header cell gets a filter button.
- Click a filter button (or select the header cell and press ⌥↓ / Alt+↓). Untick values, or choose By condition, then click OK. The rows that do not match are hidden. The views bar shows Unsaved changes.
- Click Save View. In the Default view this asks for a name: type
Open items, choose Only me or Everyone with access to this project, and click Save View. The new view is a tab in the views bar. Other people still see their own view.
Import an Excel workbook
- Press ⌘K / Ctrl+K and choose Import Excel Workbook as a Spreadsheet…, or open Import and Export and click Import as a Spreadsheet….
- Choose the .xlsx or .xlsm file. A progress card shows the stages for a large workbook.
- Read the Import Summary, then click Close. The spreadsheet opens with its sheets, formulas, formats, tables and charts.
See Excel import and export for what comes in and what the summary means.
Chart a range
- Select the cells to chart, including the header row and the label column.
- Click Chart ▾ in the toolbar and choose a type, or right-click and choose Insert Chart. A chart floats over the sheet. It changes when the cells change.
- Drag it into place and drag its corner to resize it. It stays exactly where you drop it; hold ⌥ / Alt (Alt) to line it up with the cell edges. Double-click it for Select Data….
- To change an axis, right-click the chart and choose Format Vertical Axis… or Format Horizontal Axis…, then set the minimum, maximum, units or title in the Format panel.
To show the chart on a whiteboard, right-click it and choose Copy for a Whiteboard, then paste it on the board. The pasted chart stays linked to the cells.
Tips and good practice
- Put one header row at the top of each data block. Filters, sort, tables and charts use it.
- Turn a data block into a table (Table ▾, ⌘T / Ctrl+T) when other formulas use it.
Parts[Mass]follows the table as it grows. - Keep values that belong to the model on the items, and read them with a model table or
HOLARCH.VALUE. A formula never writes to the model. - Save the filters you use often as shared views, so the team looks at the same lists.
- Edits recalculate by themselves. Use Recalculate only when a value looks out of date.
Common mistakes
- A formula shows as text. The cell has the Text number format. Set the format to General and enter the formula again.
- A filter hides the wrong rows. The filter range starts at the wrong header row. Click a cell in the right block and turn Filter Buttons off and on again.
- Typing in a cell is refused. The cell is part of a model table ("Cells in model table … come from the model and can't be edited."), or you have view access only ("You can view this spreadsheet but not change it.").
- The status bar says "Circular reference". A formula refers to its own cell, directly or through other cells. Its value shows as 0 until the loop is removed.
- A number typed as
1,5becomes text. Typed numbers and dates follow your browser's language. In English, type1.5.
How it connects
- Model: model tables and
HOLARCH.VALUE/HOLARCH.COUNTread items and their attributes; the sheet follows model edits. - Whiteboards: charts and live tables on a board follow sheet ranges (Whiteboards).
- Documents: Insert → Spreadsheet Range… places a live table or chart in a document; Word export uses the values at export time (Documents).
- Presentations: Picture of… lists spreadsheets (Presentations).
- Baselines: spreadsheets are part of every baseline; Compare lists changed cells.
- API and MCP: read ranges and write cells from scripts and AI clients (API and MCP).
The Spreadsheets list
Plan → Spreadsheets lists the project's spreadsheets as cards, newest change first. Each card shows the first sheet's top-left cells, the name, and "n sheets · n filled cells · date".
- Search spreadsheets: filters by name, number and description.
- + New Spreadsheet: creates an empty spreadsheet and opens it (not shown with view access).
- Card menu (the ⋯ button, or right-click a card): Open, Rename…, Duplicate, Open Item View, Move to Trash. Move to Trash asks first: "Move “name” and all its sheets to the project's Trash? It can be restored from there."
Empty states: "No spreadsheets yet. Create one with + New Spreadsheet." With view access: "This project has no spreadsheets."
The editor
The editor is laid out from top to bottom: the toolbar, the formula bar with the Name Box, the views bar, the grid, the sheet tabs and the status bar. F6 and ⇧F6 / Shift+F6 move between these areas.
Toolbar
The toolbar groups its controls. Every button has a tooltip.
| Group | Controls |
|---|---|
| Edit | Undo (⌘Z / Ctrl+Z), Redo (⇧⌘Z / Ctrl+Shift+Z), Cut ✂ (⌘X / Ctrl+X), Copy ⧉ (⌘C / Ctrl+C), Paste ▾ (Paste, Paste Special…), Cells ▾, Fill ▾ |
| View | Freeze ▾ (Freeze Panes, Freeze Top Row, Freeze First Column, Unfreeze Panes), Gridlines (on or off for this sheet), Sort & Filter ▾, Find |
| Format | Recalculate, B, I, U, fill colour, text colour, Borders ▾, Alignment ▾, Number Format ▾, %, , (thousands separator, two decimals), .0+ and .0− (more or fewer decimals), Conditional ▾ |
| Insert | Note, Table ▾, Validation ▾, Model Table…, Chart ▾ |
| AI | ✦ AI ▾ (Explain Formula, Write a Formula…) |
| More ⋯ | Keyboard Shortcuts, Rename Spreadsheet, Open Item View, All Spreadsheets, Help for Spreadsheets, Export Spreadsheet to Excel, Export Sheet as CSV, Export Sheet as TSV, Show Import Summary and Export Recalculation Report… (imported spreadsheets), Move to Trash |
With view access, the editing controls are hidden; Copy, Freeze, Sort & Filter, Find, Recalculate, Explain Formula and the exports remain.
Cells ▾: Insert Cells… (⌘+ / Ctrl++), Insert Rows, Insert Columns, Delete Cells… (⌘− / Ctrl−), Delete Rows, Delete Columns, Row Height…, Column Width…, Format Cells… (⌘1 / Ctrl+1).
Fill ▾: Fill Down (⌘D / Ctrl+D), Fill Right (⌘R / Ctrl+R), Fill Up, Fill Left, Series….
Formula bar and Name Box
- Name Box (left): the address of the selection, such as
C4orA1:C10, or a name. Type an address or a name and press Enter to go there. Type a new name with cells selected to name the selection ("Name “Total” defined."). Its list shows the defined names. ⌃G / Ctrl+G or F5 puts the cursor in it. - ƒx (Insert Function): starts a formula and lists the functions.
- Formula bar: the active cell's contents as typed, so a formula shows in A1 form. Edit there or in the cell. The expand button shows long formulas on several lines. References are colored to match their outlines in the grid.
- ✕ and ✓: cancel the entry (Esc) or enter it (Enter).
An address that is not valid gives "“text” is not a cell address or a name. Enter an address such as B3 or A1:C10, or a name."
Grid
- Click a cell to select it, drag to select a range, ⇧ / Shift-click to extend, ⌘ / Ctrl-click to add another area. Click a row or column header to select it; the corner selects all cells.
- Drag a column header's right edge to change the width; double-click the edge to fit the contents. Drag a row header's bottom edge for the height.
- Right-click a cell, or press ⇧F10 / Shift+F10, for the cell menu: Cut, Copy, Paste Special…, Insert Cells…, Delete Cells…, Clear Contents, Format Cells…, Sort ▸ (Sort A to Z, Sort Z to A, Custom Sort…), Filter by Selected Cell's Value, the table's menu, Insert Model Table… (on a model table: Edit Model Table…, Open Items in Database, Remove Model Table), Insert Chart, New Note or Edit Note and Delete Note, Explain Formula and Write a Formula….
- Right-click a column header for Insert Columns, Delete Columns, Column Width…, Hide Columns and Unhide Columns; a row header for the same with rows.
- The fill handle sits at the corner of the selection (see Fill and series).
- A cell with a note has a corner marker; its text shows as a tooltip.
Views bar
The views bar lists the sheet's views as tabs: Default, then the shared views (👥), then your personal views (👤). A dot marks a view with unsaved changes.
- + New View: saves the current filters, sort and columns as a named view.
- View actions (the ⋯ button, or right-click a view tab): Save as New View…, Rename View…, Delete View.
- Unsaved changes, Save View and Revert appear while the view you are looking at has changes only you see.
See Named views.
Sheet tabs
- Click a tab to show the sheet. ⌥← / Alt+← / ⌥→ / Alt+→ or ⌃PageUp / Ctrl+PageUp / ⌃PageDown / Ctrl+PageDown move to the previous or next sheet.
- All Sheets (the list button) lists every sheet, hidden ones included.
- + (Add Sheet) adds a sheet after the current one.
- Double-click a tab (or press F2 on it) to rename it. Drag a tab to move it.
- Right-click a tab, or press ⇧F10 / Shift+F10 on it: Insert Sheet, Rename, Move Left, Move Right, Tab Color (No color, Red, Orange, Yellow, Green, Blue, Purple, Gray), Hide Sheet, Unhide Sheet…, Delete Sheet.
Delete Sheet asks first and names the consequence: "Delete the sheet “name” and its n filled cells? Formulas on other sheets that use it show #REF!. Undo brings it back." The last sheet cannot be deleted, and the last shown sheet cannot be hidden.
Sheet names have at most 31 characters, cannot contain : \ / ? * [ ], cannot start or end with an apostrophe, and must be unique in the spreadsheet.
Status bar
- Mode: Ready, Enter (arrow keys finish the entry), Edit (arrow keys move in the text) or Point (arrow keys pick a reference).
- Circular reference: "Circular reference: Sheet1!C12" when a formula depends on itself.
- Average, Count and Sum of the selection, when it has two or more filled cells.
Cells and formulas
Entering values
What you type is read as Excel reads it:
- numbers, percentages (
10%), and numbers with the thousands separator of your browser's language; - dates and times in your browser's language (
2026-12-31always works); TRUEandFALSE;- anything else is text. Start with an apostrophe (
'0042) to keep a number as text.
⌥Enter / Alt+Enter adds a line break inside the cell. ⌘Enter / Ctrl+Enter puts the entry into every selected cell. ⌘; / Ctrl+; enters today's date and ⇧⌘; / Ctrl+Shift+; the current time. Delete clears the selected cells.
Input methods. Japanese, Chinese and Korean input methods, dead keys (such as ⌥E / Alt+E on a Mac), dictation and the emoji viewer work in the active cell: the composition runs in the cell and its text becomes the start of the entry. Because the active cell accepts text this way, single-key shortcuts such as ? for Help do not work while a cell has focus. Use ⋯ → Help for Spreadsheets, or press F6 to leave the grid first.
Formulas
- Start with
=. A leading+is accepted too. - Function names are English, arguments are separated by commas, and the decimal mark is a dot, in every interface language.
- References:
B2,B2:D20, whole columnsB:Band rows3:3, other sheetsSheet2!A1or'Test Data'!A1:C9, names, and table references (see Tables). - Operators:
+ - * / ^ &, comparisons= <> < > <= >=, the range:, the intersection (a space) and%. As in Excel,=-2^2is 4. - Dynamic arrays spill into the cells next to the formula, as in Excel. A spill that would cover filled cells shows
#SPILL!.A2#refers to a whole spill range, and@gives implicit intersection. LETnames values inside a formula.LAMBDAmakes your own function; save it as a defined name to call it like a built-in one.- Limits, as in Excel: 8,192 characters per formula, 64 levels of nested functions, 255 arguments per function, 32,767 characters per text value.
- A formula's result can have at most 1,000,000 cells; one whole column (1,048,576 cells) is allowed. A larger result is
#NUM!, and the formula bar says the result exceeds 1,000,000 cells.
Errors: #NULL!, #DIV/0!, #VALUE!, #REF!, #NAME?, #NUM!, #N/A, #SPILL! and #CALC!, with Excel's meanings. When Holarch knows more about an error (a result over the cell limit, a regular expression it cannot run), the note under the formula bar, the cell's tooltip and Explain Formula say why.
Messages while entering a formula:
| Message | Meaning |
|---|---|
| "The formula is empty." | Only = was typed. |
| "A parenthesis is not closed." | Add the missing ). |
| "A text value is not closed with a quotation mark." | Add the closing ". |
| "A quoted sheet name is not closed." | Add the closing ' after the sheet name. |
| "This reference is not valid." / "This table reference is not valid." | Check the address, or the table and column names. |
| "This number is not valid." | Check the digits and the decimal dot. |
| "The formula is too long. Split it into several cells." | Over 8,192 characters. |
| "Too many functions inside each other. Split it into several cells." | Over 64 nested functions. |
| "Too many arguments for one function." | Over 255 arguments. |
| "LET names start with a letter and cannot look like a cell reference." | Rename the LET variable. |
Recalculation
Edits recalculate their dependent cells at once. A spreadsheet is fully calculated when it opens and after an import. Click Recalculate in the toolbar (or press ⌘K / Ctrl+K and choose Recalculate) to calculate every formula again ("Recalculated.").
A recalculation that takes longer shows a progress card, "Recalculating", with Cancel. Cancel stops it between two steps: "Recalculation stopped. Shown values may be out of date." Very long calculations stop at the calculation limit: "Recalculation stopped at the calculation limit. Shown values may be out of date."
NOW and TODAY use your computer's clock and time zone. RAND, RANDBETWEEN and RANDARRAY give new values on each recalculation.
Names
Name a cell or range by typing the name in the Name Box with the cells selected. Names start with a letter or underscore, have no spaces and cannot look like a cell address. Use them in formulas like references: =Total*1.1. A name defined as a LAMBDA works as a function: =Margin(D7,D6).
Editing
Clipboard and Excel
- ⌘C / Ctrl+C copies the selection as tab-separated text and as a formatted table, so it pastes into Excel, Word, Numbers or Google Sheets with values and basic formats.
- ⌘V / Ctrl+V pastes a copy from Holarch (formulas adjust their relative references), cells copied from Excel (values, number formats and formulas when Excel includes them), or tab-separated text (an entry starting with
=becomes a formula). - ⌘X / Ctrl+X then ⌘V / Ctrl+V moves cells, and formulas that point at them follow. Cut works on one block of cells.
- Each paste can be undone with a single Undo. A copy, paste or fill handles at most 200,000 cells.
Paste Special (⌃⌘V / Ctrl+V; on Windows and Linux Ctrl+Alt+V): choose All, Values, Formulas, Formats or Notes, and optionally Transpose (rows become columns) and Skip blanks (empty copied cells leave the cells they land on unchanged).
If the browser blocks reading the clipboard from the Paste menu: "The browser blocked reading the clipboard. Press ⌘V / Ctrl+V to paste instead."
Fill and series
- Drag the fill handle at the corner of the selection to copy the cells or extend a series. Hold ⌥ / Alt while dragging to switch between copying and a series.
- Double-click the fill handle to fill down to the end of the data next to it.
- ⌘D / Ctrl+D fills down and ⌘R / Ctrl+R fills right from the first row or column of the selection.
- Series: numbers continue their step (1, 3, 5 …), dates continue by day, month or year, text ending in a number continues (
Test 1,Test 2), and weekday and month names continue. Anything else repeats. - After a fill, the Auto Fill Options button offers Copy Cells, Fill Series, Fill Formatting Only and Fill Without Formatting.
- Fill ▾ → Series… sets Series in (Rows or Columns), Type (Linear, Growth, Date, AutoFill), the Date unit (Day, Weekday, Month, Year), the Step value and an optional Stop value. The first cell needs a number or a date.
Insert and delete
- Insert Rows and Insert Columns add as many rows or columns as are selected; Delete Rows and Delete Columns remove them. References adjust; a reference to a deleted cell becomes
#REF!. - Insert Cells… (⌘+ / Ctrl++) asks Shift cells right, Shift cells down, Entire row or Entire column. Delete Cells… (⌘− / Ctrl−) asks Shift cells left, Shift cells up, Entire row or Entire column.
- Hide Rows/Columns and Unhide Rows/Columns are in the header menus. Row Height… (0–800 px) and Column Width… (0–2,000 px) are in the header menus and Cells ▾.
- Two people inserting rows at the same time keep both rows, and formulas stay correct.
Format Cells and number formats
Format Cells… (⌘1 / Ctrl+1, the cell menu or Cells ▾) has five tabs:
- Number: Category General, Number, Currency, Accounting, Date, Time, Percentage, Fraction, Scientific, Text or Custom, with Decimal places, Use thousands separator, Symbol and Negative numbers where they apply. The Sample shows the result. Custom takes an Excel format code of up to 255 characters, such as
0.00" kg"or[Red]-0.0;0.0. - Alignment: Horizontal (General, Left, Center, Right), Vertical (Top, Middle, Bottom), Wrap text and Indent (0–15).
- Font: bold, italic, underline, strikethrough, size and text colour.
- Border: None, Thin, Medium or Thick per side.
- Fill: the cell colour.
The toolbar has the common formats: Number Format ▾ (General, Number, Currency, Accounting, Short Date, Long Date, Time, Percentage, Fraction, Scientific, Text, More Number Formats…), %, , and the decimal buttons; Borders ▾ (All Borders, Outside Borders, Top, Bottom, Left, Right, Thick Bottom Border, No Border); Alignment ▾ (horizontal and vertical alignment, Wrap text, Increase Indent, Decrease Indent); fill colour and text colour (a palette, No Fill or Automatic, and More Colours…).
Number formats are stored as Excel format codes, so they survive an export. Values show with your browser's decimal and thousands separators.
Conditional formatting
Conditional ▾ highlights cells by their values:
- Highlight Cells Rules: Greater Than, Less Than, Between, Equal To, Text that Contains (case does not matter), A Date Occurring (Yesterday, Today, Tomorrow, In the last 7 days, Last week, This week, Next week, Last month, This month, Next month), Duplicate Values, Unique Values.
- Top and Bottom Rules: Top Items and Bottom Items, a number of cells or a per cent of the cells.
- Colour Scales: Green, yellow, red; Red, yellow, green; White to green; White to red; White to blue.
- Clear Rules from Selected Cells and Clear Rules from Entire Sheet.
- Manage Rules…: the sheet's rules with Edit, Delete and move up or down. Rules higher in the list win when several apply to a cell.
Each highlight rule takes a style: Light red fill with dark red text, Yellow fill with dark yellow text, Green fill with dark green text, Light red fill, Red text, or Bold text. A highlighted cell's screen-reader name says which rules apply. Rules from a formula, data bars and icon sets are not available yet.
Tables
A table gives a block of cells a header row, a name and a style.
- Click a cell in the data and click Table ▾, or press ⌘T / Ctrl+T (also ⌃L / Ctrl+L on a Mac; Ctrl+T or Ctrl+L on Windows and Linux).
- Check Cells, My table has headers, the Table name and the Style (Blue, Green, Orange, Gray), then click Create Table.
The Table ▾ menu, or the table entry in the cell menu, then offers Total Row (a Total row with a SUBTOTAL of each numeric column), Banded Rows, Style, Select Table, Rename Table and Convert to Range (the cells and formats stay). Typing directly below or right of a table adds the row or column to it.
Structured references name a table's parts in formulas:
| Reference | Means |
|---|---|
Parts or Parts[#Data] | the data rows |
Parts[Mass] | the Mass column's data |
Parts[#All], Parts[#Headers], Parts[#Totals] | the whole table, the header row, the total row |
Parts[@Mass] or Parts[[#This Row],[Mass]] | the Mass cell in the formula's own row |
Parts[[Mass]:[Power]] | the columns from Mass to Power |
Column names are the header text, compared without regard to case. Renaming a table (Rename Table) or typing a new header over a table's header cell updates every formula that names it, and a single Undo takes it all back ("3 formulas updated.").
Data validation
Validation ▾ → Data Validation… limits what can be typed in the selected cells.
- Settings: Allow Any value, Whole number, Decimal, List, Date or Text length; the condition (between, not between, equal to, not equal to, greater than, less than, greater than or equal to, less than or equal to) with Minimum and Maximum or a value; Allow empty cells. A list takes items separated by commas, or a range on the same sheet starting with
=, and Show a dropdown list in the cell. - Input message: a title and message shown beside the cell when it is selected.
- Error alert: Style Stop (the entry is refused), Warning (asks "Keep this entry anyway?") or Information (the entry is kept after OK), with your own title and message.
In a cell with a list, press ⌥↓ / Alt+↓ or click the arrow to choose a value. Validation ▾ → Clear Validation removes the rules from the selected cells. When the selection is partly covered by a rule, Holarch asks whether to extend it; when it holds several kinds of rule, Erase and Continue clears them first.
Note: As in Excel, validation checks typed entries only. Pasted and filled cells are not checked, and formula cells are not checked.
Notes
A note is plain text on a cell.
- Press ⇧F2 / Shift+F2, click Note in the toolbar, or choose New Note in the cell menu. Type the text and press ⌘Enter / Ctrl+Enter (or click Save Note); Esc cancels.
- Save empty text, or choose Delete Note, to remove it.
- A cell with a note shows a corner marker; the note is its tooltip and is read by screen readers with the cell.
Find and Replace
Find (⌘F / Ctrl+F) opens a bar above the grid; ⇧⌘H / Ctrl+Shift+H (Ctrl+H on Windows and Linux) opens it with Replace.
- Within: This sheet or Workbook. Look in: Formulas (as shown in the formula bar) or Values (as shown in cells). Match case and Entire cell.
- Next (Enter) and Previous (⇧Enter / Shift+Enter) move to each match ("3 of 12, Sheet1!C5"). Find All lists the matches, each a button that goes to the cell; the first 500 are listed.
- Replace changes the current match; Replace All changes every match, and a single Undo takes it all back. A replacement that would leave a formula that cannot be read is skipped and counted.
Named views
Views let several people look at the same sheet in different ways. Filters and sort are always part of a view and never change the cells, so one person's filter never changes what others see.
Totals ignore personal view filters, so every viewer sees the same values; use SUMIFS to total a filtered subset.
Kinds of view
- Default: the sheet as stored, the same for everyone. In the Default view, an editor's column widths, hidden columns and frozen panes change the sheet itself, as in Excel. Filters and sort in the Default view are only yours until you save them as a new view.
- Shared views (👥): seen by everyone with access to the project. Editors create, rename and delete them; they are part of the project and its undo history.
- Personal views (👤): seen only by you. Anyone who can open the spreadsheet, viewers included, can save personal views. In the hosted app they are saved on the server with your account, so they are there in any browser you sign in from.
A view stores: filters, sort (up to 8 levels), hidden and reordered columns, column widths, frozen rows and columns, and its filter range. A spreadsheet can have up to 100 views.
Unsaved changes
When you filter, sort, hide columns or change widths in a view, the change is a draft that only you see. The views bar shows Unsaved changes with:
- Save View: keeps the changes in this view. In the Default view, or in a shared view you cannot edit, it opens Save as New View… instead.
- Revert: drops your changes ("Unsaved view changes dropped.").
Create, rename and delete views
- + New View (or ⌘K / Ctrl+K → New View…): type the View name (for example "Open budget items", "By subsystem", "Mass over 1 kg") and choose Who can see it: Only me or Everyone with access to this project. Shared views can be made by editors.
- View actions → Save as New View… copies any view.
- Rename View… and Delete View work on your personal views, and for editors on shared views. Deleting a shared view asks first: "Delete the shared view “name” for everyone? The data stays. Undo brings the view back." Deleting a personal view cannot be undone.
The last view you used on each sheet opens again next time.
Sort and filter
Sort and filter work on a block of data: its first row is the header row, which stays in place and is never filtered. Rows above and below the block keep their places. A view has one filter range.
- The block is the table at the active cell, else the selection when it is more than one cell, else the data around the active cell.
- Sort & Filter ▾: Sort A to Z, Sort Z to A (by the active cell's column), Custom Sort…, Filter Buttons (⇧⌘F / Ctrl+Shift+F; Ctrl+Shift+L on Windows and Linux), Clear All Filters, Clear Sort.
- Custom Sort…: Sort by a column and its order (Ascending, Descending), Add Level for Then by (up to 8 levels), Remove level.
- Filter buttons on the header row: click one, or select the header cell and press ⌥↓ / Alt+↓. By value lists the column's values with Search values, (Select All) and (Blanks); the first 1,000 values are listed. By condition takes one or two conditions joined with and or or: equal to, not equal to, greater than, less than, greater than or equal to, less than or equal to, between, contains, does not contain, begins with, ends with, top N, bottom N, above average, below average. The dialog also sorts the column and has Clear Filter.
- Filter by Selected Cell's Value (cell menu) keeps only rows with the active cell's value in its column.
Turning filter buttons on for another block moves the view's filter range there and clears the old filters. Screen readers hear how many rows are shown, for example "12 of 40 rows shown."
Model data
A spreadsheet can read the model. It never writes to it: a formula or a model table cannot change an item.
Model tables
A model table is a live, read-only table of items, starting at a cell: a header row, then one row per item.
- Select the top-left cell and click Model Table… in the toolbar (or Insert Model Table… in the cell menu, or ⌘K / Ctrl+K → Insert Model Table).
- Under Items from, choose A class (for example every Requirement), A saved view (a Database view shared with the project) or A query (a Database search such as
class:Requirement AND Status:Approved; empty means every item). The dialog shows how many items match. - Tick the Columns: Number, Name, Description, Class and the class's attributes.
- Type a Table name, such as
Reqs, and click Insert. The table fills in, sorted by number. It changes when items are added, changed or deleted.
In a SysML v2 project, a class source offers Definitions instead of usages: the table lists part definitions, such as Battery, rather than the parts typed by them. On a model table's cells, the cell menu offers Edit Model Table…, Open Items in Database and Remove Model Table (the items stay; formulas that use the table show #REF!).
- Read-only: typing in a model table is refused with "Cells in model table name come from the model and can't be edited. Change the items in the Database, or edit the table to change its columns."
- Size: at most 5,000 rows ("The table shows the first 5000.") and 40 columns.
- Blocked: a table that would cover filled cells shows
#SPILL!in its first cell until those cells are cleared or the table moves. - Missing source: when the class or saved view is gone, the first cell shows
#REF!and the reason; a query that cannot be read shows#VALUE!. - Formulas use a model table like any table:
=SUM(Reqs[Mass]),Reqs[@Mass],Reqs[#All]. A model table has no total row, so[#Totals]is#REF!.
Units
Numbers in a model table are plain numbers in one unit per column. The header names it, such as "Mass (kg)". The first item with a unit sets the column's unit, and other items convert to it, so 2,100 g shows as 2.1 in a kg column. A value of another kind (a length in a mass column) is #VALUE!. A number without a unit stays as it is.
HOLARCH.VALUE and HOLARCH.COUNT
HOLARCH.VALUE(item, attribute, [unit])gives one value of one item. item is the item's number, name or id; attribute is Number, Name, Description, Class, an attribute or a named value of the item (in SysML v2, an attribute usage). A quantity gives its number in its own unit, or converted to unit when given:=HOLARCH.VALUE("1.1","Mass","g").HOLARCH.COUNT(query)counts the items that match a Database search:=HOLARCH.COUNT("class:Requirement"). An empty query counts every item.
| Result | Meaning |
|---|---|
#N/A | No item has that number, name or id. |
#VALUE! | More than one item has that name; the item or attribute is empty; the unit does not fit the value (or the value has no unit); the query cannot be read. Use the item's number to pick one item. |
#REF! | The item has no attribute or value of that name. |
#GETTING_DATA | The value is being read from the model. It is replaced as soon as the read finishes, usually before the sheet shows it. |
In a SysML v2 project, a name shared by one definition and its usages (the part definition Battery and the battery parts typed by it) finds the definition. Both functions recalculate when the model changes. Booleans give TRUE or FALSE, percentages give a fraction (50 % is 0.5), and dates and durations give their text.
Model data in exports
Excel cannot calculate model data. An export to Excel writes model-table cells and cells with HOLARCH.* formulas as their current values, and adds a sheet named About this export at the end of the workbook. It lists each model table (its cells and source) and each HOLARCH.* cell (its address and formula).
Functions
The Holarch Calc Engine calculates 353 of the 524 Excel worksheet functions, checked against values calculated by Excel. Function names, arguments and results follow Excel. The formula bar's list and argument hints show every function.
| Category | Count | Functions |
|---|---|---|
| Array, dynamic and LAMBDA | 8 | LAMBDA, MAP, REDUCE, SCAN, BYROW, BYCOL, MAKEARRAY, ISOMITTED |
| Compatibility (older names) | 24 | BINOMDIST, CHIDIST, CHIINV, CONFIDENCE, EXPONDIST, LOGINV, LOGNORMDIST, MODE, NORMDIST, NORMINV, NORMSDIST, NORMSINV, PERCENTILE, POISSON, QUARTILE, RANK, STDEV, STDEVP, TDIST, TINV, TTEST, VAR, VARP, WEIBULL |
| Database | 4 | DAVERAGE, DCOUNT, DGET, DSUM |
| Date and time | 25 | DATE, DATEDIF, DATEVALUE, DAY, DAYS, DAYS360, EDATE, EOMONTH, HOUR, ISOWEEKNUM, MINUTE, MONTH, NETWORKDAYS, NETWORKDAYS.INTL, NOW, SECOND, TIME, TIMEVALUE, TODAY, WEEKDAY, WEEKNUM, WORKDAY, WORKDAY.INTL, YEAR, YEARFRAC |
| Engineering | 46 | BIN2DEC, BIN2HEX, BITAND, BITLSHIFT, BITOR, BITRSHIFT, BITXOR, COMPLEX, CONVERT, DEC2BIN, DEC2HEX, DEC2OCT, DELTA, ERF, ERF.PRECISE, ERFC, ERFC.PRECISE, GESTEP, HEX2BIN, HEX2DEC, IMABS, IMAGINARY, IMARGUMENT, IMCONJUGATE, IMCOS, IMCOSH, IMCOT, IMCSC, IMCSCH, IMDIV, IMEXP, IMLN, IMLOG10, IMLOG2, IMPOWER, IMPRODUCT, IMREAL, IMSEC, IMSECH, IMSIN, IMSINH, IMSQRT, IMSUB, IMSUM, IMTAN, OCT2DEC |
| Financial | 15 | DDB, EFFECT, FV, IPMT, IRR, NOMINAL, NPER, NPV, PMT, PPMT, PV, RATE, SLN, XIRR, XNPV |
| Information | 19 | CELL, ERROR.TYPE, ISBLANK, ISERR, ISERROR, ISEVEN, ISFORMULA, ISLOGICAL, ISNA, ISNONTEXT, ISNUMBER, ISODD, ISREF, ISTEXT, N, NA, SHEET, SHEETS, TYPE |
| Logical | 12 | AND, FALSE, IF, IFERROR, IFNA, IFS, NOT, OR, SWITCH, TRUE, XOR, LET |
| Lookup and reference | 33 | ADDRESS, CHOOSE, CHOOSECOLS, CHOOSEROWS, COLUMN, COLUMNS, DROP, EXPAND, FILTER, FORMULATEXT, HLOOKUP, HSTACK, HYPERLINK, INDEX, INDIRECT, LOOKUP, MATCH, OFFSET, ROW, ROWS, SORT, SORTBY, TAKE, TOCOL, TOROW, TRANSPOSE, UNIQUE, VLOOKUP, VSTACK, WRAPCOLS, WRAPROWS, XLOOKUP, XMATCH |
| Math and trigonometry | 64 | ABS, ACOS, ACOSH, AGGREGATE, ASIN, ASINH, ATAN, ATAN2, ATANH, CEILING, CEILING.MATH, COMBIN, COS, COSH, COT, COTH, CSC, CSCH, DEGREES, EVEN, EXP, FACT, FLOOR, FLOOR.MATH, GCD, INT, LCM, LN, LOG, LOG10, MDETERM, MINVERSE, MMULT, MOD, MROUND, MUNIT, ODD, PI, POWER, PRODUCT, QUOTIENT, RADIANS, RAND, RANDARRAY, RANDBETWEEN, ROUND, ROUNDDOWN, ROUNDUP, SEC, SECH, SEQUENCE, SIGN, SIN, SINH, SQRT, SUBTOTAL, SUM, SUMIF, SUMIFS, SUMPRODUCT, SUMSQ, TAN, TANH, TRUNC |
| Statistical | 67 | AVERAGE, AVERAGEA, AVERAGEIF, AVERAGEIFS, BINOM.DIST, CHISQ.DIST.RT, CHISQ.INV.RT, CONFIDENCE.NORM, CONFIDENCE.T, CORREL, COUNT, COUNTA, COUNTBLANK, COUNTIF, COUNTIFS, COVARIANCE.P, COVARIANCE.S, DEVSQ, EXPON.DIST, FORECAST, FORECAST.LINEAR, FREQUENCY, GAMMALN, GEOMEAN, GROWTH, INTERCEPT, LARGE, LINEST, LOGEST, LOGNORM.DIST, LOGNORM.INV, MAX, MAXA, MAXIFS, MEDIAN, MIN, MINA, MINIFS, MODE.SNGL, NORM.DIST, NORM.INV, NORM.S.DIST, NORM.S.INV, PERCENTILE.EXC, PERCENTILE.INC, PERCENTRANK.INC, POISSON.DIST, QUARTILE.INC, RANK.AVG, RANK.EQ, RSQ, SLOPE, SMALL, STANDARDIZE, STDEV.P, STDEV.S, STEYX, T.DIST, T.DIST.2T, T.DIST.RT, T.INV, T.INV.2T, T.TEST, TREND, VAR.P, VAR.S, WEIBULL.DIST |
| Text | 36 | ARRAYTOTEXT, CHAR, CLEAN, CODE, CONCAT, CONCATENATE, DOLLAR, EXACT, FIND, FIXED, LEFT, LEN, LOWER, MID, NUMBERVALUE, PROPER, REGEXEXTRACT, REGEXREPLACE, REGEXTEST, REPLACE, REPT, RIGHT, SEARCH, SUBSTITUTE, T, TEXT, TEXTAFTER, TEXTBEFORE, TEXTJOIN, TEXTSPLIT, TRIM, UNICHAR, UNICODE, UPPER, VALUE, VALUETOTEXT |
Holarch's own functions: HOLARCH.VALUE and HOLARCH.COUNT (see Model data).
Differences from Excel
- RAND, RANDBETWEEN, RANDARRAY: the same distribution, different values.
- NOW, TODAY: your computer's clock and time zone. Through the API, the request's time zone (UTC by default).
- CELL:
"filename"gives the spreadsheet and sheet name, not a file path. The other info types match. - HYPERLINK: the value matches; the link opens only http, https and mailto addresses.
- REGEXTEST, REGEXEXTRACT, REGEXREPLACE: patterns run on a linear-time engine, so no pattern can slow the sheet down. Patterns that need backtracking are
#VALUE!, and the formula bar names the construct: look-ahead and look-behind ((?=,(?!,(?<=,(?<!), back-references (\1,\k<name>), atomic groups(?>…), possessive quantifiers (a++), recursion(?R),\K, conditionals and verbs such as(*SKIP). A repetition of a repetition, such as(a+)+, is#VALUE!too (Excel gives up on it for texts that do not match); writea+instead. An invalid pattern is#VALUE!, as in Excel.\d,\w,\sand\bfollow Unicode, so\dalso matches digits of other scripts. - DATEVALUE, VALUE: text in formulas is read with US English rules (
"12/31/2026"), as stored in Excel files. Typed cell entries follow your browser's language. - Dates: the 1900 date system keeps Excel's leap-year quirk (29 February 1900 exists); the 1904 system is a workbook setting read from Excel files.
- Circular references: with iterative calculation on (imported from Excel), the order of steps can differ from Excel's in the last digits before the result settles.
Functions that keep Excel's value
Some functions depend on services or programs outside the workbook. Holarch never makes a network call from a formula. In an imported workbook these cells keep Excel's last value, with a marker:
- WEBSERVICE, FILTERXML, RTD, STOCKHISTORY, FIELDVALUE, the CUBE functions (CUBEKPIMEMBER, CUBEMEMBER, CUBEMEMBERPROPERTY, CUBERANKEDMEMBER, CUBESET, CUBESETCOUNT, CUBEVALUE), PY, COPILOT, TRANSLATE, DETECTLANGUAGE, IMAGE, EUROCONVERT and GETPIVOTDATA.
- CALL and REGISTER.ID are never run.
The other 150 functions (for example the bond and coupon functions, the Bessel functions, GROUPBY and PIVOTBY) come in later versions. Until then, imported cells that use them keep Excel's value with a marker, and formulas that use those cells calculate normally. Typed into a cell, they give #NAME?.
Excel import and export
Import a workbook
Import an .xlsx or .xlsm workbook with ⌘K / Ctrl+K → Import Excel Workbook as a Spreadsheet… or Import and Export → Import as a Spreadsheet…. If the Import page already holds a workbook, the button reads Import This Workbook as a Spreadsheet. A single Undo removes the whole import.
What comes in:
- every sheet (hidden sheets stay hidden), values, formulas (shared, array and dynamic-array formulas) and Excel's last values;
- number formats, fonts, fills, borders, alignment, column widths, row heights, hidden rows and columns, merged cells and frozen panes;
- defined names, tables, data validation, conditional formatting of the kinds Holarch has, notes and threaded comments (as notes);
- the date system, iterative calculation and precision-as-displayed settings;
- charts of the kinds Holarch draws (column, bar, line, pie, doughnut, scatter), linked to their cells;
- the sheet's filter, as a shared view named Imported filter, so the sheet itself shows every row.
What keeps Excel's value. These cells are marked Value only, not recalculating: a struck-through fx badge at the start of the cell, a note under the formula bar when the cell is selected, the same words in the cell's name for screen readers, and the reason as its tooltip and description. The reasons:
- a formula with a function Holarch does not calculate yet: "Calculated in Excel: XLOOKUP isn't supported yet. The value is Excel's last result." When the cell's inputs change, the marker adds "Inputs changed since Excel calculated this.";
- a formula Holarch cannot read yet: "Calculated in Excel: Holarch cannot read this formula yet. The value is Excel's last result.";
- a formula that uses another workbook: "Value from another workbook (Rates.xlsx). Not updated here." Holarch never opens linked files;
- What-If data table cells.
Formulas that use these values calculate normally and get a lighter marker: "Uses a value from an unsupported function." When a later version adds the function, the cell calculates by itself the next time the spreadsheet opens, and the marker goes.
What is not imported: macros (removed and never run; an export writes .xlsx without them), pivot tables (their output cells stay as values), data connections and query tables (their cells stay as values), slicers, timelines, form controls, images, shapes, embedded objects, sparklines, data bar and icon set rules, conditional formats and validation rules of other kinds, filter conditions Holarch cannot express (colour, icon or date groups), and charts of other kinds (their data stays in the sheet). The summary counts each.
The Import Summary
After the import, the Import Summary shows:
- "Sheets: 4 · Cells: 12,410 · Formulas: 3,872 · Charts: 2";
- the Recalculation check: Holarch calculates every formula and compares it with Excel's value at 15 significant digits, for example "Recalculation check: 3,861 of 3,872 formula cells match Excel (99.7 %)." Cells that keep Excel's value are not compared;
- Not supported yet: each function with its number of cells, for example "XLOOKUP (412 cells)";
- Kept as Excel's values and Not imported;
- Value only, not recalculating: a table of the cells that keep Excel's value, with each cell's address and reason (the first 100; Show Cells steps through all of them). A cell drops off the list once you enter a new value or formula in it, or when a later version calculates it;
- the differences from Excel, with the cell, the formula, Excel's value and Holarch's value.
Buttons:
- Show Cells marks the cells that keep Excel's value and selects the first. Press F8 for the next marked cell and ⇧F8 / Shift+F8 for the previous one ("Marked cell 3 of 12.").
- Export Recalculation Report… saves the differences as a CSV file (cell, formula, Excel's value, Holarch's value; no other cell content). Attach it to a support request when a value looks wrong. Nothing is sent by itself.
- Close.
The summary stays with the spreadsheet: ⋯ → Show Import Summary, or ⌘K / Ctrl+K → Show Import Summary.
Import limits and messages
- At most 50 MB per file on the hosted service (100 MB in the desktop app and self-hosted installations) and 500,000 filled cells per import. The spreadsheet cell limits apply too.
- "The workbook is larger than 50 MB. Remove unused sheets or split the workbook, then import it again."
- "The workbook has n filled cells, more than the 500,000 an import can hold. Remove unused sheets or split the workbook, then import it again."
- "The workbook needs more than 2,097,152 rows and columns in all, usually because formatting or merged cells reach the last row or column. Clear the unused rows and columns or split the workbook, then import it again." The rows and columns of every sheet count, up to the last one that holds a value, a format, a merge or a table, so two sheets that each reach row 1,048,576 fit and a third does not.
- "Old Excel files (.xls) cannot be read. Save the workbook as .xlsx, then import it."
- "The workbook is password-protected. Remove the password in Excel, save it, then import it again."
- "The file is not a valid Excel workbook. Open it in Excel, save it as .xlsx, then import it again."
- "This project is read-only. Ask an owner for edit access, then import the workbook."
Export
On a spreadsheet, use ⋯ in the toolbar, or ⌘K / Ctrl+K:
- Export Spreadsheet to Excel: every sheet as an .xlsx workbook with formulas, Holarch's calculated values, formats, widths, merged cells, frozen panes, names, tables, notes and charts. The filter and sort of the view you are looking at are written as the sheet's filter; other views are not exported. Cells that keep Excel's value are written back with their original formula, so a workbook that goes from Excel to Holarch and back keeps every formula. Model data is written as values with an About this export sheet (see Model data in exports). Charts linked to cells of the same spreadsheet stay linked to those cells in Excel, with their current values; stacked and 100% stacked charts stay stacked. A chart linked to another spreadsheet is written with its current values only.
#SPILL!and#CALC!are saved the way Excel saves them, so Excel shows the same error, and Excel recalculates the workbook when it opens. A project or document marking becomes the page header and footer. - Export Sheet as CSV and Export Sheet as TSV: the values of the current sheet, without formulas. Text that starts with
=,+,-or@is written so that Excel shows it as text.
Charts
Charts on a sheet
- Select the cells, then click Chart ▾ and choose Column, Stacked Column, 100% Stacked Column, Bar, Stacked Bar, 100% Stacked Bar, Line, Pie, Donut or Scatter. Insert Chart in the cell menu inserts a column chart.
- The chart floats over the sheet and reads the cells live.
With a chart selected:
- drag to move it and drag the corner to resize it. The chart goes exactly where you drop it. Hold ⌥ / Alt (Alt on Windows and Linux) while you drag to snap its edges to the nearest cell edges;
- double-click, or press Enter, for Select Data…;
- right-click, or press ⇧F10 / Shift+F10, for Change Chart Type…, Select Data…, Chart Title…, Legend, Data Labels, Format Horizontal Axis…, Format Vertical Axis…, Format Chart Title…, Format Legend…, Format Data Series… (Format Slices… on pie and donut charts), Snap to Cells, Copy for a Whiteboard and Delete Chart.
Snap to Cells in the chart menu makes snapping the default: moved and resized charts line up with cell edges, and ⌥ / Alt then drops a chart freely. Holarch remembers the setting for you (on the hosted service it follows your account to other computers).
A chart is anchored to the cells under its corners. Inserting, deleting or resizing rows and columns before it moves it with its cells; Excel export and import keep its exact position.
Format a chart element
Click a selected chart's axis, title, legend or a series (a slice on pie charts) to select it, or choose one of the Format … items in the chart menu. The Format panel opens at the right of the cells with the settings of that element:
- Axis: minimum and maximum, major and minor units, where the other axis crosses, logarithmic scale, reverse order, axis title and number format.
- Chart title: the title text.
- Legend: its position.
- Data series or slice: its fill color, and Default Color.
Each change is drawn at once and can be undone with a single Undo. Esc or × closes the panel. Axis settings, the crossing point and series colors are kept in Excel export and import.
From the keyboard: Tab to the chart, then the arrow keys move it by a cell, Shift+arrow keys resize it, Enter opens Select Data, Delete removes it, ⇧F10 / Shift+F10 opens its menu and Esc returns to the cells. Each change can be undone with a single Undo.
Stacked charts put the series of a category on top of each other, so the bar shows their total; 100% stacked charts make every bar reach 100 %. Change Chart Type… previews each type with the chart's own data. The Format panel and Select Data work as for whiteboard charts: see the chart sections of Whiteboards.
Charts on whiteboards
- Copy for a Whiteboard, then paste on a board: the chart keeps its link to the cells.
- On a board, Select Data… → Link to Spreadsheet Range… links an existing chart to cells. Choose the spreadsheet, the sheet and the range (type it, or drag over the cells), and what it follows: The whole sheet or one of the sheet's shared views (its filter, sort and hidden columns apply). Only shared views can be followed, so everyone sees the same chart.
- Header row and Label column (Automatic, Yes or No) say whether the range's first row holds the series names and its first column the category labels. Include hidden rows and columns adds what the sheet or view hides.
- Show empty cells as: new charts leave gaps; charts made before this setting draw empty cells as 0 until you change it.
- When the spreadsheet, sheet or range is deleted, the chart keeps its last data and shows Data source missing.
Paste Excel cells on a whiteboard
Copy cells in Excel, Numbers or Google Sheets and paste them on a board. Holarch asks how to paste them:
- Chart (the default): a chart of the copied values. It does not change later.
- Live spreadsheet: a new spreadsheet with the copied cells, shown on the board as a live, read-only table. The table shows the cells as the spreadsheet does, number formats included, and changes when they change. Double-click it, or select it and press Enter, to open the spreadsheet at that range. Resize it to show more or fewer rows and columns; a note such as "+12 more rows" says what is cut off. Copied cells carry values, not formulas, so the new spreadsheet holds values. A live table of an imported spreadsheet shows the same fx badge on cells that keep Excel's value, and a screen reader reads them as "value only, not recalculating". When the spreadsheet or range is deleted, the table shows the cells as pasted and Data source missing. To chart the cells, select them in the spreadsheet and insert a chart there, or paste again and choose Chart.
- Text: the cells as plain text.
Each choice can be undone with a single Undo. See Whiteboards for the board side.
Documents and presentations
- In a document, Insert → Spreadsheet Range… places cells as a Table of cells, as a chart of a type you choose, or as one of the sheet's own charts, with a Caption. Pick the spreadsheet, sheet and range, and optionally a shared view to Follow. The table or chart shows the current values and updates when the cells change; Word and other exports use the values at export time. When the range is deleted, the table keeps its last rows and is marked as missing. See Live content.
- In a presentation, Picture ▾ → Picture of… lists spreadsheets. A spreadsheet shows its first chart, or its first cells when it has none, with the current values.
AI formula help
The ✦ AI ▾ button and the cell menu offer two AI actions. They work with any configured AI provider. Cell contents are sent to the provider as data: the formula, the cells it uses (at most 200, with their addresses and values) and the column headings near the cell.
- Explain Formula: what the active cell's formula computes, in plain words, based on the formula and the ranges it uses. Nothing changes. It needs Questions about the project in the AI permissions (see AI).
- Write a Formula…: describe what the cell should compute ("Total mass of the parts in column C") and click Write Formula. The suggested formula is checked and calculated on the current data, and the preview shows the formula, its result and what it replaces. Click Insert to put it in the cell, and a single Undo takes it all back. A suggestion that cannot be read shows the reason and cannot be inserted. It needs Suggestions and changes in the AI permissions, and edit access.
Collaboration, history and baselines
- Editing together: each cell is saved on its own, so two people editing different cells never collide. When two people change the same cell at once, the last change wins and the other is listed in Conflicts. A value and its format are separate, so one person can format a cell while another types in it.
- Undo: a single Undo takes back one action: an entry, a paste, a fill, an insert or delete, a format change, an import. Undo skips opening a sheet and switching views.
- History: spreadsheet edits are recorded in the project's history like other edits.
- Baselines: spreadsheets are part of every baseline. Compare lists added, removed and changed cells per sheet ("Cell changed", "Format changed", "Sheet added", "Sheet removed"), with formulas as A1 text.
- Configuration control: a locked or approved spreadsheet is read-only, like any locked item.
- View access: viewers and reviewers can open, filter, sort, copy, find, export and save personal views; they cannot change cells.
API and MCP
Scripts and AI clients can work with spreadsheets. See API and MCP for tokens, plans and rate limits.
GET /projects/<project>/spreadsheetslists the spreadsheets with their sheets (id, name, used rows and columns, filled cells).GET /projects/<project>/spreadsheets/<id>/range?ref=B2:D20reads computed values, calculated on the server by the same engine;formulas=trueadds each cell's formula. At most 10,000 cells per read.POST /projects/<project>/spreadsheets/<id>/cellswrites up to 1,000 cells as one change. A formula that cannot be read refuses the whole write and names the cell. The cell limits apply.- MCP tools:
list_spreadsheets,read_rangeandwrite_cells.write_cellscreates a proposal: a person reviews each cell before and after in Holarch and applies it. The MCP tools are on the hosted service and follow the AI permissions.
Keyboard shortcuts
On Windows and Linux, use Ctrl for ⌘ / Ctrl and Alt for ⌥. / Alt+. ⋯ → Keyboard Shortcuts lists them in the app.
| Keys | Action |
|---|---|
| ← ↑ ↓ → | Move one cell |
| ⇧ / Shift+arrow | Extend the selection |
| ⌘ / Ctrl+arrow / ⇧⌘ / Ctrl+Shift+arrow | Move to the edge of the data / extend the selection to it |
| Enter / ⇧Enter / Shift+Enter | Move down / up (inside a selection: the next cell) |
| Tab / ⇧Tab / Shift+Tab | Move right / left (inside a selection: the next cell) |
| Home / ⌘Home / Ctrl+Home / ⌘End / Ctrl+End | Start of the row / first cell / last used cell |
| PageUp / PageDown | Move one screen up / down |
| ⌃Space / Ctrl+Space / ⇧Space / Shift+Space | Select the whole column / row |
| ⌘A / Ctrl+A | Select all cells |
| F2 | Edit the active cell |
| Esc | Cancel the entry |
| ⌥Enter / Alt+Enter | New line inside the cell |
| ⌘Enter / Ctrl+Enter | Put the entry into every selected cell |
| Delete | Clear the selected cells |
| F4 / ⌘T / Ctrl+T while editing | Switch the reference at the cursor between relative and absolute |
| Tab in the function list | Insert the chosen function |
| ⌘; / Ctrl+; / ⇧⌘; / Ctrl+Shift+; | Today's date / the current time |
| ⌃G / Ctrl+G / F5 | Go to a cell or name (Name Box) |
| ⌥← / Alt+← ⌥→ / Alt+→, ⌃PageUp / Ctrl+PageUp ⌃PageDown / Ctrl+PageDown | Previous / next sheet |
| ⌥F1 / Alt+F1 | Read the active cell's formula |
| ⇧F10 / Shift+F10 | Cell menu (also on a sheet tab, a view tab and a chart) |
| F6 / ⇧F6 / Shift+F6 | Next / previous area: toolbar, formula bar, views, sheet, sheet tabs |
| F8 / ⇧F8 / Shift+F8 | Next / previous marked cell |
| ⌘Z / Ctrl+Z / ⇧⌘Z / Ctrl+Shift+Z | Undo / redo |
| ⌘K / Ctrl+K | Command palette |
| ⌘C / Ctrl+C / ⌘X / Ctrl+X / ⌘V / Ctrl+V | Copy, cut, paste |
| ⌃⌘V / Ctrl+V | Paste Special |
| ⌘D / Ctrl+D / ⌘R / Ctrl+R | Fill down / right |
| ⌥ / Alt while dragging the fill handle | Switch between copying and a series |
| ⌘+ / Ctrl++ / ⌘− / Ctrl− | Insert / delete cells, rows or columns |
| ⌘B / Ctrl+B / ⌘I / Ctrl+I / ⌘U / Ctrl+U | Bold / italic / underline |
| ⇧⌘X / Ctrl+Shift+X (Windows and Linux: Ctrl+5) | Strikethrough |
| ⌘1 / Ctrl+1 | Format Cells |
| ⇧⌘~ / Ctrl+Shift~ | General format |
| ⇧⌘! / Ctrl+Shift! | Number with thousands separator and two decimals |
| ⇧⌘$ / Ctrl+Shift$ | Currency |
| ⇧⌘% / Ctrl+Shift% | Percentage |
| ⇧⌘^ / Ctrl+Shift^ | Scientific |
| ⇧⌘# / Ctrl+Shift# | Date (d-mmm-yy) |
| ⇧⌘@ / Ctrl+Shift@ | Time (h:mm AM/PM) |
| ⇧⌘F / Ctrl+Shift+F (Windows and Linux: Ctrl+Shift+L) | Filter buttons on or off |
| ⌥↓ / Alt+↓ | Open the filter or the validation list of the cell |
| ⌘T / Ctrl+T / ⌃L / Ctrl+L | Format as Table |
| ⇧F2 / Shift+F2 | Add or edit the note of the cell |
| ⌘F / Ctrl+F | Find |
| ⇧⌘H / Ctrl+Shift+H (Windows and Linux: Ctrl+H) | Replace |
| Enter / ⇧Enter / Shift+Enter in Find | Next / previous match |
Command palette (⌘K / Ctrl+K) commands: New Spreadsheet… and Import Excel Workbook as a Spreadsheet… anywhere in a project; in the editor also Go To Cell or Name…, Add Sheet, Freeze Top Row, Unfreeze Panes, New View…, Save View (with unsaved changes), Recalculate, Keyboard Shortcuts, Insert Model Table, Export Spreadsheet to Excel, Export Sheet as CSV, Export Sheet as TSV and Show Import Summary. Spreadsheets also open by name from the palette.
Accessibility
- Grid roles: the sheet is an ARIA grid with the row and column count of the whole sheet, so a screen reader reports a cell's real row and column even though only the visible cells are drawn. Headers are column and row headers, the selection is marked selected, and read-only sheets and model tables are marked read-only.
- Cell names: moving to a cell reads its address, column heading, value and state, for example "C4, Mass, 12.5, formula". ⌥F1 / Alt+F1 reads the formula. Notes, highlight rules, validation lists, filter buttons and import markers are read with the cell. A cell that keeps Excel's value reads "Value only, not recalculating" in its name and the reason in its description.
- Announcements: a polite live region reads selection sizes ("3 rows by 2 columns selected, Sum 42.5"), pastes, fills, sorts, filters, view changes, find results and errors.
- Keyboard only: everything the mouse does has a keyboard path: fill (⌘D / Ctrl+D, ⌘R / Ctrl+R, Fill ▾ → Series…), sizes (Row Height…, Column Width…), the menus (⇧F10 / Shift+F10 on cells, sheet tabs, view tabs and charts), range picking in dialogs (type the range), chart insertion, moving and resizing (arrow keys), and the areas of the page (F6). One cell at a time is in the Tab order; Tab inside the grid moves between cells, and F6 leaves it.
- Focus: the active cell has a 2 px outline and its row and column headers are highlighted, not colour alone.
Limits
| Limit | Hosted service | Desktop app and self-hosted |
|---|---|---|
| Rows × columns per sheet | 1,048,576 × 16,384 (as Excel) | the same |
| Filled cells per spreadsheet | 100,000 | 1,000,000 |
| Filled cells in all spreadsheets of a project | 250,000 | no limit |
| Excel file size for an import | 50 MB | 100 MB |
| Filled cells in one import | 500,000 | 500,000 |
| Rows plus columns in one import (all sheets) | 2,097,152 | 2,097,152 |
The cell limits are the same on every plan. Formats, notes and empty rows do not count as filled cells.
- An edit that would go over a limit is refused with the reason: "This spreadsheet would have more than 100,000 filled cells, the limit per spreadsheet. Clear cells you no longer need, or split the data into another spreadsheet." or "The spreadsheets in this project would have more than 250,000 filled cells, the limit per project. Clear cells you no longer need, or move a spreadsheet to another project."
- A spreadsheet already above a limit stays editable, and clearing cells always works.
- Formula results: at most 1,000,000 cells per formula (one whole column, 1,048,576 cells, is allowed); a larger result is
#NUM!. - Other limits: 200,000 cells per copy, paste or fill; 5,000 rows and 40 columns per model table; 100 views per spreadsheet and 8 sort levels per view; 10,000 cells per API read and 1,000 per API write.
Messages
| Message | What to do |
|---|---|
| "You can view this spreadsheet but not change it." | You have view access. Ask an owner for edit access. |
| "The spreadsheet could not be opened. Reload the page to try again." | Reload the page. |
| "The spreadsheet engine stopped and restarted. Values were calculated again." | No action needed. |
| "Nothing to sort. Add rows below the header row first." | Add data rows below the header. |
| "Nothing to filter. Add a header row and data first." | Add a header row and data. |
| "The header row is never filtered. Select a cell below it." | Select a data cell. |
| "The range overlaps table name. Choose cells outside it." | Pick another range for the new table. |
| "A table or defined name already uses this name. Enter another name." | Choose another table name. |
| "Cut works on one block of cells. Select a single range, then cut." | Select one range. |
| "The pasted cells do not fit on the sheet. Select a cell further up or left." | Paste further up or left. |
| "Select at most 200,000 cells for this." | Work on a smaller selection. |
| "No room for more rows. Delete rows at the end of the sheet first." | Delete rows at the end of the sheet. |
| "Shared views can be made by editors of this project." | Save a personal view instead. |
| "The view was not saved. Check the connection and try again." | Check the connection and save again. |
| "No cells are marked. Use Show Cells in the import report to mark them." | Open the Import Summary and click Show Cells. |
| "This cell cannot be empty." | The validation rule does not allow empty cells. |
| "Choose a value from the list." | The cell's validation list does not hold the entry. |
| "Model table name can't be shown: cells in its way hold data. Clear those cells, or move the table." | Clear the cells under the table. |
Related pages
- Whiteboards: linked charts and live tables.
- Documents: spreadsheet ranges in documents.
- Presentations: pictures of spreadsheets.
- Import and export: other Excel imports (requirements, tables).
- Model Database: queries and saved views used by model tables.
- AI: AI providers and permissions.
- API and MCP: the spreadsheet routes and tools.
- Attachments, Storage and Limits: plan limits.
Microsoft Excel is a trademark of Microsoft Corporation.