Overview
If you’ve ever searched for a command or setting in an app for more than 10 seconds, you know how frustrating it can be. As pretty as the menu is, as well-organized as all the commands are, odds are you’ll still end up searching quite a bit. This problem is compounded in apps like Excel because there are thousands of actions, commands, and features. I used to notice myself performing the same actions over and over again, so I’d write a macro and set a keyboard shortcut to it so I didn’t have to search as much. It worked great! That is, until I ran out of letters to assign shortcuts to.
I noticed several of them were similar, so I decided to consolidate them into what I call “pickers.” They save me a tremendous amount of time in Excel (Word and PowerPoint too!). In this article, we’ll look at what they are, why they are helpful, and a few examples.
What is a picker?
A picker is a macro that displays a prompt with several options or commands you can easily run. The intention is to complete your task without using the mouse, which is proven to be significantly slower than using your keyboard. Below are the four key components of pickers.
They show several related commands in one place
Take a quick look at the screenshot at the top – it’s called the Sheet Action Picker. Notice several sheet-related commands like jumping to the first sheet or last sheet. These used to be two separate keyboard shortcuts – now they are both available with just one shortcut.
Run commands by entering 1-2 characters
Each option has a character code to run it – usually just one character. Often you can add a character or two to control how it works, and sometimes you can add more context like option R above to rename the sheet – you can add the new name too! Enter the character(s), press Enter, and it runs. No mouse needed!
They add extra features
While many commands run native app functions, some also add new features. In the Sheet Action Picker screenshot above, look at option T. It adds a table of contents with hyperlinks to all the sheets in your workbook. That’s pretty helpful if you have 5+ sheets in your workbook! Option U is another good example – it will unhide all hidden sheets – something you can only do one sheet at a time natively in Excel.
Set defaults
You can set your favorite or most commonly-used command as a default. The default is automatically entered and highlighted when the prompt is displayed. That way you can either just press Enter to run it, or press the key for a different option and Enter to run it – no mouse needed! Notice in the screenshot above, my default is F (for jump to first sheet) – something I use very frequently – especially when the first sheet is a table of contents! You can easily change the default over and over again based on your preferences. Many pickers also let you define custom options, such as your favorite fill colors, number formats, borders, etc.
Why use pickers
Like most software, there are three key reasons why pickers are so helpful.
They save you time
Time (and brainpower!) is saved in four ways. First, using the keyboard for a couple of keystrokes is significantly faster than using the mouse. Second, setting your default options thoughtfully can enhance this (it quickly adds up!). Third, by bringing together related commands, you don’t have to search all over for them. Lastly, most pickers have options you just can’t do natively – these are usually designed to combined steps or complete steps in bulk (like unhiding all the sheets at once).
They reduce errors
Many apps have tiny buttons and icons. It’s so easy to click the wrong button. At best, you must undo and redo, but sometimes that’s more easily said than done. Pickers give clarity as to what the action does, and you can always cancel by clicking the Escape key or the Cancel button.
They make tasks easier
Most pickers include options that can’t easily be done without them (if at all!). In the screenshot at the top, option T is a good example – it adds a table of contents with hyperlinks to each sheet in the workbook. That’s something you probably wouldn’t consider doing manually yourself – it would take several minutes or longer! These enhancements can be quite valuable for producing high-quality, accurate work in an efficient manner.
Examples
Check out the carousel below for 10 examples of commonly-used pickers.
Sheet Action Picker
Key options
1️⃣ Navigation – jump to first/last/previous sheet, auto-complete sheet name, and build a table of contents
2️⃣ Edit – copy, insert, delete, or rename sheets
3️⃣ Visibility – move up/down/specify, hide, and unhide sheets
4️⃣ Select – select sheets based on a highlighted cell list or to the end of the workbook
Filter Picker
Key options
1️⃣ Activating – toggle filters on/off, extend filtered range, clear in one/all columns, and even apply favorite filter criteria
2️⃣ Traversing – filter to previous/next value in filter list, filter to selected value(s) or filter them out
3️⃣ Numbers – filter to numbers/dates, filter to clipboard contents, and filter to zero/blank values or filter them out
4️⃣ Other – filter to fill colors or errors
Copy Special Picker
Key options
1️⃣ Sum of cells – copy the sum of the selected cells (raw or money format) or delimited text values if not numbers
2️⃣ Formulas/etc. – copy the raw formula, formats, values as formula, delimited values, or a screenshot
3️⃣ Address – copy named range, range address, sheet name, file path, or delimited selected cell addresses
4️⃣ Workbook – copy the entire workbook or a shortcut to your clipboard
Paste Special Picker
Key options
1️⃣ Values/formulas – paste all, values, unique values, raw text, formulas, or formulas with absolute values
2️⃣ Formats – paste all formats or number formats/formulas
3️⃣ Helpful – paste data validation or column widths
4️⃣ Custom – paste your most common values/formulas in just one step!
Number Format Picker
Key options
1️⃣ Numbers – accounting, money, or percentage formats
2️⃣ Date/time – short date, long date, or time formats
3️⃣ Helpful – text format or even hidden from user view
4️⃣ Custom – apply your most common number formats in just one step!
Data Validation Picker
Key options
1️⃣ Numbers/dates – apply number or date range restrictions
2️⃣ Text/list – apply text or list restrictions
3️⃣ Prompts – toggle/display input messages or error messages
4️⃣ Custom – apply your most common data validation settings in just one step!
Insert Picker
Key options
1️⃣ Structure – insert files, sheets, rows, columns, cells, or set a named range
2️⃣ Objects – insert a shape, checkbox, picture, or sparkline
3️⃣ Other – insert a link, comment, date, or time
4️⃣ Table/chart – insert a table, PivotTable, or PivotChart
Border Picker
Key options
1️⃣ Side – define the border side(s)
2️⃣ Style – define the border style
3️⃣ Custom – apply your most common border settings (including colors!) in just one step!
Conditional Formatting Picker
Key options
1️⃣ Polish – add banding and variance formats to aid users
2️⃣ Error checks – identify variances, hard-coded values, blanks, and unmatched values
3️⃣ Icons/scales – apply color scales, icons, or data bars easy analysis
4️⃣ Clear – quickly clear conditional formatting from the selection or the entire sheet
Print Picker
Key options
1️⃣ Common settings – set margins, orientation, fit-to-pages, repeated rows/columns, centering, and a common footer
2️⃣ Printing – quickly show the preview, set/clear the print area, or print based on selected cells/sheets/range
3️⃣ Custom – apply your most common print settings to all selected sheets in just one step!
Summary
Pickers are a clever way to save time, reduce the risk of errors, and make Excel (and other apps) easier to use. Set them to run with keyboard shortcuts for the features you use most to give yourself several shortcuts in one. Make sure to take advantage of the default and custom options to make them even more powerful. With 40+ pickers available, the XLEV8 add-in can save you several hours a week with just this approach alone! Try it out today and see what you’ve been missing!
What types of actions do you find challenging in Excel? Would an approach like this help you, or is there something you wish could be added? Please share your thoughts in the comments below!
Recent Comments