blog
Future-proofing your Excel skills for AI and Copilot
Posted 20 March 2024
Written by George Mount
If you’re a data analyst looking to future-proof your skills and stay ahead in the age of artificial intelligence, one valuable resource might be closer than you think: Excel. Microsoft is continuously enhancing this tool with new features, most notably with Copilot, which offers AI assistance for tasks ranging from data cleaning to analysis and visualization.
However, if you’ve already experimented with AI tools like ChatGPT, you’ll be aware that they don’t always deliver perfect results. It’s crucial to view AI as an assistant; while it can provide support, the responsibility to make critical decisions and execute significant improvements rests with you. Utilizing Copilot in Excel means mastering specific tools and features. Without expertise in these features, you’ll find it challenging to guide Copilot effectively when it errs and to maximize its potential.
Preparing your Excel skills for AI and Copilot
- Tables and structured references.
To work with Copilot, your data has to be in tables; otherwise, it won’t function. Make a habit of organizing your data into table formats and leveraging these for your calculations, sums, and more. This might be a bit of a shift if you’re accustomed to Excel’s classic alphanumeric cell references, but it turns Excel into a more programming-like tool, which is what AI tools like Copilot work best with. - Data cleaning with Power Query.
The AI revolution has underscored a key point: the effectiveness of a model hinges on the quality of its input data. For tools like Copilot to truly shine, your data needs to be in a normalized, “tidy” format.
This means your data should be neatly organized by column, and every cell that’s supposed to have a value, does—no empty spaces just for the sake of layout. The good news is, you don’t need to master coding to get your data in shape. Power Query is a fantastic, free tool right in Excel that can help you clean and prep your data. Before diving into AI, getting a handle on Power Query is a smart move.
- PivotTables and Charts.
Based on personal experience, I’ve observed that Copilot tends to favor presenting its outputs in PivotTables and Charts. This approach makes sense, as these features integrate well with tables, but it also requires users to be familiar with them. It’s important to remember that sometimes AI might not possess the necessary subject matter expertise, or it may not fully grasp the intended goal.
This means you may need to adjust suggested PivotTables, possibly by changing a sum to an average or drilling down on an extra level of detail. When it comes to data visualization, Excel’s charts, developed many years ago by engineers, don’t always reflect the advancements made in the field of data visualization. There’s a significant opportunity to enhance the default charts created by Excel’s AI tools, making them more understandable and actionable.
Therefore, it’s crucial to become adept at manipulating and customizing charts, especially PivotCharts, to ensure your data visualization is as effective as possible.
- Bonus! Python.
After years of anticipation and discreet hints from Microsoft, the official integration of Python into Excel has finally arrived. This integration opens up numerous possibilities for tasks that were challenging or even impossible to achieve with native Excel alone, such as conducting various time series analyses, building statistical models, and employing more modern, customizable plots.
If the thought of adding yet another tool to your AI learning toolkit is overwhelming, don’t fret. However, if you’re eager for a new challenge but feel somewhat daunted, there’s no need to worry. Many of the Python packages designed for data analysis are geared towards individuals without a formal computer science background, allowing you to accomplish a great deal with just a single line of code.
Moreover, with generative AI and Copilot at your fingertips, you’ll receive ample assistance in refining your Python code to meet your objectives. Additionally, the abundant training data available online for Python means that generative AI tools are particularly adept at working with this language, potentially deepening your understanding of AI mechanics through this skill.
I’ve shared a wealth of information with you, but it’s important to approach it step by step. The key takeaway: diving into AI for Excel isn’t necessarily advisable for beginners. Ensure you have a solid understanding of the basics first.
About the author
George Mount
View George Mount’s profile
