Use AI To Become An Excel Pro

AI is rapidly changing the way we interact with technology and the world around us.

In the simplest of words, AI is how machines perform tasks that typically require human intelligence. Like interpreting and communicating in natural language, recognizing images, making decisions, and learning from experience.

We have outgrown the times of 0 and 1!

You might think that AI is something only super-smart people with specialized knowledge and skills can use. But that’s not true anymore.

Try saying “Hey Siri!” to your phone, and what responds is an AI-powered assistant. Anyone can talk to it without being a tech whiz.

Just like that, there are now plenty of other user-friendly AI systems and platforms available online that anyone can use to take advantage of AI in their personal and professional lives.

Increasingly these are now being used by Excel users. Our recent survey showed that over half of Excel users have used an AI tool in Excel.

In the tutorial below, we will go through some of the most popular AI tools – Excel Formula Bot, ChatGPT, Ajelix, and much more. Make sure you’re tuned in till the end.

We also offer comprehensive Excel courses to keep you up to date with all of Excel’s tools!

How Can AI Help You Use Excel?

Will AI change how the world looks like right now? Yes. Can you take advantage of AI if you’re not a pro at it? Yes!

Artificial Intelligence may sound like a big fat tech word but it is only meant to ease our lives. AI can help you use Excel in a variety of ways. Lets discuss some of them here.

Data cleaning:

AI can help identify and correct errors in your data, as well as identify missing or incomplete data. You can use it to run complex data analysis tools, including predictive modeling, data clustering, and data segmentation.

It can also help you automate tasks in Excel, such as data entry, formatting, and report generation. Ask AI bots to write you scripts and codes to clean your data – sit back and watch the magic happen.

For more on Linking Data in excel instead, read here!

NLP (Natural Language Processing):

With AI-Language models and LLM (Large Language models), the world ahead is all about you asking your bot,

“Hey Bot! Grab some data files from XYZ location, summarize and analyze and narrate to me the key findings”.

And guess what? It’ll be able to do that. With the help of AI, you’d no longer have to be a hardcore programmer to write VBA codes. Or an Excel expert to write formulas.

You can explain that to AI models in simple plain English, and they’ll do the rest. The language barrier of 0’s and 1’s between us and machines is sailing away and will be gone before you know it.

Machine learning:

AI can be used to train Excel to recognize patterns in your data, make predictions, and identify anomalies.

It will help you personalize your Excel experience, providing tailored recommendations and insights based on your individual needs and preferences.

Create datasets using AI:

The functionality of AI is not limited to running defined operations on datasets only. You can use it to create them too. Be precise with your instructions and requirements, and ChatGPT will create a dataset out of it. We will see that happen in the section below.

Write complex scripts / generate formulas:

Excel AI bots are developed by Excel experts to introduce you to the world of Excel data munching.

60% of Excel functionalities and commands are never used by ordinary Excel users because they don’t know how to use them. With AIs, you don’t need to have a whiz-level understanding of every function yourself.

Simply feed your problem to the bots in plain English. And they will devise an Excel formula for that. We will do that with ExcelFormulaBot and Ajelix.com in a bit now.

There is even AI logic in how Excel Recovers Unsaved Files!

Enough of the talk! Let’s now learn how AI is transforming spreadsheeting across the globe, and how you can take advantage.

Excel Formula Bot – How To Use

If you’re a regular (or a newbie) Excel user who has to put his thinking cap on for his everyday Excel jobs – reading this section might help you pass that cap on to a bot.

The bot we call the ExcelFormulaBot.

This AI Chat Bot can help your Excel and Google Sheets jobs in a variety of ways. You can use it to:

1. Generate formulas against natural language instruction.
2. Generate explanations for any formula in plain English
3. Create spreadsheet templates that are up to 10 rows and 10 columns wide.
4. Generate step-by-step instructions for different non-formula requests like creating a graph, a pivot table, etc. If you want to learn how to create a flowchart read here!
5. Generate VBA Codes and scripts to automate repetitive tasks

Lets go through a quick demo of it works.

Here we have some employee-related data in Excel.

Each employee has a unique alpha-numeric code, and we want to extract the numeric codes from each code.

Don’t start scratching your head thinking already! Let’s ask the Excel formula bot to do that for us. For that:

• Go to ExcelFormulaBot.com

• Click on the Login / Signup button on the top left. If you’ve already signed up, use those credentials to log in. You can also take 2 minutes to quickly sign up using your Google account.
• Once signed in, you will be navigated to your Dashboard, which looks like this here.

