16 August, 2021

TIPS for CLICKS: Formatting / Editing / Moving around in a Workbook

Excel is equipped with hundreds of formatting and editing tools, key codes and alternative handling of data. Some of them are worth more useful than others. The following list presents some of the most helpful and time saving tips for frequent users.

Format Painter > If you double-click the Painter in the ribbon you can copy formatting of your selection (cell, range) repeatedly or singularly into multiple disparate cells or ranges. Click Format Painter once to quit it, if necessary.

Multiple lines in a cell > Two or more lines can be inserted into a cell by pressing ALT+ENTER after entering some value in the original line. It's just another way of wrapping text in a cell.

Borders >  If you select (in Home tab) Borders>More Borders..., you can select and add diagonal lines in active cell or range of cells to divide them e.g. in two parts. Then, you can add some value/text into the cell and press ALT+ENTER to type another value/text in the second line. Alignment of the entries can be fixed using spacebar, if needed. Example:

The diagonal lines can be entered also into a range of cells as shown here:

Shortcut menu for any selection > to display the menu use Right-click with your mouse or press SHIFT+F10 .

Moving around in a workbook > ALT key is the most essential for this purpose. You can get to every command on the ribbon by using Key Tips (the little black boxes with letters or digits) available by pressing the ALT key or F10 key first. To access a menu of additional options use the following combinations:

  • ALT+F > to open the File page options
  • ALT+H > to open the Home tab options
  • ALT+N > to open the Insert tab options
  • ALT+A > to open the Data tab options
  • ALT+R > to open the Review tab options
  • ALT+W > to open the View tab options

Use again the ALT key and appropriate Key Tips (as needed) to select desired option in the available menu. You can go directly to your option if you remember the key sequence. E.g., you can use ALT+E+B to display Office clipboard, or ALT+' (apostrophe) to display Style>Format menu for your selection.

Selected shortcuts using CTRL and SHIFT keys > These are quite handy:

  • CTRL+1 > to display Format Cells dialog box
  • CTRL+T or CTRL+L > to display Create Table dialog box
  • CTRL+Arrow keys > to move up/down/right/left on the worksheet
  • CTRL+SHIFT+= > to display Insert dialog box (for inserting cells, rows, columns)
  • CTRL+` (single left quatation mark) > to toggle formula view (alternate between value and formula)
  • CTRL+# > to apply the Date format with the day,month,year
  • CTRL+SHIFT+! > to apply the Number format with 2 decimals, 1000 separator
  • CTRL+SHIFT+& > to apply outline border to a cell or selected range
  • SHIFT+F2 > to add (or edit) Comment to a selected cell

There are, obviously, many more shortcuts available for moving around in Excel and elsewhere. One of them, namely ALT+TAB is worth mentioning here, because it can be used (without engaging the mouse) to switch between windows; sometimes called as 'task switcher'. You can use it to alternate between the two opened workbooks or between a full-scale window and the desktop, or between several application-level windows.


No comments:

Post a Comment

All comments are held for moderation. I reserve the right to edit, censor, delete and - if necessary - block comments.