Unlocking Insights with Copilot 365: A Guide to Analyzing Pipedrive Data in Excel

AI has seamlessly integrated into our daily lives over the past year, and now it has made its way into Microsoft Excel, too. Known as Copilot 365, this powerful AI assistant is now embedded in key Office apps like Word, PowerPoint, Outlook, and Excel.

In this article, we will look how Copilot can help you with drawing insights from your Pipedrive data. Using apimoon, we will transfer the data into Excel and use Copilot to automate the data analysis, generate meaningful visualizations, and uncover actionable insights efficiently.

Exporting Pipedrive data into Excel

The first essential step is transferring your data from Pipedrive to Excel. This can be easily achieved using apimoon - an automated data platform.

Apimoon can export your data either on demand or according to a set schedule, ensuring that your most recent Pipedrive data is integrated with your existing Excel files. So that you can focus on sales, not on tedious manual tasks.

Exporting data with apimoon is easy - but if you find yourself struggling, check out our Step-by-Step Guide on Exporting Data from Pipedrive. In the article, we go over setting up connection to your Excel account, defining what data you want to export and creating an automated export schedule.

Using Copilot 365 on your data

Copilot in Excel is like having a quick, reliable assistant right by your side. Struggling with creating a formula? Need to replace text throughout an entire column quickly? Looking to generate a graph? Copilot can handle all of these tasks and much more.

Note on Copilot: At the time of writing, Copilot 365 is only available through the Excel desktop app for files with AutoSave on and requires Copilot for Microsoft 365 subscription.

‍

To start using Copilot, open the Excel spreadsheet with your Pipedrive data. Locate the Copilot button in the top right corner. This is where you can interact with Copilot, review its suggestions, and confirm or undo its actions.

A new window will appear on the right. At the bottom of this window, there is a textbox where you can enter your prompts. Above this textbox, you'll see your ongoing conversation with Copilot, and some suggested prompts that you can try out.

The suggestions are usually worth keeping an eye on, as they help you get acquainted with Copilot in the beginning. Copilot can take some time to get used to.

Copilot can help with any problem you might face in Excel. Whatever you want to do with your data, Copilot should be able to do it for you.

As an example, we will show you the 4 most common use-cases:

1. Sorting and Filtering
2. Writing Formulas
3. Creating Tables
4. Visualizing Data

1. Sorting and Filtering

Copilot is suitable for tedious tasks such as sorting or filtering. Simply tell Copilot which column you want sorted and how, and the rest will be done in a matter of seconds.

As an example, we could sort Pipedrive Deals by its status (open, won, lost), so that corresponding records are grouped together:

1. Prompt Copilot:  Write e.g. “Sort data alphabetically by the status column” and Copilot will automatically sort your data.
2. Review the Changes: Copilot can sometimes be wrong, so be sure to check that the data has been sorted according to your prompt. Once you are sure the data is correct, click the “Apply” button.

Next, you could filter the records to only show open Deals that are worth more than €10,000, so that you know what to focus on.

1. Prompt Copilot:  Type for example “Filter the data to only show open deals worth more than 10000” the data will be filtered for you.
2. Review the Changes: Copilot can sometimes be wrong, so be sure to check that the data has been filtered according to your prompt. Once you are sure the data is correct, click the “Apply” button.

There are no limits to sorting and filtering your data just as you need it. You can always keep your original data by telling Copilot to copy the new data over to a new sheet.

2. Writing Formulas

Is math a struggle? Do you find yourself having to look up all the function names all over the internet?

Well Copilot was made just for you. Tell Copilot what you want your formula to calculate, and it will make the perfect formula, covering the edge cases too.

In the example below, I asked Copilot to calculate a potential profit accounting for the money spent. It came up with the (arguably simple) formula that accounts for empty cells, and automatically inserts a new column into the sheet.

I had a couple of issues explaining Copilot what formula exactly I want it to generate. For example, I asked it to “Create a formula that calculates the total salary paid per department”, for which it made a table in a new Sheet instead of a formula.

So I tried to be more specific: “create a summary table starting from column F that shows the total salary paid for each department. Use the data in columns B (Department) and C (Salary). List each unique department in column F and calculate the total salary for each department in column G.”, but it still made a table.

So, in my opinion, it can still struggle with the creation of formulas, which is a thing to be aware of.

3. Creating Tables

What Copilot in Excel excels at, is creating tables. Sometimes it makes a table even if you don’t want it to. Still, it is capable of generating complex tables that uncover deep insights from your data.

Pivot Tables are a powerful feature in Excel for summarizing large amounts of data. For instance, if you want to analyze the total value of deals per stage from your Pipedrive data, you can simply ask Copilot to generate it for you.

1. Ask Copilot: Type a prompt like “Create a Pivot Table that summarizes the total value of deals per stage.” Copilot will automatically arrange your data, placing each stage in the rows and the total deal values in the columns.
2. Review and Customize: Once the Pivot Table is created, you can review the results to ensure they meet your needs. You might want to customize it further by adding filters or adjusting the layout. For example, you could add a filter to only include deals from a specific date range or salesperson.

Copilot clearly and correctly analyzes the data and shows in which stage most deals were lost and won, including the money sums.

You can further use these tables yourself to create graphs or continue asking Copilot to visualize the data for you.

4. Visualizing data

Visualizing data is crucial for understanding trends and patterns. Copilot excels at creating charts and graphs, which can help you interpret your Pipedrive data more effectively. Here’s how you can use Copilot to create impactful visualizations:

1. Ask for a Chart: For example, if you want to see the total value of deals and how much money was lost per stage, you might prompt Copilot with, “Generate a Column chart showing the total deal value per stage and a Line chart indicating money lost per stage.”
2. Customize the Chart: Once Copilot generates the charts, you can adjust them to better fit your presentation needs. You can change chart types, modify colors, or add data labels to enhance clarity. For instance, switching from a Column chart to a Pie chart can provide a different perspective on the distribution of deal values.

Copilot extracts only the required data and composes a clear chart out of them. Copilot works with any type of chart, be it Column, Line, Pie, Bar or any available chart within Excel.

Should you use Copilot in Excel?

In conclusion, Copilot 365 in Excel is a powerful tool for transforming your data into actionable insights. Whether you're sorting and filtering, writing complex formulas, creating summary tables, or visualizing data with charts, Copilot streamlines these tasks, making data analysis more efficient and intuitive.

By leveraging Copilot's capabilities, you can focus on strategic decisions rather than getting bogged down in manual data processing. Explore these features and see how they can enhance your workflow and drive better business outcomes.

‍