From Zero to Hero: Practical Ways to Boost Productivity with Copilot in Excel

Excel has long been the Swiss Army knife of the modern workplace, powering everything from basic record-keeping to complex data analysis across various industries. Yet, for all its versatility, countless day-to-day spreadsheet tasks, such as cleaning data, crafting formulas, building pivot tables, and generating reports, still demand tedious manual effort.

The introduction of Microsoft Copilot for Excel is a game-changer, liberating users from these tedious tasks. Copilot combines the raw calculation power you already know with generative AI, transforming natural language requests into instant, accurate results. Users can now surface insights, automate repetitive workflows, and create polished dashboards in a fraction of the time it takes.

In this comprehensive guide, we’ll show you how to harness the power of Copilot in Excel, moving from zero to hero in no time. By leveraging Copilot, you can boost productivity and unlock the full potential of every spreadsheet, making your work more efficient and effective.

Understanding Copilot in Excel

Copilot is Microsoft’s generative AI assistant, built on large language models and integrated directly into Microsoft 365. It is a conversational assistant that understands the structure of your workbook and the intent behind natural language requests. Microsoft Copilot represents a fundamental shift in how users engage with data in Excel.

Instead of hand-crafting formulas or poring over dense tables, you can ask Copilot to “build a Pivot Table of sales by region,” “flag any anomalies from last month,” or “summarise the customer reviews.” It instantly generates the required calculations, charts, or summaries, and even explains the reasoning behind its results.

Beyond speeding up routine tasks, Copilot functions as a collaborative partner in data exploration. It can uncover insights in data, suggest next steps in analysis, and support forecasting. By slashing the time spent on routine operations, Copilot allows professionals to shift their focus toward strategic analysis and creative problem-solving.

Getting Started with Copilot

Access Requirements:

• A subscription to Microsoft 365
• A copilot subscription

Step by Step

Step 1: Open Excel and ensure you're signed into your Microsoft 365 account.

Step 2: Open an existing workbook or create a new one and save it on OneDrive or SharePoint.

Note: Ensure AutoSave is enabled for Copilot to work in the workbook. AutoSave automatically saves your changes in the cloud as you work, ensuring that Copilot has access to the most up-to-date data and can provide accurate results.

Step 3: Click the Copilot button on the ribbon to open the chat pane. It is usually located at the top right, as shown by the arrow.

If you can’t find the Copilot icon, click File in the top right corner, then Account, and Update License.

Step 4: Enter your prompt in the Copilot text box. Let’s see an example.

Best practice: Convert the data range to a table. By using the ‘Format to table’ icon or highlighting the data range and using Ctrl + T.

Step 5: Review Copilot's response. Here, you can multiply columns c and d to ensure the result is accurate.

Step 6: Insert the generated content into your worksheet by selecting "Insert column."

Practical Ways to Boost Productivity with Copilot in Excel

Now that your Copilot is up and running in Excel, let’s explore how you can use it to improve your workflow. With natural language prompts, Copilot instantly converts routine spreadsheets into dynamic insight engines. Here are practical ways to boost your productivity with Copilot.

1. Automate Repetitive Tasks

Many spreadsheets bog you down with chores like highlighting duplicate rows, filling blank cells, and re-applying the same conditional formatting rules. For example, I can ask Copilot to “Highlight duplicates in column B,” and it completes the job instantly.

2. Data Cleaning

Cleaning data is a critical step in data analysis. Copilot can assist by executing tasks such as removing duplicates, filling in missing values, or standardising data formats based on your instructions. Just tell Copilot, “Create a new column for year from Date column.”

Now, let’s ensure all product names are capitalised and remove the extra spaces.

3. Generate Complex Formula

For a newbie, the many formulas in Excel can be daunting. Copilot demystifies this process by translating natural language requests into accurate formulas. This way, the steep learning curve for data analytics is eradicated.

4. Instant Data Visualisation

Copilot can also interpret data into visuals, such as charts or graphs, using simple prompts. This feature facilitates a deeper understanding and more effective communication of data-driven insights.​

5. Summarise Texts

Rows of customer comments or survey answers can feel endless. Tell Copilot “summarise the review in Column G,” and it skims thousands of rows in seconds, then hands you concise takeaways or bullet-point themes.

Note: Always remember to use “summarise”, especially when working with textual data.

10 Bonus Copilot Prompts

Below are ready-to-use prompts you can use in Copilot, along with descriptions of what each one does.

Copilot turns Excel into a quicker, smarter partner. It handles routine tasks, freeing you to focus on insights and informed decisions. Try it today and see how much more you can do with every spreadsheet.

Contact us today to learn more about our Copilot instructor-led training courses!