Creating a Task Management Workbook with Excel Agent: Verifying Formulas, Data Validation, and Change Impact

About this article
This article was created using an automated generation workflow leveraging generative AI. Official information from Microsoft Learn and Microsoft Support was re-verified on November 2, 2026, and organized into a procedure for creating a task management workbook with Excel Agent using fictional internal study session data and inspecting it as standard Excel functionality. No actual operations were performed in Microsoft 365 tenants, Excel Agent, OneDrive, or Excel.

Verification Status: 📘 Official Information Verified, Actual Tenant Unverified

First, verify through three operations.(1) Select Excel Agent in the Copilot app, (2) create a workbook by specifying the 8 fictional rows below, and (3) open the workbook saved in OneDrive using Excel to cross-check aggregations and data validation. This is an operational guide and does not guarantee success in an actual tenant.

Supported Environments, Licensing, Entry Points, and Constraints (Official Verification: November 2, 2026)

The Excel Agent referred to here is a dedicated agent for creating new books from the Microsoft Copilot application. The entry point, licensing, and saving workflow differ from Copilot's edit, plan, and chat features that directly edit existing books within the Excel application. Because the names are similar, verification records must explicitly distinguish between the 'Copilot app Excel Agent' and 'in-Excel Copilot'.

The Excel Agent is not only used to create a table-like appearance, but also serves as an entry point for generating Excel files on demand. However, a book that appears complete does not necessarily accept input safely, maintain aggregation when rows are added, or be ready to share with third parties.

Therefore, this time we will create a task management book for a fictional 'Internal AI Study Session'. We will sequentially check columns, data types, data validation, summary formulas, and the impact of changes, rather than focusing on generation speed. We will not use real names, departments, projects, email addresses, venues, or customer information.

Locking fictional data

First, we lock the input conditions to make it easier to distinguish between values supplemented by the agent and values provided by us.

The expected initial summary is 8 total items, 1 completed, 2 in progress, and 5 not started. The high-priority incomplete items are 3: T-002, T-003, and T-006. This count will serve as the acceptance criteria.

Step 1: Select the Excel Agent

Open the Microsoft Copilot app and select the Excel Agent from Tools or Agents. According to Microsoft Support, Word, Excel, and PowerPoint Agents are available in the web and desktop versions of the Copilot app as well as Teams, and generated files are saved to OneDrive.

If you cannot find the Agent, check Tools / All Agents in the Microsoft Copilot app, and then check the following in order.

1. Are you signed in with a work or school account?

2. Have you been assigned a paid Microsoft 365 Copilot license?

3. Has the administrator enabled the Anthropic models?

4. Are you included in the organization's policies and deployment scope?

5. Can you actually launch the agent, rather than just viewing it in the list?

Instead of concluding that it is available based solely on the license screen display, verify the administrator settings and the actual entry point separately.

Step 2: Create a workbook using the paste prompt

Paste the following prompt. Do not mix in real-world information.

架空の「社内AI勉強会」のタスク管理用Excelブックを作ってください。

要件:

- シート名は「タスク一覧」と「集計」

- タスク一覧はExcelテーブルにする

- 列は ID / タスク / 担当 / 開始日 / 期限 / 状態 / 優先度

- 下記の架空データ8行を入力する

- 状態は「未着手 / 対応中 / 完了」のリスト入力

- 優先度は「高 / 中 / 低」のリスト入力

- 開始日と期限は日付として扱う

- 集計シートに全件、未着手、対応中、完了、高優先度かつ未完了の件数を数式で表示する

- 元データを変更したら集計が追従する構造にする

- 外部リンク、マクロ、実在人物、メールアドレス、会場、効果数値は追加しない

- 不明な情報は推測せず「未決」とする

- 生成後、作成したシート名、テーブル名、入力規則の範囲、集計セルの数式を箇条書きで説明する

データ:
T-001 | 開催目的の下書き | 担当A | 2026-11-02 | 2026-11-04 | 完了 | 高
T-002 | 参加者向け案内 | 担当B | 2026-11-03 | 2026-11-06 | 対応中 | 高
T-003 | ダミー演習データ作成 | 担当C | 2026-11-04 | 2026-11-09 | 未着手 | 高
T-004 | 会場候補の比較 | 担当D | 2026-11-04 | 2026-11-07 | 対応中 | 中
T-005 | 受付手順の確認 | 担当A | 2026-11-05 | 2026-11-10 | 未着手 | 中
T-006 | 資料の読み合わせ | 担当B | 2026-11-06 | 2026-11-11 | 未着手 | 高
T-007 | アンケート項目案 | 担当C | 2026-11-06 | 2026-11-11 | 未着手 | 低
T-008 | 終了後の振り返り枠 | 担当D | 2026-11-12 | 2026-11-13 | 未着手 | 低

If the agent asks clarifying questions, provide the target range, column names, and aggregation conditions. Do not make it guess new actual results or assignees on the spot. Even if a preview is generated, do not share it immediately; open the file saved in OneDrive with Excel to verify it.

