In my career I’ve had to deal with a lot of numbers. I started my work career just as spreadsheets and the personal computer were beginning to be used. So, I remember doing things by hand that are now done with spreadsheets. I’ve been using Excel for over 20 years as it came to become the standard for all spreadsheets. You might call me an expert, but I know there’s still a lot that I don’t know about Excel.
What I do know, I’ve picked up over time from seminars, books, magazines, internet searches and sometimes just clicking on things to see what happens. I’d like to share some of what I know. Today’s post will show you where to find some things that are hidden in Excel. I’ll also show you a tool to use to that will cut down on the amount of clicks and mousing to use Excel commands that you use the most.
The first thing I’d like to share with you is using Excel’s quick access toolbar. Microsoft did a good job of grouping the various commands together by different categories in the ribbons. But when I first started using the ribbons, I found myself jumping between ribbons for the various commands. That led me to start using the quick access toolbar. Let me introduce you to what you can do with this.
If you haven’t used your quick access toolbar, it probably looks something like this.
It’s the part up in the green band. You may have clicked on the dropdown arrow. When you do that, you’ll see a list of different commands. Some will be checked, and some will not. The checked ones are the commands that show up on the toolbar. And that’s how I started using this. Then I found some additional things that turned it into a good tool for me.
The first thing did was look at the dropdown menu. The very last item on the list lets you change the placement of the menu to above or below the ribbons. I changed mine to appear below. My reasoning was that if it’s truly quick access then I want to access it quicker. There ended up being a side benefit to being below also. If you can add more commands before you run out of space when it’s below.
This is where the second from the last item on the dropdown menu comes into play. This is called “More Commands…” and it lets you add in more than just the ones listed in in the dropdown menu. When you click on this, you’ll get a box that looks like this.
From here you’ll be able to modify your quick access toolbar. You can see from the menu at the side that this box can also be accessed from within Excel Options. As is frequently the case, there are multiple ways to do things within Excel to accomplish the same task. Another example of this would be at the bottom of the box towards the left side, there is another way to move your toolbar to below the ribbon.
On the left side at the top there is a dropdown labeled “Choose commands from:”. The default is a list of popular commands. If the command you’re looking for isn’t in this list, you can click the dropdown and choose which ribbon tab the command belongs in and find it that way. When you click this dropdown, also notice there is a category for commands that aren’t on any ribbons. Hidden Excel.
Once you’ve picked and arranged your commands you can click on OK and your toolbar will be updated. Then your Excel may look something like mine. Here’s what my quick access toolbar.
In order here’s what’s on my quick access.
Save As (If you’re wondering why this isn’t put together with Save, I have a reason. If I’m working on a spreadsheet making modifications that I want to save but still retain the original file, the last thing I want to do is accidentally click on Save instead of Save As.)
Select Visible Cells (A command that doesn’t show up in the ribbons. It allows you to highlight a range of cells to copy that include hidden cells without having the hidden cells show up when you paste.)
Add or Remove Filters
Set Zoom to 100%
Zoom to Selection
Insert Page Break
Just above the OK button there is a button that lets you import and export your customizations. This is a way you can save any customizations you make. It’s helpful when you switch computers to be able to quickly set up your toolbar again. You can make a copy of the export file that is saved an import it on another computer.
I have a file of my toolbar. I have placed it here if you want to download it to use for yourself or use as a base to modify. Just be aware that if you import this file if you already have any customizations of the toolbar or the ribbons, you will lose those.
Thanks for reading, Tim