Are you still wasting time filling up your Excel sheets manually?
Manually importing, formatting, and updating data in Excel used to be a time-consuming process. However, with Microsoft’s capability Query and the built-in capability of Copilot AI, importing and automating your data workflow has never been easier, smarter, or more dynamic.
In our previous blogs, we learnt the method of customising entities and fields in Dynamics 365 CRM and generating and downloading PDFS directly from PowerApps Forms. We also explored the way of creating and customising dashboards in Dynamics 365 for better decision-making and the introduction of new Copilot Agents. Now, it’s time to dive into the magic of Power Query + Copilot for Excel automation.
Copilot and Microsoft Power Query can handle the heavy lifting with a single request, whether you need online data, internal organisational material, or cross-sheet connectors.
But how do you do it, and what are its benefits?
Let us answer your questions in this article!
Adapt Automation and Say Goodbye to the Manual Excel Sheet Data Entry
One of the key benefits of using Microsoft Power Query with Copilot is its simplicity. You no longer have to take hours. One click and you import data. But where did the data come from? Prompt the copilot to collect it for you..
But how can we automate Excel tasks with Microsoft Power Query?
- Data connections created with Copilot remain active and renew automatically. So, any changes made to the source data are reflected in Excel, ensuring that users always have the most up-to-date information.
- Furthermore, the interface's adaptability allows users to combine a variety of data.
This versatility not only helps with data importation but also improves decision-making by allowing users to edit and review imported data easily. Tasks such as currency conversion and summarisation become straightforward, enabling users to make informed decisions based on credible data.
Step by Step: How to import data in Excel using Power Query
Not sure where to start? Here’s a brief Excel Copilot data automation tutorial for you:
1. Importing Web Data with Copilot
Copilot enables you to import real-time data from the web directly into Excel.
Example Use Case: Currency Conversion.
Suppose you have a product list in USD and require prices in several currencies.
- Step 1: Click the Copilot icon in the top ribbon (Home tab) or from within a cell.
- Step 2: Use natural language prompts, such as "Make a table showing the current exchange rates between these currencies."
- Step 3: Copilot searches the web and returns a table containing current exchange rates.
- Step 4: To add the data to your workbook, click "Insert to new sheet".
This information is obtained from credible sources, and Copilot even provides a citation link to help you confirm the source.
2. Using Copilot for Currency Calculations
Once you’ve determined your exchange rates, Copilot can help you convert your product prices to local currencies.
- Prompt Copilot: Calculate the prices for these cookies in local currencies based on USD prices.
- It uses the exchange rate table and formulae to calculate local prices.
- The resulting data is presented in additional columns without overwriting your original dataset.
3. Pulling Internal Data: Emails, Calendars, and More
Copilot fits smoothly into your Microsoft 365 ecosystem.
Use Case: Analyse Meeting Load.
- Prompt: "Show me this week's meetings in a table."
- Copilot extracts calendar data (such as meeting names, times, and organisers) and enters it into Excel.
- You might inquire further by asking, "Can you also include the description?" Copilot changes the database accordingly.
This function is ideal for tracking productivity, assessing workloads, and reporting on team schedules.
4. Importing Organisational Data from Azure Active Directory
Do you want a picture of your company’s hierarchy?
- Prompt: "Show me all employees who report to [Manager Name].
- Copilot retrieves the organisational chart straight from Azure Active Directory, providing a real-time personnel structure.
- Again, you may insert the results into a new sheet with a single click.
This is extremely valuable in HR reporting, team overviews, and project planning.
5. Connecting Excel to Other Spreadsheets with Microsoft Power Query + Copilot
While you’re training corporate teams on Power Platform, add this point to it as well.
Previously, importing data from other spreadsheets necessitated knowledge of the query editor. Now, Copilot makes it simple.
- Use Case: Syncing Customer Orders from Another Sheet
- Suppose you have an external workbook stored on SharePoint.
- Prompt: Show all customer orders in a table. The orders are saved in another spreadsheet in SharePoint."
- Copilot locates the appropriate file, gives a preview, and attaches it to your current sheet.
Even better, the connection is active and can be reloaded.
- Change a value (such as delivery status) in the source file.
- Go to the "Query" tab and click "Refresh."
- Your Excel file will update automatically.
This is a game-changer for:
- Sales dashboards
- Inventory tracking
- Financial reporting
- Collaborative analytics
Key Benefits of Automating Excel with Microsoft Power Query & Copilot
- Saves time by eliminating manual data entry
- Ensures real-time data updates with active connections
- Reduces the risk of human error in data handling
- Simplifies complex calculations like currency conversion
- Allows natural language commands for faster execution
- Pulls credible, up-to-date data from online sources
- Seamlessly integrates with the Microsoft 365 ecosystem
- Improves productivity by syncing with Outlook and calendars
- Provides flexible data modelling and formatting
- Enhances team collaboration and reporting
- Makes decision-making faster with clean, dynamic visuals
- Boosts accuracy in financial and sales reporting
- Connects multiple spreadsheets and maintains data flow
- Customises dashboards without advanced technical skills
- Supports better tracking across departments like HR, Sales, and Inventory
Conclusion
Automating Excel with Microsoft Power Query and Microsoft Copilot can completely transform how your business manages data, making it faster, smarter, and error-free. Whether you’re tracking sales, analysing HR performance, or syncing data across platforms, automation with Microsoft tools gives you a competitive edge.
If your business is looking to streamline operations and unlock full potential, DHRP’s Microsoft 365 implementation services offer end-to-end support—from integration to customisation—ensuring you get the most from your investment.
Let DHRP help your team transition from manual tasks to intelligent automation, achieving productivity on a larger scale.
All the screenshots in this blog are taken from this video.
