copilot in excel asking for data to be put in table format

Copilot in Excel initially wouldn’t do anything unless your data was in table format.


However, Microsoft recently announced some improvements to Copilot in Excel. The biggest one: Copilot will no longer be limited to use in tables. If you format your data with a single row of headers on top, you’ll be able to use Copilot on it.

This change is gradually rolling out to users, and I don’t have it yet, so I’m currently unable to test it. But it remedies one of the major limitations I encountered with Copilot in Excel.

Creating charts

Continuing my testing, I created a table that included revenue data, including monthly revenue. I asked Copilot the most basic of requests: “Chart revenue by month.” I wanted to see whether Copilot would choose the right kind of chart (a line chart), and whether it would then create it for me.

The answer was both yes and no. It knew enough to create a line chart and to select and chart the proper data. However, Copilot created the chart in its side pane, not in the spreadsheet itself. I couldn’t find a way to have Copilot insert the chart into the spreadsheet. I had to ask Copilot to create a new spreadsheet, and then asked Copilot to insert the line chart there by clicking “Add to new sheet.”

At that point I had a spreadsheet that had only the line chart and the small amount of data accompanying it. But I wanted the chart in the original spreadsheet. So now I had to manually copy the chart back to the original spreadsheet. I ended up spending far too much time doing something very simple: creating a basic line chart. I could have saved myself a lot of time by creating the chart myself. Copilot had made me less efficient and productive, not more.


Again, though, a fix appears to be coming. Among the improvements being rolled out, Microsoft says, are “more conversational and comprehensive answers to a wide array of Excel-related questions.” So you should be able to do things such as getting step-by-step instructions for accomplishing certain tasks in Excel. These instructions can include formula examples. Copilot, Microsoft claims, will also be able to correct and explain formula errors it finds.

Because I don’t have the new features yet, I can’t say how well the improved Copilot lives up to these claims — but Microsoft is clearly working toward addressing a major shortcoming of the initial version of Copilot in Excel.

Finding data insights

Even testing its earlier iteration, however, I found one very useful application for Copilot in Excel. Instead of asking Copilot to do a specific task for me — say, create a specific chart — I asked it to find insights that I might not have found myself in an existing table. With a table opened in a spreadsheet, I clicked “Show data insights” in the Copilot pane. It created a bar chart that showed the total number of days that were required to finish each type of task in the table. I most likely would never have found that on my own — I wouldn’t have even thought of looking for it.

It was, however, quite a useful insight. It could help me do a number of things: more accurately put together a project schedule, identify bottlenecks in projects, examine tasks that took the most time to see if they could be streamlined.

I kept clicking “Can I see another insight” and saw other charts— some useful, some not. I still couldn’t directly insert the charts next to my original table — each chart was placed, along with its data, into a new spreadsheet on a new tab, and I would have to copy and paste them into a new tab or spreadsheet if I wanted to use them in other ways. Still, it’s a powerful tool that does a very good job of mining tables for useful, real-world insights.

Copilot is open-ended enough that you should be able to endlessly query data for useful insight and information in this way.

Microsoft claims Copilot can also help with creating complex, advanced formulas. However, I’m not a spreadsheet jockey, so can’t judge its ability to do this.

Copilot in Excel: The verdict

Copilot in Excel falls short in several important ways, especially for people who don’t have advanced spreadsheet skills. It can’t create spreadsheets from scratch based on a person’s needs, which can be the most difficult and time-consuming task for many people. And when you ask it to create charts, it can’t insert them into your existing spreadsheet — it can only insert them into a new spreadsheet in a new tab.

In its initial iteration, Copilot in Excel would only work with data in tables, and it was less than helpful when asked to perform basic spreadsheet tasks. Microsoft is gradually rolling out changes that address these limitations; you may or may not yet have them in your instance of Copilot for Microsoft 365.

