Search for Features with Right-Click

It takes time to learn where all the Excel features are in the various ribbons.  Excel possesses about forty different ribbon tabs, both standard and contextual, to house the hundreds of application features.

If a needed feature is being used for the first time (or hasn’t been used in some time), locating the feature can waste valuable time.

A fast way to locate any feature within the application is to right-click on any cell and use the “Search the menus” option located at the top of the right-click menu.

Excel search the menu feature when in a right-click menu

For example, say you want to wrap the text in a cell but can’t remember where the text-wrapping feature is located.

Right-click the cell with the text and type something like “wrap” in the search box.

Searching for the wrap text feature in Excel

This doesn’t just display the name of the feature that will serve your needs, rather it is the ACTUAL feature.  You can click the listed feature and your needs have been met.

Executing the Excel wrap text option from a search result

When presented with multiple actions in the search results, you can use the up and down arrow keys to navigate the list, then hit the spacebar or enter to invoke the needed action.

Searching for the filter features in Excel

Featured Course

Excel Essentials for the Real World

Learn time-saving hacks to work smarter in Excel. Our members also consistently report a newfound enthusiasm for using Excel after taking our course.
Learn More

Navigation Pane to locate Sheets and Objects

When working with workbooks that contain many sheets, often more than can be displayed at the bottom of the screen, it’s easy to lose track of where charts, shapes, tables, and other objects are located.

 Locating and managing these assets is made easier when using the Navigation Pane.

To activate the Navigation Pane, click View (tab) – Show (group) – Navigation.

Excel's View tab and navigation button

The right side of the screen displays a panel showing all of the sheets, charts, tables, and other objects contained in the workbook.

Excel Navigation pane showing sheets, charts, and other objects

Right-clicking a sheet name will allow you to rename, hide, unhide, and delete the sheet.

Sheet management from the Navigation pane

Hidden sheets will remain listed in the Navigation panel, but the names will appear in light gray to indicate a hidden state.

Clicking the Expand button to the left of a sheet’s name will display a list of all objects contained on the sheet.  These can include (but are not limited to) charts, pictures, slicers, icons, tables, shapes, etc.

Excel Navigation pane showing sheets, charts, and other objects

Clicking on a listed item will navigate you to that sheet and select the object in question.

You can even leverage the search bar at the top of the Navigation panel to locate sheets or objects by name or type.

Using the search feature in the navigation pane

New Keyboard Shortcuts

Open the Power Query Editor

A super-fast way to open the Power Query Editor is to press the keyboard shortcut Alt-F12.

Alt-F12 keyboard shortcut to open the Power Query Editor

Copy and Paste as Values

To copy and then paste the cell results as static text (as opposed to the underlying formulas used to generate the results), you can do this quickly by following up the CTRL-C copy action with a CTRL-Shift-V instead of the normally used CTRL-V to paste.

CTRL-Shift-V keyboard shortcut to paste content as values

This will perform the “Paste as Values” action normally accessed from a right-click, Paste Special menu.

Featured Course

Automate With Power Query – Recipes to solve business data challenges

Do you want to become more confident using Power Query and automate your entire data workflow? Join this course and learn from real-world scenarios.
Learn More

Automate Tasks with Office Scripts

You may have noticed in later versions of Excel a new tab named Automate. It groups Excel’s newer automation features.

Automate ribbon in Excel

The Automate tab allows you to create scripts that will run on Excel for Desktops as well as Excel for the Web.

For example, suppose you need to make a chart based on data where the chart has the same consistent look and type.

With the Automate tab and Office Scripts, this can be performed in just a few clicks.

  1. Highlight the data to be charted
  2. Click the Automate tab
  3. Click “Chart Script” to activate the script created to make charts
  4. Click the Run button to execute the script
Step to launch an Office Script from the Automate tab in Excel

I have several videos for you that provide detailed demonstrations, examples, and instructions for creating, executing, and managing Office Scripts.  Check these links below.

