BENTECH

Microsoft Excel Productivity Masterclass: 10 Essential Tricks

Master ten high-impact Excel workflow techniques including quick analysis, dynamic filtering, automated series entry, absolute referencing, text parsing, screen capture, and…

This course covers core Excel productivity techniques demonstrated in real-world scenarios. Students will learn how to extract file directories, rapidly summarize datasets with Quick Analysis, clean formatting with Autofit and Autofill, enforce static parameters using absolute references, reorient tables with Transpose, parse messy imported text using Text to Columns, embed screenshots, and audit formulas instantly.

Section 1: Workbook Navigation and Quick Analysis

Learn file path extraction and rapid data visualization techniques.

Lesson 1.1: Extracting File Path and Quick Analysis

  • CELL function filename parameter
  • Quick Analysis shortcut (Ctrl + Q)
  • Instant chart creation and conditional formatting

Finding the storage path of a working file can be cumbersome when dealing with deep directory structures. Entering `=CELL("filename")` extracts the complete file path, file name, and active worksheet tab name directly into a cell. The Quick Analysis tool (Ctrl + Q) provides instant analysis options for highlighted data. Rather than browsing the ribbon menu, users can click the small pop-up icon or use the shortcut to apply data bars, highlight rules, clustered column charts, and summary statistics in seconds. Charts generated via Quick Analysis dynamically reflect subsequent changes to the underlying cell data.

Practical work

  1. Enter `=CELL("filename")` in an empty cell of a saved workbook and verify the path output.
  2. Create a small 4x4 numbers table, select it, press Ctrl + Q, and insert a Clustered Column chart.
  3. Change one number in the table and confirm that the chart updates instantly.

Section 2: Data Organization and Sheet Formatting

Control data visibility and formatting using filtering, autofit, and sequence autofill.

Lesson 2.1: Filtering Records and Range Criteria

  • Applying header auto-filters
  • Selecting discrete values
  • Number Filters > Between

Filters allow users to isolate specific rows in large tables without modifying underlying records. Applying a filter creates dropdown selectors on the header row. In addition to manual checkbox selections, Excel supports rule-based filtering. For numeric columns, 'Number Filters' provides operators such as 'Between', enabling users to isolate records within lower and upper bounds (for instance, ages between 1 and 20).

Practical work

  1. Select a table header row and activate filters from the Data tab.
  2. Filter a numeric column using the 'Between' condition with custom minimum and maximum values.

Lesson 2.2: Sheet Autofit and Autofill Series

  • Resolving '####' overflow errors
  • Global sheet autofit via top-left corner
  • Autofill arithmetic patterns, months, and dates

When cell entries exceed column widths, text gets clipped and numbers display as '####'. Double-clicking the line between column headers resizes the column to fit the widest entry. To resize all columns at once, click the triangle between row 1 and column A to select the entire sheet, then double-click any column header divider. Excel's Autofill feature reads series patterns. Dragging a single number duplicates it, while providing two numbers (e.g., 1 and 2) allows Excel to continue incrementing the count. Text lists like 'January' or formatted dates like '01/01/2020' automatically increment when dragged using the fill handle.

Practical work

  1. Enter long text strings into multiple columns, select the entire worksheet via the top-left corner, and double-click a column border to autofit all columns.
  2. Type 1 and 2 in consecutive rows, select both, and drag the fill handle to generate numbers 1 to 20.
  3. Type a starting date in DD/MM/YYYY format and drag down to generate sequential daily dates.

Section 3: Formulas, Data Restructuring, and Tools

Master absolute referencing, table transposing, text parsing, screenshot tools, and formula auditing.

Lesson 3.1: Absolute Cell References in Formulas

  • Relative vs. absolute references
  • Locking cells with dollar signs ($)
  • Batch rate calculations

Formulas in Excel use relative cell references by default. When a formula is dragged down a column, referenced row numbers increment automatically. However, when multiplying a column of amounts by a static discount rate stored in a single cell (e.g., cell C1), relative referencing causes subsequent rows to reference empty cells below C1. To keep the target reference fixed, convert it into an absolute reference by placing dollar signs before both the column letter and row number (e.g., `$C$1`). This allows the relative amount cell (e.g., E4) to change to E5, E6, and E7 while keeping the discount rate locked to C1.

Practical work

  1. Create a table with Price, Quantity, Amount (`=Price*Quantity`), and a single fixed Discount cell.
  2. Write a formula multiplying Amount by the fixed Discount using `$C$1` syntax.
  3. Drag the formula down and verify that each row multiplies against cell C1 without errors.

Lesson 3.2: Transposing and Text to Columns

  • Paste Special > Transpose
  • Delimited Text to Columns wizard
  • Splitting comma-separated external data

To switch data layout from horizontal to vertical or vice versa, use Transpose. Copy the source data range, right-click the destination cell, select Paste Special, check the Transpose box, and click OK. Headers and records swap axes instantly. When text copied from external software (such as Microsoft Word) pastes entirely into a single column, use 'Text to Columns' located under the Data tab. By choosing 'Delimited' and checking delimiters like Tab, Comma, or Space, Excel splits the combined strings into clean, separate columns.

Practical work

  1. Copy a multi-row, multi-column table and paste it using Paste Special > Transpose.
  2. Paste a string of comma-separated names into a single cell, run Data > Text to Columns, select Comma delimiter, and complete the wizard.

Lesson 3.3: Screen Clipping and Formula Auditing

  • Insert > Screenshot > Screen Clipping
  • Cropping and formatting embedded visuals
  • Formula auditing with Ctrl + ` (tilde)

Excel includes a screen capture tool on the Insert tab under Screenshot. Users can insert full program windows or select 'Screen Clipping' to draw a bounding rectangle around any visible window or browser view. The captured image embeds onto the active sheet and can be cropped, resized, and styled. To audit an Excel worksheet and check all underlying calculation logic, press Ctrl + ` (tilde). This toggles formula auditing mode, displaying the formulas instead of calculated numerical values. Pressing Ctrl + ` again restores normal value view.

Practical work

  1. Use Insert > Screenshot > Screen Clipping to capture a portion of an open window into your spreadsheet.
  2. Crop the inserted image using Picture Tools > Format > Crop.
  3. Press Ctrl + ` on a spreadsheet containing formulas to toggle formula view on, verify the formulas, and press Ctrl + ` again to toggle back.