All that said, Copilot in Excel can also be quite useful, notably in identifying insights that you might otherwise miss, no matter your level of expertise. And it may be able to create advanced formulas that could be quite useful. Overall, it seems more suited to people who are comfortable with spreadsheets, rather than those who don’t use them on a regular basis.

Copilot in PowerPoint

Could there be anything more dispiriting than encountering an empty PowerPoint screen when you’ve got to build a presentation for a product launch, marketing plan, or any other reason? A blank Word document is bad enough — but there, you’ve only got words to work with. With PowerPoint, there are visuals, slides, transitions, maybe even multimedia…what could be more intimidating?

Copilot in PowerPoint can help. It can draft an entire presentation for you from scratch, or by pointing it at a document. Will the presentation be perfect from the get-go? No. But it’ll give you a solid starting point.

At first glance, there appears to be no way to get Copilot to create a presentation. When you launch PowerPoint, the Copilot icon on the upper right is grayed out, so there appears no way to use it. Pressing the Alt-I key combination, as you do in Word to have Copilot draft a new document for you, won’t work either.

Instead, you have to create a new presentation or else open an existing one, then click the Copilot icon. When you do that, you’ll be asked if you want to create a new presentation from an existing document, create a new slide in the presentation you’ve opened, or create a new presentation based on your description of what you want done.


Creating presentations from a description

Asking Copilot to create a presentation from scratch proved to be significantly problematic. Using the Copilot prompt, I asked it to create a marketing document in presentation form for my imaginary Work@Home business selling home office furniture. I didn’t provide a document as a starting point. Instead, I wrote a brief description of what I wanted: “Create a sales presentation for Work@Home home office products.”

Within minutes, Copilot created a comprehensive 15-slide presentation with a remarkable amount of granular detail and accompanying graphics. One slide, for example, touted the Work@Home line of chairs due to their “Ergonomic design,” “Variety of Styles and Colors,” and “Adjustable Features,” along with detailed, paragraph-long descriptions of each of those benefits.  Other slides did the same thing for Cable Management, Desks, Lighting, Optimal Illumination, Monitor stands, and many more.

It was impressive, given the bare-bones instruction I had given Copilot. Unfortunately, it was all purely a hallucination. Copilot had gone out to the web, done research on its own, and created a presentation that had nothing to do with reality.

In subsequent attempts, I provided more details, including the specific number of slides and what each should say. That led to a presentation without hallucinations. But it didn’t save me much time, if at all. I might as well have created the presentation myself without Copilot’s help.

Creating presentations from existing materials

A better approach is to feed an existing document with the appropriate information into Copilot for PowerPoint. (Copilot in PowerPoint can handle the same file types as Copilot in Word, listed above.) If you’ve got the right document as a starting point, that’s the way to go.

When I used the Work@Home marketing document that Copilot had created for me in Word, I got exactly what I wanted. Within minutes, Copilot built a well-organized, seven-slide presentation, complete with graphics and speaker notes, that closely mirrored the marketing document.


The presentation flowed the way it should, starting with an opening slide and the tagline: Work@Home: The Perfect Solution for Your Home Office. The next slide was an agenda, the next was an introduction, and after that were several slides with the top selling points, another detailing how to order the furniture, and a conclusion. Copilot hit all the high points, made everything succinct, and chose suitable, if quite bland, graphics.

The speaker’s notes left much to be desired — they were just a rehash of what’s on the slide. The graphics were generic and dull. And there were no transitions between slides. Still, it was more than a solid start. For many purposes, you could consider it 75% ready to go; it just needed some polish, new graphics, and animated slide transitions.

As with Word, there was nothing unique here, nothing to knock anyone’s socks off. But it was serviceable. More important, it was a great jumping off point to a solid presentation.

Enhancing presentations, summarizing them, and more

