digest
Short Summary
Main recommendation: Before using Copilot in Microsoft Excel, convert your data range into an Excel Table (select the data, go to Insert, then Table, then OK), since the speaker found Copilot responses are often better and error-free with table-formatted data, then use Copilot's prompt suggestions, saved prompts, and natural-language requests to highlight, format, filter, analyze, and chart the data. The demo uses a fictitious human resources spreadsheet to show these features in practice.
Key Points
- Copilot in Excel is accessed three ways: by clicking the Copilot icon that appears when you select a cell, through the Copilot pane at the top right of the Home tab, or through the icons at the bottom right (add image, view prompts, manage content, microphone).
- The Copilot Prompt Gallery (reached via "See All Prompts") offers Excel prompts plus prompts for Word, PowerPoint, OneNote, Outlook, and Teams, filterable by application, task, or job title; prompts you bookmark with the ribbon icon appear under "Your Prompts."
- Copilot can apply highlighting, conditional formatting, and color scales, and it generates the underlying formulas for you, so no formula expertise is required.
- Natural-language queries (for example, filtering employees by salary and tenure) can be sent to a new sheet for further work, and "Show data insights" generates visual insights that can be added to a grid.
- For deeper work, "Get deeper analysis results using Python" runs advanced analysis such as identifying employees at risk of leaving and visualizing salary distributions, and results can be cross-checked against the source data.
Details Worth Noting
- Concrete instructions:
- Select the entire data range from the first cell to the last, go to Insert, choose Table, and click OK before using Copilot.
- To access Copilot, select any cell and click the Copilot icon, or open the Copilot pane from the top right of the Home tab.
- To run a saved or gallery prompt, click View Prompts; for more options, click See All Prompts to open the Copilot Prompt Gallery, select an application (e.g., Excel), and use Show More to see additional prompts.
- To save a prompt for reuse, click the ribbon icon on that prompt in the gallery.
- To highlight data, select the "highlight" prompt, type your request (e.g., "highlight the top five salaries"), review the preview (the speaker's example uses yellow with black text), then click Apply.
- For conditional formatting, click the column, select Copilot, choose "Suggest conditional formatting," pick a suggestion such as highlighting rows where salary is greater than 100,000, review the generated formula, and click Apply.
- To filter with natural language, type a request such as "Show me all the people that make over 100,000 and have been at the company less than 10 years," then select Add to New Sheet to keep results separate.
- For charts and insights, ask "show some data insights," then select "Add all insights to grid" to create a new sheet with the insights.
- For Python analysis, select "Get deeper analysis results using Python," confirm to start advanced analysis, and choose a suggested analysis; stop the advanced analysis before issuing a different prompt.
- To color code a column, use the prompt "apply color coding to column for salary," which produced a red-white-green scale (dark red for lowest, dark green for highest), then click Apply.
- To query with voice, use the microphone icon at the bottom right and speak a question, such as counting employees by job title.
- The speaker's example dataset includes names, job titles, start and retirement dates, years of service, salary, performance rating (out of 10), pay range, at-risk status, manager status, team attrition rate, training completion percentage, PTO balance, and work-life balance score (out of 10). The data is fictitious.
- In the demo, the Python-based at-risk analysis flagged employees based on job title, years of service, salary, performance level, work-life balance score, and PTO balance; the speaker noted the results should be verified manually against source rows.
- The speaker observed in the demo that the Manager title showed a noticeably higher PTO balance, and that a person earning about 226,000 appeared at risk of leaving; the speaker noted there weren't many people who earn that much but a few of them seemed to want to leave.
- The microphone example returned a count of 10 managers, 3 project managers, and 2 IT managers from the sample data.
Bottom Line
Converting data into an Excel Table first and then using Copilot's prompts, gallery, and Python analysis lets Excel users handle filtering, formatting, charting, and analysis with plain-language requests, while still checking outputs against the source data.