• Under Generators > Go to Excel Formulas.
• Select Excel (we want to generate this formula for Excel) and Generate (we want to generate a formula) from the relevant questions.
• Now type in the instructions to create the formula in plain English. Use cell references, and names of specific characters, and break the sentences into smaller ones for better results.

We have written our query as follows:

Extract the numeric characters from Cell A2.

Let’s run this query in Excel Formula Bot to see what we get.

Woah! That’s a very long formula.

• Let’s copy and paste the same to our Excel sheet to see if it works.

It works like magic. We get all the numeric characters extracted. This is phenomenal.

But honestly, this is a confusing formula. Let’s ask ExcelFormulaBot to explain it to us.

We have a dense summary of the formula.

This was a quick demo of how you can use the Excel Formula Bot to generate and explain formulas.

And that’s not it, you can bring it to use for a variety of other tasks too.

If we had to write this formula thinking of the solution ourselves, it would seriously have taken ages to get there – thanks to the smart AI.

Possible Downsides:

Excel Formula Bot, though a very smart AI model, has its fair share of downsides too.

Pricing & Subscriptions:

With all due regret, this bot doesn’t offer a free service. The basic free plan has only five requests per month to offer that’ll be used before you know it.

And the paid plans start from $75 to $84 a year.

Difficult to write instructions:

As the requirements grow longer and more complex, it might be difficult to translate them into plain English for a bot.

And as the bot works on instructions, it will be of no use if the instructions are not rightly fed into it.

Limitations of Formulas:

The bot itself says to keep in mind the limitations of formulas. A problem that Excel or Google functions cannot resolve, the bot can’t do either.

Also as the instructions are to be fed by humans, the results are prone to error. As the instructions get longer and more complicated, there is a higher chance of the bot not understanding what is being conveyed.

Keep in mind AI will change the entire landscape of Excel – and a lot of people are already not feeling confident in Excel. Check out our Excel Statistics here for more.

ChatGPT – Making Test Data

Coming down to the talk of the town – ChatGPT.

This advanced AI-driven giant can answer almost all of your queries (unless those are personal or judgment-based). It can talk, it can translate, it can generate text.

It can perform sentiment analysis, summarize texts, and so much more. Also, it remembers the previous input you gave into the chat in one session.

While we all are yet only processing these attributes of ChatGPT, there’s much more that this AI language tool can do. It can create data for your Excel jobs.

Don’t believe it? Lets go through a demo here.

So let’s say, we want to practice running the VLOOKUP function in Excel. But we don’t have a dataset to do that. Let’s ask ChatGPT to create one for us.

• Go to ChatGPT on the web.
• Sign up with your email ID to have your account registered.

P.S. Let’s hope you’re not put on the waiting list before the bot is available to talk to you. Crazy but true – half the world is in the queue to talk to this very smart bot!

• You’ll then be navigated to the chat page where you can send queries to ChatGPT.

• In the Send a Message box, write the following query.

“Please generate a dummy dataset to run the VLOOKUP function in Excel.”

And here’s what happens.

Okay wow! That’s jaw-droppingly fast and smart.

ChatGPT has generated a dataset that has some products, their codes, and prices. Then there’s another table, where there are only product codes.

You can use the product codes in Table 2 to run VLOOKUP and find the product names from Table 1. Like here.

Okay! This was telling ChatGPT to create random data by itself. The world of AI doesn’t just stop there.

You can also customize this data through simple instructions. For example, let’s now ask ChatGPT to make us a dummy dataset for running VLOOKUP that is related to sales of different products.

• So go to the ‘Send a Message’ box and type this in.

Please make me a dummy dataset for running the VLOOKUP function in Excel. I want a table that has five-digit long alphanumeric product codes, product names (should be food items), Sales (in USD), and random transaction dates. The table must be 4 columns wide and 10 rows long.

Those are a lot of instructions that precisely specify the data:

1. Must be four columns wide, and there should be 10 entries to it
2. Column headers should be Product Codes, Product Names, Sales, Dates
3. Product codes should be alphanumeric and five digits long
4. The items will be different food items
5. Dates should be random

With all that dose of information, let’s see how ChatGPT processes it.

Woohoo! Look at that.

The dataset aligns with all the directions fed by us.

And it hardly took ChatGPT 3 seconds to process the same. Had it been us doing that manually, it would have taken us at least 10 minutes.