🔗Introduction to Office Scripts

🔗How to Use Office Scripts with Power Automate

You don’t have to be a master coder to create Office Scripts.  The Automate tab provides access to a script recorder labeled “Record Actions” that will write the code for you based on the buttons clicked and typed data.

The Record Actions button on the Automate tab in Excel

Value Preview Tooltip to Evaluate Formulas in Part or Whole

If you are troubleshooting a formula error and can’t figure out where things went awry, a common trick is to highlight part of a formula in the Formula Bar and press the F9 key.

This will replace the highlighted section with the result that the section calculates.

Old way of displaying partial formula evaluation in Excel using the F9 key

This can be dangerous as the F9 action will alter the formula from a dynamic calculation to a static value.  Users must be mindful to hit the ESC key to return the formula to its original dynamic state.

To eliminate that danger, and make the process easier, you can use the Formula Bar to highlight portions (or all) of a formula, then observe the automatic tooltip that appears to see what that portion of the formula works out to.

Value Preview tooltip in Excel

You can select references, functions, parameters within a function, or even the entire formula.

There’s no longer a need to press F9 and run the risk of corrupting the formula.

If you select a part of the formula that can’t be evaluated, you won’t see a tooltip.

Featured Course

Master NEW Excel Functions in Office 365 & Office 2021

Ready to supercharge your Excel skills? This course will help you master the new set of functions introduced to Excel for Office 365. You’ll create professional-grade reports in a fraction of the time it used to take you.
Learn More
Excel new functions course cover

Use Dynamic Array Results in Power Query

Historically, Dynamic Array results could not be used as a data source in Power Query.

That’s not to say that the results couldn’t be brought into Power Query, it’s that Power Query would fail to properly recognize the source data if the results were to expand beyond the originally selected cell range.

That issue has now been rectified with this new feature in Excel 365.

To bring the results of a Dynamic Array formula into Power Query, select the single cell that holds the formula, then click Data (tab) – Get & Transform Data (group) – From Table/Range.

Steps to import Dynamic Array output into Power Query

This brings the Dynamic Array results into Power Query and names the query “FromArray_X” (“X” being the instance number of imported arrays.)

Dynamic Array results in the Power Query Editor

If the results of the Dynamic Array formula change, a refresh of the query will reflect the new formula results.

Use Dynamic Array Results as a Chart Source

In the same vein as Power Query, Excel charts could not utilize the changing range of Dynamic Array output as a data source. Or, at least, it required a lot of effort and complex formulas to set it up. It became much simpler.

Charts can now use this output and automatically update when said output changes.

For example, we have used a series of Dynamic Array functions to create the output seen in cell E5.

Example of a sort and filter formula in Excel using Dynamic Array functions

Next, we have created a chart based on the Dynamic Array output to show the yearly salary for all employees in the “Procurement” department.

Excel chart using user-selected input as the driver for x-axis data

If the department dropdown is changed to “Finance”, the chart updates to reflect the new Dynamic Array output.

Excel chart using user-selected input as the driver for x-axis data

I have a separate video that demonstrates the process of using Dynamic Array output as a chart data source:

📺Effortlessly Create Dynamic Charts in Excel: New Feature Alert!

Cheat Sheet

Download the handy Cheat Sheet with all the exciting new features:

Featured Course

Black Belt Excel Package

What would your life look like if you became an Excel Black Belt? You’ll get the best deal with this (cost savings) package. It will take you from Excel Newbie to Grand Master.
Learn More
Excel Black Belt course cover

Leila Gharani

I'm a 6x Microsoft MVP with over 15 years of experience implementing and professionals on Management Information Systems of different sizes and nature.

My background is Masters in Economics, Economist, Consultant, Oracle HFM Accounting Systems Expert, SAP BW Project Manager. My passion is teaching, experimenting and sharing. I am also addicted to learning and enjoy taking online courses on a variety of topics.