Copilot does more than just create a draft of a presentation. You can also ask it to create new slides, after you describe what you want them to say. Particularly impressive is that it can also gather information by itself about a new slide. I asked it to create a slide explaining that a business might pay for someone’s home office furniture if they work from home. It provided details that I hadn’t suggested, such as asking HR for help in getting reimbursement.

You can also ask Copilot to improve a presentation, for example, asking if there are any slides that should be deleted. I found it was only partially helpful. When I added a nonsensical slide titled “Time to Buy a New Horse” into the presentation, Copilot flagged it immediately. However, when I added one that was off-topic, although peripherally related — “How to Improve Your Posture at Your Desk” — it didn’t flag it.

And as in Word, you can ask Copilot in PowerPoint for a summary or other information about a presentation shared with you by a co-worker in OneDrive.

Copilot can also offer design advice. When prompted, it will have Microsoft’s design tool Designer suggest several design alternatives.


There are limits to what Copilot can do in PowerPoint, though. I asked it to create transitions between slides and it responded, “I’m sorry, but I can’t do that. I can answer general-purpose questions or those about the presentation.” I asked it to find royalty-free images I could add to the presentation without violating copyright laws. Here, too, it said that was beyond the scope of what it could do, but it did point to several web sites that it claimed were good places to find those kinds of images.

In general, I found a thin line between what Copilot will and won’t do in PowerPoint. My recommendation: Ask it to do anything you want, just in case it can do it. You can also type “View prompts” to get a sample of things it can do that you might not have thought of.

Copilot in PowerPoint: The verdict

Copilot can be a powerful tool for creating presentations, notably by creating presentations from scratch based on an existing document. It can be a tremendous time-saver, giving you a first draft of a presentation in minutes with surprising accuracy. Slides follow a logical flow. It also does quite a good job of creating new slides for an existing presentation when you describe what you want added.

That’s not to say it will do everything you want. It won’t add transitions between slides, for example, and it won’t find new graphics. Also, don’t expect knock-your-socks-off creativity. Rather, you’ll get a solid straightforward starting point, which you can then customize and improve.

Keep in mind that if you ask Copilot to create a presentation via your typed-in description rather than by using a document, you may end up with a presentation riddled with misinformation. So whenever possible, use an existing document. And the same caveat applies here as in Word: Double-check your presentation very carefully for errors and misinformation.

Copilot in Outlook

American may run on Dunkin’, if you believe the coffee chain’s sales pitch, but businesses run on email. So Microsoft made sure to integrate Copilot into Outlook to handle the many problems email causes for people — notably drafting emails, responding to emails, and keeping track of complex, often-rambling email conversations.

Composing emails

I started testing Copilot in Outlook by asking it to help me create new emails. To do it, click the Copilot icon on the Outlook ribbon, select Draft with Copilot, and describe what you want done. If you’d like, select the Generate options icon (it’s at the bottom left of the screen and looks like two sliders) to customize the length and tone of the email. You can choose short, medium, or long and a tone of direct, neutral, casual and formal. (There’s also an option to “make it a poem” that clearly is unsuitable for business communications.)

copilot tone and length options for composing email

Choosing options when drafting a new email.


After it generates the draft and you’ve reviewed it, you can make changes yourself, ask Copilot to discard or retry creating a draft, or ask it to make specific changes — for example, to make it shorter or longer, more direct, or more casual.

I found in every instance that Copilot did precisely what I asked it to do, creating well-organized drafts that laid out all the points I requested. Copilot didn’t hallucinate or add errors at any point, even when I wrote an email complaining about data errors someone’s team had made — essentially the same request I had made of Copilot in Word. However, you should always double-check any email crafted by Copilot for errors.

 Although the emails were clear and to the point, they also tended to be quite stilted and came across as being written by an AI chatbot rather than a human being. This happened even when I asked that Copilot use a casual tone. So you’d do well to spend a few minutes editing the draft to make it sound more human and like yourself.

