Excel Can Do That? 9 Surprisingly Useful Tricks!

Excel Features and Shortcuts

The video demonstrates nine Excel features and shortcuts for checking data, editing worksheets, and entering records more efficiently.

1. Find mismatched values: Select two comparable columns and press Ctrl + \. Excel selects cells that differ from the active cell’s column in the same rows. The active cell determines the direction of the comparison.

2. Move or insert rows and columns by dragging: Drag a selected row or column from its edge. Hold Shift to insert it at the drop point, or hold Ctrl to make a copy. The video also shows using Shift with the fill handle to insert or remove blank rows or columns.

3. Repeat an action with F4: After formatting one chart data series, select another series and press F4 to repeat the formatting. When editing a formula, F4 can also cycle through relative and absolute cell-reference types. Some laptops may require Fn or Function Lock.

4. Redistribute text with Fill Justify: Choose Home → Fill → Justify to spread text across selected cells and rows, or consolidate text from several lines into fewer rows. This feature handles only the first 255 characters in a cell.

5. Fit two headings in one corner cell: Add a diagonal border through Format Cells (Ctrl + 1) → Borders, then enter both headings in the cell using Alt + Enter to insert a line break. Alignment and spacing may need adjustment, so this technique is best suited to fixed tables intended for printing or PDF export.

6. Have Excel read cells aloud: Add Speak Cells and Speak Cells after Enter to the Quick Access Toolbar. Use them to hear selected values or individual entries. Speech speed and voice can be adjusted in Windows text-to-speech settings. This feature uses Windows speech and is available in Excel desktop for Windows.

7. Filter shared workbooks without changing others’ views: In a workbook saved to OneDrive or SharePoint, create a New Sheet View from the View tab before sorting or filtering. Changes stay in your view. You can also name views and make them available to other workbook users.

8. Choose from existing entries without setting up a drop-down: Press Alt + Down Arrow in a cell to display text entries already present in the nearby column. This helps you reuse existing values, but unlike data validation, it does not restrict what you can enter.

9. Use Excel’s built-in data-entry form: In Excel desktop for Windows, select a cell in a table with headers and press Alt + D + O. The form lets you browse, edit, search, delete, and create records. Formula cells can’t be edited through the form, and deletions can’t be undone. The form supports up to 32 fields and does not offer formatting, drop-downs, or date pickers.

The presenter demonstrates each technique with examples and mentions a free practice file for following along.

Main speaker/source: A single presenter and narrator from MyOnlineTrainingHub; the presenter’s name is not stated in the subtitles.