Create Rows for Unique Project Names in Excel
Question details
Create an analysis list that accommodates new unique project names from booking data.

- 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.
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.
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.
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.
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.
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.

Refresh a unique project table with Power Query
Use Power Query when you prefer a project list that updates on refresh rather than recalculating as a spilled formula.
Insert worksheet rows for new projects with VBA
Use this example when you must insert physical worksheet rows and can run VBA macros in Excel.
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.

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.




