Alex Ruben
How To Create Any Excel Formula Without Prior Knowledge Effortlessly
Honestly, trying to whip up formulas in Excel can be a nightmare if you don’t know what you’re doing. But if you’ve got Microsoft 365, there’s this copilot thing that *sort of* makes it feel like magic. It’s like having an AI buddy who’ll generate formulas just from a simple prompt. Not sure why it works sometimes, but hey, it does — or at least, it’s worth a shot.
Step 1: Enabling Copilot in Excel
First off, you gotta turn on the Copilot. Good news — it’s in the options, but it’s not enabled by default.
- Open Excel (make sure it’s Microsoft 365, not some ancient version).
- Go to File > Options. Yeah, that little gear icon or whatever.
- In the Excel Options window, look on the left menu for Privacy & Security (or maybe directly under General depending on your build).
- Scroll down until you see Enable Copilot (or sometimes under a tab called Ai & Data). Check that box.
- Hit OK. Because of course, Windows has to make it harder than necessary.
Sometimes, this step isn’t recognized immediately or it needs a restart. If it doesn’t show up, try closing Excel completely, reopening, and double-checking. On some setups, it fails the first time, then magically works after a reboot.
Step 2: Turn on AutoSave
Nope, Copilot won’t do much if you’re not saving stuff automatically. So, turn on AutoSave in the top-left toggle. Sign into your Microsoft account if prompted, or it won’t sync your prompts right.
Step 3: Creating Your First Formula
Now that Copilot’s turned on and AutoSave is live, it’s time to get some formulas going. Select the cell where you want the magic to happen.
- Click on the Copilot icon (it might be in the ribbon, or as a floating button, depends on version).
- Type in a prompt, like:
Add 18% GST to the total amount in cell A1 and display the result in the next cell. - Press Enter. Watch as it uses AI to generate a formula — I still get a bit surprised each time.
- Copy the generated formula and paste it into your target cell if needed, or just let Copilot handle it.
This trick works because it asks the AI to interpret what you need and turn it into a formula. Weirdly, it doesn’t always nail it on the first try, so don’t be discouraged if you need to tweak the prompt a bit.
Step 4: Play Around with Prompts
If you want to do more than just adding percentages, just type out what you’re after. For example:
- To calculate a total after a discount:
Calculate the total amount after 10% discount. - For more complex stuff, be as descriptive as possible — the clearer, the better.
It’s kind of wild, because sometimes it just gets it right away, and other times you have to guide it a little more. But hey, it’s quicker than trying to write complex formulas yourself if you don’t remember every function.
Step 5: Fill Down or Fill Across
Once you have something that works, you’ll want to copy it. Easy enough:
- Select the cell with the formula.
- Use the fill handle (that tiny square at the bottom right of the cell) to drag down or sideways. Quickest way to apply similar formulas in a row or column.
- Or, if you prefer shortcuts, click the cell, then press Ctrl + D for fill down — I’ve used that too, works just fine.
Sometimes, Excel bugs out if you drag too fast or if the cells aren’t formatted right. So, save often, and maybe double-check that formulas look correct after dragging.
Step 6: For Complex Stuff
Need to split names or do some data analysis? Just tell Copilot:
- Type:
Separate first and last name from B1. - It’ll generate a formula or even suggest using TEXTSPLIT or LEFT/RIGHT functions or whatever is needed.
It kind of feels like cheating, honestly. Still not sure why some prompts work better than others, but if it can get it close, it’s worth a shot.
Extra Tips & Possible Hiccups
Some things to watch out for:
- Be precise: “Add 18% tax to total in A1” is better than “Tax.”
- Play around: Sometimes a different wording makes all the difference.
- If it bombs out: Check if Copilot is really enabled, and consider restarting Excel. Also, sometimes on Win 10 vs. 11, or different updates, things get funky.
Summary
- Turn on Copilot in options (sometimes requires a restart).
- Enable AutoSave for smoother AI prompts.
- Type clear prompts into Copilot to generate formulas.
- Drag formulas or copy paste to apply widely.
- Experiment with prompts for complex formulas.
Hopefully, this can save someone a few headaches — it’s still a bit quirky, but it’s definitely faster than digging through function lists.