Prompt Engineering With Excel  ## Metadata - Author: [[Chris Price]] - Full Title: Prompt Engineering With Excel - Category: #articles - Document Note: --- tags: - #Excel - #OpenAI - #PromptEngineering - #GPT4 - #Productivity - #DataAnalysis - #WordPress - #DeveloperAdvocacy - #Education - #OSPO Author: Chris Price Source: YouTube Video Reference: https://www.youtube.com/watch?v=g8b2jDl9QlQ date created: 2025-08-23 00:00 --- # Title: Prompt engineering with Excel — OpenAI Custom Functions, Evaluation, and Chain-of-Thought ## Table of Contents: - [📝 Summary](#-summary) - [📖 Main Content](#-main-content) - [🗒️ Notes](#-notes) - [📖 References](#-references) - [🗣️ Paraphrase](#-paraphrase) - [📰 Newsletter](#-newsletter) - [✅ Action Items](#-action-items) - [🧪 Hypotheses](#-hypotheses) - [📊 Methodology](#-methodology) - [❓ Questions](#-questions) - [🔮 Future Research](#-future-research) - [💡 Implications](#-implications) - [🧠 Learning Styles Assessment](#-learning-styles-assessment) - [📊 Accessibility Assessment](#-accessibility-assessment) - [🌍 DEIB Assessment](#-deib-deib-assessment) - [✅ Logical Validity](#-logical-validity) - [🔍 DARVO Analysis](#-darvo-analysis) - [🔍 SEO Evaluation](#-seo-evaluation) - [🎯 Conclusion](#-conclusion) ### References: - Video: Prompt engineering with Excel — https://www.youtube.com/watch?v=g8b2jDl9QlQ - GitHub (custom Excel functions): https://github.com/robatwilliams/openai-excel-functions - Blog post: Prompt engineering with spreadsheets — https://blog.scottlogic.com/2024/01/10/prompt-engineering-with-spreadsheets.html ## 📝 Summary: The video demonstrates a complete workflow for evaluating prompt engineering strategies inside Excel using OpenAI custom functions. The task is deceptively hard for GPTs: extract a word from a phrase by index. The workflow includes building a parameterized prompt template, calling the Chat Completions API via a custom Excel function, extracting results, automating scoring across 100 samples, measuring cost, and iterating on prompt design. Results progress from 12% accuracy with a baseline model to 65% with GPT-4, then to 100% by introducing a simple Chain-of-Thought delimiter (“end Chain of Thought” + newline) and extracting only the post-delimiter answer. Finally, a cheaper model retains perfect accuracy at roughly 2× the baseline cost, versus 48× when using verbose CoT with the most expensive setup. ## 📖 Main Content: The tutorial uses Excel as a lightweight experiment harness for prompt engineering. A template prompt is dynamically populated from sample rows and model parameters. The custom function returns a rich response object, which is post-processed to isolate the message text and then scored against expected answers. Aggregated metrics summarize accuracy and cost across the dataset. By prompting the model to “think” (Chain of Thought) and separating reasoning from the final answer with a delimiter, accuracy jumps to 100%. The final optimization shows how to maintain perfect accuracy on a cheaper model with much lower cost than using long CoT on a premium model. - Getting started with Excel + OpenAI [00:02](https://www.youtube.com/watch?v=g8b2jDl9QlQ&t=2) - Understanding the custom function wrapper [00:40](https://www.youtube.com/watch?v=g8b2jDl9QlQ&t=40) - Template substitution and parameter assembly [01:16](https://www.youtube.com/watch?v=g8b2jDl9QlQ&t=76) - Automating evaluation, scoring, and aggregation [01:53](https://www.youtube.com/watch?v=g8b2jDl9QlQ&t=113) - Model comparison: GPT‑3.5 vs GPT‑4 (12% → 65%) [02:29](https://www.youtube.com/watch?v=g8b2jDl9QlQ&t=149) - Introducing Chain of Thought + delimiter strategy [03:08](https://www.youtube.com/watch?v=g8b2jDl9QlQ&t=188) - Extracting only the post‑delimiter answer [03:49](https://www.youtube.com/watch?v=g8b2jDl9QlQ&t=229) - Cost/accuracy tradeoffs; cheaper model with perfect score [04:33](https://www.youtube.com/watch?v=g8b2jDl9QlQ&t=273) ### 🗒️ Notes - Task framing - Problem: “Select the Nth word from a phrase,” a case where LLMs can be unreliable without guidance. [00:02](https://www.youtube.com/watch?v=g8b2jDl9QlQ&t=2) - Dataset: 100 samples with phrase, index, and expected result aligned in rows. - Excel integration - Custom function wraps Chat Completions; takes a prompt and parameters. [00:40](https://www.youtube.com/watch?v=g8b2jDl9QlQ&t=40) - Prompt template + cell substitution using built-in functions. [01:16](https://www.youtube.com/watch?v=g8b2jDl9QlQ&t=76) - Parameters composed from named ranges (e.g., API keys) + model selector cell. [01:16–01:40](https://www.youtube.com/watch?v=g8b2jDl9QlQ&t=76) - Result handling and scoring - Custom data type includes full API response; VALUE2TEXT (or similar) used to isolate content. [01:53](https://www.youtube.com/watch?v=g8b2jDl9QlQ&t=113) - Binary scoring per-row; aggregation across dataset for accuracy and cost. [01:53–02:10](https://www.youtube.com/watch?v=g8b2jDl9QlQ&t=113) - Model comparison - Baseline across 100 samples ~12% accuracy. [02:29](https://www.youtube.com/watch?v=g8b2jDl9QlQ&t=149) - GPT-4 improves to ~65% with higher cost. [02:29–03:05](https://www.youtube.com/watch?v=g8b2jDl9QlQ&t=149) - Chain-of-Thought intervention - Encourage counting; add delimiter “end Chain of Thought” + newline to separate reasoning from the answer. [03:08](https://www.youtube.com/watch?v=g8b2jDl9QlQ&t=188) - Expand and parse lines; extract only text after delimiter. [03:49](https://www.youtube.com/watch?v=g8b2jDl9QlQ&t=229) - Perfect score achieved, but naive CoT increases cost ~48× due to longer completions. [04:10–04:35](https://www.youtube.com/watch?v=g8b2jDl9QlQ&t=273) - Cost optimization - Switching to a cheaper model retains perfect accuracy at ~2× cost vs baseline. [04:33](https://www.youtube.com/watch?v=g8b2jDl9QlQ&t=273) - Takeaways - Excel is a fast, transparent harness for prompt experiments, quantitative evaluation, and cost tracking. - Structured reasoning + explicit delimiters can unlock reliability for deterministic-like tasks. ## 📖 References: - Prompt engineering with Excel (YouTube): https://www.youtube.com/watch?v=g8b2jDl9QlQ - GitHub: OpenAI Excel functions: https://github.com/robatwilliams/openai-excel-functions - Blog: Prompt engineering with spreadsheets: https://blog.scottlogic.com/2024/01/10/prompt-engineering-with-spreadsheets.html ## 🗣️ Paraphrase: - Original: “This formula might look intimidating, but it's quite easy to follow if we break it down.” [00:40](https://www.youtube.com/watch?v=g8b2jDl9QlQ&t=40) - Paraphrase: The formula appears complex at first glance, yet it becomes straightforward once we unpack its parts. - Original: “Let's automate that evaluation, a simple quality comparison later, and we can have a score of one for a correct result and zero for an incorrect one.” [01:53](https://www.youtube.com/watch?v=g8b2jDl9QlQ&t=113) - Paraphrase: We can automate the checking by running a basic equality test and assigning one for correct outputs and zero for wrong ones. ## 📰 Newsletter: This week in WordPress and open source: We’re tinkering with a surprisingly powerful lab bench—Excel. Yes, that Excel. Using custom OpenAI functions, you can template prompts, run batch evaluations across 100+ samples, and score accuracy and cost right in your spreadsheet. In our feature demo, a deceptively simple task—“pick the Nth word”—went from 12% accuracy to 65% with GPT‑4, then to 100% by adding a lightweight Chain‑of‑Thought with a delimiter. Best part? A cheaper model kept perfect accuracy at roughly 2× cost instead of 48×. If you’re iterating prompts for docs, support bots, or contributor onboarding flows, the spreadsheet harness makes A/B testing, telemetry, and transparency delightfully accessible. Dive deeper with the video “Prompt engineering with Excel” and the write‑up “Prompt engineering with spreadsheets.” Plugins of the week: consider tools that bring TTS for accessibility, structured data for docs, and data‑layer events for evaluating prompt variants in production. Got a favorite prompt evaluation stack? Share it with the community and we’ll feature the best in next week’s issue. - Video: Prompt engineering with Excel — https://www.youtube.com/watch?v=g8b2jDl9QlQ - Blog: Prompt engineering with spreadsheets — https://blog.scottl - Summary: This video tutorial demonstrates how to use an Excel spreadsheet with open AI custom functions for prompt engineering. The task involves selecting words from a phrase based on their index. The tutorial walks through the process of creating prompts, invoking the chat completion endpoint for an open AI model, and evaluating the results. It also explores the use of prompts with Chain of Thought to improve the model's thinking and accuracy. The tutorial concludes with the ability to automate the evaluation of multiple samples and make quantitative assessments at each stage using just an Excel spreadsheet and open AI custom functions. - URL: https://www.youtube.com/watch?v=g8b2jDl9QlQ this video demonstrates how to use an Excel spreadsheet under open AI custom functions for a basic prompt engineering task that of selecting words from a phrase by their index which is known to be an unexpectedly tricky task for gpts if not one you'd actually want to use AI for to orient you down the left we have the samples we're going to use to evaluate our prompts the phrase word index and expected result at the top we have the prompt we're currently evaluating complete with placeholders for the sample data and the model we'll be invoking below that we have some performance Aggregates across all samples we'll come back to these in a minute finally below that we have the selected cell which is where the interesting stuff is happening this formula might look intimidating but it's quite easy to follow if we break it down the outermost function is our custom function which invokes the chat completion endpoint for an open AI model as with using the API or the playground it requires a prompt and some miscellaneous parameters The Prompt which you can see in the tool tip is calculated by taking the template prompt from the blue cell and using various built-in Excel functions to substitute the placeholders with the sample values from the purple and red cells the parameters are calculated using a different set of built-in functions to combine the common parameters EG API keys from the params named range in the param sheet with the model specified by the green cell let's evaluate this formula and see what result we get the result of our custom function is a custom data type containing the whole API response we'll see later why this is useful but for now we'll use the built-in value to text function to strip away the rest of the metadata and leave just the message we can see by I that this is the expected value but let's automate that evaluation a simple quality comparison later and we can have a score of one for a correct result and zero for an incorrect one and because it's a spreadsheet we can easily aggregate the scores and costs up across all of the samples speaking of all of the samples let's evaluate all 100 of them as we can see when considering all of the samples the score doesn't look nearly as good at just 12% I've heard GPT 4 is better than GPT 3.5 turbo let's see how much better it's certainly correct for a single sample but as we saw one result isn't always representative so let's evaluate them all again 65% is a marked improvement at a significant bumping cost but there's also still a lot of room for improvement let's now focus on the prompt itself and see if giv giving the model some time to think makes for any Improvement thinking in this case being an approximation of Chain of Thought whereby we'll encourage the model to count the words we'll need some way to disambiguate the thinking from the answer we'll arbitrarily choose the string end Chain of Thought followed by a new line to delimit the two let's bring over the Aggregates and the first sample for evaluation as you might have expected the completion is now prefixed by The Chain of Thought expanding the custom data card allows us to see a bit more of this but the best way is to extract the line's property to a blank range of cells each cell represents a line in the completion let's change the result formula to extract just the text after the end Chain of Thought delimiter remembering that we also need to include a new line in the delimiter and there's the answer we're looking for let's remove the cells we use for debugging and run all the samples as you can see it's taking significantly longer to run due to the increased completion length from The Chain of Thought thinking and a perfect score at only a 48 times increas in cost let's try and do something about that whilst keeping the accuracy up what happens if we drop back to using a cheaper model promising how about the rest of the samples a perfect score again but this time I only double the cost using just an Excel spreadsheet and our open AI custom functions we've been able to rapidly perform a basic prompt engineering task all the while making quantitative assessments at each stage, Prompt Engineering With Excel, Chris Price, 01hksr9wfjn0axgveqvwr5m4t1.