Technology

13 Essential Excel Functions for Data Entry

Entering data in your spreadsheet is the basic principle behind using Excel. From adding the current date and time to looking up values to changing the letter case of your text, some functions can help you tremendously with data entry.

Enter the Current Date and Time: TODAY and NOW

You may want to see the current date with or without the time with each opening of your sheet. The TODAY function provides the current date, and the NOW function provides the date and time.

RELATED: How to Insert Today’s Date in Microsoft Excel

The syntax for each function is simple enough. Use TODAY() and NOW() with no arguments or characters in the parentheses.

Just enter the following formula for the function you want, press Enter or Return, and each time you open your sheet, you’ll be current.

=TODAY()
=NOW()

TODAY and NOW functions in Excel

Obtain Parts of a Text String: LEFT, RIGHT, and MID

If you’re working with strings of text where you need to obtain part of that string for your entry, you can do so with the LEFT, RIGHT, and MID functions.

The syntax for each function is as follows:

  • LEFT(text, number_characters), first argument required
  • RIGHT(text, number_characters), first argument required
  • MID(text, start_number, number_characters), all arguments required

With this formula, you can obtain the first five characters in the text string in cell A1:

=LEFT(A1,5)

LEFT function in Excel

With the next formula, you can obtain the last five characters in the text string in cell A1:

=RIGHT(A1,5)

RIGHT function in Excel

And with this formula, you can obtain the five characters starting with the seventh character in cell A1:

=MID(A1,7,5)

MID function in Excel

Change the Letter Case: UPPER, LOWER, and PROPER

Maybe you have some inconsistencies in the way the text in your sheet was entered. You can convert letters to all uppercase or lowercase, or capitalize the first letter of each word with UPPER, LOWER, and PROPER.

The syntax for each is the same with the argument required:

  • UPPER(cell_reference)
  • LOWER(cell_reference)
  • PROPER(cell_reference)

To change the text in cell A1 to all uppercase letters, use the following formula:

=UPPER(A1)

To change the text in that same cell to all lowercase letters, use this formula instead:

=LOWER(A1)

To change the text in that cell to capitalize the first letter of each word, use this formula:

=PROPER(A1)

PROPER function in Excel

Round Your Numbers: ROUNDUP and ROUNDDOWN

You might have a spreadsheet containing decimal numbers that you prefer to round up or down, rather than display the entire string. The ROUNDUP and ROUNDDOWN functions in Excel let you round numbers easily.

RELATED: How to Round Off Decimal Values in Excel

The syntaxes are ROUNDUP(number, number_digits) and ROUNDDOWN(number, number_digits) where both arguments are required for each.

To round the number in cell A1 up two digits, use this formula:

=ROUNDUP(A1,2)

ROUNDUP function in Excel

To round that same number down two digits, you’d use this formula:

=ROUNDDOWN(A1,2)

ROUNDDOWN function in Excel

Use a positive number for the number_digits argument to round decimal places to the right and a negative number to round decimal places to the left.

Remove Unnecessary Spaces: TRIM

Maybe you have extra spaces in your cells that you want to remove. The TRIM function eliminates spaces.

The syntax for the function is TRIM(text) where you can use a cell reference or enter the text in quotes.

To remove the extra spaces in the text in cell A1, use the cell reference as in this formula:

=TRIM(A1)

TRIM function in Excel

To remove the additional spaces in the phrase "   Extra   Spaces   " you would use the following formula:

=TRIM("   Extra   Spaces   ")

TRIM text in Excel

Compare a Value and Return a Result: IF

The IF function is a popular tool for comparing values and returning numerical or textual results. You can then analyze those results or use them elsewhere like in another formula.

RELATED: How to Use Logical Functions in Excel: IF, AND, OR, XOR, NOT

The syntax is IF(test, output_if_true, output_if_false) where the first two arguments are required.

To test the value in cell A1, which is a Yes or No option, and return a 1 for Yes and 2 for No, you would use this formula:

=IF(A1="Yes",1,2)

IF a value equals function

For a text example, you can see if a value (A1) is greater than another value (B1) and then return “Over” if it is and “Under” if it isn’t.

=IF(A1>B1,"Over","Under")

IF a value is greater than function

Look Up Values: XLOOKUP

When you need to look up a value or text from another location and enter it in your sheet, the XLOOKUP function is ideal.

The syntax is XLOOKUP(value, lookup, return, not_found, match_code, search_code) where the first three arguments are required and the last three are optional.

Because we have a full tutorial on the XLOOKUP function in Excel that goes into further detail, we’ll use basic examples here.

To find the phone number for a customer, you can use this formula:

=XLOOKUP(H2,A2:A10,C2:C10)

To break down the formula, H2 is the value to find, A2:A10 is where to look up the value, and C2:C10 is where to find the value to return.

XLOOKUP for one field

As another example, you can use this formula to find both the phone number and the email address for that customer:

=XLOOKUP(H2,A2:A10,B2:C10)

Here, we simply expanded the return argument to cover each column that includes the phone number and email address (B2:C10). So, the formula provided both results.

XLOOKUP for two fields

Data entry is enough of a task on its own. Hopefully you can make it easier using these Excel data entry functions. Do you have different functions that help you enter data in Excel? Let us know!

RELATED: How to Create a Data Entry Form in Microsoft Excel



File source

Tags
Show More

Related Articles

Back to top button
Close