So, using ChatGPT to make dummy datasets for yourself will save you effort, brain, and lots of time.

Possible Downsides:

However,  using ChatGPT to generate dummy datasets for Excel can have some downsides, too:

Limited Control over the Data:

ChatGPT can generate random datasets in line with a given set of instructions. However, as the dataset continues to grow larger and more complex (with a specific pattern of distribution), you may lose control over the specific characteristics or distribution of the data.

For that, you’d still need more specialized tools and manual intervention.

Distorted Format:

By the end of the day, ChatGPT is only an AI-language model. And while it can generate data, it may not be able to generate data with specific formatting or structure required by Excel.

For example, copy and paste the above data into Excel to see how distorted the formatting gets.

The data is not aligned. Prices are not formatted as currency, the data has a grey background and no borders. It will still take you time to bring the data back in shape and format through manual editing. And if the data is large, a lot of time.

Accuracy and validation issues:

ChatGPT may generate nonsensical answers that are inaccurate or contain errors. This may not be apparent until the data is exported and processed in Excel.

Overall, ChatGPT can be a useful tool (a mind-boggling tool too!) for generating dummy datasets for Excel. But don’t go over-relying on AI machines. Always keep a rigorous validation process intact.

After all, AI bots are created by humans, and errors are human!

Excel AI Tools

This is going to be the most amazing part of this article. Because you probably didn’t know about it before.

We are going to talk about Ajelix.com, the AI tool portal that didn’t make as many headlines as ChatGPT and other AI tools did.

But this just doesn’t bring it down in terms of utility. Ajelix Excel Assistant is a Saas Product designed for Excel and Google Sheets users around the globe. It is meant to help them get through their Excel jobs better, and faster.

To start working with this power AI Excel assistant:

• Log on now to Ajelix.com.
• Sign up with your Google Account (or create an account using any email ID).

• Once you’ve verified your account, you’ll be navigated to the Dashboard.

• From the pane on the left, go to AI Spreadsheet Tools.

Using Ajelix, you can primarily rock the following areas:

1. Create Excel scripts. Long ones too.
2. Create table templates. This function has a limited scope as of now as it only allows you to create a table template that’s a maximum of 5 rows long and 10 columns wide.
3. Add it as an Add-In to your Excel. The add-in allows you to translate the data in your sheet into 28 different languages.
4. The formulas you create or explain using Ajelix can be saved, shared, and collaborated across your circle.

How to use Ajelix.com? Super simple.

Let’s try using it to write an Excel formula.

Here we have a dataset in Excel.

How to write the SUMIF formula, to sum up, the sales for New York before 05 January 2023?

Let’s not think that hard – Ajelix will do that for us.

• From AI Spreadsheet tools, go to Formulas > Generate.

Now we need to explain our situation (and how our dataset is arranged) to Ajelix. Plus, we need to type down our requirements in simple English.

So what do we need?

1. Sum of Sales
2. If the state is New York
3. And if the date is before 05 January 2022

Let’s try writing that in simple words as below:

Sum sales in cells C2:C8 if the corresponding date in Column A is not greater than 05 January 2022 and the category in Column B is “New York”.

Important! Must note how we have referred to the cell references and column references where the relevant data sets are. This is to aid Ajelix in using the right cell references in the resulting formula.

• So let’s write that in and see what happens.

Here is what Ajelix has to suggest about the query posted above.

Not only is it a formula, but it also suggests the possible mistakes one might make with this given formula.

Also, there are alternative other formulas too.

Time to see if this formula works for our data set or not.

• Copy the formula from Ajelix.
• Paste it into your Excel sheet.

And that gives us $325! Which is the right answer, by the way!

Check out here, Excel only adds the sales for New York that occurred before 05 January 2022.

Ajelix works!

This also works in the reverse direction. You can feed Ajelix with any Excel formula, and it will explain it to you. You can also use Ajelix to generate table templates for you – this is a different feature so, let’s see how this works.

For example, you are starting a new business and want a payroll sheet to keep a track of newly inducted employees.

To make a table template for that:

• Go to Templates (Table Ideas) > Generate.

• Set the number of rows and columns that you want in your template. We are setting it to 2 and 7, respectively.
• Write a simple description of how you want the template to be. Like the following:

An employee record master template for managing new hires.

• Hit Generate.

1. Once generated, download it to your system (it will be in .xlsx format).

And here’s what we get.

