10 Excel Data Entry Tips You Can't Afford to Miss

Excel is a popular software application used by millions of people with good reason. It is used for a variety of applications. From simple record keeping to accounting to numerical calculations to logical functions. It is even used by people for word processing and presentations, despite much more powerful applications being available for those specific purposes. As it is an application with a variety of functions and capabilities, it is quite possible that infrequent users, or light users, are able to leverage only the apparent or basic functions of the tool without ever realizing the power it possesses. And how easy it is to use or activate those functions. Despite this anomaly, for most people, even the basic functions they use seem to deliver a satisfactory experience. Such is its capability. oWorkers offers a wide range of Excel data entry solutions. With our experience in providing data-based services to clients for over seven years, we possess the experience and knowledge to create efficiencies in your data entry processes. We have been identified as one of the top three BPOs in the world for data services. On multiple occasions. In order that the power of Excel be revealed to more users, listed below are 10 excel data entry tips that are easy to use and can deliver surprising benefits in terms of enhancement of efficiency and accuracy. As is the case with many software applications, if you have created an Excel workbook for recording a limited data set, say the grades in the six subjects that both your children got in the last set of examinations, the benefit will be limited. The magnitude of benefit will rise with the volume of data that is being entered or maintained in a particular workbook. This is actually intuitive and self-explanatory as well. If you save 1 second for every field because of the efficiency that has been created, in a worksheet with only 12 fields to be filled, you will save 12 seconds. Not really worth anyone’s time to invest on learning about new techniques that will help save time. However, if the worksheet has 12000 fields to be input, 12000 seconds, or 200 minutes, or over 3 hours, stand to be saved by creating a certain efficiency. Hence, even if it takes half an hour to learn the trick, it is perhaps much more worth it.

1. Keyboard shortcuts are great excel data entry tips

Let us begin with the low hanging fruit; keyboard shortcuts. While the menus on most application software programs are put together after a lot of research and designed to be intuitive, at times there still is a struggle to find the right buttons and menu choices. The pain can be mitigated to some extent if we know the keyboard shortcuts that can be used for some of our common needs. Ctrl + O – Opens dialog box to open or find a file Ctrl + N – Creates a new workbook Ctrl + S – Saves file Ctrl + F2– Opens the Print Preview dialog box Alt + Enter – Create a new line in the same cell Alt + Page Down – Move to the next screen to the right Ctrl + Down (arrow) – Move to the bottom edge of the data Ctrl + Z – Undo This is indicative, and not exhaustive. Once you start using keyboard shortcuts, you will perhaps discover many others using similar logic.

2. Entering numbers with decimal numbers fixed

What does this mean? Sometimes one needs to enter a number in a field that has a decimal portion to it, like 903.78 or 2.24. When you enter this information you need to perforce press the decimal (dot) key for each number you enter, increasing the time taken to enter. What can you do? You can create a format in that field so that the worksheet automatically assumes the decimal at a certain place. This way, you will not need to hit the decimal key each time you enter a number. If you are entering 12000 numbers, this means saving 12000 keystrokes. How do you do this? Choose Options from the File menu. In the Dialog Box that opens up, choose Advanced In the New screen of the Dialog Box, check the option ‘Automatically insert a decimal point.’ Just below that there is another dropdown box with the name ‘Places.’ Choose the number of decimals you want, say 2. Now, each time you enter a number, it will place the last two after the decimal. So, if you enter 224, it will be recorded as 2.24 in that cell. For more excel data entry tips read on.

3. Entering Date and Time in cells

With our increasing reliance on devices, we often struggle to recollect simple pieces of information like the data and time. If they have to be entered into an Excel spreadsheet, we might end up referring to other devices so that the correct data and time might be entered. However, let us not forget that Excel is also a device, and a pretty smart one at that. If the date and time can be maintained by other devices why not Excel? Of course, it can be. All you need to do is ‘Control’ along with the ‘;’ (semicolon) key. As if by magic, the current date will appear in the cell where you took that action. Not dissimilarly, for entering the current time, you need to press ‘Control’ with ‘Shift’ with ‘:’ (colon) keys. Yes, three keys instead of two. The ‘Shift’ key is required to access the colon. You may want to ensure that the display of the date and time is as per your requirement. You can choose from a variety of display options that Excel makes available. Also, these are values and not variables. What it means is that once entered, they will continue to be the same unless someone chooses to change them. They will not change to reflect the new data and time as it keeps changing.

4. Filling the entire selection with content of the active cell

What is an active cell? When you select multiple cells in Excel, the selection gets shaded except for one cell where the cursor is/ was when you initiated the selection. That unshaded cell is the active cell in this selection. Now, when you enter something in this cell, the active one, followed by ‘Control’ and ‘Enter’ that entry gets replicated across the entire selection. This happens in case of formulas just as well as it happens for values. In case of formulas, not only will the formula get copied but the cell references will also get adjusted across the selection. How is that for simple excel data entry tips?

5. Using abbreviations to enter long text

