excel shortcuts

In daily use of Excel, many users rely only on basic shortcuts and common functions, but some relatively obscure features can often bring significant improvements in efficiency. The following content, based on actual work scenarios, summarizes several shortcuts and functions worth noting for readers' reference.

I. Obscure shortcuts to improve operational efficiency

Excel has more than 200 built-in shortcuts, but most people are only familiar with basic combinations like copy and paste. Although the following shortcuts are not often mentioned, they can greatly reduce mouse operations in specific scenarios.

  • Ctrl+Shift+L: Quickly enable or disable auto-filter, suitable for tables with large amounts of data, without needing to click through menus layer by layer.
  • Ctrl+Alt+V: Opens the "Paste Special" dialog box, allowing quick selection of values, formats, or transpose options, which is more direct than the right-click menu.
  • F4 key: Repeats the last action, such as inserting rows, setting formats, or merging cells, which is highly efficient during continuous operations.
  • Ctrl+Shift+Arrow keys: Quickly jump to the edge within a data region; combined with Ctrl+Shift+End, it can select an entire contiguous data block.
  • Alt+=: Inserts a sum formula (SUM) with one click and automatically identifies the contiguous data region above or to the left.

II. Practical but lesser-known functions

Besides common functions like VLOOKUP and SUMIF, the following functions have unique value in specific data processing but are relatively less widely known.

1. AGGREGATE function

This function can replace SUBTOTAL, supports ignoring error values or hidden rows, and allows the use of more aggregation methods (such as MEDIAN, PERCENTILE). For example,=AGGREGATE(1,6,区域)it can calculate averages while ignoring error values, preventing formula errors.

2. TEXTJOIN function (Excel 2016 and later)

Used to merge text from multiple cells, and supports specifying a delimiter and ignoring empty values. Compared to traditional & concatenation, TEXTJOIN is more concise and can handle dynamic ranges.

3. FORECAST.ETS function

Suitable for time series forecasting, it can automatically generate predicted values based on historical data and supports seasonal adjustments. This function is available in Excel 2016 and later versions, and is suitable for sales or inventory forecasting scenarios.

4. DATEDIF function

Used to calculate the complete number of years, months, or days between two dates. Although it does not appear in the function list, it can be entered directly for use. For example,=DATEDIF(A1,B1,"y")it returns the full number of years, often used for calculating age or length of service.

5. INDEX+MATCH combination

Although VLOOKUP is more common, the combination of INDEX and MATCH is more flexible when the lookup direction or number of columns changes, and it performs better. This combination can replace VLOOKUP for leftward lookups or dynamic column references.

III. Usage suggestions and precautions

The above shortcuts and functions are not universal across all versions; some features (such as TEXTJOIN, FORECAST.ETS) require Excel 2016 or a Microsoft 365 subscription. It is recommended that readers test according to their own software version and pay attention to whether range references in function parameters include header rows to avoid erroneous results.

Additionally, the availability of shortcuts may be affected by the operating system or keyboard layout; for example, on Mac systems, some key combinations need to use the Command key instead of the Ctrl key. In actual work, it is recommended to gradually try them based on personal habits and solidify high-frequency operations into muscle memory.

In summary, mastering these obscure but practical features helps reduce repetitive operations and improve the accuracy of data processing. For advanced users, continuously exploring Excel's hidden capabilities often leads to unexpected efficiency gains.