Edit Data
Move Data
The alt key is used for data moving operations. You can move individual cells, entire rows and columns, and even the entire table.
Individual cells
alt+arrow or Move Cell Contents [Up|Down|Left|Right]
To drag and drop cells with the mouse, shift+click on the selected cells and drag them wherever you want.
Move Cell Contents To ...
Use this command to type in the location you want to move the selected data to.
Rows or columns
alt+shift+arrow or Move [Row(s)|Column(s)] [Up|Down|Left|Right]
To drag and drop rows or columns with the mouse, shift+click on the row/column labels and drag them wherever you want.
Delete Data
You can do the following:
- Clear cell contents
- Remove rows and columns
- Remove duplicate rows and columns
- Remove empty rows and columns
Clear cell contents
delete or backspace or Clear Cell Contents
Remove rows and columns
Rows: ctrl+shift+k or Remove Row(s)
Columns: ctrl+shift+l or Remove Column(s)
Rows: cmd+shift+k or Remove Row(s)
Columns: cmd+shift+l or Remove Column(s)
To clear a row or cell of its contents without removing it, select it and use delete or backspace.
Remove duplicate rows and columns
Rows: Remove Duplicate Rows and Remove Duplicate Rows (Based on Selected Columns Only)
Columns: Remove Duplicate Columns and Remove Duplicate Columns (Based on Selected Rows Only)
The Remove Duplicate Rows (Based on Selected Columns Only) doesn't require the entire row to be the same, only in the selected columns. This is useful if, for instance, every row has a unique ID and two rows are exactly the same except the ID. You could select every column except the ID column call the command (the Invert Selection by Columns (Header Excluded) command may help with that). The Remove Duplicate Columns (Based on Selected Rows Only) command is the column equivalent.
Remove empty rows and columns
Rows: Remove Empty Rows and Remove Empty Rows (Based on Selected Columns Only)
Columns: Remove Empty Columns and Remove Empty Columns (Based on Selected Rows Only)
The Remove Empty Rows (Based on Selected Columns Only) command only requires the selected columns in a given row to be empty for its deletion. Remove Empty Columns (Based on Selected Rows Only) is the column equivalent.
Duplicate Data
The data duplication commands all use ctrl+alt in their shortcuts.
Duplicate rows and columns
Rows: ctrl+alt+r, Duplicate Row(s), or Duplicate Row(s) Multiple Times
Columns: ctrl+alt+c, Duplicate Column(s), or Duplicate Column(s) Multiple Times
Rows: cmd+alt+r, Duplicate Row(s), or Duplicate Row(s) Multiple Times
Columns: cmd+alt+c, Duplicate Column(s), or Duplicate Column(s) Multiple Times
Duplicate individual cells
ctrl+alt+arrows or Duplicate Selected Cell Contents [Up|Down|Left|Right]
cmd+alt+arrows or Duplicate Selected Cell Contents [Up|Down|Left|Right]
You can also duplicate cells with the mouse. Press the middle mouse button down over a cell and move the mouse in the direction you want.
Use this command to type in the location you want to duplicate cells to.
Insert Data
Rows and Columns
Rows: alt+r or Insert Row(s)
Columns: alt+c or Insert Column(s)
Cells
Push the rest of the row to the right: Insert Cells, Move Rest of Row Right
Push the rest of the column down: Insert Cells, Move Rest of Column Down
Find and Replace Data
Find window: ctrl+f or Find
Replace window: ctrl+h or Replace
Find window: cmd+f or Find
Replace window: cmd+h or Replace
esc closes both of them.
The option buttons are at the top. Here they are from left to right:
| Button | Option | Keyboard Shortcut |
|---|---|---|
 |
Case Sensitive | alt+a |
 |
Regular Expressions | alt+/ |
 |
Match Whole Word | alt+w |
 |
Match Whole Cell | alt+e |
 |
Match Only in Selected Cells | alt+s |
 |
Highlight Matches | alt+h |
 |