That’s very smart from AI. We get a table with details like the ID, name, Email Address, Number, etc. Probably, all the details that you’d seek from an employee at the time of onboarding. AI never fails to disappoint us, does it?

Possible Downsides:

With all the quick and smart sides of the AI Tools from Ajelix.com, here are some downsides that you must not lose sight of:

Paid:

Good things come for a price. So do these AI tools. Ajelix is a paid AI tool where different pricing plans range from $7.99 per month to $143.99 per month.

Elsewise, you can run up to 5 free queries each month.

Limited Functionality:

It might get a little difficult to explain what you’re seeking for an AI tool. And until you’ve fed in the right information, Ajelix or any other AI tool may not be able to produce the right formula.

Especially, as the formulas and functions get complex and large, the chances of wrong outputs increase.

AI Use Cases

That’s not all for AI though!

There are so many more ways in which AI can make your Excel jobs a lot simpler, easier, and faster. Let’s discuss some of them here.

Microsoft 365 Copilot:

If you didn’t know about this crazily smart upcoming release by Microsoft, you were missing out on something big.

Microsoft Copilot is designed to bring the next-generation AI to work. It uses a large language model (LLM) to turn natural language commands into actions.

It will be embedded in all the Microsoft 365 apps used daily – Word, Excel, Outlook, Teams, PowerPoint, etc. The hundreds of commands and functions of all these apps that never came to light because users never knew how to work around them, now will.

With co-pilot, users would be able to give natural language prompts like “update the data based on our updated strategy”. And Co-pilot will sort the data based on your morning meetings, emails, statuses, and threads.

Co-pilot in Excel will allow automation of trend analysis and the creation of data visuals in seconds. There’d be no need to hire transcript writers for your Team meetings – Co-pilot will do that in real time making sure all instances of agreements and disagreements are justly highlighted.

With Business Chat, which will be accessible from Microsoft365.com and will work across all your apps, you’ll be able to pick all the relevant insights from a sea of presently untapped data. The world ahead looks so much clearer and brighter.

Predictive Analytics:

Excel is widely used all around the globe for performing predictive analysis on huge datasets. You’d see data experts using Excel to forecast trends, identify patterns and make data-driven decisions.

AI can bring more accuracy to this field. It can be used to identify patterns and trends in large, unstructured datasets that may be difficult or impossible to detect using traditional statistical methods. Machine learning algorithms can identify complex relationships between variables that might not be apparent to human analysts.

Give up on clerical work:

While we see these AI-driven bots acting super savvy, it’d be an understatement to say AI won’t be able to take up clerical jobs.

AI could be used to clean and standardize data in Excel, saving users time and effort. For example, Excel could use natural language processing (NLP) to identify and correct common data entry errors or inconsistencies in data.

Smart Formatting:

Excel could use AI to automatically format data based on its type. This will make it easier to read and analyze data. And not to forget, this will also save users a great deal of time. For example, Excel could automatically format currency values with dollar signs and decimal points, and dates with the appropriate format.

Natural Language Query:

Until a few years ago (or even months ago), natural language queries sounded like a dream tech world of 2050.

However, with the recent developments in AI, we are now living this dream. Excel could now use AI to enable natural language queries, allowing users to ask questions in plain English and receive relevant data in response.

For example, a user could ask “What was our sales revenue in Q1?” and Excel could return the appropriate data from the relevant spreadsheet.

Voice Commands:

Excel could use AI to enable voice commands, allowing users to interact with Excel hands-free. For example, a user could say “Select cells A1 to F10” and Excel would perform the selection automatically.

All in all, AI will help us get rid of the drudgery of work on clerical jobs that offer no productivity, creativity, or innovation. It will help us break free of all the little jobs that zap our time and offer nothing phenomenal in return.

And why not? Humans are meant to innovate and create things like AI – the rest must be done by AI.

Conclusion:

So here we come to an end. Until now, we have seen so much from AI and other advancements in the world of tech that it feels like living in 2030 already!

We learned how you can put ChatGPT to work and not mere talking. It can make some amazing dummy datasets for you. And how you can create amazing formulas, table templates, VBA codes, and Google Sheet scripts using Ajelix and Excel Formula Bot.

AI has taken the world years ahead, and it will keep bettering with time. Keep reading with us to learn how AI gets better and better.

Looking for more tips on Excel? Check out our VBA Introduction here!

Special thank you to Tara and Pixabay for the pictures!