Top 10 Excel Tricks You Must Know

Overview

This video tutorial covers ten essential time-saving techniques and workflow shortcuts in Microsoft Excel. The lesson demonstrates how to identify the exact file directory path using the CELL formula, streamline data visualization and conditional formatting with the Quick Analysis tool, and filter tabular datasets by specific criteria and numeric ranges. It further covers automated sheet formatting and series entry using Autofit and Autofill for numbers, months, and dates. Advanced operational techniques include locking formula parameters with absolute cell references ($), reorganizing datasets with Transpose via Paste Special, splitting unstructured text strings into columns using delimiters, capturing external visual references using Screen Clipping, and toggling formula auditing using the Ctrl + ~ shortcut.

Locating File Path with the CELL Function

00:20

When working with multiple workbooks, locating where an active file is saved on a computer drive can be difficult. Excel provides a built-in information function to reveal the exact file path and workbook name. Entering `=CELL("filename")` into any active cell extracts the complete absolute system directory path, including drive letters, folder hierarchy, file name, and the active worksheet name.

• Use `=CELL("filename")` to output full path and workbook name.
• The file must be saved to disk for the directory path to display.

=CELL("filename")

Instant Visualizations with Quick Analysis

01:17

Selecting a dataset reveals the Quick Analysis icon in the lower right corner, accessible directly via the keyboard shortcut Ctrl + Q. This tool provides instant access to conditional formatting (data bars, color scales, icon sets, top 10%), chart creation (clustered column, stacked bar), running totals, tables, and sparklines without navigating the ribbon menu. Charts dynamically link to the underlying source data and update automatically when values change.

• Access Quick Analysis via selection icon or Ctrl + Q.
• Instantly apply data bars, icon sets, and charts.
• Linked charts automatically refresh upon cell edits.

Ctrl + Q

Filtering Datasets and Custom Criteria

02:46

Filtering enables users to isolate relevant records within large volumes of data. Applying Filter to a table header creates drop-down controls on each column. Users can filter by discrete checklist values or apply Number Filters such as 'Between' to specify inclusive numerical ranges (e.g., finding ages between 1 and 20).

• Apply filters to the header row from the Data or Editing ribbon.
• Use discrete checkboxes or advanced rules like Number Filters > Between.

Data > Filter

Automating Column Widths with Autofit

03:49

When cell contents exceed default column widths, text gets truncated or numerical data displays as '####'. While users can manually drag column borders, double-clicking the column divider automatically resizes the column to match the longest text entry. To autofit the entire worksheet at once, click the top-left select all button (the triangle between row 1 and column A) and double-click any column boundary.

• Double-click column border to autofit to the widest cell entry.
• Click the top-left corner box to select the entire sheet, then double-click any column separator to resize all columns simultaneously.

Sequence Generation Using Autofill

04:56

Excel recognizes patterns and standard chronological sequences. Dragging or double-clicking the fill handle on a single number duplicates it, while establishing a two-number pattern (e.g., 1 and 2) allows Excel to increment the series down the column. Text sequences such as month names (January) and full calendar dates (01/01/2020) increment automatically when dragged down.

• Highlight two incrementing numbers (1 and 2) to continue an arithmetic sequence.
• Built-in lists like months and standard dates auto-increment automatically.
• Double-clicking the fill handle auto-populates down to the end of adjacent data.

Absolute Cell References in Formulas

06:21

When copying formulas across rows, relative cell references shift automatically. When a calculation depends on a single fixed cell—such as a tax rate or discount percentage—that cell must be locked using absolute cell referencing. Placing dollar signs before the column letter and row number (e.g., `$C$1`) ensures the reference remains anchored to that specific cell while relative row parameters increment normally.

• Relative references shift when copied down or across rows.
• Lock a static cell reference using dollar signs: `$C$1`.
• Allows batch calculations against fixed percentages or rates.

=$C$1

Transposing Rows and Columns

08:58

Transposing alters table orientation by converting rows into columns and columns into rows. To transpose, copy the source data range, right-click the destination cell, select 'Paste Special', check the 'Transpose' checkbox, and confirm with OK. Headers and data records reorient instantly without manual re-entry.

• Copy original data table.
• Use Paste Special > Transpose to swap axes.
• Eliminates manual rebuilding when structural layouts change.

Paste Special > Transpose

Splitting Delimited Text to Columns

09:41

When pasting unstructured or comma-separated text from external sources such as Microsoft Word into Excel, all content often dumps into a single column. The 'Text to Columns' wizard parses this data into individual columns. Selecting 'Delimited' and choosing appropriate separator characters (such as tabs, commas, or spaces) separates each value into its own cell.

• Select cells containing delimited text strings.
• Go to Data > Text to Columns.
• Choose Delimited and tick active delimiters such as Tab or Comma.

Data > Text to Columns

Capturing Screenshots and Screen Clippings

11:29

Excel includes a native screen capture utility within the Insert ribbon. Users can embed entire active application windows directly or use 'Screen Clipping' to draw a bounding box around any open program or browser window. The resulting image inserts directly into the worksheet, where it can be cropped, resized, and formatted.

• Navigate to Insert > Illustrations > Screenshot.
• Select an available window or click Screen Clipping to capture a custom region.
• Format and crop captured images directly on the worksheet.

Insert > Screenshot > Screen Clipping

Auditing Worksheets by Showing Formulas

13:22

By default, Excel displays formula results rather than underlying syntax. Pressing Ctrl + ` (tilde) toggles formula view across the entire worksheet, revealing the exact formulas entered in every cell. This facilitates rapid formula auditing and troubleshooting. Pressing Ctrl + ` again restores normal values.

• Press Ctrl + ` (tilde) to toggle formula display mode.
• Reveals all underlying formula structures across cells.
• Press shortcut again to return to calculated results.

Ctrl + `