Sheets, rows and tables

DataMind Oda changes the structure of the open workbook when you ask: its sheets, rows and columns, frozen panes, Excel tables and page setup. This page covers what each step does, what it asks in each permission mode and how to get back what it changed. For the cells themselves, see How Oda works in your workbook; for how the modes work, see Permission modes and approvals in Excel.

Important

Revert turn cannot undo the changes on this page: it refuses a turn that made one of them and changes nothing. Excel cannot undo deleting a sheet either. For a change you may want back, tick the snapshot box on the approval card, or ask Oda to change it back; see Review and undo Oda's changes.

Sheets

Oda adds, renames, copies, moves, hides, shows and deletes sheets, colours or uncolours their tabs, protects and unprotects them, and switches to a sheet. The step row reads Working on sheets and the sheet, then, for example, Added the sheet Forecast, Renamed Sheet1 to Inputs, Moved Summary to tab position 1 or Deleted the sheet Old.

A new sheet is empty and goes after the last tab, unless you name a place: a tab position counted from 1, the start, the end, or after a sheet you name. A copy of a sheet keeps its contents, formats and column widths, and goes after the sheet it copies unless you name a place.
A sheet name has up to 31 characters, none of \ / ? * [ ] :, does not start or end with an apostrophe, and is not History, which Excel keeps for itself. Oda also gives no sheet a name that ends like a file name, such as .xlsx or .csv, which formulas could read as another workbook.
Excel keeps at least one sheet visible, so Oda does not hide or delete the only visible sheet. Oda switches only to a visible sheet, and leaves very hidden sheets, which only the VBA editor sets, as they are.
Oda protects a sheet without a password, and never asks for, sets or types one. A sheet protected with a password stays protected: Oda asks you to unprotect it in Excel, under Review > Unprotect Sheet. Oda unprotects a sheet only when you ask.
Unprotecting asks first in every mode except Skip all permissions, on a card titled Allow Oda to remove a sheet’s protection? with the line Remove the protection of the sheet “Sales”, so anyone can change it.
Deleting a sheet asks first in every mode except Skip all permissions. The card says what goes, as in Delete the sheet “Q1” and everything on it (A1:H40 in use); formulas that refer to it show #REF!. Excel cannot undo this, and counts the charts, shapes and slicers on it. Its snapshot box starts ticked in every mode, because the copy is the only way back. If what the sheet holds changes while the card waits, nothing is deleted.
Where no card shows, as in Skip all permissions, Oda copies a sheet that holds data to a snapshot sheet before it deletes it.
After Oda moves a sheet, the cells it wrote there count as yours.

Rows and columns

Oda inserts and deletes rows, columns or cells, hides and shows rows or columns, groups and ungroups them under an outline button, sets their height or width, and fits them to their contents. The step row reads Changing rows and columns and the address, then, for example, Inserted Sales!5:7, Deleted Sales!C:C, Grouped Sales!10:20 or Fitted Sales!A:D to the contents.

Inserting whole rows moves the rows below down, as in Insert 3 rows at Sales!5:7; the rows below move down, and inserting columns moves the columns to the right. Oda can also insert or delete only some cells, moving the cells around them down or right for an insert, and up or left for a delete.
A formula whose range ends just above or just left of inserted rows or columns does not grow to include them. Oda checks the totals next to what it inserted.
An insert of cells that moves your data out of line with the data beside it asks first in Accept edits, unless Oda wrote those cells in this chat, as in Insert cells at Sales!B4:B6; the cells below move down, so 12 cells with data move out of line with the data beside them.
Deleting removes what the cells hold, and formulas that refer to them show #REF!. It asks first in every mode except Skip all permissions, with a line such as Delete 3 rows Sales!5:7; the rows below move up; 18 of these cells hold data and a preview of the first 20 cells that hold data. One delete covers at most 1,000,000 cells in use.
Charts and shapes over rows or columns that Oda deletes, hides or groups shrink or hide with them; Oda checks which ones sit there.
To tuck rows or columns away, Oda groups them unless you ask to hide them, so you can open them again from the outline button.
After Oda inserts or deletes rows, columns or cells, the cells it wrote on that sheet count as yours, and Revert turn cannot undo earlier turns on that sheet either.

Freeze panes

Oda freezes the top rows and the first columns of a sheet, so they stay in view while you scroll, or unfreezes its panes. The step row reads Freezing panes, then, for example, Froze 1 row, Froze 1 row and 2 columns or Unfroze the panes. The card line reads, for example, Freeze the top 1 row and the first 2 columns of “Sales”.

Tables

Oda creates Excel tables and changes them: it renames them, resizes them, changes their style and banded rows, adds, changes or removes the totals row, adds or deletes a column, adds rows at the end, turns a table into a plain range and deletes a table. The step row reads Working on a table, then, for example, Table Sales on Sales!A1:D120, The table Sales now covers Sales!A1:F200, Added 12 rows to Sales or Turned Sales into a plain range. In chats from before these steps, the row can read Creating a table.

