Articles on all things data...

Over 100 hidden tools in Microsoft Excel

 March 16

by Andrew Richards

Unleash the hidden power of Microsoft Excel!

As you probably know, Excel is packed with useful tools and great features.

But did you know that there are so many tools that they deliberately hide some of them away?

Many of these tools are tools which used to be available in old versions of Excel, but have been superseded by more modern versions. So, if you always used to love the "Erase border" button for getting rid of cell borders, but it's not there any more - now you'll be able to add it back in. Yes, you could use the "Borders and Shading" dialog box - but why use a tool which requires 4 clicks where there's a one-click option?

Other tools available are just really useful things which really deserve a bigger audience. 

As an example, take a look at this form, which allows a really quick and easy way to edit data in a spreadsheet:

Excel's data form makes working with data simple

This is a really user-friendly way of working with your spreadsheet data - and it took ZERO setup - just a single click of a button!

It's a tool which, way back in the days of Excel 2003 and earlier, used to be in the "Data" menu, but has subsequently disappeared.

As an aside, how many such tools are there? Well,  when I added this tool into Excel's ribbon, I thought I should check just how many such "hidden" tools exist.... but I gave up counting after the first HUNDRED - and I was still only on tools beginning with the letter E!!! 

I'd say it's a safe bet that there are over 200, and possibly closer to 300!

So, where do you find these tools, and in particular, where is this little data form hiding?

Start by clicking the "Customise" button at the end of the quick access toolbar. It looks like a small downward-pointing arrow (highlighted in yellow here):

The Quick-Access Toolbar can be easily customised
Clicking this button will bring up a selection of commonly used tools, such as "New", "Open", "Save", "Redo" and so on. But under this list of suggested tools is "More commands":
The Customise QAT menu.

Choosing "More Commands" will bring up a dialog box allowing you to select from numerous tools to add to this quick access toolbar. At the top-left of that dialog box is a drop-down box, allowing you to select the types of commands you want to look at, and within this is the option to add "Commands not in the ribbon"

Selecting "Commands not in the Ribbon" opens a huge - and previously hidden - set of tools

Selecting "Commands not in the Ribbon" opens a huge - and previously hidden - set of tools

Adding the Data Form into Excel

Once you select "Commands not in the ribbon" you'll find dozens - no, hundreds! - of tools to choose from. Have a look through - you never know what you might find in here that could be useful.

The data form that I mentioned at the start of this article is simply called "Form". So, I scrolled through the list of all these tools, and then double-clicked on "Form", which then moves across to the right side of the dialog box, and will now appear on the quick-access toolbar:

Double-clicking any item in the "Customise the QAT" dialog box adds it to the Quick Access toolbar

That's it - just click OK to save that change.

Thereafter, your quick-access toolbar will have a new button on the end:

The new Data Form button on the Quick Access Toolbar

Now, any time you're in a spreadsheet containing a list of data, as long as the list has headings, you can just click that button to use the data form. It provides a convenient way to not only see your data, but also a set of tools to allow you to search for a row (click the "Criteria" button), to add new rows and delete rows, as highlighted here:

The Data Form, with its tools highlighted

The Data Form provides a complete set of options for working with your data

Useful?

Hopefully so, but even if you can't see how the Data Form would help you, there may be something else among the hundreds of commands "Not in the ribbon" which you would find helpful. 

It's worth mentioning that Microsoft have (of course!) a great in-depth article about more ways in which you can customise the ribbon. Take a look at Customize the ribbon in Office (microsoft.com) for more guidance and ideas.

share this

Related Posts

Sign up now to be the first to hear about new articles!