As a Supply Chain Manager, you have probably used Microsoft Excel for inventory control, financial-supply chain planning and a myriad of other tasks.

More often than not, multiple employees and other stakeholders work collaboratively on an organization’s spreadsheets.

One of the challenges you will probably encounter, is that not everybody on your team has the same Excel skills. Formula creation particularly, remains quite a challenge for many users.

However, there is now a simple way to address this common problem. In this tutorial, I will cover how to use two of the most popular AI tools, namely ChatGPT and Formula Bot, for Excel formula generation.

So, let’s get started.

AI and Supply Chain Management

AI has garnered a considerable amount of attention in the media. One of the key benefits of AI is that it can assist with automating and simplifying complex processes. You can also use AI for data analysis purposes.

Now it may be a daunting thought at first, to consider integrating AI into your existing supply chain. You would probably want to test drive it first, in a familiar interface. This is where Excel comes in.

By using AI to simplify the process of formula creation for example, you can get buy-in from your team and other stakeholders to integrate AI into other areas of your work.

Using ChatGPT for Excel Formula Creation

ChatGPT is a popular, natural processing language AI chatbot. This means that you can have human-like conversations with the chatbot to assist you with writing essays, code or in this case generating Excel formulas.

So, let’s see how to do this using a simple example.

A few years ago, I was working as a supply chain consultant for a CPG company. One of my main tasks was evaluating suppliers and the amount spent on the different product categories. We designated suppliers as either preferred for a certain product category, or backup.

Backup suppliers were only utilized, if the preferred supplier couldn’t meet the company’s demand due to the required volume, for example.

In our dataset below we have a list of supplier names, the products they provide, their designation and the amount spent on each specific supplier.

We want to use ChatGPT to give us a formula that will calculate the total amount spent on preferred suppliers, in cell D11.

• So, the first thing you need to do is go to the ChatGPT website and sign up for an account.
• Once you have logged in, you should be taken to the chat page, where you can start sending prompts to the chatbot.

• Type the following prompt in the Message Box.

Create an Excel formula to add the amount spent on only the preferred suppliers. The supplier type is in range C2 to C9. The amount spent for each supplier is in range D2 to D9.

• Click on the black arrow button and you should see the following.

• You can see that ChatGPT has generated a formula for us. So, click the Copy code option to copy the formula.
• Now go back to Excel and paste the formula in cell D11.

Tips for Using ChatGPT for Excel Formula Generation

• Think about what you want to do in Excel in terms of your desired output.
• Provide information in plain English about the type of calculation you’d like ChatGPT to perform (addition, subtraction etc.), the inputs and desired output where applicable.

Limitations of ChatGPT for Excel Formula Generation

• Bear in mind that if you need a complicated formula for a more advanced task then it’s best to create the formula yourself or get someone with advanced Excel skills to create it for you. At the moment, ChatGPT is best utilized for simple formulas.
• The free version of ChatGPT is based on data that goes up to the year 2023.
• You may get a different formula for the same prompt, and at times this formula may not work so always double-check.
• Since ChatGPT is such a popular tool, there are times when it may be quite slow because of high traffic volume. So don’t use ChatGPT if you need formulas for an urgent work-related deadline.

Using Formula Bot for Excel Formula Creation

As the name implies, this AI bot was designed specifically with Excel and Google Sheets formulas in mind. Currently it can create slightly more complex formulas than ChatGPT.

It can also return more than one formula to solve a problem.

Supply chain extends to revenue generation—beyond cost reduction—with focus on the customer.
This implies that the organization’s customers should get the right product, when they need it, at the price they are happy to pay for.

Consequently, evaluation of the customer experience is a critical area of supply chain management and optimization.

I consult in the B2B space, and often have to assist companies with evaluating their customer experience.

So, let’s look at a simple example involving customer experience.

A hypothetical retail store has outlets in several states. The customer service department sent surveys to customers in each state they operate in, asking them to evaluate their customer experience.

They recorded the location, and the general customer experience garnered from data analysis of the survey data. The data was imported into Excel in one column.

