5 Excel NON-Keyboard Shortcuts You Wish You’d Learned Sooner

Did you know that the Excel application has been around since 1985? As an almost 40 year old program, Excel has added not only calculation tools and functionality over the years, it has also invested in solid user experience and quality-of-life useability improvements.

To that end, there are many (non-keyboard) “shortcuts” savvy users will employ to save clicks and save time. Here’s a list of several, from familiar to maybe some you’ve not seen before.

Quick Access Toolbar

The Quick Access Toolbar (QAT) sits at the top of your Excel window. With a little customization, you can add almost any command and have its icon available to you from anywhere in the workbook. This is especially helpful if you frequently use a command that’s buried in the ribbon. To customize the QAT, click the arrow at the end of the toolbar and select ‘More Commands.’

Command Search

When you want to do something new, or just can’t remember where a command is without clicking on every tab, the search bar at the top of your Excel window is your friend. Type the name of a command, or a keyword that can help you find it.

In this example, we’ve typed “split cells”, and Excel has included the command “Unmerge Cells” in the options, getting us to an answer without using the exact name. Simply click on the suggestion to execute the command or open the correct dialog. There is also a search box at the top of the right-click menu.

This can save you a lot of time digging through tabs.

Right-Click Menus

And speaking of right-click menus! The right-click options in Excel are contextual and designed to anticipate your most frequent tasks.

This may fall into the category of “obvious”, but if you are a new Excel user, it is likely that you were taught mostly by working through the ribbon. This makes sense, as the ribbon offers you the most variety of options and learning the ribbon is very important for new users. Once you are comfortable with how Excel is organized, begin exploring the right-click menus.

Though they are not customizable, Excel has included many useful commands there, connecting you to common tasks without having to click the ribbon.

Even if you frequently use the most popular right-click options, take a second to pause and read through the whole menu now and then. You may be surprised by the options you find! Such as a search box, for example. Also, did you know that you can create a new comment, or initiate a simple sort right from the right-click menu?

Auto Fill & Flash Fill

Most of us learn quickly how to drag the fill handle across cells to copy content or formulas. This is a time saver all on its own! But the Auto Fill tool can do more than just copy:

• It can fill sequences, such as dates, months, years, etc., and even fill by non-linear sequences such as 10s, 100s, or “every other day”.
• You can drag and copy cell formatting to quickly apply a format to a whole row or column.
• You can create custom AutoFill series, such as a list of store locations or members of a group. In our example, we’ve created an AutoFill series of company titles.

Learn More! Create a Custom AutoFill Series in Excel

5 Excel Data Entry Tips and Tricks You’ll Wish You’d Learned Earlier

Flash Fill takes this to the next level by allowing you to enter rows and columns based on other rows and columns. You might want to create a “Full Name” column, for example, based on other columns that just contain the “first name”, just the “last name”, and so on.

All you have to do is enter the completed result in in the first cell or two and then Excel will offer you a fill result based on that pattern. To accept the fill, just hit Enter.

Quick Analysis & Analyze Data

Quick Analysis tool is your go-to for rapid application of on-the-spot analysis directly from your worksheet. To access the Quick Analysis tool, select a range of data, then click the button that appears in the bottom right corner of the selection. From the dialog window you can choose from Formatting suggestions, Chart & Table suggestions, computation suggestions and even add sparklines. In addition to the most common tools, Excel will recommend charts that specifically fit your data on the Charts tab, or a specific PivotTable layout on the Tables tab. Hover over any of the options to see a preview on your worksheet.

See Also: How to Create PivotTables Using the Quick Analysis Tool in Excel

Learn More: Use Sparklines to Display Trends in Excel

Microsoft Excel Charts and Graphs Made Easy

If you are working with clean, tabular data, the Analyze Data button lets Excel recommend several different ways to view and understand your information. Click the Analyze Data command on the Home tab to open a pane with high-level summaries, trends, and patterns.

In our table that reports on utility bills throughout one year, the Analyze Data pane creates a PivotTable of the “Amount by Payee”, it prepares a bar chart and notices that the EVERGY Payments total is significantly higher than the other payees. Other insights are included and you can then apply any of the examples to your workbook by clicking the +Insert buttons.

At the top of the Analyze Data pane is a text field that allows you to type in a natural language question about your data. Excel offers you suggestions for questions you might ask, or you can type in a description of the analysis you would like.

We asked Excel to show us the total amount of utility expenses per month and it produced a PivotChart showing the total of “Amount” by year and month of the “Date” column. Exactly what we wanted without going through the PivotTable dialogs.

In the current version of Excel 365 (October, 2023) the natural language ability of this tool is quite limited. However, Microsoft is promising integration with its Generative AI technology in the Fall of 2023 that, if lived up to, will greatly increase the capabilities of Excel’s natural language interactions.

What are your favorite non-keyboard Excel shortcuts?