Project RACI Matrix in Excel with Validations and Dashboard
ESCOPO
This prompt turns real project information into a complete RACI Matrix solution for Microsoft Excel. It guides the AI to design the workbook architecture, structure registries, build the responsibility matrix, apply validations, and identify governance issues such as activities without an owner or with multiple approvers.
Ideal for PMOs, project managers, operations leaders, consultants, and analysts who need to create a robust role-definition tool. The output is practical and implementation-ready, including sheet layout, tables, formulas in Portuguese Excel syntax, conditional formatting, indicators, and optional VBA macro.
The prompt also includes executive analysis and a slide structure for presenting governance, making it possible to turn the matrix into an artifact useful both for day-to-day operations and for project committees.
Conteudo
Prompt principal
Act as a Senior Expert in PMO, Project Governance, and Microsoft Excel, with deep knowledge of RACI matrices, spreadsheet modeling, formulas, data validation, dashboards, and VBA. Your mission is to convert the real data I provide into a professional Excel workbook for project responsibility management. Produce technical, actionable instructions ready for execution in Excel desktop in Brazilian Portuguese, without generic answers. Before developing the solution, analyze the data I will paste below. If there are critical gaps, ask at most 8 objective questions and wait for my answers. If the data is sufficient, proceed directly. If any relevant field is not provided, adopt an explicit assumption, identifying it as [ASSUMPTION TO BE VALIDATED]. PROJECT DATA TO PASTE: 1. Project name, objective, sponsor, and project manager: 2. Scope, deliverables, phases, and main activities: 3. Areas, companies, or units involved: 4. Roles, positions, and/or names of participants: 5. Responsibilities already known by activity or deliverable: 6. Dates, milestones, status, and criticality, if available: 7. Existing governance rules (approvals, committees, sign-offs): 8. Excel version and permission to use VBA macros (yes/no): 9. Audience for the material (operational team, leadership, client, PMO, etc.): 10. Current responsibility issues that need to be resolved: Follow the framework below strictly. STEP 1 — Governance diagnosis: interpret the context, identify the relevant entities, and propose a clear taxonomy for RACI: R = Responsible for execution; A = Accountable/final approver; C = Consulted; I = Informed. Differentiate executor, decision owner, and approver. Point out risks of ambiguity, gaps, role overlap, approval conflicts, and interdependencies between areas. Recommend objective rules, including: each activity must have exactly one A; it must have at least one R; C and I must be used sparingly; the same person should not accumulate A in incompatible activities without justification. STEP 2 — Workbook architecture: define a sheet structure with names, purpose, columns, and usage guidance. As a standard, consider: 01_Instructions, 02_Registers, 03_RACI_Matrix, 04_Validations, 05_Dashboard, and 06_History. For each sheet, present a table with: column name, description, input type (manual, formula, drop-down list, or automatic), recommended format, and filled example. Use Excel Tables (Ctrl+T) and suggest technical names such as tbActivities, tbRoles, and tbRACI. STEP 3 — Matrix construction: describe the exact layout of the 03_RACI_Matrix sheet. Include activity ID, phase, deliverable, activity, description, lead area, role/participant in the columns, RACI classification, due date, status, criticality, notes, and approval evidence when applicable. If the matrix is better suited with activities in rows and roles in columns, deliver that model as the main one and also explain how to generate a normalized tabular view for filters, Power Query, or PivotTable. Present a sample matrix filled with at least 6 activities and 5 fictional roles, clearly separated from the real data. STEP 4 — Excel implementation: provide numbered, precise, click-by-click instructions when necessary to create drop-down lists for R/A/C/I, protect formula cells, and freeze panes. Write formulas compatible with Excel in Brazilian Portuguese, using semicolons as separators. Include formulas to: count R, A, C, and I per activity; flag missing R; flag missing or multiple A; classify activity compliance; count responsibilities per person; identify overload of A; and calculate the percentage of compliant activities. Specify exactly in which cells or columns each formula should be entered and how to replicate it. When there is an alternative between VLOOKUP and XLOOKUP, prioritize XLOOKUP and provide a compatible alternative. STEP 5 — Visual controls and quality: specify conditional formatting rules, with corporate and accessible colors, for R, A, C, I, pending items, and risks. Create quality criteria: red highlight for 0 or more than 1 A; yellow for missing R; orange for overdue due date; blue for activity under validation. Recommend sheet protection, header locking, validation against out-of-standard values, and version control in the history sheet. STEP 6 — Dashboard and executive management: detail the recommended KPIs, charts, and slicers for the 05_Dashboard sheet. Include: RACI compliance percentage, activities without A, activities without R, distribution of responsibilities by area and by person, activities by phase/status, and main owners by number of A. Indicate the data source for each indicator, the required formula or PivotTable, and the management message each visual should answer. STEP 7 — Optional VBA: only if macros are allowed, provide complete, commented VBA code ready to paste, which validates the matrix and generates a sheet or report of inconsistencies. Explain the steps to insert the module, save as .xlsm, and run the macro. The code must handle blank cells, headers, and errors without stopping execution. If VBA is not allowed, deliver a full alternative using formulas and filters. STEP 8 — Executive presentation: propose a 5-slide PowerPoint structure about RACI, with title, slide objective, suggested content, and key message. The slides should cover context, governance model, main gaps, responsibility distribution, and action plan. MANDATORY RESPONSE FORMAT: deliver in the following order: 1) executive summary; 2) assumptions and questions, if needed; 3) sheet design; 4) matrix model; 5) implementation instructions; 6) formulas; 7) validations and conditional formatting; 8) dashboard; 9) VBA or no-macro alternative; 10) slide structure; 11) final deployment checklist. Use Markdown tables whenever they improve clarity. Do not invent names, deadlines, or decisions as if they were facts. Explicitly distinguish provided data, illustrative examples, and assumptions to be validated.
