how to make the table for below case without filter and i have headers for table

Chaturvedi, Santosh 430 Reputation points
2026-09-18T12:27:56.8133333+00:00

Data below is fake and not real

Hello -->

how to make the table for below case without filter and i have headers for table

User's image

Microsoft 365 and Office | Excel | For business | Windows
0 comments

3 answers

Sort by: Newest
Most helpful Newest Oldest
  1. Aetherin 1,725 Reputation points Independent Advisor
    2026-09-18T12:42:00.67+00:00

    Hi @Chaturvedi, Santosh,

    If your formula uses dynamic array functions such as FILTER, SORT, HSTACK, or UNIQUE, Excel cannot spill the results directly into an Excel Table. This is a current limitation of Excel, as spilled array formulas are not supported within Excel Tables. They can reference data stored in Tables, but the spill range itself must exist in a normal worksheet range.

    User's image

    For your reference: Dynamic array formulas and spilled array behavior

    Since you already have column headers, you can keep those headers above the output range and enter the dynamic array formula in the first data cell directly below them. Excel will then spill the results into the cells beneath, creating a dynamic output range. While the spilled range will not be an actual Excel Table, you can manually apply Table-style formatting to make it look like a table.

    If you specifically need an actual Excel Table object, there is unfortunately no way to have a dynamic array formula spill directly into the Table. In that case, you would need to copy the spilled results and paste them as values before creating the Table. However, the resulting Table will be static and will not automatically expand or shrink when the formula output changes.

    If your goal is to maintain a refreshable, table-like structure that updates automatically as the source data changes, Power Query may be a better option, as it can load results directly into an Excel Table and refresh them when needed.

    I hope this helps clarify the limitation and the available alternatives.

    If the answer is helpful, please kindly click "Yes" button below. If you have extra questions about this answer, please click "Comment".    

    Note: Please follow the steps in the forum documentation to enable e-mail notifications if you want to receive the related email notification for this thread.  

    Was this answer helpful?

    0 comments
  2. Chaturvedi, Santosh 430 Reputation points
    2026-09-18T12:37:51.7166667+00:00

    Hello please notice that when i do the way said then there is problem of SPILL

    The cell J2 have formula buil in that

    User's image

    Was this answer helpful?

  3. AI answer

    2026-09-18T12:28:13.6+00:00

    If the goal is to create an Excel table without using filter buttons and the data already has headers, use these steps:

    1. Select the data range, including the header row.
    2. On the Home tab, select Format As Table.
    3. Choose a table style.
    4. In the Format As Table dialog box, select My table has headers.
    5. Select OK.

    If the table is already created and only the filter buttons need to be removed:

    1. Click anywhere in the table.
    2. Go to Table Design.
    3. In Table Style Options, clear Filter Button.

    Important behavior:

    • A table normally has a Header Row.
    • If the Header Row is turned off, AutoFilter is turned off and any applied filters are removed.
    • The header names entered are retained if the header row is turned back on later.

    If headers are not currently showing in an existing table:

    1. Click anywhere in the table.
    2. Go to Table Design.
    3. In Table Style Options, select Header Row.
    AI-generated content may be incorrect. Read our transparency notes for more information.

    Was this answer helpful?

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.