May the Power Be with You with Power Query in Excel (With Instructional Video)
Data cleaning is a crucial step in the data analysis process, but it can be time-consuming and tedious. Fortunately, Power Query in Microsoft Excel offers a powerful solution to automate data cleaning, saving you valuable time and effort. In this blog, we'll explore how to use Power Query to streamline your data cleaning process and ensure your data is ready for analysis.In this blog, we'll explore how to use Power Query to streamline your data cleaning process and ensure your data is ready for analysis. To complement the blog, we've also included an instructional video that walks you through the key steps, making it even easier to follow along and apply what you learn. Instructional Video: Blog: What is Power Query? Power Query is a data connection, cleaning, and shaping technology that is part of the Microsoft modern analytics suite of business intelligence tools. It allows you to connect to various data sources, transform and clean the data, and load it into Excel or Power BI for further analysis. Benefits of Using Power Query Automation: Once you set up your data cleaning steps, Power Query can automatically apply them every time you refresh your data. Efficiency: Power Query simplifies complex data cleaning tasks, reducing the time and effort required. Consistency: By automating the process, you ensure that your data cleaning steps are applied consistently every time. Getting Started with Power Query Step 1: Launch Power Query and Connect Data To launch Power Query in Excel: 1. Open Excel and go to the "Data" tab. 2. Click on "Get Data" and select the data source you want to connect to (e.g., Excel, CSV, database). For this demo we will use From File > From Excel Workbook to connect to a single Excel source. 3. This process launches a Windows Explorer window, where you can navigate to the source data's folder and select the workbook to Import. Power Query supports a wide range of data sources, including Excel files, CSV files, databases, and online services. Select your data source and follow the prompts to connect to your data. 4. The Navigator window launches, showing the selected file's contents. Most data sources follow this general layout, with different types of objects represented by different icons. For an Excel workbook, the object types include worksheets and tables. 5. Select the Transform Data button. This selection launches the Power Query editor window. Step 2: Clean and Transform Your Data Once your data is loaded into Power Query, you can start cleaning and transforming it. Here are some common data cleaning tasks you can automate with Power Query: Remove Duplicates: Identify and remove duplicate rows from your dataset. How to: In the Power Query Editor, select the column with duplicate values, go to the "Home" tab, and click "Remove Duplicates." Filter Rows: Filter out unwanted rows based on specific criteria How to: Click on the filter icon in the column header and select the criteria to filter out unwanted rows. Replace Values: Replace incorrect or missing values with the correct ones. How to: Right-click on the column with incorrect values, select "Replace Values," and enter the old and new values Split Columns: Split a column into multiple columns based on a delimiter. How to: Select the column to split, go to the "Transform" tab, and click "Split Column" > "By Delimiter." Choose the delimiter and click "OK." Merge Columns: Combine multiple columns into a single column. How to: Select the columns to merge, go to the "Transform" tab, and click "Merge Columns." Choose a separator and click "OK." Step 3: Apply and Save Your Changes After cleaning and transforming your data, click "Close & Load" to apply the changes and load the cleaned data into Excel. Power Query will save your steps, so you can refresh the data and reapply the same cleaning steps automatically. Conclusion: Power Query is a powerful tool that can automate and streamline your data cleaning process, making it easier to prepare your data for analysis. By following the steps outlined in this guide, you can save time, ensure consistency, and focus on deriving insights from your data. Start using Power Query today and transform your data cleaning workflow!What’s Included with Microsoft’s Granted Offerings for Nonprofits?
Are you a nonprofit looking to boost your impact with cutting-edge technology? Microsoft is here to help! From free software licenses to guided technical documentation and support, this program offers a range of resources designed to empower your organization. In this blog, we’ll dive into the incredible tools and grants available to nonprofits through Microsoft, showing you how to make the most of these generous offerings. Whether you’re managing projects or just trying to simplify your day-to-day tasks, there’s something here for everyone. Let’s explore what’s possible!Copilot in Excel: Transforming Nonprofit Data Management
What is Microsoft Copilot in Excel? Copilot in Excel is like having a data expert built right into your spreadsheet. Using artificial intelligence and natural language processing, Copilot helps you ask questions about your data, automate routine tasks, and quickly generate reports — all without needing to be an Excel power user. Instead of spending hours figuring out complex formulas or cleaning messy data, you can simply type what you need in plain English, and Copilot will assist you. Why Should Nonprofits Care About Copilot in Excel? Here’s how Copilot can help your nonprofit organization work smarter: Save Valuable Time Nonprofits often juggle multiple roles. Copilot can help you automate repetitive tasks like summarizing data, creating charts, or forecasting trends, so you can shift your attention to fundraising, community impact, or delivering services. Turn Data into Insights Donor information, grant reporting, volunteer hours — nonprofits manage lots of data. Copilot can help you quickly find patterns, identify trends, or generate reports that tell your story in a compelling way to funders, board members, or stakeholders. Simplify Complex Excel Tasks Not every nonprofit has a data analyst on staff. Copilot makes Excel easier to use, even for those without advanced spreadsheet skills. Whether it’s generating pivot tables, calculating year-over-year growth, or cleaning up data sets, Copilot can help guide you step-by-step. Support for Grant Writing & Impact Reporting Need to present data in a grant application or an annual report? Copilot can assist in summarizing key metrics, forecasting future trends, and creating visuals that clearly show your organization’s impact — helping you stand out to funders. Real-World Example: Copilot at Work in a Nonprofit Let’s say your nonprofit is managing a donor database in Excel. Instead of manually sorting donations by month and writing formulas to calculate totals and averages, you could simply ask Copilot: “Summarize total donations by month.” “Create a chart showing year-over-year donation growth.” “Highlight donors who gave more than $500 this year.” In seconds, Copilot will help generate answers and even suggest next steps. Getting Started with Copilot in Excel Copilot is available as part of Microsoft 365, which many nonprofits may already use or have access to at discounted rates through Microsoft’s nonprofit program. Nonprofits receive 10 free Business Premium licenses through the program, including access to Excel! Copilot is also now available with a Family or Personal M365 subscription with the current allotment of 60 AI credits per month. To get started: Make sure your organization is using a Microsoft 365 plan that includes Copilot. Open Excel and look for the Copilot icon at the top right. Start asking questions or giving tasks in natural language or take advantage of the pre-built prompts! What Can You Ask Copilot in Excel? Pre-Built Prompts to Get You Started To make things even easier, Copilot in Excel comes with built-in prompt suggestions, so you don’t have to start from scratch. These prompts are designed to help you tackle common Excel tasks quickly. Here are some of the most helpful ones for nonprofit use cases: In 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. Create Formulas: Suggest a Formula Column Copilot can automatically suggest formulas based on the data in your table. For example, if you have donation dates and amounts, Copilot might suggest a formula to calculate "Donation Year-to-Date Totals" or "Donor Lifetime Value." Example: After selecting the Create Formulas pre-built prompt for the sample data I generated for a nonprofit use case, Copilot suggested the following formula: “Looking at A1:M1001, here's 1 formula column to review and insert in Column N: Donation Status2 — Classifies each donation as 'High' or 'Low' based on whether the donation amount exceeds 1000 (dollars).” 2. Understand: Summarize Using PivotTables or Charts Copilot can help you make sense of your data by generating PivotTables and visual charts to highlight patterns and trends. PivotTables are one of Excel’s most powerful tools — but they often intimidate non-technical users. Copilot takes the guesswork out. Example: After selecting the Understand pre-built prompt for the sample data given above, Copilot generated the following: “I analyzed data in A1:J1001, and here's what I found:” 3. Advanced Analysis: Get Deeper Analysis Results Using Python For nonprofits with more technical needs or data-heavy projects, Copilot integrates with Python right inside Excel. This means you can perform advanced data analysis, forecasting, or statistical modeling without leaving your spreadsheet. Example: After selecting the Advanced analysis pre-built prompt for the sample data given above, Copilot suggested that I give more details on what kind of analysis I wanted and of what data. I asked it to add a trend line to donation data over the past year. 4. Apply Color and Formatting: Highlight All Cells with [Incomplete] in Red Copilot can also help with data formatting and cleaning — a common time-drain for many nonprofit teams. You can automate conditional formatting to flag certain data points visually. Example: After selecting the Apply Color and Formatting prompt, I asked Copilot to highlight all donation amounts under 500 in red. This makes your spreadsheets cleaner, more readable, and easier to present to internal or external stakeholders. Why These Prompts Matter for Nonprofits These ready-to-use prompts can help nonprofits: Speed up data analysis even if they don’t have a dedicated data team. Prepare reports faster for funders, board members, or stakeholders. Gain insights to make better programmatic and fundraising decisions. Final Thoughts Nonprofits don’t have time or resources to waste. With Copilot in Excel, you can streamline data tasks, reduce manual work, and make data-driven decisions with confidence — all while focusing more energy on your mission and community.Level Up Your Nonprofit with Microsoft Learn!
That’s where Microsoft Learn comes in! It’s a free, on-demand training platform where nonprofit professionals can level up their skills in key areas like: ✅ Cybersecurity – Learn how to protect sensitive donor and program data ✅ Cloud Computing – Get hands-on with Microsoft Azure to modernize operations ✅ AI & Automation – Discover how tools like Copilot can save time and improve workflows ✅ Data Analytics – Use Power BI to track impact and make data-driven decisions ✅ Collaboration & Productivity – Master Microsoft 365 tools like Teams, SharePoint, and OneDrive Bonus: If you’re looking to get certified, Microsoft Learn offers guided learning paths, interactive modules, practice assessments, and even free certification exam prep—helping your nonprofit’s team earn industry-recognized credentials that boost both confidence and credibility. Ready to upskill and transform the way your nonprofit works? Let’s dive into the keyways Microsoft Learn can help your team thrive! (Be sure to click the links included above and below throughout the blog!) 1. Free Training That Won’t Break the Budget The reality is—most nonprofits are working with tight budgets. Hiring consultants or paying for expensive training programs isn’t always an option. Microsoft Learn changes the game by providing free, high-quality training on essential tech skills, so your team can stay ahead without spending a dime. Whether you want to become a cybersecurity pro, optimize your nonprofit’s cloud strategy, or automate repetitive tasks, you’ll find step-by-step learning modules, hands-on exercises, and even sandbox environments to practice your skills. With zero cost and unlimited access, there’s no reason NOT to start learning today! 💡 2. Content Designed Just for Nonprofits Not all training is created equal, and Microsoft understands that nonprofits have unique needs. That’s why Microsoft Learn offers custom content that focus on real-world nonprofit challenges, such as: ✔ Volunteer Engagement – Learn how to manage and communicate with volunteers using Microsoft 365 ✔ Program Impact Tracking – Use Power BI and other analytics tools to measure outcomes and improve reporting ✔ Accelerate Nonprofit Funding with AI – Learn how to use Microsoft Copilot to streamline grant writing, improve donor outreach, and optimize funding strategies with AI-powered insights. ✔ Introduction to AI Skills for Nonprofits – Discover how AI can enhance nonprofit operations, improve decision-making, and automate tasks to maximize impact and efficiency. ✔ Data Governance – Understand how to structure, manage, and secure your nonprofit's data using Microsoft tools like Purview to maintain privacy and compliance. With these specialized courses, your team can gain relevant, actionable skills that will improve efficiency and amplify your mission! 3. AI & Automation: Your Digital Assistants Are Here! Ever wish you had a little extra help? AI-powered tools like Copilot in Word, Excel, and Teams can automate tasks, analyze data, and boost collaboration—so you can focus on impact instead of admin work. Need to draft a grant proposal? Copilot can help. Want insights from donor data? Copilot's got you. Running out of ideas for your fundraising campaign? Learn how you can accelerate nonprofit funding solutions with Copilot here. Microsoft Learn offers hands-on courses that teach you how to use AI tools to save time, improve accuracy, and enhance productivity—so your team can work smarter, not harder! 4. A Culture of Continuous Learning Tech is always evolving, and staying ahead of the game is key. Microsoft Learn makes ongoing training engaging, interactive, and accessible, helping your nonprofit adapt, innovate, and thrive in an ever-changing digital world. Want to level up your team’s expertise? Encourage staff and volunteers to take Microsoft certification courses—which include: Practice assessments to test knowledge before exams Certification prep courses to guide learning Industry-recognized credentials to showcase expertise Certifications like Microsoft Certified: Security, Compliance, and Identity Fundamentals or Azure Fundamentals can boost credibility and help your nonprofit build trust with donors and partners. Ready to Take Your Nonprofit to the Next Level? Microsoft Learn is your nonprofit’s free ticket to a tech-savvy future—helping you upskill your team, simplify processes, and amplify your mission. So, what are you waiting for? Start learning today and turn your nonprofit into a digital powerhouse! 💻✨ 👉 Explore Microsoft Learn Now!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. 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.Master Excel Like a Pro: Must-Know Tutorials & Templates for Nonprofits
How These Tutorials & Templates Can Help Nonprofits Work Smarter Nonprofits often have to do a lot with limited resources, so finding affordable and efficient ways to manage data is a game-changer. However, knowing how to use Excel effectively can be a challenge. That’s where Excel’s built-in tutorials, templates, and additional learning resources come in—they provide step-by-step guidance and ready-made solutions to help nonprofits: Stay organized – Whether you’re tracking donations or planning events, Excel keeps everything in one place, and templates like the Project Tracker make managing tasks easier. Work smarter, not harder – Automate calculations, generate reports, and analyze data without needing an IT team, with help from formulas tutorials and tools like the Expense Report Basic template. Visualize your impact – Use charts and graphs to turn numbers into stories that resonate with donors and stakeholders, aided by tutorials on Pivot Tables and data visualization. Collaborate effortlessly – Since Excel integrates with Microsoft 365, your team can work together in real time, making tools like the Weekly Schedule Planner even more effective. By leveraging these tutorials and templates, nonprofits can overcome the learning curve and unlock Excel’s full potential, making everyday operations smoother and more efficient. Easy-to-Follow Excel Tutorials If you’re new to Excel (or just need a refresher), Microsoft has free tutorials to help you get the hang of things. Here are some great ways to learn: In-App Guided Tutorials Did you know Excel has built-in tutorials? Just open Excel, click New, and search amongst the tutorials and templates to find interactive lessons that walk you through different features. Some of these include: Pivot Table Tutorial Formulas Tutorial Python in Excel Tutorial Microsoft’s Official Excel Help & Learning Microsoft offers a user-friendly Excel Help & Learning page with step-by-step guides on: Formatting spreadsheets Using formulas to automate calculations Creating charts and pivot tables Automating tasks with macros Microsoft Learn Platform If you’re looking for deeper knowledge, check out Microsoft Learn for structured courses on everything from basic spreadsheets to data visualization and automation. Must-Have Excel Templates for Nonprofits Excel comes packed with ready-to-use templates that can save you time and effort. Here are some of the most useful ones for nonprofits: Project Tracker Stay on top of ongoing projects by tracking tasks, deadlines, and progress in one easy-to-use template. Weekly Schedule Planner Plan out your week efficiently by organizing meetings, tasks, and deadlines in a structured format. Marketing Project Planner Manage your nonprofit’s marketing efforts by outlining key strategies, deadlines, and campaign milestones. Expense Report Basic Keep a clear record of expenses to maintain financial transparency and simplify reporting. Weekly Timesheet Track staff and volunteer hours effortlessly with a weekly timesheet template. Budget for Fundraiser Event Organize and manage your fundraising event budget to ensure financial planning is on track. How to Find and Use Excel Templates Using a template in Excel is super simple: Open Excel. Click New. Use the search bar to find templates like Project Tracker, Weekly Schedule Planner, or Expense Report Basic. Click Create and start using the template right away. Final Thoughts If you’re not using Excel to its full potential, you’re probably working harder than you need to! Whether you’re tracking donations, managing budgets, or planning events, these tutorials and templates can help you save time and focus on what truly matters—your mission. Take a few minutes to explore these features and see how they can simplify your work. Do you have a favorite Excel trick or template that helps your nonprofit? Share it in the comments below—we’d love to hear about it!
Transform data in excel
Hi Recently, my client has shared raw data, which I have processed a bit (attached here for reference). The workbook has 2 sheets: 1. Raw Data 2. Converted Data I have data in the format provided in Raw Data sheet and want to get the data in format provided in Converted Data sheet. Is it possible in excel to get the data in the desired format