In our dataset below, we have the location of the outlets and customer experience given in column A. The state and customer experience in each cell in column A, is separated by a comma.

We want to use Formula Bot to give us a formula or formulas, that will return only the location in terms of state in cell B2 and the general customer experience in cell C2.

So B2, should have the location from cell A2, and C2 should have the customer experience from cell A2 noted.

• So, the first thing you need to do is go to the Formula Bot website and create a free account.
• Once you have logged in, you should be taken to the Dashboard portal page.

• Scroll down to the Generators section. In the green Formulas section, click on the Use Generator option.

• You should see the following.

• Scroll down and while still in the Input Section, check the Excel option. In this case just the formula is needed, so check Generated.

• Type the following instructions.

Split the text in cell A2 containing two words separated by a comma, into two separate words in cell B2 and C2

• Click the Submit button.
• The following output formulas are generated, as shown in the Output section. The bot generates two formulas to solve the problem in this case.

=TRIM(LEFT(A2,FIND(“,”,A2)-1))

=TRIM(RIGHT(A2,LEN(A2)-FIND(“,”,A2)))

• You can copy the first formula by highlighting it in the Output section, and pressing CTRL-C on your keyboard.

You can then return to Excel and paste the first formula in cell B2.

• Drag the formula down the column to see the following.

• Now return to the browser and copy the second formula. Paste it in cell C2 as shown below.

• Drag the formula down the column to see the following.

Note: While there is a newer, dynamic array function called TEXTSPLIT that will do the same thing, these formulas will work in all versions of Excel.

All in all, Formula Bot generated two formulas to solve the problem. This is quite useful, and a level up compared to ChatGPT.

Tips for Using Formula Bot for Excel Formula Generation

• Like with ChatGPT think about what you want to accomplish in Excel and give the bot specific instructions.
• Provide more information about the problem you’d like to solve or the calculation you’d like to perform. In addition, provide the cell references or columns where necessary.

Limitations of Formula Bot for Excel Formula Generation

• The free version only allows one to generate five formulas a month. This is not nearly enough if you constantly need to generate formulas, for work-related purposes.
• For longer and more complicated formulas there is a slim chance that the bot may not understand what you are requesting. So, you should always check for errors.
• The interface is not as user-friendly as some other AI tools. However, it is being constantly updated.

Going Beyond Excel Formulas with AI

Creating formulas with AI is incredibly useful but you might be wondering about what the next step in your AI integration journey is. The answer to that question could be Microsoft 365 Copilot.

Microsoft 365 Copilot is an AI tool developed by Microsoft for Microsoft 365 apps. It is embedded in apps such as Word, Excel, PowerPoint and Teams.

You can use co-pilot for more advanced data analysis in Excel such as analyzing trends, and creating sophisticated charts. You simply instruct the AI, using natural language.

Considering recent developments, this tool seems like it has the potential to be a real game-changer for many Excel users in the future.

Conclusion

AI can open a whole world of possibilities for your organization. However, if you want to start integrating AI into your existing supply chain processes, I recommend starting with simple tasks.

Since Excel is used for both simple and complex data analysis, it’s a good starting point for AI integration.

I hope you found this tutorial useful. Please let us know in the comments below, about your thoughts on using AI with Excel.

A special thank you to Taryn Nefdt for collaborating on this article.

---

About Marcia Williams

Marcia Williams, Managing Partner, has 18 years of experience in Supply Chain, with expertise in optimizing Supply Chain-Finance Planning (S&OP/ IBP) at Large Fast-Growing CPGs for GREATER Profits with Automation in Excel, RPA, & Power BI.

Marcia has helped mid-sized and large companies including Lindt Chocolates, Hershey, and Coty. She holds an MBA from Michigan State University and a degree in Accounting from Universidad de la Republica, Uruguay (South America).

Marcia is also a Forbes Council Contributor based out of New York, and author of the book series Supply Chains with Maria in storytelling style. A recent speaker’s engagement is Marcia TEDx Talk: TEDxMSU – How Supply Chain Impacts You: A Transformational Journey.