PivotTable Text Values Alternative with FILTER

This is the third and final post in the PivotTable Text Values Alternative series, where we are discussing alternatives to displaying text values in PivotTables. In the first post, we used Power Query as an alternative. In the second post, we combined multiple text values. In this post, we’ll use the FILTER function as an alternative to PivotTable text values. Let’s get to it.

Objective – PivotTable with Text Values

Before we get too far, let’s confirm our goal. We export some data like this:

PivotTable data set

And we’d like to create a summary report like this:

PivotTable report with text values

In our previous posts, we used Power Query as an alternative to using a PivotTable to create this report. This time, we’ll use Excel formulas.

Video – PivotTable Text Values Alternative with FILTER


This narrative provides the specific steps to create an alternative to PivotTable text values with the FILTER function. We’ll create the report using the following three steps:

  • Report labels with UNIQUE and TRANSPOSE
  • Retrieve text values with FILTER
  • Join text values with ARRAYTOTEXT

Let’s take these steps one by one.

Note: depending on your version of Excel, you may or may not have access to the functions discussed.

Report Labels

The first step in creating this formula-based report is to create the report labels. These are the row and column headers. As this is Excel, there are several ways to accomplish this.

One option would be to create them manually. To create the row labels, we would copy the entire Client column, and the paste special values in an empty area of the worksheet. Then, we would use the Data > Remove Duplicates command to remove any duplicate values. To create the column labels, we would copy the entire Return column, and then paste special values in an empty area. Then, we would remove duplicates. Then, we could copy and do a paste special transpose. Then, we could move them into place as desired.

Another option would be to use a formula. To create a list of the unique row labels (no duplicates), we would enter the following formula:


We hit Enter, and bam:

Using the UNIQUE function to create the row labels

If we wanted the row labels sorted, we could just wrap the SORT function around the UNIQUE function, like this:


And bam:

Add the SORT function to sort the values

To create the column labels, we would use the UNIQUE function to retrieve a list of the Return column values without duplicates. We want to transpose them from rows to columns, so we wrap the TRANSPOSE function around the UNIQUE function like this:


We hit Enter and bam:

Use the TRANSPOSE and UNIQUE functions to create the column labels

With the report labels looking good, it is time to retrieve our text values with the FILTER function.

Retrieve text values with FILTER

We will use the FILTER function to retrieve the text values from the Staff column. We can use the following formula:



  • Table1[Staff] is the column that has the values to return
  • ((Table1[Client]=$F7)*(Table1[Return]=G$6)) is the criteria for which values to include
  • “” returns an empty string if there are no Staff for any given Client Return

Note: if you’d like to learn more about how to construct the criteria expression, check out this post.

We hit Enter and bam:

Use the FILTER function to create the text values

We notice that DAR and DMK are both returned. This is because both are assigned to DIG290 Personal. So, we need a way to combine multiple staff values into a single cell. We’ll tackle this final step with ARRAYTOTEXT.

Join text values with ARRAYTOTEXT

To combine multiple staff values with a comma-space delimiter, we wrap the ARRAYTOTEXT function around the FILTER function, like this:


We hit Enter and bam:

Join the text values with the ARRAYTOTEXT function

Note: if you wanted to use a delimiter other than a comma-space, you can use TEXTJOIN instead of ARRAYTOTEXT.

Finally, we need to fill the formula down and right. We Copy the formula and Paste it into the other empty cells. Bam:

Thus, a PivotTable text values alternative created with the FILTER function, as well as UNIQUE, SORT, TRANSPOSE, and ARRAYTOTEXT

Note: if you try to fill the formula right by clicking-and-dragging, you’ll probably get unexpected results because the column references will be treated as relative. So, instead of clicking-and-dragging, you’ll want to use Copy/Paste or the Fill > Right command. Also, be sure to use the correct cell reference style, such as $F7 to lock down the column reference and G$6 to lock down the row reference.

With the report labels and values in good shape, we can now apply any desired formatting. This could include bold font for the labels, centering the values, and applying some cell borders:

Add the cosmetics formatting, and you are all set

Yay … we did it!


This is the final post in the series, and I hope it has been helpful.

If you have any alternatives or suggestions, please share by posting a comment below … thanks!

Sample file:

Posted in ,

Jeff Lenning

I love sharing the things I've learned about Excel, and I built Excel University to help me do that. My motto is: Learn Excel. Work Faster.

Excel is not what it used to be.

You need the Excel Proficiency Roadmap now. Includes 6 steps for a successful journey, 3 things to avoid, and weekly Excel tips.

Want to learn Excel?

Our training programs start at $29 and will help you learn Excel quickly.


  1. Shair on October 13, 2021 at 8:25 am

    Six functions in six minutes! All explained so each is easily understood and put to practical use. Always a pleasure to watch your training videos. Thank you!

    • Jeff Lenning on October 13, 2021 at 8:33 am

      Thank you, I appreciate your kind note 🙂

  2. Bridget on October 13, 2021 at 8:32 am

    Fantastic. Thank you.

  3. Michael V Bernot on October 13, 2021 at 11:07 am

    Hi Jeff,

    I always learn so much from your videos. Thank you so much! I might have also used the # operator in the conditions for filter. I was not familiar with “ArrayToText”.

  4. Keith on October 13, 2021 at 4:32 pm

    Brilliant Jeff – really appreciated. So easy. and replacing ArrayToText with Sum, text can be replaced with figures…

  5. Peter Roberts on October 13, 2021 at 5:36 pm

    I read this article [and several others you have published]
    I need to address the scale issues
    In accounting you may have say 10 clients with 5 years data a total of say 500 000 transactions plus chart of accounts and budgets
    Is excel capable of dealing with such numbers within a viable time frame

    • Jeff Lenning on October 15, 2021 at 12:40 pm

      The max rows that can fit on a single worksheet is about 1 million … but if you have large data sets it probably best to retrieve them with Power Query and reduce/aggregate before they land in Excel. Power Pivot also is designed for large data sets. I’ve written many articles on these tools and hopefully they can help you get started.

Leave a Comment