The Data Validation dialog allows you to limit or control what a user enters into a cell. There are two keyboard shortcuts to open it.
Tag Archives: Shortcut key
Change in Ctrl Key Selections
Latest versions
Do you use the Ctrl key and the mouse to select multiple ranges? Well you may want to watch this short video.
Free Excel Webinar Recording – Keyboard Shortcuts
Feedback score 93%
When I ran some face to face training sessions recently I was reminded how much people LOVE keyboard shortcuts. So I decided to update my keyboard shortcut webinar.
In October 2018 I shared lots of keyboard shortcuts. Download the materials using the button below and watch the video.
Using the mouse isn’t always the quickest way to perform tasks in Excel. Keyboard shortcuts can speed up your work and save you hunting through screens and dialogs. Some of the keyboard shortcuts you will learn in this session are:
- wrap text and other useful formats
- applying row and column grouping
- selecting a table quickly
- copying visible cells only
- apply and remove Freeze Panes
- returning after following a hyperlink
- how to avoid an annoying feature of formula and reference dialogs when you press an arrow key to move around
- get the most out of the Tab key
There will be lots of other shortcuts as well. Even if you prefer using the mouse you might learn a few useful new techniques.
Unable to Unhide Columns or Rows in Excel
It may not be a protection issue
There are certain situations when you can’t “unhide” columns on the left and/or rows at the top and it is not a sheet protection issue.
Copying a Date Down a Long List in Excel
Two techniques make it quick and easy
There are a number of mouse and keyboard shortcuts for copying. But there is one type of copy that can be frustrating. Copying dates can be challenging because, in general, Excel wants to increment them, not copy them. There is a simple technique to instruct Excel to copy a date.
Following Links in Excel
A Name Box trick
Excel has a shortcut that allows you to follow a link to its source but it has a limitation find out how to get around that limitation.
Reduce Excel File Size
Ctrl + End is your friend
I recently helped a client reduce the size of an Excel file. The file took a while to save which was frustrating and time consuming. I thought I would share this reasonably easy solution.
Filter by Cell Value Keyboard Shortcut
Its quicker to use the keyboard
I have found a keyboard shortcut combination to one on my favourite right click options and its quicker to use.
Alt Key Shortcuts
Two more
I use Alt key shortcuts a lot when I am working and I have found a couple more useful ones.
Excel and Ctrl + A
A powerful shortcut
This Excel keyboard shortcut is powerful, but it has a few quirks you need to be aware of. Its quick to use because the A and Ctrl keys are so close together.
Excel Full Screen
Maximise your grid area
There are times in Excel when you want to see as much of the Excel grid as possible. You may be reviewing or presenting a dashboard or looking at a large PivotTable. Excel has a Full Screen mode that shows just the grid and the sheet tabs.
Custom List Trick
Create looping sequential codes
It is common to use Q1 for quarter one. Excel will even cycle through Q1,Q2,Q3 and Q4 when you drag a cell contain Q1. What if you want to use the sequence M1 to M12 for months? Custom Lists to the rescue!
Clearing Input Cells with Colour
Let’s say you have a file that has numerous input cells, in numerous sheets. Each month you must clear the input cells and start the new month. There is a technique that allows you to clear the input cells if you have used a dedicated colour for those cells.
Is Excel a drag? [Video]
Maybe a few dragging tips will help
Excel’s Fill Handle has a lot of cool tricks. This short video demonstrates a couple of them.
Getting Excel to Recognise an Email Address
Converting it into a hyperlink
Sometimes when Excel imports email addresses they are not recognised as emails and are not hyperlinks. They are two ways to fix this.
Selecting Cells in Excel Using Comments
Comments are like post it notes for cells. They are a great documentation feature because you can add cell-specific notes to help you and other users understand the spreadsheet better. There are also a few “outside the box” uses for cell comments.
Restricting the Scroll Area in Excel
Non-VBA and VBA solution
If you need to limit where a user can scroll to in a sheet you can change a setting in the VBA screen to restrict access to a specific range.
Printing in Excel
Some tips and tricks
It is easy to print things from Word. It is not so easy in Excel. Excel has this huge grid and if you don’t tell Excel otherwise, it will print everything on the sheet, whether you want it to or not.
Excel Navigation Quick Tips
Making moving around quick and easy
There are numerous keyboard and mouse shortcuts to navigate around your spreadsheet and file.
Excel Data and Formula Entry Tips
Speeding up data entry
There are number of shortcuts you can use to speed up your data and formula entry in Excel.
Number keypad
Use the number keypad on the right of the keyboard. This has all the numbers, as well as most of the formula operators (+ * – /), you need to create formulas. It also has a large Enter key. The numbers are laid out like a calculator and so are easy to use.