Create dynamic named formulas — AI Prompts for Microsoft Excel

The four heights

The same task, four distances: today's deadline, the next reviewer, the stuck moment, the pattern.

Execute — do the immediate task

Create dynamic named formulas that calc the current-year sales, last-year sales, and year-to-date…

Create dynamic named formulas that calc the current-year sales, last-year sales, and year-to-date average so the dashboard pulls correct numbers as new rows arrive. Name them CurrentYearSales, LastYearSales, and YTD_Avg, and make them robust to extra header rows or future columns. Test them on the July import and confirm the dashboard linked cells update.

Improve — make it easier to accept

Before I hand the workbook to reporting, simplify maintenance by turning hard-coded totals into…

Before I hand the workbook to reporting, simplify maintenance by turning hard-coded totals into dynamic named formulas. Create names for CurrentYearSales, PriorYearSales, YTD_Average and make each ignore blank rows and tolerate inserted columns. Add a short cell note showing the formula logic and a checkbox on the dashboard labeled ‘Use Named Ranges’ so reviewers can see how numbers are sourced.

Decide — diagnose the stuck moment

I added a named formula for CurrentYearSales and after the overnight import the dashboard shows…

I created a named formula and the dashboard shows #REF after the import

I added a named formula for CurrentYearSales and after the overnight import the dashboard shows #REF; the file goes to Tom in an hour and I don’t know what broke. I’m unsure if the name references shifted, the import added a header row, or the sheet name changed. What sequence of checks will reveal the root cause and the fastest fix to restore numbers before Tom sees it?

Become — change the pattern

Over time named formulas I create break after each monthly import and I spend hours relinking…

Named formulas drift and require manual repair after each import

Over time named formulas I create break after each monthly import and I spend hours relinking sheets. It erodes confidence in reporting and wastes my week. Which naming conventions, workbook layout rules, or validation steps should I adopt so named formulas survive imports and require little maintenance?

Next to this one

Other spreadsheet work people do in Microsoft Excel.

Create and modify tables Create audit reports Create charts for presentations Create client presentations of plan details.Create dashboards for stakeholders Create financial reports Create forecasting trendlines Create formulas for calculations Create new artificial substances and useful processes Define distribution of work according to production capacity

Every task here came from the work, not from a feature list — which is why the prompts name what you want done and never the button that does it. The tool changes; the work does not.

The rest of the map

Same library, five ways in.

Work Atlasthe whole map, interactive Careers1,949 jobs and their real tasks Soft skillsthe human part of the work Activities500 things people do Prompt Librarybrowse every prompt