Function Problems

Create Rows for Unique Project Names in Excel

Steven ParkerSteven Parker Nov 18, 2026 868 views

Question details

Create an analysis list that accommodates new unique project names from booking data.

How to Create Rows for Unique Project Names in Excel
Product
Microsoft Excel
Device & OS
not provided
Scenario
A workbook lists projects from booking records and uses analysis formulas alongside those projects.
Observed behavior
UNIQUE and SORT return a dynamic array but do not insert worksheet rows, so formulas tied to fixed positions can become misaligned.
Before you start

Identify the booking column that contains project names and check whether the analysis needs actual worksheet rows or only an expanding report. If you use the VBA example, compare its sheet names and column layout with your workbook first.

Solution 1Recommended

Create an expanding project report with an Excel table

Use this approach when a dynamic report is sufficient and physical worksheet rows are not required.

A source table expands as booking records are added. The formula spills the project list outside the table; it does not insert rows or automatically create matching records in a separate analysis table.

1
Create the booking table

Select the booking data, choose Insert > Table, and confirm that the table has headers. On Table Design, name the table Bookings and ensure the project-name column is named Project.

2
Add the project list

In a clear cell outside any table, enter =SORT(UNIQUE(FILTER(Bookings[Project],Bookings[Project]<>"",""))). Leave room below the cell for the results to spill.

3
Align the analysis

Base related calculations on the spilled list or project names rather than fixed row positions. For example, a reference such as A2# refers to the full list when its formula starts in A2.

Create an expanding project report with an Excel table
Dynamic array requirement: This formula requires an Excel version that supports dynamic arrays, UNIQUE, SORT, and FILTER. Clear any cells blocking the spill range if Excel displays #SPILL!.
Free Microsoft Office alternative

Manage Project Workbooks with WPS Spreadsheet

WPS Office is a free, lightweight alternative with a familiar spreadsheet interface. Open compatible Excel workbooks and continue reviewing project data in WPS Spreadsheet; Excel-specific Power Query and VBA workflows may not work the same way.

Review and organize project booking data in a spreadsheetCompatible with Microsoft Excel XLS and XLSX formatsFamiliar menus make it easier to move from Microsoft OfficeFree and lightweight for everyday document work
microsoft office alternative - wps office

Frequently Asked Questions

Does UNIQUE insert new worksheet rows?

No. UNIQUE returns results in a spill range and does not insert physical rows. Use a dynamic report if that is sufficient, or a VBA macro if worksheet rows must be inserted.

Why does my project list show #SPILL!?

Check the cells below and beside the formula for content blocking the spill range. Place the formula outside an Excel table and clear the required output area.

Will an Excel table automatically add an analysis row for every new project?

Not by itself. A booking table can expand as records are entered, but a separate analysis table needs a process, such as a query or macro, to add missing projects.

How do I update a Power Query project list after adding bookings?

Add the bookings to the source table, then choose Data > Refresh All. The query will rebuild the loaded list using its saved filtering, duplicate-removal, and sorting steps.

Will the VBA example remove projects that are no longer booked?

No. The example only adds missing project names and sorts the list. Removing obsolete projects requires additional logic and a decision about what should happen to their analysis data.