Excel Copilot for Associations: Getting Started

Like many things in life, the hardest part of adopting a new technology like Microsoft 365 Copilot in Excel (referred to throughout this article as Excel Copilot) is simply getting started.  This article shows how simple that first step can be, using a practical association finance scenario to demonstrate structured, repeatable ways to prepare, transform, and calculate with Copilot.

This is the second installment in SoundPost’s Excel Copilot series.  The first article focused on spreadsheet habits that are not only considered best practice, but that also enhance Copilot’s ability to generate useful results.

Preparing for Excel Copilot

Much of Copilot’s effectiveness is determined before the first prompt is entered.  Well-structured spreadsheets and strong data governance practices create the foundation for accurate, secure, and productive use.  The steps below outline how to prepare for reliable, AI-assisted work.

1. Confirm Access

To get started, click the Copilot button in the Excel ribbon.  This opens the Copilot pane on the right side of the screen, where you can begin interacting with your workbook using natural language prompts.

Copilot is available as an add-on for select Microsoft 365 Business and Enterprise plans.  If the Copilot button does not appear in the Excel ribbon, your IT administrator should verify that the current license includes Microsoft 365 Copilot and that it has been enabled correctly.

To learn more about plan availability and features, visit Microsoft’s official Copilot page.  Detailed licensing options and eligibility requirements can be found at the Microsoft 365 Copilot Licensing Overview.

2. Build on a Solid Spreadsheet Foundation

Copilot does not correct poorly structured spreadsheets.  It analyzes data as it appears.  Inconsistent formatting, unlabeled ranges, or ambiguous naming can produce misleading results.

To set yourself up for success:

• Use Excel Tables instead of flat ranges

• Apply Named Ranges for clarity and reusability

• Label columns and sheets descriptively

• Separate inputs, calculations, and reports into distinct sections or tabs

The first article of this series, Want Better Excel Copilot Results? Start With a Better Spreadsheet, outlines eight best practices that dramatically improve Copilot’s reliability and output.

3. Protect Sensitive Data

Copilot does not connect to external systems or transmit your data outside the Microsoft 365 environment.  However, it can still surface, summarize, or reveal sensitive information in ways that may not be immediately obvious.  For association finance professionals who routinely work with member data, donation records, and internal compensation figures, it is important to treat spreadsheet privacy with the same care applied to any other financial or member system.

Even within a secure Microsoft environment, several risks remain:

• Human visibility: Copilot may extract and re-display personally identifiable information (PII) or financial details in response to broad prompts such as “summarize this sheet,” even if those data points were not your focus.

• Prompt history and logging: Depending on your organization’s configuration, prompt activity may be stored in audit logs or accessible to compliance administrators. This may include prompts that inadvertently reference sensitive data.

• Unexpected results in collaborative settings: During screen sharing or joint editing sessions, Copilot may surface details such as names or donation amounts that were not top of mind but remain present in the workbook. Well-structured files and clearly written prompts help keep outputs relevant and appropriate.

To reduce the risk of accidental exposure:

• Remove or redact confidential or unnecessary PII before working with Copilot.

• Avoid storing payment details or donor history in general-purpose analysis workbooks.  For additional guidance on protecting sensitive financial information, see PCI DSS 4.0: A Financial and Security Priority for Associations.

• Follow your organization’s internal data governance and security protocols.

Associations unsure where to begin in the rapidly evolving world of AI often turn to industry experts who specialize in helping mission-driven organizations navigate emerging technologies with confidence.

As Michael Keller, AAiP, Chief Operating Officer at Cimatri, explains, "Responsible AI adoption starts with responsible data stewardship.  At Cimatri, we guide associations in establishing governance frameworks that ensure tools like Copilot are used securely and ethically.  Clear policies on data classification, access controls, and prompt hygiene are essential to protecting sensitive information and maintaining member trust.”

For associations seeking more structured support, Cimatri’s free guide on Responsible AI for Associations outlines best practices for integrating AI tools while maintaining compliance and trust.

Working with Raw Data Exports: Chapter Event Registrations

This familiar scenario provides a practical starting point for working with Excel Copilot.  A U.S.-based association receives a registration export from a Canadian chapter event held on February 15.  The file contains over 350 records, with fields such as registrant name, member type, ticket type, registration date, amounts in CAD, and optional donations.

