TAKEAWAYS
Excel is the go-to tool for most accountants. Whether it is crunching numbers, performing analysis or organising data, accountants rely heavily on Excel to complement tasks done in accounting software. But, what if artificial intelligence or AI – often touted as a game-changer for the accounting sector – were integrated into Excel? Can it improve efficiency significantly? The answer, if you identify the right use case, is a resounding “yes”.
In this article, we will explore how AI can be leveraged in Excel, the built-in functionalities already available, and how integrating third-party AI services can push Excel’s capabilities even further.
Broadly speaking, there are two ways to harness the power of AI in Excel:
1) Through built-in AI functions: These are features that Microsoft has incorporated directly into Excel; they help users without the need for additional software or programming knowledge.
2) Through integration with AI services: This involves connecting Excel with third-party AI platforms, often through APIs (application programming interfaces) or add-ins, offering a wider range of advanced AI capabilities.
1) Built-in AI functions in Excel
Excel already offers several AI-powered features that enhance productivity and make certain tasks simpler. Two notable examples are Flash Fill and Analyze Data.
1. Flash Fill
Imagine you have a list of journal entries, and you need to cleanse the data before analysis. One of the key pieces of information might be hidden in the journal descriptions, such as vendor names. Traditionally, you would either:
Flash Fill is an intelligent tool that extracts or cleanses your data, recognising patterns based on examples you provide.
In our journal entry example, if you manually extract one or two vendor names, Flash Fill will recognise the pattern and complete the task for the rest of the dataset. It is not just faster, it is also more user-friendly.
Enter “Global Enterprises” in cell F2, then press Ctrl + E.
Excel will recognise the pattern almost immediately and repeat it for the rest of the rows in the same column, following the same structure as the initial entry.
If the structure of the source cells is inconsistent, errors can occur. For example, in this case, cells F9 and F10 were extracted incorrectly, including extra information like the expense nature (“ - Utility Payment”).
Do not be disappointed just yet, as Excel can learn from your corrections. Go to cell F9 and manually remove “ - Utility Payment”. As soon as you make the change, you will notice a quick flash through the borders of the cells, indicating that Excel has recognised the correction. Instantly, cell F10 is updated to match the corrected pattern.
2. Analyze Data
For accountants who love working with pivot tables and charts but struggle with the setup, Analyze Data will help you. It automatically analyses your data and offers suggestions to help you gain valuable insights.
To use it, go to the Home tab and click on Analyze Data. This will open the Analyze pane on the right side of the screen. If one of the suggestions meets your needs, simply click “+ Insert PivotTable” or PivotChart. Without having to manually navigate through the pivot table setup, your PivotTable will be inserted instantly.
If none of the suggestions are suitable, you can try asking a specific question to get the insights you need. For example, which are the “top 3 vendors”?
Limitations of built-in AI functions
If you have been using mainstream AI services like ChatGPT, Gemini, or Copilot, you might find Excel’s built-in AI features somewhat limited. While Flash Fill and Analyze Data provide a degree of intelligence, they fall short compared to the capabilities of more advanced AI platforms. Mainstream AI services are continually updated, offering more frequent enhancements and a broader range of features. They excel in areas such as:
For tasks that require these higher-level AI capabilities, you will need to leverage third-party AI services. One option is to submit a prompt along with your data directly to these platforms and then manually update your workbook with the response. Alternatively, you can integrate these AI services directly into Excel, allowing for seamless interaction with third-party AI services.
2) Integrating AI services into Excel
Integrating it with external AI services can open up new possibilities. This can be done through APIs or by using add-ins that act as a bridge between Excel and the AI platform.
1. Using APIs to access AI in Excel
APIs allow different applications to communicate seamlessly with one another. By sending an API request from Excel to an AI service, you can receive AI-powered responses that enhance your data analysis or reporting processes.
For example, imagine you are working with unstructured text data, such as descriptions in financial records. An AI service could help you categorise transactions into the correct general ledger accounts based on the transaction descriptions, or even summarise the nature of these transactions. These types of tasks are beyond Excel’s built-in capabilities but, with the integration of AI services, they become highly feasible.
However, implementing AI in Excel via APIs comes with some costs. You will likely need to pay a subscription fee for the AI service, usually in the form of tokens or usage credits. Additionally, there will be development costs associated with creating the API connection. For those unfamiliar with programming or API development, this method may present a challenge and require technical assistance.
2. Using add-ins for AI in Excel
If you are not familiar with writing code or sending API requests, using an add-in is likely the simplest way to bring AI capabilities into Excel. Add-ins are pre-built tools that integrate directly with Excel’s interface, allowing you to access AI functionalities without needing much technical expertise.
To access add-ins, go to the Home tab and click on Add-ins. You will be presented with a list of available add-ins.
These add-ins can provide AI features such as data analysis, modelling, and NLP, all without the need for coding. While some add-ins are free, others may come with a cost, so it is important to review their pricing and features before deciding which are most suitable for your needs.
AI in Excel is not just a futuristic idea, it is a reality that is already transforming the way accountants work. Whether through built-in features or by integrating external AI services, there are endless opportunities to enhance productivity and accuracy. However, identifying the right use cases and understanding where AI can truly make a difference is crucial. Embracing the power of AI in the right areas will help you complement your accounting skills effectively and achieve significant improvements in efficiency.
Excel is a powerful tool that can help simplify the work of accountants. Power Query, for example, allows users to create connections between a workbook and various data sources. Any changes to the data can be updated simply by clicking a refresh button in Excel to repull the data. Read “Streamline Consolidation with Power Query” to find out how.
Ng Xian Hui, CA (Singapore), is Founder of Backbone Pte Ltd.
© 2024 Institute of Singapore Chartered Accountants