For my ultimate test of drafting new emails, I set Copilot a task every manager dreads: sending a stern message to a team member warning that his work has been subpar, he comes late to work too often, and he misses deadlines. I also asked that he come to my office for a meeting.

Copilot did an excellent job, not only accurately portraying the issues I asked it to address, but getting the tone right — in fact, likely better than I might have done. It’s easy in pressure-filled situations like these to let one’s emotions seep into the email. Copilot, being an AI, doesn’t have emotions, and so the message it sent was less confrontational and likely to be a better received than one that I might have sent. For example, Copilot’s email concluded, “I look forward to our discussion and finding a solution to these issues.” The language may sound stilted, but it was spot on for the task.

Replying to emails

Copilot shines when replying to emails as well, at least if you use it correctly. There are two ways to respond to an email: you can give Copilot specific suggestions about what you want the email to say, or you can allow it to automatically respond on its own, without your input.

I tested the feature by responding to an email sent to me that had a recommendation for new lines of business to help revive Work@Home’s flagging business. First, I had Copilot respond automatically.

It gave me four choices for general ways to respond: “Working on memo,” “Memo attached,” “Need more time,” and “Custom.” The first three ways resulted in a generic, stilted response whose recipient would know it was drafted by AI. I’d recommend never using the first three automated responses, because it could come across as almost offensive to the recipient that you didn’t bother to take the three or four minutes yourself to draft a simple email response.

The fourth choice, Custom, allows you to describe the draft you want Copilot to create. This choice is a winner. I asked Copilot to thank the sender, summarize his recommendations, and ask for a more detailed memo of 1,500 words by the following Tuesday.


Within about 15 seconds, it created a draft that was right on target. Not only did it follow my request exactly, but it put in all the niceties that can make business communications flow smoothly rather than being misunderstood or fraught with tension. It noted that the recommendations “all seem like promising options,” for example. And its phrasing was similarly thoughtful throughout — for example, “I would appreciate your insights and further thoughts on how we can move forward.”

Yes, it’s true those are timeworn phrases and can feel like throwaway clichés. But there’s a lot to be said for simple politeness and directness in business communications, and in this instance, Copilot did a very good job of getting the tone right, even if the phrasing felt at times a bit wooden. That’s why I recommend that all email communications created by Copilot not just be checked carefully for accuracy, but also edited to make each message sound like a human being composed it, not a chatbot.

Before sending any email message drafted with Copilot, whether it’s a new email or a response to received mail, Outlook flashes a warning: “Check the message. AI-generated content may be incorrect, so be sure to check the message before sending it.” Make sure to follow the advice. In my testing, Copilot didn’t make errors, but that may have only been luck.

Copilot also warns you if you send a message without a subject, which can be useful for people who draft so many emails in a day they forget at times to do that.

Email draft coaching

There’s another Copilot in Outlook feature that I found quite useful: having Copilot analyze a draft of an email you’re planning on sending and suggesting recommendations to improve it. To use it, select Coaching by Copilot from the drop-down menu that appears when you click the Copilot icon in the toolbar.

I wrote a rambling, somewhat incoherent, clearly offensive email full of typos critiquing someone’s work, and had Copilot offer its recommendations. It was absolutely on target and pulled no punches, finding serious problems with the tone and clarity of the writing, as well as how the recipient would likely feel after receiving it. It went well beyond generic advice and offered concrete suggestions for improving individual words and sentences.

For example, about the tone it wrote: “Be more respectful. The email is very informal and casual and might make the reader feel like their work is not taken seriously. The email also uses language that is vague, dismissive, and insulting, such as ‘pretty good,’ ‘kind of,’ ‘suck’ and ‘do lot’s better.’”


Summarizing email threads

Outlook also does a good job of helping solve one of the most problematic and time-consuming of email tasks — reading through a threaded conversation made up of multiple emails, and identifying the gist of what occurred.

