Project Schedule with Native Gantt in Excel
ESCOPO
This prompt turns raw project data into a complete technical guide for building a schedule with a Gantt chart directly in Microsoft Excel, without add-ins, Power BI, or external tools. It guides the creation of the data base, parameters, visual calendar, formulas, conditional formatting rules, and management indicators.
Ideal for PMOs, project managers, analysts, consultants, and professionals who need to produce auditable schedules that are easy to maintain in corporate environments. The result is adapted to the real data provided, considering Excel language and version, holidays, dependencies, owners, milestones, status, and executive tracking needs.
In addition to the formula-based solution, the prompt can request optional VBA for safe automations, such as schedule updates, highlighting the current date, and PDF export. All instructions are returned in operational sequence, ready to apply in the spreadsheet.
Conteudo
Prompt principal
Act as a senior specialist in Microsoft Excel, PMO, and project planning. Based on the real data I will provide, develop a complete, executable specification to create a project schedule with a Gantt chart in plain Excel, without add-ins, Power BI, or external dependencies. Your goal is to give me instructions that I can apply directly in Excel, with formulas, cell references, visual setup, conditional formatting rules, and, only if requested or truly useful, optional, commented VBA. Before proposing the solution, analyze the information below. If any essential item is missing, ask at most 8 objective questions, grouped by topic. If I do not answer, state the assumptions adopted and proceed with a functional version. DATA AND CONTEXT TO BE PASTED BY THE USER: - Excel version and language: [e.g., Microsoft 365 PT-BR, Excel 2021 in English]. - Task data: [paste table or describe available columns]. - Project start date, desired end date, and time scale unit: [days, weeks, or months]. - Work schedule and calendar: [business days, weekends, national/local holidays, and specific holidays]. - Desired fields: [ID, WBS, task, owner, start, finish, duration, predecessor, status, % complete, priority, cost, etc.]. - Dependency rules: [FS, SS, FF, SF, lags; if any]. - Visual needs: [corporate colors, milestone highlighting, today line, delays, baseline, filters, and printing]. - Allowed automation: [no VBA, optional VBA, or required VBA]. - Audience and purpose: [operations, executive meeting, client, governance/PMO]. Follow this framework strictly: 1. Diagnosis and assumptions: validate data quality, identify ambiguities, and state the assumptions. Clearly distinguish manual input fields, calculated fields, and global parameters. 2. File architecture: propose a lean sheet structure. As a default, evaluate: “Config”, “Schedule”, “Holidays”, “Dashboard”, and “Instructions”. For each sheet, state the purpose, columns, key cells, named ranges/tables, and protection rules. Prioritize Excel Tables (Ctrl+T) and structured references when appropriate. 3. Schedule data model: define the main table with column order, input type, and data validation. Include examples with fictional data only to illustrate the structure. Explain how to handle summary tasks, child tasks, zero-duration milestones, completed tasks, rescheduled tasks, and canceled tasks. 4. Formulas: provide ready-to-copy formulas, using the correct separator and function names for the language provided. When there is a relevant difference between PT-BR and English Excel, provide both versions. Include, when applicable: duration in business days, calculated end date, delay versus deadline, automatic status, planned percentage up to the current date, criticality indicator, and start/end calculation from predecessors. Do not invent nonexistent functions. For complex dependencies that are not reliable by formula, present a controlled manual alternative or optional VBA. 5. Building the Gantt: describe, in numbered steps, how to create the dynamic date header and the time grid. Indicate starting cells, horizontal fill formulas, column width, freezing panes, period grouping, and filters. Create exact conditional formatting rules for: planned bars, actual progress, milestones, weekends/holidays, current date line, delayed tasks, and baseline, if requested. For each rule, provide the “Applies to” range, formula, and priority order. 6. Governance and usability: recommend controls to prevent date errors, duplicate IDs, invalid predecessors, and percentages outside 0% to 100%. Include an accessible color convention, guidance for landscape printing/PDF, and measures for performance in files with many tasks. 7. Optional VBA: include this section only if I authorize VBA or if a critical formula limitation requires automation. Deliver complete, commented code, with no external references, including instructions for inserting it in VBA, running it, macro permissions, and a no-macro fallback plan. Never use destructive commands without confirmation. 8. Executive summary: propose 4 to 6 KPIs for a Dashboard sheet, with formulas or native PivotTable, such as overall progress, overdue tasks, upcoming milestones, workload by owner, and schedule variance. MANDATORY RESPONSE FORMAT: A. Assumptions and diagnosis; B. Sheet structure; C. Schedule table with field dictionary; D. Step-by-step implementation; E. Formulas in code blocks, identifying destination cell/column; F. Conditional formatting rules; G. Optional VBA, if applicable; H. Test and acceptance checklist; I. Final questions or known limitations. Be technically precise, practical, and auditable. Do not provide generic explanations: use concrete references to cells, tables, and ranges. Prioritize a sustainable solution for weekly updates and make explicit what must be adjusted when new tasks, dates, or holidays are included.
