In this series we will be looking at the Excel tools and techniques that help you accomplish a range of day-to-day Excel tasks more efficiently and effectively.
As part of each article, we will be scouring the extensive Excel Community archive to provide links to additional details and ideas.
This is the story of the series so far:
Speed up formula entry
- Excel how to: speed up entering formulae part 1 - using the dollar signs to fix all or part of a reference
- Excel how to: speed up entering formulae part 2 - using Range Names to make formulae easier to enter and understand
- Excel how to: speed up formulae - using Excel tables – creating dynamic references by referring to Table columns and using Table structured references to make formulae easier to understand
- Excel how to: speed up formulae - using Excel tables part 2 – adding calculated columns to an Excel Table
- Excel how to: speed up entering formulae – Dynamic Arrays – how the recent introduction of Dynamic Arrays can dramatically reduce the number of formulae you need to enter in the first place
Speed up formatting
- Excel how to: speed up formatting using Excel Styles – using Excel Styles to format Excel cells with a single click and to help implement the ICAEW 20 Principles for Good Spreadsheet Practice.
- Excel how to: speed up formatting using Excel Styles. Part 2 – using the three number styles to set up your own number formats that can be applied directly from the commands in the Number group of the Home Ribbon tab.
Introduction
Although entering text, numbers and dates into cells might seem straightforward, there are Excel features and techniques that can make data entry easier and quicker.
Excel options
There are several Excel options that can affect how Excel behaves when you enter data. Not only can these be helpful in speeding up data entry, but knowing about these options can also help in troubleshooting when Excel doesn’t do what you expect.
The first of these options definitely falls into the unexpected behaviour category. If you type in a number and Excel inserts the decimal points for you, so you type in 120 and Excel changes this to 1.2, then you probably have the ‘Automatically insert a decimal point’ option turned on this, can be found in the Editing options section of the Advanced category of Excel Options:
This option might be useful if you want to use Excel as an adding machine but knowing how to turn it off is probably the most important point (decimal or otherwise).
The ‘Enable automatic percent entry’ option has a similar function. First of all, when you enter percentages, you can either format the cells with the Percent format first and then enter the values, or you can enter the values followed by the % character. What doesn’t work so well is to enter the value and then apply the Percent format – this will multiply whatever you have entered by 100, so entering 12 in a cell, and then applying the Percent format will result in a value of 1200% whether this option is turned on or off. The automatic percent entry option only affects entries made in cells that have been pre-formatted as percentages as we can see below. The top section shows that whether the option is on or off, numbers below 1 (and above -1) will be as entered, but values of 1 or over (or -1 or under) will be multiplied by 100 when the option is turned on, but not when it is turned off.
Values entered in cells not formatted as percentages, but with the % character entered manually, are not affected by this option – as seen in rows 6 to 8:
Also, in this section of Excel Options, the ‘Enable AutoComplete for cell values’ when turned on will cause Excel to prompt to AutoComplete any entries in a column that uniquely match the beginning of any previous items in the list.
Active cell selection
The first part of this section could also be included in the Options section. The ‘After pressing Enter, move selection’ option and dropdown allows you to choose which cell becomes active after you press the keyboard Enter key. Perhaps unsurprisingly, the dropdown options are Down, Right, Up and Left. Once set, the direction can be overridden by using the appropriate keyboard cursor key to accept a data entry rather than the Enter key.
If you select one or more ranges of cells, the active cell position will wrap within the selected range or ranges, respecting the ‘move selection’ direction. Here, we have chosen ‘Right’ as the direction and started typing numbers in cell F6. The numbering sequence shows how the active cell moves and wraps within each range with the end of range 2 wrapping back to the top left-hand corner of range 1:
Similarly, the active cell position will wrap within an Excel table and, once the end of the Table is reached, will start adding rows to the Table.
Where certain cells in a worksheet have their Protection set to ‘Unlocked’, and with Protect Sheet turned on and the Select locked cells option turned off, the active cell will wrap through all the unlocked cells in the worksheet:
Keyboard shortcuts
There are some useful keyboard shortcuts available to help you enter data more quickly, or copy data or a formula to other cells:
| CTRL +; (semi-colon) | Enters the current date. |
| CTRL+SHIFT+; (colon) | Enters the current time. |
| CTRL+' | Copies a formula or entered data from the cell above the active cell into the cell. |
| CTRL+SHIFT+" | Copies the formula result or entered data from the cell above the active cell into the cell. |
| CTRL+Enter | Fills all selected cells with the contents of the cell being edited |
| CTRL+D | Fill down - fills the selected column or columns with the topmost value in each column (including overwriting any existing values) |
| CTRL+R | Fill right– fills the selected row or rows with the leftmost value in each row (including overwriting any existing values) |
Automatic Data Conversion
It’s also worth being aware of a new or imminent set of options, again in the Advanced category, that control whether Excel applies automatic conversions to the data that you enter:
Although this will undoubtedly be useful if you compile lists of credit card numbers, these options don’t yet appear to address a subject that hit the national news a few years ago when scientists realised that hundreds of lists of gene names included entries that Excel had automatically converted to dates:
I’m not sure whether or not Quattro Pro suffers from the same issue.
Related links
Archive and Knowledge Base
This archive of Excel Community content from the ION platform will allow you to read the content of the articles but the functionality on the pages is limited. The ION search box, tags and navigation buttons on the archived pages will not work. Pages will load more slowly than a live website. You may be able to follow links to other articles but if this does not work, please return to the archive search. You can also search our Knowledge Base for access to all articles, new and archived, organised by topic.