In my tests, I created a thread between two people, one whom asked the other for recommendations on how to revive the Home@Work business, which was starting to flag because fewer people were working remotely from home. Copilot succinctly summarized every important part of the conversation, including mentioning that a spreadsheet with projected new revenue figures was sent in one email.


Copilot was also able to separate the important topics from less important ones. Within the email thread, I had the two people discuss a concert by the guitar player and songwriter Richard Thompson, including a description of some of the finer points of Thompson’s technique. Copilot recognized that it was a side issue and included that information as a minor aside at the end of the summary. I also included typos in the emails to see whether Copilot would repeat them in its summary. It didn’t; it corrected them each time.

There’s one important thing that Copilot doesn’t do, though — look inside the attachments people send to one another and summarize the data therein. It’s a vital shortcoming, because frequently, more succinct detailed information is available in attachments than in the threads themselves. In this instance, each of the people sent revenue projections to each other. That’s important information that Copilot ignores.

Still, even without that, summarizing email threads is a useful and powerful time saver and productivity booster. For people who don’t need help drafting emails, it will be the most important Copilot feature.

Copilot in Outlook: The verdict

Used properly, Copilot can help you draft new emails and responses more quickly, be more precise in your use of language, and help you choose the right writing tone. It also does a good job of summarizing complex email threads. That being said, its emails generally have a stilted, somewhat artificial tone. You’d do well not to send them as is, but make them sound more natural and personalize them in some way.

Copilot for Microsoft 365: Getting big-picture information

One of Copilot for Microsoft 365’s more intriguing features is its potential to give you a big-picture view of your projects, and then let you drill down into any of them to get more granular information — for example, a specific spreadsheet that has revenue projections for the next five years for new lines of business.

It does this by looking through all your emails and files (including those shared with you by co-workers) based on your request, analyzing what it finds, and then giving you a succinct summary of it all. Based on that, you can ask for more detailed information or follow one of its suggested prompts that recommends data you might want to see.

You do this in the Copilot app itself, rather than starting in Word or another Microsoft 365 app. Make sure you’re signed into your business account in the Edge browser. (If you’re signed into your personal account rather than your business account, it will search the web, not your files and emails.) Then click Edge’s Copilot icon on the upper right of the screen and type in what you’d like it to do. (Note: Your organization may have set up this search differently, so you should check with your IT department when trying to do this.)

I tested out Copilot’s summarization powers with a simple prompt: “Tell me more about Work@Home.” It responded by giving me a clear and accurate description of the imaginary business I had created, and then noted, “There have been discussions on how to revive Work@Home in a post-COVID world,” and briefly and accurately summed summarized a memo I had written about how to do that.

copilot summarizing info from multiple files and apps

Copilot does a stellar job of giving you big-picture information and granular data by looking through all your documents and emails.


So far, so good. It was an accurate summation. Next, I asked it to show me any emails that have details about new lines of business for reviving Work@Home. Again it was on target. It summarized the email and included a link to it. All I needed was to click the link to get to it.

I then asked for any presentations related to Work@Home, and once again it succeeded. It summarized the presentation, identified the presentation’s author and when it was last modified, and provided a link to it.

It wasn’t always perfect, though. I found that I had to offer as precise a description as possible when asking for documents, or it might not find them. But overall, the results were impressive.

Getting big-picture information: The verdict

Using Copilot to gather and summarize information from multiple documents and emails could eventually prove to be one of the most powerful uses of Copilot for Microsoft 365. In a typical workplace, you’ll be involved with multiple projects at a time, and can’t possibly know detailed information about all of them. An ability to quickly get updates on projects and find the specific information, email, or document could be a tremendous time saver and productivity booster.

Copilot for Microsoft 365 extras

Is Copilot for Microsoft 365 right for your business?

