How to Use Copilot in Excel to Add Hire Quarter and Year Columns
Question details
The user wants to use Microsoft Copilot in Excel to automatically create calculated columns that extract the hiring quarter and year from an existing Hire Date column.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Organizing employee records or HR data by extracting specific time periods (quarter and year) from a standard date column to facilitate reporting and pivot table analysis.
- Observed behavior
- Excel Copilot requires a specific prompt to generate the correct CHOOSE and YEAR formulas, which can then be previewed and inserted into the structured table automatically.
Ensure your Excel workbook is saved to OneDrive or SharePoint and that your data range is formatted as an official Excel Table, as Copilot requires structured tables to generate and insert new calculated columns.
Use a Direct Prompt in the Excel Copilot Pane
Leverage Microsoft Copilot in Excel to automatically write the extraction formulas and insert the new columns with a single text prompt.
By providing a clear instruction to Copilot, Excel will automatically deduce the necessary math to calculate the quarter and extract the year, eliminating the need to write complex nested formulas from scratch.
Click anywhere inside your formatted Excel Table that contains the 'Hire Date' column.
Navigate to the 'Home' tab on the Excel ribbon and click the 'Copilot' button located on the far right to open the chat pane.
In the Copilot chat box, type the following prompt exactly: 'Add columns for the quarter and year of the hire date.' and press Enter.
Copilot will analyze the request and suggest the formulas: `=CHOOSE(CEILING(MONTH([@[Hire Date]])/3,1),"Q1","Q2","Q3","Q4")` for the quarter and `=YEAR([@[Hire Date]])` for the year. Check the preview to ensure accuracy.
Click the 'Insert Columns' button provided in the Copilot response to apply the calculated columns directly to your table.
Manually Add Formulas to Extract Quarter and Year
If Copilot is unavailable or grayed out, you can manually input the formulas to achieve the exact same result in your table.
Experience Smart Data Management with WPS Office
While Microsoft Copilot requires a premium Microsoft 365 subscription, WPS Office offers a completely free, lightweight, and highly compatible suite for your spreadsheet needs. Effortlessly manage complex formulas, create data tables, and extract date values without expensive add-ons.
- 1. Download and Install: Download WPS Office for free from the official website and follow the easy installation instructions.
- 2. Open Your Excel File: Launch WPS Spreadsheet and open your existing .xlsx workbook. Your formatting and table data will remain perfectly intact.
- 3. Manage Data Easily: Use the familiar formula bar and built-in functions to extract quarters and years just as you would in Microsoft Excel.
Frequently Asked Questions
Why is the Copilot button missing or grayed out in Excel?
Copilot may be unavailable if you do not have a qualifying Microsoft 365 Copilot license, if your workbook is saved locally rather than on OneDrive/SharePoint, or if AutoSave is turned off.
Does Copilot in Excel work with standard data ranges?
No, currently Copilot in Excel requires your data to be formatted as an official Excel Table. You can convert your data by selecting it and pressing Ctrl+T before using Copilot.
How does the Quarter extraction formula actually work?
The formula divides the numerical month by 3 and uses the CEILING function to round up to the nearest whole number (1 through 4). The CHOOSE function then matches that number to the labels 'Q1', 'Q2', 'Q3', or 'Q4'.
Can I ask Copilot to combine the Quarter and Year into one column?
Yes. You can adjust your prompt to ask Copilot to combine them. For example: 'Add a column that shows the quarter and year of the hire date together, like Q1 2023'.
