In this video, learn how to use the natural language query feature to ask a question in plain English instead of writing a formula to gain insights into your data.
- [Instructor] Sometimes gaining insights into our data requires writing complicated formulas, which can be time consuming if you're good at it. And if you're not good at writing formulas, it can be daunting. Well, there's a new feature here that allows us to use plain English to ask a question of our data to get the results we're looking for. It's called natural language query. We're going to explore it here. I have a spreadsheet open that has quite a bit of data, broken up into quarters, and you can see different line items, estimated versus actual values. If I wanted to, say, for example, understand what the estimated interest income is by quarter, I could write a formula for that, or I can go up here, with the Home tab selected, to the Ideas button off to the right-hand side, to ask a question. Now, the first you'll notice when you click this button is the pane opens up with a bunch of ideas already showing up down below. You can see I can insert a chart if I wanted to, showing the actual by month. That's close to what I'm looking for. Oh, there's estimated by quarter and line item. That might be useful. It's a PivotChart, and clicking down below allows me to insert that. And as I scroll further, I see some other insights and, at the bottom, additional results. And I can show them all by clicking the Show all link, and scroll down. Now, I may find exactly what I'm looking for. It's going to find things maybe I didn't notice, like that quarter one has a noticeably higher prior year variance. That's interesting. Scrolling further down, quarter one and four have higher estimated than normal, kind of interesting. So I'm getting some good information here. But to get exactly what I want, I click up here in the Ask a question about your data field. Click in there, and simply type out a plain English question, looking at the data that appears over here to the left. So I'm wondering what is the estimated interest income by quarter? I'm going to use qtr because that's how it appears over here in my spreadsheet. And a question mark and press Enter or Return, and check it out. Right there, you can see I'm getting the graph or PivotChart that shows exactly what I need. And if I want that included in my workbook, all I have to do is click Insert PivotChart. You're going to notice a new tab gets created, called Idea1, which, of course, I can rename. I'm going to click inside this tab. Double-clicking opens up the Rename Sheet dialog, Idea1 already highlighted. So I can type in what I want, so Estimated Interest by Qtr, like so. Clicking OK creates that label down below. And if I want to, I can use the PivotChart fields and work with the PivotChart the way I want, using filters, et cetera. When I'm done, clicking the Close button brings me back to my ideas. If I'm done with the natural language query, closing that up takes me back to my new tab, where I can see the data that would've required probably somewhat of a complicated formula. If I'm not good at formulas, it's a great way to get results quickly, a new feature here in Excel for Office 365.
- Using natural language queries in Excel
- Getting writing assistance from Editor
- Using the Resume Assistant in Word
- Turning data into maps in Excel
- Setting access to linked files right within Outlook
- Creating better PowerPoint lists with Designer
- Using the math assistant in OneNote
- Working with Microsoft To-Do and Forms