Working with Artificial Intelligence Series: Excel with Generative AI
In the current era of remarkable technological advancement, a standout development has been the emergence of generative artificial intelligence (generative AI). This specific subset of AI generates new content to answer almost any request. All it needs from its user is a few words of text.
Each instalment in the Working with Artificial Intelligence Series delves into a specific use case, illustrating how generative AI can be effectively used in various scenarios. We investigate the most suitable tools for each task, reveal the most effective prompts, and share insider tricks to speed up the journey from input to ideal output.
Disclaimers
When using AI tools, remember that you're working with a machine, not a person. AI can offer helpful suggestions, but it doesn't always get things 100% right. Always double-check its answers, validate sources, and follow your workplace's policies or best practices before applying anything it generates. We recommend reviewing and following the Government of Canada's Guide on the use of generative artificial intelligence to ensure responsible and ethical use of these technologies.
In addition, please note that even with the same prompt, AI tools may provide a different response every time they are queried. This means that you may get slightly different responses than those shown in this article.
Introduction
Microsoft Excel is one of the most widely used tools for managing data, analyzing information, and automating repetitive tasks, and is a default software application available on every employee's computer in the Government of Canada. From basic budgets to complex data models, Excel's capabilities are vast. But let's face it, while Excel has hundreds of functions and formulas, finding the exact one you need can be overwhelming, especially if you're not sure what it's called. For example, you might want to combine text from two cells, but unless you know the term "concatenate," you're left searching or experimenting.
For many people, the hardest part of using Excel isn't the math; it's figuring out what to ask the program to do and how to ask it. This is where generative AI (GenAI) becomes your new best friend. GenAI allows you to ask questions in conversational language, transforming the way you use Excel. Instead of searching through help menus or googling for answers, you can describe your goal or problem to a GenAI tool and receive step-by-step instructions tailored to your needs.
Let's explore how GenAI simplifies Excel with three practical examples you can try today.
What is GenAI and how can it help with Excel?
Generative AI refers to advanced artificial intelligence systems that generate human-like text in response to prompts. These tools, like ChatGPT, are designed to interpret and process natural language (meaning how we speak and write).
When paired with Excel, GenAI can eliminate obstacles and allow you to:
- ask questions in plain language: describe your task without knowing Excel terminology
- get instant answers: receive detailed instructions, formulas, or visual guides in seconds
- learn as you go: follow step-by-step guidance to understand the "why" behind each solution
Whether you're a seasoned analyst or a beginner, GenAI offers a faster, smarter, and less frustrating way to work with Excel.
Three simple examples of GenAI and Excel in action
Here are three common Excel challenges and how GenAI can help solve them.
- Combine text without knowing the word "concatenate"
Imagine you're working with a spreadsheet that has first names in Column A and last names in Column B. You want to create a new column with full names in Column C. Instead of trying to remember the CONCAT function or develop a formula, you can simply ask GenAI.
Prompt
"How can I combine text from two cells into one using Excel?"
The AI might respond:
- Step 1: Select the cell where you want the combined text (e.g., C2).
- Step 2: Enter the formula: ‘=A2 & " " & B2'.
- Step 3: Drag the formula down to fill the entire column.
It might also suggest using CONCAT or TEXTJOIN if you have more than two columns to combine, and explain the difference between these approaches, but as you can see, in just seconds, GenAI provides a simple explanation and a formula that works, even if you don't know the term for what you need.
- Calculate the difference between two dates
Date calculations are a key part of working with Excel, whether you are tracking deadlines, project durations, or time between events. Suppose you need to calculate the number of days between two dates in columns A and B. If you're not familiar with Excel's date functions, you can ask GenAI.
Prompt
"How do I calculate the number of days between two dates in Excel?"
Here's how the AI might guide you:
- Step 1: Select the first empty cell in the column where you want the result.
- Step 2: Use the formula: ‘=B2 - A2' (assuming Column A is the start date and Column B is the end date)
- Step 3: Ensure the column is formatted as a number to display the difference in days if it appears as a date.
It may also provide tips like using the DATEDIF function for more advanced scenarios, like calculating months or years.
- Find duplicate values
Duplicate entries can clutter your data and lead to errors. Let's say that you're cleaning up a dataset with thousands of entries, and you need to identify and highlight duplicates. Without knowing about conditional formatting, you can ask GenAI.
Prompt
"How can I find my duplicate values in my Excel spreadsheet?
The AI might provide these instructions:
- Step 1: Highlight the relevant columns (the columns where you want to find the duplicates).
- Step 2: Go to the Home tab, click on "Conditional Formatting", and select Highlight Cells Rules > Duplicate Values.
- Step 3: Select a colour for the duplicates and click OK (e.g., red).
This approach saves you time and not only solves your problem, but also teaches you a valuable feature of Excel that you can reuse in future projects.
The benefits of using GenAI with Excel
If you're cautious and intentional about how you use it, GenAI can be a great tool for Excel users. Here's why:
- It saves time: No need to search through menus or tutorials. You get answers quickly without endless googling.
- It's intuitive: You don't need technical jargon; just describe what you want to do.
- It offers learning opportunities: Because AI tools often provide explanations alongside their answers, you can improve your understanding of Excel over time, gradually building your skills.
What's the catch?
While GenAI sounds amazing (and it often is), there are a few things to keep in mind:
- It's not always right: AI tools can misunderstand your question or give you a solution that doesn't quite fit your situation. For example, they might suggest a formula that works for small datasets but isn't efficient for large ones.
- It's not private: If your workplace has strict data privacy policies, you need to be careful not to share sensitive information with AI tools.
- It doesn't know your workplace rules: Always verify that using AI aligns with your organization's guidelines. Government of Canada employees can refer to the Guide on the use of generative artificial intelligence.
- You still need to double-check: AI won't know if your data contains errors or if you've formatted something incorrectly. It's up to you to validate the results and make sure they're accurate.
- It's not a replacement for learning: While GenAI can save you time, relying on it too much can limit your understanding of Excel. Sometimes, it's worth learning the basics yourself.
Responsible use
While GenAI has the potential to make working with Excel faster and more intuitive, it's not a substitute for critical thinking or technical expertise. Here's how you can use it responsibly:
- Ask specific questions: The more precise your question, the better the answer. For example, instead of "How do I use Excel?" try "How do I calculate averages in Excel?"
- Review outputs carefully: Test any formulas or solutions in a safe place before using them on your main data.
- Try and learn: Use the AI's suggestions and adjust them to suit your needs.
- Refer to workplace policies: Make sure you follow data and usage guidelines when using AI tools.
- Don't upload Government of Canada datasets into public applications: Unless your department has secure software for approved internal use with government data, never upload datasets with personal or protected information into an AI application, especially a public or free one.
Should I use GenAI for Excel?
The answer depends on how you use Excel and what you're trying to accomplish. If you're stuck on a task and need quick guidance, GenAI can be really useful. On the other hand, if you're working with sensitive data or performing a complex task, it might not be the best choice without extra oversight.
Whether or not you use GenAI, the examples in this article show how thinking in simple terms can help you work more efficiently. Start with simple tasks like combining names, finding duplicates, or calculating dates, and explore the tools that Excel offers.
If you do decide to try GenAI, treat it as a helpful assistant, not a magic wand. Validate its suggestions, keep learning, and always try to improve your Excel skills.
After all, the real power lies in knowing how to solve problems—and now you've got a few extra tools to make that easier!
Resources