Step 3: Verify the expected observations by count

Check the values first rather than the appearance.

Microsoft explains that Copilot in Excel can edit tables, formulas, data validation, and other built-in Excel features. During the inspection after opening a workbook generated by Excel Agent in Excel, this editability as a standard feature is verified. On the other hand, since generated content can be inaccurate, both formulas and results must be checked.

Do not pass data validation simply because the drop-down arrow is visible. Verify that it is set on all data rows in the "Status" column and test whether entering an unspecified value triggers an error or warning. Microsoft Support states that data validation can be used to restrict lists and dates, and recommends testing with both valid and invalid values after creation.

Step 4: Change a single condition and verify the tracking

After completing the initial check, change only the status of T-006 from "Not Started" to "Completed". Do not modify the assignee, due date, priority, or any other rows.

The expected difference is as follows.

If the total count or in-progress count also changes, the formula range or criteria are inappropriate. If the aggregation does not change, verify whether the cells match the strings in the list, whether the formula is set to a fixed value, and whether the calculation method is set to automatic. Microsoft Support also states that the calculation method being set to automatic is a required condition for Copilot editing support.

This single-criterion change is not intended to evaluate the AI's response, but rather serves as a test to confirm whether the workbook reacts correctly as a standard Excel file.

Failure examples and reasons

Agent not found

Possible causes include unassigned paid licenses, disabled Anthropic models, deployment out of scope, or signing in with a different account. Rather than repeatedly refreshing the browser, isolate issues in order: contracts, administrator settings, and entry points.

Aggregation is hardcoded with direct numeric input

Even if the initial values match 8, 5, 2, and 1, they will not track changes in the source data. Select the aggregation cell and check the formula in the formula bar. The difference after changing T-006 is useful for detection.

Adding rows to the end of the table does not update the aggregation

The formula may be referencing only a fixed range, the generated range may not be an Excel table, or the aggregation criteria may be pointing to a different column. Verify the table name, structured references, and the source range for aggregation.

Data validation exists on only some rows

Even if the dropdown is visible in the preview, it may be applied only to the existing 8 rows and fail to expand to new rows. Add a new row and enter both an unspecified value and an allowed value to check the behavior.

Dates are formatted as strings

Even if the display is 2026-11-09, if it is a string, sorting and period calculations may break. Check the cell data type, sorting, and usage in formulas. Merely changing the display format to a date may not fix the issue if the underlying value is a string.

Directly modifying a shared workbook

Copilot may directly modify the workbook. In files being co-authored, changes will also be visible to other users. First, test with a new workbook containing dummy data, and verify the OneDrive save location and share recipients. If necessary, use a test copy with autosave disabled or version history to ensure you can revert.

Evaluation criteria in practice

Deployment effectiveness cannot be measured solely by "how many seconds it took to create the workbook." Record the following in the same format:

• Time from prompt entry to the first draft

• Time spent verifying formulas, data validation, data types, and formatting

• Number of modified cells and reasons for modification

• Number of metrics that correctly updated following a single condition change

• Number of unspecified information items added by the agent

• Errors found before sharing

• Total time difference compared to manual creation from scratch

• Whether users can maintain it using standard Excel features

The minimum requirements for adoption are that the initial aggregation is correct, a single condition change propagates as expected, data validation functions on new rows, there are no external links or non-existent information, and the save location and sharing permissions can be explained. If any of these cannot be explained, the workbook is a "first draft" and not a business template.

In addition, do not use personal information, customer data, or human resources, evaluation, medical, legal, or financial judgments as initial test materials. When having the agent reference organizational data, it retrieves information from files, emails, meetings, and sites that the user has access to via Work IQ, and passes the relevant context to the model. Anthropic is treated as a Microsoft subprocessor, but because it is not subject to the EU Data Boundary and domestic/regional processing commitments, you must enable it only after passing your organization's data location and vendor screening. Microsoft also notes that AI-generated results may contain errors, and requests human review depending on the confidentiality and the importance of the decision.

Conclusion

Excel Agent can be used as a starting point to create new workbooks from Copilot Chat. Its value emerges not when you blindly trust the generated table, but when you can verify its structure and behavior as standard Excel features.

Fix initial values with dummy data, verify formulas, data validation, data types, and external references, and finally change only the state of T-006 to observe the difference in aggregation. Running this small test allows you to evaluate "created quickly" and "usable for work" separately.

Official and Primary Sources

• Microsoft Learn — Get started with Word, Excel, and PowerPoint Agents in Microsoft Copilot

• Microsoft Support — Get started with Word, Excel, and PowerPoint Agents in Microsoft Copilot

• Microsoft Support — Get started with Copilot in Excel

• Microsoft Support — Frequently asked questions about Copilot in Excel

• Microsoft Support — Apply data validation to cells

• Microsoft Support — Copilot in Excel tips