The growing excitement around artificial intelligence (AI) may lead you to wonder about the relevance of your Excel skills in an era increasingly dominated by intelligent machines and automation. I’m here to reassure you that, as AI tools become more directly integrated into Excel, you are ideally positioned to work alongside AI, rather than against it. To facilitate this transition into AI-powered Excel smoothly, however, there are certain prerequisites you must meet. The goal of this post is to discuss those prerequisites.
First, we will examine some Excel features that you should know inside and out as they will form the cornerstone of your AI-powered Excel experience. Following that, we will introduce some fundamental concepts in mathematics, statistics, and AI to help you grasp what AI-powered Excel truly entails. There’s no need for in-depth exploration of complex equations—just a high-level intuition of what terms like “probabilistic models” and “neural networks” mean.
After covering both the conceptual framework and the practical skills, we’ll dive into what to know about the actual use of AI in Excel. This includes managing the uncertainties associated with AI analysis, considering ethical issues in data sharing, and keeping pace with rapid AI advancements.
The Excel basics
Excel tables
To maximize the benefits of AI-powered Excel tools, mastering Excel tables is essential. Almost every application of AI and advanced analytics in Excel functions more effectively when the source data is in tables, or even requires it to be in table format. Starting to utilize tables now is critical for accessing advanced Excel features in the future. Structured references within tables simplify tasks such as data combination and creating calculated columns, utilizing table names and column headers to produce clearer and more manageable formulas.
Power Query
Power Query, Excel’s native Extract-Transform-Load (ETL) tool, streamlines the process of connecting to and transforming data, ensuring it is in a format suitable for AI analysis. It excels at data cleaning by segregating information into separate columns, unpivoting datasets for clearer variable representation, and adeptly handling blank rows, effectively preparing data for AI processing. An AI model will only perform as well as the structured and normalized data it receives, making the ability to shape data correctly crucial.
To delve deeper into the optimal methods for storing data in Excel and how Power Query can assist, explore this post:
PivotTables and PivotCharts
Finally, the transition to AI-driven insights typically involves PivotTables and PivotCharts, as the actual AI tools in Excel tend to utilize these features for their outputs. While AI augments Excel’s analytical capabilities, knowing how to customize the output is vital for tailoring AI insights to specific requirements and enriching them with personal expertise.
I would classify many of these prerequisites as intermediate Excel skills. They are not the sorts of things that beginners are going to know or learn immediately. So, it really does take some time in Excel to reach a point where you’ll benefit from AI-powered Excel. Don’t rush it.
Next, let’s delve into a high-level understanding of how AI works, what it truly entails, and what is meant when we say “AI-powered Excel.”
The math and stats concepts
Probability and statistics
A solid grasp of probability and statistics is crucial when delving into AI-powered Excel. This knowledge provides users with the necessary tools to navigate the uncertainties and biases present in data.
Probability serves to quantify uncertainty by illustrating potential outcomes and their likelihood. Rather than suggesting complete unpredictability, it acknowledges a range of possibilities, aiding users in preparing for various scenarios. Familiarity with probability distributions, such as normal or binomial distributions, is key as they help identify patterns within data, facilitating informed predictions.
However, the accuracy of predictions heavily relies on the quality of the data. Collecting precise and unbiased data is paramount, as inaccurate data can lead to flawed insights. Additionally, biases in data collection or interpretation can skew results significantly. Excel users must rigorously evaluate their data to ensure it faithfully represents the analyzed scenario, thereby preserving the integrity of their conclusions.
This statistical approach extends beyond calculations, fostering a mindset that is both skeptical and open-minded. It encourages continuous questioning of the data’s narrative and its contextual significance. By acknowledging data variability and the potential for misleading conclusions, users can navigate Excel with a more discerning eye.
For a solid resource on the basics of probability and statistics with the experience of the typical Excel user in mind, check out my book Advancing into Analytics:
From AI to LLMs
Next, Excel users should grasp the basics of AI, including its relationship to subsets like machine learning and large language models. This is a complex subject and this post isn’t intended to cover it in depth. For a comprehensive introduction and to better navigate these topics, consider reading Becoming a Data Head by Alex J. Gutman and Jordan Goldmeier.
The previous diagram offers a straightforward visual representation of the relationship between these concepts. Each concept is a field that encompasses the subsequent one. AI is the broadest category, with large language models (LLMs) as the most specialized subset. Let’s examine each of these levels in turn.
Artificial intelligence
AI is the grand field of creating machines that can perform tasks in ways that we consider “smart” or intelligent. This encompasses a wide range of technologies, including simple rule-based systems where the intelligence is entirely predefined by human programmers, as well as more complex systems that can learn and adapt. AI applications can range from simple ones like a computer game where the AI follows a set of rules to play, to more complex ones like autonomous vehicles that need to perceive their environment and make decisions in real-time.
Machine learning
ML is the science of getting computers to act without being explicitly programmed for specific tasks. In traditional programming, a developer writes the code that specifies every step needed to solve a problem. With machine learning, a model is fed large amounts of data and uses statistical analysis to figure out how to complete the task by identifying patterns in the data. This data-driven approach allows the system to improve its performance as it processes more data. Machine learning can be further divided into categories such as supervised learning, unsupervised learning, and reinforcement learning.
Deep learning
Deep learning is a more sophisticated subset of machine learning characterized by neural networks with many layers—hence the “deep” part. These neural networks attempt to simulate the activity of the human brain—albeit in a very simplified form—to process data and create patterns for decision making. It’s the technology behind many advanced AI functions that we see today, such as voice recognition systems (like Siri or Alexa), recommendation systems (like those on Netflix or Amazon), and even medical diagnosis systems that can identify diseases from images.
Large language models
LLMs are designed specifically to understand and generate human language. They’re trained on extensive collections of text and can perform tasks such as translating languages, answering questions, summarizing documents, and generating coherent and contextually relevant text based on prompts. They are considered “large” because they have a massive number of parameters, which are the aspects of the model that are learned from the training data. For example, GPT-3, one of the most well-known LLMs, has 175 billion parameters. The size of these models allows them to have a broad understanding of language nuances, grammar, and even context.
Gaining a deep understanding of the theoretical mathematics and statistics that form the backbone of artificial intelligence and its connections to significant areas like large language models will enhance your proficiency with AI-powered Excel. This knowledge will offer insights into the operation and expectations of AI within Excel. Again, it’s not necessary to master the underlying mathematics. Rather, acquiring a good grasp of how these models function and the impact of their probabilistic foundations on their performance is crucial.
With this theoretical framework in mind, we will explore the practical aspects of using AI-powered Excel and how it may differ from the traditional Excel experience.
The practical implications
Probabilistic results
Integrating AI into Excel introduces a paradigm shift from the deterministic nature of traditional formulas to the probabilistic framework of generative models. These AI models, when applied within Excel, produce outcomes that can vary with each execution. This variability is inherent to their design, as they generate predictions based on probabilities learned from vast datasets. Users must adjust their expectations to accommodate these probabilistic results, recognizing that AI brings a layer of uncertainty that requires a new approach to data analysis.
Quality output depends on quality input
The effectiveness of AI models is deeply intertwined with the quality and quantity of the input data. Unimpressive outcomes from an AI-powered Excel feature might be significantly improved by enriching the model with additional or more relevant data. This underscores the importance of a meticulous approach to data curation, ensuring that the AI has access to the best possible information to inform its predictions.
Trial and error of models
AI’s behavior in processing different types of data can be unpredictable, with some models excelling in areas where others falter, often without transparent reasons. This unpredictability highlights the need for a culture of experimentation and flexibility in model selection. Users should be prepared to test various models, adopting a trial-and-error methodology to discover the most effective solution for their unique data challenges.
Data privacy
Incorporating AI into Excel workflows raises critical questions about data privacy and ethics. Users should exercise caution, ensuring they understand the flow of their data through AI models, including how it is used, stored, and protected. A foundational understanding of AI mechanisms is beneficial for navigating these concerns, empowering users to make informed decisions about the security of their sensitive information.
Staying agile
The AI domain is characterized by swift advancements, rendering today’s innovative tools potentially obsolete tomorrow. For Excel users integrating AI, this means traditional tutorials or guides may not always reflect the latest practices or capabilities. Embracing a mindset of continuous learning and adaptability is essential. Through experimentation and a willingness to explore new techniques, users can stay ahead of the curve, ensuring they leverage AI in Excel to its fullest potential, despite the fast-paced changes in the field.
Conclusion
As you are seeing, starting with AI in Excel introduces several starting points. You’ll need a solid foundation in both practical Excel skills and a high-level understanding of AI concepts.
This journey may seem overwhelming, but it’s not about mastering complex formulas or becoming a data scientist. It’s about leveraging AI-powered features to enhance your Excel experience, adding a layer of what feels like human insight to your work.
Do you have any questions about AI-powered Excel or how to begin with AI in Excel? Are there any prerequisites you would recommend? Share your thoughts in the comments.
If you’ve found this overview helpful, I’m delighted to say there’s much more available on my blog. Explore a range of posts covering modern data analytics in Excel.
For organizations aiming to equip their teams with the knowledge and tools for success in an AI-enhanced Excel environment, I offer corporate training. My sessions are customized to meet your specific requirements, ensuring that your team not only understands AI concepts but also applies them effectively in their everyday tasks. Please drop me a line to get started.
Leave a Reply