Formula Errors

How to Create a Shortcut for TrimRange Notation in Excel

Partner Editor Sep 29, 2026 869 views

Question details

The user wants a quick way to insert the TrimRange notation (.:.) into formulas without having to manually type out the characters.

How to Create a Shortcut for TrimRange Notation in Excel

Product: Microsoft Excel
Device & OS: not provided
Scenario: Writing array formulas and referencing ranges that require the TrimRange operator to optimize calculations.
Observed behavior: There is currently no native built-in keyboard shortcut to insert the full .:. TrimRange notation in Excel, making repetitive data entry tedious.

Before you start

Ensure you are using a version of Excel that supports the TrimRange operator, and decide on a unique text trigger (like ',,t') that you wouldn't naturally type elsewhere in your spreadsheets.

Solution 1Recommended

Use Excel AutoCorrect to Create a Custom TrimRange Shortcut

Since Excel lacks a native shortcut for the TrimRange operator, you can configure an AutoCorrect rule to automatically replace a specific text string with the .:. notation.

The AutoCorrect feature is traditionally used to fix typos, but it can be highly effective as a custom text expander for complex mathematical operators or formula notations.

Open AutoCorrect Options

Click on 'File' in the top-left corner, select 'Options' at the bottom of the menu, and then click on 'Proofing'. From there, click the 'AutoCorrect Options' button.

Define the Shortcut Trigger

In the AutoCorrect dialog box, locate the 'Replace text as you type' section. In the 'Replace' field, enter your unique trigger (for example, ,,t). In the 'With' field, type the TrimRange notation (.:.).

Add and Save the Rule

Click the 'Add' button to save your new custom entry, then click 'OK' to close the AutoCorrect dialog box, and 'OK' again to exit Excel Options.

Test the Shortcut

Click into any cell, start typing a formula, and enter your trigger (,,t). Follow it with a space or another operator, and Excel will instantly replace it with the .:. notation.

Use Excel AutoCorrect to Create a Custom TrimRange Shortcut

Trigger Selection Tip: Always choose a trigger that doesn't occur naturally in your typical data entry. Using a double comma or a special character before a letter prevents accidental replacements.

Efficient Formula Entry with WPS Office

Create Custom Formula Shortcuts in WPS Spreadsheet

WPS Office offers a fully-featured, lightweight spreadsheet application that flawlessly handles complex formulas. You can easily set up AutoCorrect rules in WPS Spreadsheet to create custom shortcuts for specialized operators like TrimRange, boosting your productivity.

1. Open WPS Spreadsheet Options: Launch WPS Spreadsheet, click the 'Menu' at the top left, and select 'Options' from the bottom of the drop-down list.
2. Navigate to Spell Check: In the Options window, click on 'Spell Check' from the left-hand sidebar, then click the 'AutoCorrect Options' button.
3. Set Up Your Custom Notation: In the 'Replace' box, type a unique text string like ',,t'. In the 'With' box, enter the '.:.' notation.
4. Apply and Use: Click 'Add', then 'OK'. Return to your spreadsheet, type your trigger in the formula bar, and watch it automatically expand into the desired notation.

100% compatible with Microsoft Excel formulas, functions, and formatsLightweight architecture that runs smoothly even on older devicesHighly customizable AutoCorrect rules for faster data entryFree to use with an intuitive, tabbed interface

microsoft office alternative - wps office

Frequently Asked Questions

What is the TrimRange notation (.:.) used for in Excel?

The TrimRange notation is a specialized operator that automatically trims empty cells from the edges of a referenced range, streamlining array formulas and significantly improving calculation performance for dynamic data.

Why doesn't my AutoCorrect shortcut trigger inside a formula?

AutoCorrect typically requires a space, enter, or punctuation mark to recognize the end of a word and trigger the replacement. If it doesn't expand immediately in your formula bar, try typing a space after your trigger.

Does this AutoCorrect rule apply to all Excel workbooks?

Yes. AutoCorrect entries are saved at the application level on your computer. Once you create the TrimRange shortcut, it will be available in any Excel workbook you open on that specific machine.

Can I delete or change the custom TrimRange shortcut later?

Absolutely. Navigate back to File > Options > Proofing > AutoCorrect Options, scroll through the list or type your trigger in the 'Replace' box to find it, select the entry, and click 'Delete'.

Excel & Data Problems