
Copilot in Excel helps you analyze spreadsheet data and work with formulas, tables and charts through natural-language requests. It can now work with locally saved workbooks, rather than requiring every file to be stored in OneDrive or SharePoint. Its chat, planning and editing modes offer different levels of control over changes, according to My Online Training Hub.
Explore how saved preferences and workbook rules give Copilot instructions across files or within a specific workbook. The deep dive also explains how to create a reusable skill for a monthly sales summary, including a table and chart. Finally, see how to connect permitted Power BI data and check Copilot’s calculations before relying on the results.
Choose How Copilot Works
TL;DR Key Takeaways :
- Copilot offers Chat only, Plan and allow editing modes. The review recommends Plan for requests that could make substantial changes, so you can review the approach before allowing edits.
- Personalization saves account-wide preferences, while workbook rules provide file-specific guidance in a visible .rules sheet. Shared workbooks retain their rules, but not personal preferences.
- Skills let you reuse instructions across workbooks, including built-in options for branding, design and finance. Python skill availability is still rolling out, so options can differ between users.
- Custom skills use written instructions in a skill.md file stored in a matching OneDrive subfolder. They are available in Excel for Windows, Mac and the web, though access can depend on licensing, update channel and organizational settings.
- Copilot can draw on web, Microsoft 365 work account, Power BI and external connector data, subject to access and configuration. My Online Training Hub recommends checking Copilot’s calculations rather than assuming a polished report is accurate.
You can open Copilot through the dynamic action button at the bottom right of Excel. Right-click the button to dock it on the right or move it back to the ribbon.
Above the chat prompt, three modes control what Copilot can do. Chat only answers questions and analyzes the workbook without changing it. Plan proposes steps for you to review, while allow editing permits changes such as editing cells, adding formulas and creating sheets.
The review recommends Plan for requests that could make substantial changes, so you can review the proposed approach before allowing edits. The models in the menu depend on your subscription and, for work accounts, the options your administrator has allowed. A manual model choice lasts for the current session; Excel returns to its default afterward.
Personalize Instructions and Workbook Rules
Personalization saves preferences to your account and applies them across workbooks. Open Personalization from the three-dot menu, then add short, specific instructions on separate lines. For example, you can specify that Copilot should not merge cells or use currency formats in table columns.
These preferences do not travel with a workbook when you share it. A prompt that asks for something different also overrides a saved preference.
Workbook rules, by contrast, belong to one file. They can define its terminology, conventions and calculation methods, or identify sheets Copilot should not change. Copilot uses the rules as guidance in that file, so you do not have to repeat the context in each prompt.
Place the rules in a sheet named .rules, with one instruction per cell down column A. You can group longer lists under headings prefixed with ##. The create workbook rules option in the prompt box inserts a sheet with placeholders.
Because the rules are stored in the workbook, a colleague who receives the file receives them too. The sheet must currently remain visible: Copilot stops reading it if you hide it.
Advance your skills in Copilot in Excel by reading more of our detailed content.
- M365 Copilot Adds Custom Skills to Microsoft Excel Worksheets
- Save Hours of Work with These 10 Microsoft Copilot Prompts
- Copilot Excel AI Beginners Guide: Faster Cleaning, Smart Analysis, Python Insights
- How to use Excel Copilot AI assistant to simplify complex spreadsheet tasks
- MS Copilot’s Excel Agent Cleans, Validates & Speeds Checks : 10x Your Productivity
Reuse Instructions With Skills
Skills save instructions for processes you want to repeat across workbooks. Open Skills from the prompt box to see the built-in options, or call a skill by typing @ followed by its name. You can still call a disabled skill with an @mention.
Microsoft’s @brandkit applies an organization’s approved colors, fonts and logo if the company has set up a brand kit in Microsoft 365. @themedesign applies a coordinated palette to cells, tables and charts. The library also includes finance skills for three-statement models and comparable company analysis.
Under Formulas, you may see Python skills that let Copilot insert Python formulas and outputs into the workbook. Their availability is still rolling out, so the options shown can differ between users.
Create a Custom Skill
The review finds custom skills more useful than the skills library. A basic custom skill needs written instructions rather than code. For a monthly sales summary, Copilot can use those instructions to create a sheet with grouped revenue, each region’s percentage, a table and a related chart.
Copilot creates the required skills folder in OneDrive. You can write the skill file in Notepad or a similar editor. Its front matter contains the skill’s name and a description of when Copilot should use it. The body underneath contains the instructions.
Each skill needs its own subfolder, named to match the skill name in the front matter. Save the file as skill.md, then refresh Excel to find and turn on the skill. Custom skills are available in Excel for Windows, Mac and the web, although access can depend on your license, update channel and organizational settings.
Bring External Data Into Excel
Copilot can draw context from the web, your Microsoft 365 work account, Power BI and external connectors. Web search returns links to the pages it used. The Work source can search files, emails, meetings and conversations you already have permission to access. It is intended for eligible commercial Microsoft Copilot accounts.
Power BI has its own connection route, so Copilot can analyze report data without a manual export. In the add-content menu, all sources are allowed by default, but you can filter for Power BI and select a data set. Copilot can answer questions about it or insert editable tables, charts and other Excel elements.
Federated connectors retrieve external information in real time without first indexing it in Microsoft 365; examples include LSEG and Moody’s. Synced connectors instead index content from external business systems and are managed by an organization’s IT department. They should appear among the available sources if your organization has configured them and you have permission to use them.
Copilot in Excel requires an internet connection, a supported Microsoft 365 subscription and a current version of Excel. Power BI access follows existing permissions, including applicable row-level security. If Power BI is missing from the menu, an administrator needs to turn on Fabric data for Copilot in Excel. My Online Training Hub warns that a polished report is not necessarily accurate and recommends checking Copilot’s calculations rather than assuming it checked its own work.
If you want more control over how AI works with your spreadsheets, Copilot in Excel is worth a closer look for applying your working conventions and drawing on information beyond a workbook.
Media Credit: MyOnlineTrainingHub
Disclosure: Some of our articles include affiliate links. If you buy something through one of these links, Geeky Gadgets may earn an affiliate commission. Learn about our Disclosure Policy.