Hello all and welcome back to the Excel Tip of the Week! This week, we have a Basic User post in which we are looking at some shortcuts, best practice, and general ideas for how to do data entry in Excel.
Filling out tabular data
Let’s say we are filling in data in a format like this:
You can do this typewriter-style, going across lines and then back to the start of the next row, by using Tab and Enter. After typing each value, just press Tab to move to the next cell:
When you reach the end, just press Enter after entering your value instead of tab, and the selected cell will move down to the next row but in the column you began tabbing in!
Autocomplete
In data like this, where you have things like customer names, repeating them can be helpful rather than typing them out completely. Not only is this quicker, but it helps avoid typos.
Excel will automatically suggest an autocomplete option if what you’re typing matches only one entry in the preceding rows:
Alternatively, you can press Alt and the Down key to open a dropdown of all the entries previously used in the current column:
From here you can use the arrow keys to pick an option and Enter to fill it.
Copy down
Instantly repeat a value or formula from the previous row with Ctrl D. You can also use Ctrl R to copy the cell to the left.
Use data validation
One way of reducing the number of errors made during data entry is to use data validation. This tool can restrict what is allowed to be entered in a cell – whether that’s restricting to a number in a certain range (e.g. putting a cap on invoice amounts), only allowing whole numbers (e.g. for quantity and other discrete fields), or even allow listing only certain items (e.g. for customer or account names).
For more on data validation, see TOTW #296.
Use a Table
Even more so than data validation, Excel Tables simplify entering and working with data. There’s a detailed account of Table functionality in TOTW #163 and #164, but here are the basics.
You can transform data into an Excel Table from the Home menu, or with Ctrl T. After doing that, Excel will help with column consistency by automatically copying formatting, data validation rules, and formulas down into new rows of the table as they are created. You can add more rows by typing or pasting data underneath the table, dragging down the bottom corner of the table, or pressing Tab while in the last cell of the last row.
Note that this means you can continue entering data into a Table with only Tab, and never need Enter.
And that’s my list of tips for high-quality, low-error, easy data entry!