Data entry can be a repetitive task. You may be entering a long list of tasks in an Excel sheet with some attributes. One of the attributes is the assignment or responsibility. In other words, which of the seven resources or persons is that particular task assigned to? If a hundred tasks are getting assigned between seven people, with seven unique names most likely, each of the seven would appear many times in that list. For example, if Rosalind Chambers will have 15 tasks assigned to her, you will need to type her name as many times. Including the space, that is 17 characters each time. 15 occurrences of the name make it a total of 235 characters worth data entry. You can create a short ‘code’ that equates to the long word or phrase you need to type. This can be done through the Autocorrect feature of Excel. Excel already has a library of commonly misspelt words that it automatically corrects. You can add to the list and save time on your data entry. This is how you do it:

1. Choose Options from the File tab
2. In the dialog box, choose Proofing, followed by Autocorrect Options
3. In the space for Replace you can add the code. In this case let us add ‘Ros.’ In the space for With we add the word we actually want, in this case ‘Rosalind Chambers.’
4. Complete the sequence with Add followed by OK and we are ready.

Each time you type ‘Ros’ Excel will change it to ‘Rosalind Chambers.’ Cool, isn’t it? Please note that this is an Excel level change and will be applicable in all your workbooks. Even when you want to type Ros it will change it to Rosalind. So, choose a code that you will not use otherwise. Choose wisely.

6. Data entry in non-contiguous cells

Occasionally, a task may require data entry in a ‘broken’ set of cells. A2, followed by B7, followed by D4, etc. This is usually when a workbook is used like a word processor more than a database or accounting sheet. Data entry in non-contiguous cells can cause errors not only in data being incorrect but also being entered in the wrong cells. Excel data entry tips have a solution that can mitigate such issues. You can lessen the error margin by selecting the cells and the sequence in which you will enter data in them. This can be done by selecting all the cells, in the right sequence, once. This can be done by selecting them while the Control key is kept pressed. While selected, type a name for this range in the name Box on the left of the formula bar. Now, whenever you need to enter data in this sequence and range, choose the name you gave to this range and bring those cells up. When you complete typing in one cell, the cursor will automatically jump to the next cell in the range.

7. Freezing panes to lock headers of Rows and Columns

Many times when entering data in large sheets, it is important to know that you are entering information in the right column and row. Because of its size, as you keep scrolling, the column and row headers get lost. Excel provides a facility through which you can lock the header rows and columns so that regardless of how deep you are in the sheet, they will always be visible and you will know exactly which column and row your data is being entered into. To do this: Clickon the Freeze Panes dropdown in the View tab of Excel. You will see 3 options:

1. Freeze Panes
2. Freeze Top Row
3. Freeze First Column

Options 2 and 3 can be used to freeze the top row and top column respectively. They are self-explanatory. Option 1 provides the option of freezing both at the same time. In fact, it is a flexible option and you can freeze multiple rows AND columns at the same time.

8. Dropdown for quick selection

Many times one needs to enter one out of a fixed set of possible choices in a cell. For example, for a list of candidates who applied for a job, the interviewer may need to mark either Shortlisted, Rejected or Second opinion needed against each candidate based on her assessment of each candidate’s credentials. Entering one of the three again and again will be time consuming. Sequence for setting it up:

1. In the cell you wish to set up the validation, click on Data Validation after selecting Data
2. A dialog box will open up. In the Settings tab of the box, in Validation Criteria, choose List.
3. When you choose List, an additional Source Field will appear.
4. You can enter the three values Shortlisted, Rejected or Second opinion needed in the field and click OK to set it up.
5. For setting it up in multiple cells, or an entire column, select the range before creating the validation.

Now, the evaluator only needs to select from one of the three options that will be presented as soon as she moves to the cell she has to enter this information in. This will also reduce spelling errors. Excel data entry tips come to the rescue once more!

9. List of unique values to choose from

A variation of setting up a validation manually is where you use the values already keyed into a certain range of sells, to form the validation for the rest of the worksheet. If you do not know at the start that you will be entering from a set of entries in one cell, but realize that after you have input a few, you can create a dynamic dropdown for the rest of the sheet. As you go to the same cell in the next row, you can click Alt + Down (arrow) together. Unique values entered in the cells above will show up and you can choose the one you need in that cell.

10. Excel data entry tips can fill cells below

This is a variation of the copy-paste routine but perhaps quicker. For copying the above cell, formula included, press Ctrl + D. If a cell along with its formula needs to be copied to a range of cells below, just select the range of cells with the cell with the information to be replicated as the first one in the selection. It will be replicated across the selection.

The oWorkers advantage

oWorkers offers its services from three different geographical regions in the world, that enables us to offer services in over 22 languages. We are geared to provide 24×7 support based on client need. We are GDPR compliant and ISO (27001 :2013 & 9001:2015) certified. With our deep connects in the local communities, we have become referred employers and attract a consistent flow of walk-in talent. This enables us to keep our hiring costs in control and also provide need-based short-term hiring to cater to seasonal and other peaks and troughs in client businesses. Our employees rate as above 4.6 out of 5 on Glassdoor. Our clients talk of having realized savings of almost 80% after outsourcing to us. We have been working for several Unicorn marketplaces for years. Many of our clients are established technology companies. We hope you will trust oWorkers with your work like many other clients.