Using the Excel personal assistant

Q. I know that Excel has many great features, but I struggle with being able to find all of them, or even knowing if a feature exists to perform certain operations. Do you have any suggestions?

A. It can be challenging to remember how to locate all the features Excel has to offer, or to even know everything Excel can do. One of the best ways to overcome this challenge is to use what I call the “Excel Personal Assistant.” Excel refers to this as the search feature, which is hardly a worthy description of what it can do. This improved search feature is available with Excel 2016 and later versions on Windows and Mac and Excel for the Web.

The search feature is very easy to use. In the Search box located to the right of the Quick Access Toolbar and above the Ribbon, type in an action you want Excel to perform. Excel will try to determine what action you are referring to and display one or more actions from which to choose. For example, if I want to have Excel insert a table around my data, I will click somewhere within my dataset, then type insert a table in the Search box. See the screenshot below for the options that appear.

As you can see, five sections appear in these Search results. The first section is called Best Action. This section lists the action Excel thinks is the best choice based on what you entered into the Search box. In this example, Excel lists Insert Table as the best action, which is what I wanted Excel to do. I would simply click on Insert Table for Excel to complete the action for me.

The second section is called Actions. These are other actions Excel thinks you could have been looking for based on your search term. The third section is called Find in Document. This section will appear if there is text in the spreadsheet and provides an option to search the document for the word(s) you entered into the Search box. The fourth section is called Get Help on. Clicking this will provide Excel help content based on what you entered into the Search box.

The fifth section is called Quick Answer. If a quick answer to what was entered in the Search box is available online, it will be presented here. You can also click on this area, and an online search of the terms listed in the Search box will appear. Lastly, there is an area at the very bottom that says, More search results for [words entered into Search box]. Clicking this will do an internet search of the terms that you entered into the Search box. When I click on Insert Table under Best Action, instantly my data is inserted into a table.

Let’s try a second example. I would like to change the shape outline color of the circle in my spreadsheet to light green. I would prefer to skip trying to find where to do all of this. Instead, I will click on the shape in my spreadsheet and type change shape outline to light green in the Search box. See the screenshot below for the options that appear.

Note that only three of the five sections described above are listed. The section Find in Document is not listed because there is no text on this spreadsheet. The section Quick Answer is not listed because there is no quick answer available online based on the search terms. When I click on Light Green Shape Outline under Best Action, instantly my shape turns to light green.

There are many other features that you can instruct Excel to perform for you. Just a few examples — freeze panes, insert a comment, protect sheet, save as PDF, insert a sparkline — basically, anything you could ask another person to do in Excel. This is a great feature to start using to save you from having to find the location of many features, and you may also learn that Excel can do things you didn’t even realize.

You can access a video demonstration below and accompanying workbook of using the Search feature in Excel.

— By Kelly L. Williams, CPA, Ph.D.