Preview Replace | alt+p |
If you open Find/Replace while multiple cells are selected, the Match Only in Selected Cells option is automatically selected.
Join and Split Cells
Join (Concatenate) Cells
Join Selected Cells Horizontally
Join Selected Cells Vertically
You have to pick a delimiter. You are not limited to a single character.
Split Cells
Split Selected Cells Horizontally
Split Selected Cells Vertically
You can split by delimiter and it can be multiple characters.
You can also split by index. It accepts multiple index values for multiple splits and it can split from the right side with negative numbers. Example:
Input = 2 4 -3
Lorem Ipsum -> Lo | re | m Ip | sum
Date and Time
Convert Date/Time Format
You must specify the current date/time format ("From") and the new date/time format ("To").
Convert Date/Time Format
Date/Time Format Options
For both the "From" and the "To" part, there are several options:
| Option | Example | Notes |
|---|---|---|
| Enter Custom Format | MMM. d, yyyy h:m ap -> Apr. 10, 2020 8:57 AM | Syntax is described in the program and below. |
| ISO 8601 Format | 2020-04-10T08:57:02Z | For "From", T and Z are optional and milliseconds are allowed. For "To", the format will be the same as the example. |
| ISO 8601 Format (date only) | 2020-04-10 | |
| Unix Epoch Time (seconds) | 1586509063 | Number of seconds since January 1, 1970, 00:00:00 UTC (excluding leap seconds). |
| Unix Epoch Time (milliseconds) | 1586509063578 | Number of milliseconds since January 1, 1970, 00:00:00 UTC (excluding leap seconds). |
| Excel Format | 43931.3729166667 | Number of days since January 1, 1900. |
Date/Time Custom Format Syntax
The following instructions are shown in the program. Example date/time is April 4, 2020, 5:03:09.372 AM.
| Interval | Syntax |
|---|---|
| Year | yy (20), yyyy (2020) |
| Month | M (4), MM (04), MMM (Apr), MMMM (April) |
| Day | d (8), dd (08), ddd (Wed), dddd (Wednesday) |
| Hour | h (5), hh (05), H (5, 24 hour clock only), HH (05, 24 hour clock only) |
| Minute | m (3), mm (03) |
| Second | s (9), ss (09) |
| Millisecond | z or zzz (372) |
| AM/PM | AP or A (AM), ap or a (am) |
Use double quotes for text you don't want parsed. Escape character is \.
Example: yyyy-MM-dd h:mm:ss.zzz AP -> 2020-04-08 5:03:09.372 AM
Transposition
With the transpose commands, you can rotate entire tables or individual ranges so rows become columns and columns become rows.
Entire Tables
ctrl+t or Tranpose Table
cmd+t or Tranpose Table
Individual Ranges
Transpose Selected Cells
Case Conversion
UPPER CASE: ctrl+k,ctrl+u or Upper Case
lower case: ctrl+k,ctrl+l or Lower Case
Title Case: Title Case
UpperCamelCase: Upper Camel Case
lowerCamelCase: Lower Camel Case
UPPER_UNDERSCORE_CASE: Upper Underscore Case
lower_underscore_case: Lower Underscore Case
UPPER CASE: cmd+shift+u or Upper Case
lower case: cmd+shift+l or Lower Case
Title Case: Title Case
UpperCamelCase: Upper Camel Case
lowerCamelCase: Lower Camel Case
UPPER_UNDERSCORE_CASE: Upper Underscore Case
lower_underscore_case: Lower Underscore Case
Reshaping
Occasionally, you may wind up with a table where the data is "wrapped" incorrectly. For instance, sometimes if you copy and paste a table from a PDF, all the data will be pasted into a single column. The reshape commands will arrange it in the correct shape without having to manually move cells.
Reshape Selected Cells Row-Wise
Reshape Selected Cells Column-Wise
Reshaping row-wise vs. column-wise
If you have the following data:
And you reshape row-wise with 3 columns, you get this:
If you reshape column-wise with 3 rows, you get this:
Handling Zeros
There are two commands to handle zeros:
Zero-fill whole numbers
Zero-Fill Whole Numbers
Remove leading zeros
Remove Leading Zeros
This will leave a single zero if it's just a bunch of zeros (e.g. 00000 -> 0) or if it's a decimal number with a bunch of leading zeros before the decimal (e.g. 00000.23519 -> 0.23519).
Random Numbers
There are two commands to enter random numbers.
Random integers
Random Integers in Selected Cells
Press enter for 0 to 100.
Type a single number for the range of 0 to that number.
Range: Separate min and max with -. Example: 1-5
Individual Values: Separate with a comma or space. Example: 1,3,5 or 1 3 5
You can mix ranges and individual values. Example: 1-5,7,9,11
You can also have multiple ranges. Example: 1-5, 15-20
The numbers are decided according to a uniform distribution.
Random decimals (floats)
Random Decimals (Floats) in Selected Cells
Press enter for 0.0 to 10.0.
Type a single number for the range of 0.0 to that number.
Range: Separate min and max with -. Example: 1.2-5
Individual Values: Separate with a comma or space. Example: 1.2,3.6,5 or 1.2 3.6 5
You can have multiple ranges. Example: 1.2-5, -15.754 - -20.4
You cannot mix ranges and individual values. If you try, it will ignore the individual values.
Keeping Only the Left/Middle/Right Parts of Cells
Keep Only Left Part of Selected Cells
Keep Only Middle Part of Selected Cells
Keep Only Right Part of Selected Cells
These commands allow you to slice off everything except the first X characters, last X characters, or middle X characters of cells.
Left part
It will ask you for an index. If you want to keep the first 4 characters and delete everything else, type 4.
Example: Lorem Ipsum -> Lore
If you want to keep all but the last 4 characters, type -4.
Example: Lorem Ipsum -> Lorem I
Middle part
It will ask you for two numbers - an index and number of characters. If you want to keep 5 characters starting at character 2, type 2 5.
Example: Lorem Ipsum -> rem I
You can type a negative number for index. If you want to keep 3 characters starting 5 from the end, type -5 3.
Example: Lorem Ipsum -> Ips
You can also type a negative number for number of characters. In that case, it will look leftward. If you want to keep 4 characters ending at character 7, type 7 -4.
Example: Lorem Ipsum -> em I
Right part
It will ask you for an index. If you want to keep the last 4 characters and delete everything else, type '4'. If you want to keep all but the first 4 characters, type -4.
Example: Lorem Ipsum -> psum
If you want to keep all but the last 4 characters, type -4.
Example: Lorem Ipsum -> m Ipsum
Miscellaneous Editing
This section covers the following commands:
Convert to acronym
Convert to Acronym converts phrases in the selected cells to acronyms. The acronym will contain letters for small words like "of" and "the", so you may want to check that it converted it as you want.
Convert number format
Convert Number Format switches numbers in selected cells to either dot decimal or comma decimal.
Trim cells
Trim Selected Cell(s) removes the spaces before and after the non-space characters.