Copilot for Microsoft 365 can be an impressive productivity tool, especially for those who often use Word and PowerPoint. In Word it can draft entire documents merely by your prompting it or based on existing documents. The documents are generally well-organized and well-thought-out, and are excellent for first drafts, although editing and rewrites will be required. It’s particularly powerful for those who have a hard time getting started on creative content such as marketing documents. However, it has a tendency to hallucinate, and all its work needs to be very carefully vetted as a result.

It’s helpful in PowerPoint in much the same way as it in Word. You can create first drafts of an entire presentation by merely pointing PowerPoint to a Word document. It can also create new slides based on your prompts. Once you have a presentation, however, it won’t perform tasks for you such as creating transitions. Also, if you ask Copilot to create a presentation from scratch, rather than from an existing document, you may end up with one filled with hallucinations and misinformation. In all cases, as with Word, carefully vet Copilot’s output.

It’s not nearly as impressive in Excel, at least in its first iteration. For those who don’t frequently use spreadsheets, it won’t offer much help — and it has limited capabilities even for spreadsheet jockeys. It does, however, do a fine job of mining spreadsheets for insights you might otherwise have missed, and new features being rolled out may fix its most serious shortcomings.

Copilot for Microsoft 365 will likely be a productivity booster for almost anyone using Outlook, by helping to draft new emails and to respond to existing ones. As with Word, you’ll need to check it for errors, and you should rewrite its sometimes stilted language.

In my tests, Copilot also proved to be surprisingly helpful in providing big-picture information by searching through emails and files. It was equally useful when looking for a specific file or piece of information.

As for the extras, such as transcribing Teams meetings and summarizing notes in OneNote, those are nice-to-haves, not must-haves.

Also note that Copilot currently supports only about 25 languages. Microsoft says more are planned.

Keep in mind that, like the company’s other subscription-based products, Copilot for Microsoft 365 frequently gets new features. In addition to the important improvements to Copilot in Excel already discussed, upcoming Copilot enhancements include prompt autocomplete and rewrite features, as well as a Catch Up feature that can update you about meetings and shared documents. And Microsoft recently announced that users will soon be able to have Designer create images for them in both Word and PowerPoint.

So should your business use Copilot for Microsoft 365? If it were free or included in a Microsoft 365 subscription, the decision would be a no-brainer. It clearly boosts productivity, and for some people will boost it quite a bit.

But it’s not free — far from it. It costs businesses $30 per user per month, and that needs to be paid on an annual basis, not monthly. There’s also no trial version, so there’s no way to test it out for a few months and see whether it’s worth the price.

That $30 per month can increase the monthly outlay by more than 50% for subscribers to the top Microsoft 365 enterprise plan — and by a whopping 600% for subscribers to the entry-level small-business plan. The Copilot add-on fee adds up quickly, even for large enterprises. For a business with Microsoft 365 subscriptions for 500 people, it means an additional $15,000 per month — and enterprises typically have well over 500 seats.

All that said, Copilot for Microsoft 365 includes enterprise-grade privacy and security protections, including Microsoft’s promise that your company’s data and your users’ prompts are not used to train the large language models used by Copilot. These protections are not offered by consumer genAI chatbots.

For most businesses, it probably makes sense to start small, offering subscriptions to targeted users or departments that might get the most out of Copilot — for example, people or groups who most need help in drafting documents and presentations. Based on the results, a company can roll it out to others who might get good mileage out of it.

As for going in whole hog and buying it for everyone who has a Microsoft 365 subscription in your business, that’s an expensive proposition and likely won’t come close to paying for itself in productivity increases, at least for now.

Should you decide to try it now or sometime in the future, there is one caveat that I feel compelled to repeat: Copilot sometimes makes up false information and presents it as fact. You’ll need to very carefully examine everything it does before using its output, and will need to balance that step against any productivity increases you expect.  

You should also consider launching a formal training program for anyone in your business who uses Copilot, targeted primarily at making sure people are aware of potential hallucinations and errors, and teaching best practices for creating documents so they won’t be rife with errors.

Source link