Illuminate Your Nonprofit's Data: Conditional Formatting with Copilot for Microsoft Excel

Understanding Conditional Formatting

Conditional formatting allows you to apply specific formatting to cells that meet certain criteria. This can help highlight important data, identify trends, and make your spreadsheets more visually appealing and easier to understand.

How Copilot Can Help with Conditional Formatting

Copilot in Excel can guide you through the process of setting up conditional formatting, offering suggestions and automating complex tasks. The Conditional Formatting tab in the Excel menu bar can be intimidating for those not familiar with it, as it involves various rules, formulas, and formatting options. Copilot simplifies this by providing step-by-step guidance, suggesting appropriate formulas, and ensuring that you apply the correct formatting rules. This makes the process accessible even for beginners, allowing nonprofits to leverage the full power of conditional formatting without the usual complexity.

A photo of the manual configuration panel for creating a new conditional formatting rule.

Leveraging Copilot for Advanced Conditional Statements

The following examples will go over an objective or conditional statement we want to analyze. I will provide the prompt used for each objective, the suggestion or response Copilot made along with the formula, and then the result.

For this blog, I prompted Copilot to create an Excel file with 100 lines of sample data for a nonprofit use case.

If you prefer to use a larger sample dataset you can download the workbook below. Just keep in mind that the dataset is a financial dataset specific to sales.

Download the Financial Sample Excel workbook for Power BI - Power BI | Microsoft Learn

1. Categorizing Donation Amounts

Objective: Categorize donations into different tiers based on their amount.

Using Copilot:

Prompt: "Categorize donations into High, Medium, and Low based on their amount."

Copilot Suggestion:

Looking at A1:J1001, here's 1 formula column to review and insert in Column K:

Donation Category

Categorizes donations into "High", "Medium", or "Low" based on the donation amount, with "High" for donations of 4000 (dollars) or more, "Medium" for donations between 2000 (dollars) and 4000 (dollars), and "Low" for donations less than 2000 (dollars).

=IF([@[Donation Amount ($)]]>=4000,"High",IF([@[Donation Amount ($)]]>=2000,"Medium","Low"))

2. Flagging Late Donations

Objective: Flag donations that were made after a specific date.

Using Copilot:

Prompt: "Flag donations made after December 31, 2024, as Late."

Copilot Suggestion:

Looking at A1:I1001, here's 1 formula column to review and insert in Column J:

Donation Status

Determines whether each donation is late or on time by comparing the donation date to DATE(2024,12,31) (December 31, 2024).

=IF([@[Donation Date]]>DATE(2024,12,31),"Late","On Time")

3. Identifying High-Impact Donations

Objective: Identify donations that are both high in amount and made to specific high-impact programs.

Using Copilot:

Prompt: "Identify donations that are both high in amount and made to specific high-impact programs."

Copilot Suggestion:

I analyzed data in A1:K1001, and here's what I found:

'Donation Amount ($)' by 'Name' and 'Program' for 'Program' 'Job Training' or 'Housing Support' or 'Youth Mentorship' or 'Food Distribution' or 'Health Screenings', where 'Donation Amount ($)' is greater than 1000

4. Complex Date-Based Conditional Formatting

Objective: Highlight donations made in the first quarter of the year and flag them as "Q1 Donation".

Using Copilot:

Prompt: "Highlight donations made in the first quarter of the year and flag them as "Q1 Donation".

Copilot Suggestion:

The formula =AND(MONTH($H2)>=1, MONTH($H2)<=3) is used to check if a date falls within the first quarter of the year (January, February, or March).

This formula checks the donation date in cell H2. If the donation was made between January and March, it flags it as "Q1 Donation". Otherwise, it flags it as "Other".

=AND(MONTH($H2)>=1, MONTH($H2)<=3)

Conclusion

Using conditional formatting and advanced conditional statements in Excel can significantly enhance the data analysis capabilities of nonprofits. Copilot in Excel makes these features accessible even to those new to Excel, streamlining the process and offering intelligent suggestions. By leveraging these tools, nonprofits can better manage their data, identify trends, and make informed decisions about donations, volunteers, and program effectiveness.

Call to Action

Ready to simplify your data management and take your analysis to the next level with Copilot in Excel? Start exploring these features today and see how they can transform your nonprofit's data analysis capabilities.