Copilot in Excel Examples for Converting and Analyzing Data
Question details
Users are looking for practical examples and steps to use Copilot in Excel for transforming data, such as splitting columns, converting time zones, and calculating survey scores.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Transforming, converting, and analyzing spreadsheet data using AI-driven natural language prompts.
- Observed behavior
- Users need to input descriptions of their desired data transformations so the AI can generate the correct formulas or table modifications.
Ensure you have an active Microsoft 365 Copilot license, and verify that your data is formatted as an Excel Table (Ctrl+T), as Copilot requires structured tables to function properly.
Use Natural Language Prompts for Data Conversion
By describing the exact transformation you need, Copilot can generate the appropriate formulas to split text, convert formats, or calculate scores.
Copilot in Excel translates your plain-English instructions into actionable formulas and column modifications. Whether you need to split full names into first and last names, convert a column of text into numbers, or shift time zones, you simply need to describe the end goal.
Always review the formula Copilot suggests before applying it, as data complexity can sometimes lead to unexpected outputs.
Select the range of cells containing your raw data, go to the Home tab, and click 'Format as Table'. Alternatively, use the keyboard shortcut Ctrl+T.
Click the Copilot icon located on the Home tab of the Excel ribbon to open the AI chat pane on the right side of your screen.
Type a clear instruction into the chat box. For example: 'Split the Full Name column into two columns for First Name and Last Name', or 'Convert the times in the Delivery column to Eastern Standard Time'.
Copilot will generate a preview of the new formula or column. Review the suggested changes for accuracy, then click the 'Insert Column' button to apply the transformation to your table.
Use WPS Office for Powerful Data Analysis and AI Assistance
If you are looking for a highly capable spreadsheet tool without the high subscription cost of Microsoft 365 Copilot, WPS Office is an excellent free alternative. WPS Spreadsheet is fully compatible with Excel formats and features built-in data tools alongside WPS AI to help you write formulas, analyze complex datasets, and streamline your workflow effortlessly.
- 1. Download and install: Get WPS Office for free from the official website and follow the easy installation process.
- 2. Open your spreadsheet: Launch WPS Spreadsheet and open your existing Excel files without losing any formatting or data integrity.
- 3. Analyze and transform data: Utilize the comprehensive built-in data features or activate WPS AI to automate your formula creation and data conversion tasks.
Frequently Asked Questions
Why is the Copilot button missing in Excel?
The Copilot button may be missing if you do not have a qualifying Microsoft 365 Copilot subscription, if your organization's IT admin has disabled it, or if you are using an older desktop version of Excel that has not received the latest updates.
Can Copilot in Excel automatically fix data errors?
Copilot can suggest formulas to clean up data, such as trimming extra spaces or converting text to numbers, but it requires you to provide a prompt describing the issue. It will not automatically modify your data without your approval.
Does Copilot work with standard ranges instead of Excel Tables?
No, currently Copilot in Excel requires your data to be formatted as an Excel Table (using Ctrl+T). It uses the structured references of a table to accurately understand and analyze your data.