Sample Data:

As is often the case with raw data exports, the file requires refinement before it is ready for analysis.  Calculated columns need to be added, currency amounts converted to USD, and the dataset structured to support internal reporting and visualization.

Excel Copilot helps complete these tasks with greater speed and consistency. The following demonstrations offer clear prompts and expected outcomes for common association data workflows, showing how Copilot can streamline the data preparation process.  Along the way, a few unexpected behaviors may arise, but simple workarounds or follow-up prompts typically resolve them without issue.

1. Format the Dataset as a Table

Why this matters:

Tables provide structure that Copilot can reference reliably.  They enable dynamic formulas, consistent formatting, and accurate referencing by column name.

Steps:

1. Click on any cell in the dataset.

2. Press Ctrl + A to select the full dataset.

3. Convert the dataset to an Excel Table using Ctrl + T.

4. In the dialog box, ensure that My table has headers is checked.
   
   
   
   
5. Within the Table Design ribbon, rename the table to EventRegistrations.
   
   
   
   
6. To prepare for converting amounts from CAD to USD, rename the Price and Donation columns to Registration Price (CAD) and Donation (CAD), ensuring clarity in both currency and purpose.

Expected Results:

Copilot will use structured references when generating formulas.  All additions will inherit the formatting and logic of the table automatically.

2. Create a Combined Total Column

Why this matters:

Combining values simplifies summaries and analysis, especially when evaluating registrant value.  In this scenario, we want to combine each registrant’s registration fee and donation into a new column labeled Total (CAD).  This allows us to view overall value per registrant in one field.

Steps:

1. Click the Copilot button to open the pane.

2. Enter the prompt:
   Add a column that calculates the total of Registration (CAD) and Donation (CAD) for each row.  Name it Total (CAD).
   
   
   
   

3. Review the generated formula and sample results.  It should sum the two fields using structured references.  Click + Insert Column to add it to your table.
   
   
   
   

Expected Results:

A new column appears labeled Total (CAD), summing both values for each row.  Any new rows added to the table will automatically include this formula.

3. Extract Registration Month and Year

Why this matters:

Extracted date parts assist with period-based analysis, such as monthly trends or fiscal reporting, particularly for display in charts or pivot tables.  In this case, we want to create two new columns: one showing the full name of the registration month, and one showing the registration year.

Steps:

1. In the Copilot pane, type:
   Add two columns that show the registration month and registration year based on Registration Date
   
   
   

2. Copilot will generate formulas using the TEXT and YEAR functions and display sample results.   If the output looks correct, click + Insert Column to add the new columns to your table.
   
   

Expected Results:

Two columns appear: Registration Month and Registration Year.  Each row now includes time-based fields you can use in reports or summaries.

During this process, we also notice an outlier: one registration date falls in May, which is inconsistent with the event date in February.  This highlights how even simple transformations can reveal data quality issues.  After confirming with the chapter that the correct date is February 5, we can update the record.  The Registration Month field will automatically reflect the correction.

4. Calculate Days Before Event

Why this matters:

Lead time reveals behavioral insights about early or late registrations, informing outreach and planning.  In this example, the event was held on February 15, 2025, and we want to calculate how many days in advance each person registered.

Steps:

1. Use Copilot to create a new column with this prompt:
   Add a column called Days Before Event that calculates the number of days between Registration Date and February 15, 2025.

2. Copilot will suggest a formula that subtracts the registration date from the fixed event date and display sample results.  If everything looks accurate, click + Insert Column to apply the changes and add the new field to your table.
   
   

Expected Results:

Each row displays the number of days before the event that a registration occurred, revealing registration patterns and timing.

5. Highlight the Top Three Donors

Why this matters:

Recognizing top contributors is important for stewardship and reporting.  Highlighting them visually is a simple way to ensure they are noticed.

Steps:

1. Enter the prompt:
   Highlight the top three values in Donation (CAD) with a light green fill and bold font.

2. Confirm that Copilot understands your request and adjust if needed.  Otherwise, click the Apply button.

   

Expected Results:

Top donors appear with distinctive formatting.  Updates to donation values will trigger automatic re-highlighting.

6. Identify Duplicate Registrants

Why this matters:

Duplicates can inflate revenue reports or skew counts.  Visibility supports accurate validation.

