Excel Copilot Examples: How to Split Columns and Format Data
Question details
The user is looking for practical use cases and examples of how to use Copilot in Excel for everyday data manipulation, such as column splitting, generating email addresses, handling date columns, converting text to numbers, and adding units of measure.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Learning to automate and speed up data manipulation tasks using AI assistants in spreadsheets.
- Observed behavior
- The user wants to understand the exact capabilities of Excel Copilot and see concrete examples of how it can process, split, and format spreadsheet data effectively.
Ensure you have an active Microsoft 365 Copilot subscription, and verify that your Excel workbook is saved to OneDrive or SharePoint, as AutoSave must be enabled for Copilot to function.
Split columns and extract data using Copilot prompts
Use natural language prompts in the Copilot chat to automatically separate combined data into multiple columns, such as splitting full names or extracting email domains.
Copilot can analyze patterns in your data and generate the necessary formulas or instantly preview the split data across new columns. This is highly effective for cleaning up raw data imports.
Open your workbook in Excel and click the 'Copilot' button located on the right side of the Home tab.
Highlight the column or data range that contains the combined text you want to split.
In the Copilot chat pane, type a specific instruction such as 'Split the Full Name column into First Name and Last Name columns' or 'Extract the email addresses from this text'.
Review the preview or formula Copilot suggests. If it looks correct, click 'Apply' to insert the new columns into your spreadsheet.
Convert text to numbers and standardize dates
Ask Copilot to fix formatting inconsistencies, such as converting text-formatted numbers into usable numeric values or standardizing date formats across a column.
Experience Smart Spreadsheet Management with WPS Office
If you don't have access to Microsoft Copilot or are looking for a more cost-effective solution, WPS Office offers powerful spreadsheet tools with robust AI capabilities. It provides a familiar interface, built-in smart data formatting tools, and seamless migration from Microsoft Office.
Frequently Asked Questions
Why is the Copilot button missing in my Excel ribbon?
The Copilot button may not appear if you do not have an active Microsoft 365 Copilot license, if your file is saved locally instead of on OneDrive/SharePoint, or if your organization's IT admin has disabled it.
Can Excel Copilot extract email addresses from a block of text?
Yes, you can prompt Copilot to extract specific data types. By selecting a column of mixed text and asking Copilot to 'Extract all email addresses into a new column', it will use AI pattern recognition to pull out the emails.
Does Copilot support adding units of measure to existing numbers?
Yes, Copilot can append units of measure (like 'kg', 'USD', or 'cm') to existing numeric columns. Just highlight the data and prompt Copilot to format the numbers with your desired unit, and it will apply the appropriate custom number format.
Do I need to write formulas manually when using Copilot?
No, Copilot allows you to describe what you want to achieve in natural language. It will generate the corresponding formula (like TEXTSPLIT, VLOOKUP, or VALUE) and offer to insert it for you.
