Dave Bruns

In this example, the goal is to get the workbook path without the workbook name. For example, given a workbook called fruits.xlsx saved to:

C:\examples\fruits.xlsx

We want the path only like this:

...Read more

In this example, the goal is to return the name of the current worksheet (i.e. tab) in the current workbook with a formula. This is a simple problem in the latest version of Excel, which provides the TEXTAFTER function. In older versions of Excel, you...Read more

In this example, the goal is to return the name of the current workbook with a formula. This is a fairly simple problem in the latest version of Excel, which provides the TEXTAFTER function and the ...Read more

In this example, the goal is to get a normal path to the current workbook, without a sheet name, and without the square brackets ("[ ]") that normally enclose the workbook name. This is a pretty simple problem in the latest version of Excel, which provides the ...Read more

The CELL function can return various information about a worksheet. CELL can get things like address and filename, as well as information about the formatting used in the cell. The type of information to be returned is specified by the info_type ...Read more

In this example, the goal is to create a running count for a specific value that appears in column B. The value to count is entered in cell E5, which is the named range value. The core of the solution explained below is the...Read more

In this example, the goal is to sum values in rows that are visible and ignore values in rows that are hidden. The range F7:F19 contains 13 values total, 4 of which are hidden by the filter applied to column C. This is a good job for the ...Read more

In this example, the goal is to count rows that are visible and ignore rows that are hidden. This is a job for the SUBTOTAL function. SUBTOTAL can perform a variety of calculations like COUNT, SUM, MAX, MIN, and more. What makes SUBTOTAL interesting and...Read more

The TRIM function is fully automatic. It removes both leading and trailing spaces from text strings, and also "normalizes" multiple spaces between words to one space character only. All you need to do is supply a reference to a cell.

TRIM with CLEAN

If you also need to remove...Read more

In this example, the goal is to return the total for an entire column in an Excel worksheet. One way to do this is to use a full column reference.

Full column references

Excel supports "full column" like this:

...Read more