• ‘Enhance Excel with Microsoft Copilot’ webinar recording now available.

Topic: Prompting & Best Results

The question

How can users improve their Copilot prompts to get better and more accurate results? Are there best practices or guidelines for writing effective prompts?

The Answer

Yes, yes, and yes! If you understand that prompting is the MOST important concept to learn, then you are on the right path!

Microsoft has its “Goal, Context, Expectation, Source” guidance on their website. From my experience and understanding, there is much more to it. For example, in the context section, you can ask Copilot to position itself as a CEO or a CFO (I demonstrate this point clearly with an example in my latest video where I analyse a 200+ pages PDF with Copilot). Depending on the role you assign to the AI tool, the answers will vary.

I made a cheat sheet that summarises how you should ask questions to Copilot (with examples).

I totally recommend that you try this! It is like riding a bicycle, sometimes you fall, but over time you learn. Sometimes your prompt will fail, but other times it will succeed! Here is an example of a bad versus a good prompt:

Topic: Data Security and Privacy

The Question

What are the data privacy implications of using the free version of Microsoft Copilot in Excel? Is data shared or used by Microsoft, and how can businesses ensure GDPR and confidentiality compliance?

The answer

I always like to make a disclaimer here: Before using ANY AI tool, please consult your internal IT department! You want to comply and follow the company’s internal policies.

Data confidentiality is serious.

Now what I like to say:

When you use the FREE version of copilot (which means you go to copilot.microsoft.com) you can use the chatbot to upload an Excel file. You have to make sure that the Enterprise data protection is enabled (look at the top right you should see a green shield). When you hover with your mouse over it you will see the following message: “Enterprise data protection applies to this chat”. This popup will contain this link that leads to the Microsoft website.

Here are a few items we can read on this website (please be sure to read the whole website carefully and in depth):

“

1. Microsoft 365 Copilot and Microsoft 365 Copilot Chat offer the same enterprise terms available in our Microsoft 365 commercial offerings.
2. Use of Microsoft 365 Copilot and Microsoft 365 Copilot Chat involves prompts (entered by users) and responses (content generated by Copilot). With EDP, prompts and responses are protected by the same contractual terms and commitments widely trusted by our customers for their emails in Exchange and files in SharePoint.

1. We secure your data: We help protect your data with encryption at rest and in transit, rigorous physical security controls, and data isolation between tenants. 
2.  Your data is private: We won’t use your data except as you instruct. Our commitments to privacy include support for GDPR, ISO/IEC 27018, and our Data Protection Addendum. 

   “

I see many companies “banning ChatGPT” (not because ChatGPT isn’t secure), but just because it feels easier for them to go with a vendor they already know - Microsoft!

Again, I really want to add that I am not responsible for any security issues, and you should rely on your company’s IT policy before doing anything with AI.

Topic: Structured Data and Tables Requirement

The Question

Why is it essential to have data structured in Excel tables for Copilot to function effectively, and what should users do with existing, unstructured spreadsheets?

The answer

Structured data, specifically Excel tables, is essential for Copilot because AI models, like the one Copilot is using, interpret data through clear patterns and relationships. Tables provide these clear, consistent formats by defining columns, headers, and relationships explicitly. This structure enables Copilot to quickly identify the type of data and relationships, leading to more accurate insights, effective suggestions, and smoother interactions.

Additionally, Excel itself inherently performs better with structured data, tools such as PivotTables, filters, slicers, and conditional formatting all depend on a structured data layout. Most database-driven systems (CRM, ERP, etc.) naturally export structured data because consistency in data management simplifies both analysis and decision-making. For existing spreadsheets that are currently unstructured, here is my recommendation: 

• Maintain a "raw data" tab: Have one dedicated tab in Excel where you keep the original raw data. Perform all analysis (formulas, pivot tables, etc.) directly from this tab.
• Create a separate "formatted" tab: Use a separate tab to visually represent or format data, driven entirely by the structured "data" tab. Avoid performing calculations or analyses directly on formatted reports.
• Start Small: If restructuring all your spreadsheets feels overwhelming, choose your most frequently used spreadsheet and structure it step-by-step. A great starting point is learning how to "unpivot" data, Leila Gharani provides excellent tutorials demonstrating how to easily convert your existing spreadsheets into structured formats suitable for Copilot and Excel's analysis tools. (And it is easier than you think!)

By adopting structured data management practices, you will enhance your ability to leverage the full capabilities of Copilot and significantly improve your overall productivity with Excel.

The Question

Does Copilot provide real value to experienced Excel users, or is it primarily beneficial for beginners? Can advanced Excel users still gain significant productivity improvements from using Copilot?

The Answer

As of writing this, I think that Copilot helps beginners more than advanced users. But what is an advanced user? I consider myself an 'advanced' user, but I don’t know much about Python, the most popular programming language in the world.

Copilot can provide value to both beginners and experienced Excel users, but the type of value differs depending on your expertise level. Currently, experienced Excel users can certainly create highly complex formulas with Copilot, including nested IF statements, advanced INDEX & MATCH combinations, COUNTIFS, and more. However, where Copilot seems to truly shine for advanced users is in its emerging capabilities beyond traditional formulas, such as writing Python scripts directly within Excel.

Microsoft recently introduced the ability for Copilot to assist in writing Python code directly within Excel, significantly broadening what is possible. This feature opens exciting opportunities for advanced Excel users, empowering them to leverage Python without needing extensive coding knowledge.

I think a world of opportunity just opened for many advanced excel users.

You can reach out to David on LinkedIn if you have any other questions.