Table Commands

Monday, February 20, 2023

7:12 AM

# Add, Delete, Highlight, and Recalculate Formula

OneNote lets you insert an Excel table on a page or convert a table to an Excel object. This is convenient if you want complicated formulas and charts... and you have Excel installed! But what if you don't?

OneMore adds Excel-like formula functionality to native OneNote tables. Thanks to the work done by Jonathan Wood, who created a fantastic expression parser and evaluator, you can apply a formula to one or more selected cells in a table. A formula can consist of basic mathematical operators, parenthesis, and most math functions such as abs, sum, average, sin, etc.

Formula Dialog |

- Select the cell or cells into which you want to add a formula; they must be linear and contiguous, meaning in the same row or in the same column.
- Select the Add Formula command, F5. The Formula dialog is displayed showing the names of the selected cells: A1, A2, A3, etc.
- Enter the formula. As you type, OneMore validates the syntax in real-time, showing whether the formula is valid or invalid. Only when it is valid will the OK button be enabled.
- Choose the format of the result: Number, Currency, or Percentage.
- Choose whether you want to tag the cell with a lightning bolt to make it easy to identify cells with formulas in them.
- Click OK.

Formula processing is not recursive. This means that if cell A1 has a formula "A2+1" and cell A2 has a formula "1+1", then when A1 is calculated, it will not force A2 to be recalculated. Instead, each cell is calculated in order, top-down and left-to-right across the table.

The Recalculate command, Shift + F5, will recalculate all formulas in the selected table(s).

The Highlight command will select all cells on the page containing formulas so you know where you put them.

The Delete command will remove a formula from the selected cell(s) but retain the values displayed in those cell.

### Dynamic Cell Ranges for Individual Columns

Typically, you'll add rows to or remove rows from a table to manage your data, as opposed to adding or removing columns. Changing rows of the table means absolute cell ranges like A1:A9, with the result cell being A10, will ignore a new tenth row if added above the result cell. Dynamic cell ranges can be used in a single column. For example, given a table with 10 rows, you can enter a formula range such as A1:A-1 in the result cell of a column to indicate "start at cell A1 and references all cells up to the result cell's row number minus 1.

- Dynamic ranges can only be used in ranges describing contiguous cells in a single column.
- The dynamic range syntax, like A-1, can only be used in the second part of the range, such as A1:A-1
- Only negative offsets can be used and must be greater than zero. This means you can use offsets such A-2, A-3, etc.
- The result cell does not need to be the last row in your table but you can only reference negative offsets.
- Dynamic ranges are not allowed when you've selected multiple cells across columns of a row, as described below.

### Auto-Incremented Column References

If you use cell references in your formula and you've selected more than one cell then OneMore will automatically increment the references relative to each seleted cell. For example, if you select cells A10, B10, and C10 and enter the formula sum(A1:A9) then that will apply to A10, sum(B1:B9) will apply to B10, and sum(C1:C9) will apply to C10.

## Functions

Available functions include abs, acos, asin, atan, atan2, average, ceiling, cos, cosh, exp, floor, log, log10, max, median, min, mode, pow, range, round, sign, sin, sinh, sqrt, stdev, sum, tan, tanh, trunc, and variance.

Additionally, the following special functions are included.

### countif(range, criteria)

Counts the number of values in the given range that match the specified criteria. The range can be a simple math formula such as 123 or 22.5 * pi or it could be a cell range such as a2:a7. The criteria can be a numeric value such as 123 or 45.66; it can be a boolean, true or false, in which case it will look for checkbox tags in each cell and will ignore all other content in the cells; or it can be a simple string match such as abc where quotes are not allowed. Additionally, three operators are allowed for numeric and string matches: <, >, and !, again quotes are not allowed.

────────────────────────────────────────────────────────────────────────────────────────────────────

# Copy Across, Copy Down, Fill Across, and Fill Down

The Copy Across and Copy Down commands will duplicate the contents of the lead cells in the selection. For example, if you select one or more cells in the first row and choose Copy Down, the cell values, style, and shading in that row will be copied down the entire table. Restrict the scope by selecting the first cell to copy and subsequent cells to fill, either vertically or horizontally as appropriate.

The Fill Across and Fill Down commands will increment the values in the lead cells in the selection. For example, if the selected cell contains a number, that number will be incremented and added to all subsequent cells. If the second cell is populated then it will compare the second and first cell, determine the difference, and use that to increment the value. The Fill commands work for:

- Numbers including integers, decimals, and currencies, incrementing by 1 or the integer difference of the first and second cell
- Counting Strings such as "Person 1" will be incremented to populated "Person 2", "Person 3", "Person 4", etc.
- Dates of various forms will be recognized and increment by adding 1 day or the different in days between the first and second cell
- Date formats include these culture-specific formats (they change per culture)
- M/d/YYYY
- MMMM d
- dddd, MMM d, yyyy

- And these explicit formats
- d-MMM-yyyy
- MMM d, yyyy
- MMM d

- Date formats include these culture-specific formats (they change per culture)

# Convert Text to Table

Converts selected text to a table. Text must be delimited by a comma, space, or other special character so the command can detect columns.

# Insert Table Cells

Adds the ability to insert cells into a table, shifting existing content down or to the right. This is similar to the Excel functionality with one enhancement - if you select a rectangular region of cells then it will shift just those cells, possibly overwriting other cells. If you select cells from one column or cells from one row then it will insert cells above or to the left and add rows or columns as needed to make room for the new cells.

Note that if you move cells with formulas, the cell references in those formulas are not updated automatically by this command; you'll need to adjust those manually.

# Paste Table Cells

Paste copied cells into a table, overlaying cells rather than inserting a nested table as OneNote does by default. The target table is expanded with extra rows or columns as needed. All cell formatting, including cell shading, is preserved. This is useful for moving cells around within a table or copying cells from one table to another.

When copying cells with Ctrl-C and pasting back into the same table, the old cells are not erased. If your intention is to move the cells, leaving blank cells behind, then instead use Ctrl-X to copy and cut prior to running this paste command.

Note, you may notice the OneNote windows flashing once when this command is run. This is because OneMore needs to use a temporary page to do some of its work and you're seeing it navigate to that page and then back to your current page.

# Split Table

Splits the current table starting at the row containing the input cursor. Optionally, the header can be duplicated in the new table

and columns in both can be fixed to their current widths so the two tables remain aligned.

#omwiki #omcommands

© 2020 Steven M Cohn. All rights reserved.

Please consider a sponsorship or one-time donation to support ongoing development

Created with OneNote.