Steps:

1. Use this Copilot prompt:
   Highlight rows where the same name appears more than once in the Registrant column. Use a yellow fill and bold text.

2. Review the highlighted entries and confirm whether duplicates are expected.

3. Clean or comment as needed.

Expected Results:

Duplicate entries should be clearly marked for review.  However, in this case, Copilot did not complete the request as expected on the first attempt.  While it did offer step-by-step instructions to manually create the formatting rule, the rule it suggested did not work when applied.

An alternative method proved more effective.  Copilot can also be accessed directly from within a cell: selecting a cell reveals a small Copilot icon nearby. Clicking this icon opens a contextual menu with options such as Suggest Conditional Formatting.

Selecting that option opened a list of suggestions in the Copilot panel, including Highlight duplicate cells in column 'Name’.  

Choosing that recommendation applied the correct formatting rule successfully.

7. Convert Amounts from CAD to USD

Why this matters:

Cross-border transactions require standardized currency reporting.  In this scenario, the transaction currency is CAD, but the association’s functional currency is USD. 

Steps:

1. Ensure that the workbook includes a reference table named ExchangeRates containing two columns: Date and Exchange Rate.  This table should list daily exchange rates to support accurate lookups based on registration date.
   
   
   
   

2. Use Copilot to create a new column with this prompt:
   Use the ExchangeRates table to convert Registration Price (CAD) and Donation (CAD) to USD based on the Registration Date.  If no exact match is found, such as when the registration falls on a weekend or holiday, use the most recent available exchange rate as of that date.  Add columns for Exchange CAD:USD, Registration (USD), Donation (USD), and Total (USD).

3. Copilot will use lookup logic to find the correct rate and apply the conversions.  If everything looks accurate, click + Insert Column to apply the changes and add the new fields to your table.

   

Copilot will use lookup logic to find the correct rate and apply the conversions.

Expected Results:

Four new columns should appear, providing converted values in USD alongside the original CAD amounts.  However, in this case, Copilot initially added only the exchange rate column. 

The following additional prompt, with clearer instructions, successfully generated the three additional USD columns.  

Add three new columns to the EventRegistrations table:

• Registration (USD): divide Registration Price (CAD) by the corresponding Exchange CAD:USD value

• Donation (USD): divide Donation (CAD) by the corresponding Exchange CAD:USD value

• Total (USD): sum the Registration (USD) and Donation (USD) columns

As shown in the graphic, Copilot did not apply currency formatting to the new fields, although this step was not explicitly included in the prompt.  In this case, applying the correct format manually proved to be faster and more efficient than requesting it through Copilot.

Copilot is not perfect.  While its capabilities are constantly improving, it may occasionally require clarification or repetition.  Clear prompts usually produce reliable results, but be prepared to refine your request when needed.

Recommendations for Better Results

While Excel Copilot significantly accelerates routine analysis, it is not without limitations.  In the scenarios presented, Copilot occasionally required additional clarification, such as generating only a partial result or getting stuck on a formatting request.  In each instance, these issues were resolved through follow-up prompts or minor manual adjustments.

To improve reliability and reduce rework:

• Use descriptive, consistent names for Tables and Ranges

• Be specific when describing the source data, calculation method, and desired output

• Treat prompts as an iterative process; refine as needed to guide Copilot’s logic

• Review all generated outputs, especially formulas, before use in reporting

• Format manually when necessary, as Copilot does not always apply formatting correctly

• Expect occasional errors or partial completions. Copilot continues to improve with use

While Copilot handles common functions such as SUM, IF, and XLOOKUP with confidence, complex calculations or advanced Excel constructs may require more guided input or manual intervention.

The most effective use comes from pairing Copilot’s speed with professional judgment, structured workbooks, and clear analytical intent.

Closing Notes

Mastering Excel Copilot begins with using it for simple, repeatable tasks that enhance efficiency, consistency, and spreadsheet quality.  Foundational skills, such creating calculated columns, applying basic formatting, and performing data lookups, establish the confidence and precision needed to use Copilot effectively.

With these basics in place, Copilot becomes more than just a time-saver.  It supports scalable, reliable work across a range of finance processes and prepares teams to tackle more advanced use cases with clarity and control.

The next article in this series will explore how to write effective prompts, using examples drawn from everyday scenarios in association finance.