A new table gets filters and banded rows, and its name works in structured references such as =SUM(Sales[Amount]). The first row of the range holds the column names unless you say otherwise. Excel needs distinct text names, so it turns number or formula headers into plain text and renames repeated ones; the approval card lists those header cells.
A range without column names gets a new header row: Excel adds it above the data and moves the data down one row.
A table name starts with a letter or an underscore, uses only letters, digits, underscores and full stops, has no spaces, and does not look like a cell address such as A1 or R1C1.
A style is a table style such as TableStyleMedium2, or one of your own in the workbook.
The totals row sums, averages, counts, or finds the smallest or largest value of each column you name. Its formulas use SUBTOTAL, which leaves out the rows a filter hides. A new totals row goes in the row below the table; when that row holds data, adding it to a new table is refused, and adding it to an existing table asks first in Accept edits, unless Oda wrote those cells in this chat.
A new column can get one formula for every row, such as =[@Profit]/[@Revenue], which makes it a calculated column. Its header cannot start with =, +, - or @.
New rows go at the end of the table and move the cells below it down. One step adds at most 50,000 cells of rows, and text that starts with = goes in as a formula.
A resize keeps the header row in its row and must overlap the table. It brings at most 1,000,000 new cells into the table, and asks first in Accept edits when it takes in your data, as in Resize the table Sales to Sales!A1:F200, taking in 40 cells that hold data.
Adding rows or a column that moves your data out of line with the data beside it asks first in Accept edits, unless Oda wrote those cells in this chat.
Replacing totals that hold content asks first in Accept edits. Emptying a totals cell, removing a totals row that holds content, deleting a column and deleting the table ask first in every mode except Skip all permissions. Deleting a table removes its data too, as in Delete the table Sales and the data in Sales!A1:D120; a table of more than 1,000,000 cells is deleted in Excel.
Turning a table into a plain range keeps its data and formatting.
After a resize, added rows or columns, a deleted column, a new totals row, or a table created without column names or with a totals row, the cells Oda wrote on that sheet count as yours.

To sort or filter a table, or to remove its duplicate rows, see Sort, filter, rules and comments.

Page setup and printing

Oda sets how a sheet prints and shows. The step row reads Setting up the page, then Changed the page setup of Sales, and the card names each setting, as in Change the page setup of “Sales”: landscape orientation, fit to at most 1 page wide, any number of pages tall.

Print area: the range to print, or none to print everything. If Excel keeps the print area, Oda asks you to clear it in Page Layout > Print Area > Clear Print Area.
Orientation, paper and scale: portrait or landscape, a paper size such as A4, Letter or Legal, and either a number of pages wide and tall to fit the printout to, or a scale from 10% to 400%.
Margins and centring: margins in points, where 72 points make 1 inch, and centring on the page.
Header and footer: text in the left, centre and right sections, up to 255 characters each. Excel codes such as &P for the page number and &N for the number of pages work.
Print titles and page breaks: rows or columns printed on every page, and up to 100 manual page breaks at rows and 100 at columns in one step, after removing the old ones if you ask.
Gridlines and headings: printed gridlines, and gridlines and row and column headings on screen.

Settings you do not name stay as they are. Printing can look different in Excel on Windows, on a Mac and on the web.

What each permission mode asks

Change	Ask before edits	Accept edits	Skip all permissions
Adding, renaming, copying, moving, hiding or showing a sheet, colouring its tab, protecting it, switching to it	Asks	Runs	Runs
Inserting rows or columns, hiding, grouping or sizing them, freezing panes, page setup	Asks	Runs	Runs
Creating a table whose first row holds distinct text names, renaming or styling a table, turning it into a range, adding its totals row, rows or a column where that moves none of your data	Asks	Runs	Runs
Inserting cells, table rows or a column that moves your data out of line, resizing a table over your data, a totals row over your data, replacing totals, a table without column names	Asks	Asks	Runs
Deleting sheets, rows, columns, cells, tables or table columns, emptying totals, unprotecting a sheet	Asks	Asks	Runs

Cards for changes that move or remove cells, and for unprotecting a sheet, offer no Always allow in this chat.

Questions, answered

Can Oda insert rows or columns?

Yes. Ask, for example, Insert two rows above the totals. In Accept edits it inserts whole rows or columns without a card; an insert of cells that moves your data out of line with the data beside it asks first.

Can I get a sheet back after Oda deleted it?

Only from a snapshot sheet. Excel cannot undo deleting a sheet, and Revert turn cannot either. The approval card for a delete starts with its snapshot box ticked; keep it ticked for a sheet you may need.

Why will Oda not unprotect my sheet?

The sheet is protected with a password, and Oda never asks for or types passwords. Unprotect it in Excel under Review > Unprotect Sheet, then ask again.

Does Oda set a password when it protects a sheet?

No. Oda protects a sheet without a password, so you can unprotect it in Excel at any time.

Why will Oda not hide or delete this sheet?

It is the only visible sheet, and Excel keeps at least one sheet visible. Show another sheet first.

Why does a total not include the rows Oda inserted?

A formula whose range ends just above inserted rows does not grow to include them. Oda checks the totals next to an insert; if one still misses the new rows, ask Oda to extend its range.

What happens to my data when Oda makes a table without column names?

Excel adds a header row above the data and moves the data down one row. Because this moves your cells, Oda asks first unless you chose Skip all permissions.

Why was the totals row refused?

The row below the table holds data, which the totals row would take. Make room below the table, or leave the totals row out.

Why does Oda ask before resizing my table?

The new range takes in cells that hold your data. In Accept edits Oda resizes without a card only when the new cells are empty or hold what Oda wrote in this chat.

Does Oda hide rows or group them?

It groups them under an outline button unless you ask to hide them, so you can open them again with one click.