13 Essential Excel Functions for Data Entry

Entering data into your spreadsheet is the basic principle of using Excel. From adding the current date and time to looking up values ​​to changing the case of your text, there are functions that can help you immensely with data entry.

Enter the current date and time: TODAY and NOW

You may want see today’s date with or without the time each time you open 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 of each function is quite simple. Utilize TODAY() and NOW() without arguments or characters in parentheses.

Just enter the following formula for the function you want, hit enter or return, and each time you open your sheet, you’ll be up to date.

=TODAY()
=NOW()

TODAY and NOW functions in Excel

Get parts of a text string: LEFT, RIGHT and MID

If you are working with text strings where you need to get part of that string for your input, you can do this 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 required arguments

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

=LEFT(A1,5)

LEFT function in Excel

With the following formula, you can get the last five characters of the text string in cell A1:

=RIGHT(A1,5)

RIGHT function in Excel

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

=MID(A1,7,5)

MID function in Excel

Change the case of letters: UPPER, LOWER and PROPER

Perhaps you have inconsistencies in the way your sheet text was entered. You can convert letters to upper or lower case, or capitalize the first letter of each word with UPPER, LOWER and PROPER.

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

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

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

=UPPER(A1)

To replace the text in that same cell with lowercase letters, use this formula instead:

=LOWER(A1)

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

=PROPER(A1)

PROPER function in Excel

Round your numbers: ROUND UP and ROUND DOWN

You can have a spreadsheet containing any decimals you prefer round up or down, rather than displaying the entire string. Excel’s ROUNDUP and ROUNDDOWN functions allow you to easily round numbers.

RELATED: How to round 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 to two digits, use this formula:

=ROUNDUP(A1,2)

ROUND function in Excel

To round that same number to two digits, you would use this formula:

=ROUNDDOWN(A1,2)

ROUNDDOWN function in Excel

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

Remove unnecessary spaces: TRIM

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

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

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

=TRIM(A1)

TRIM function in Excel

To remove extra spaces in the sentence "   Extra   Spaces   " you would use the following formula:

=TRIM("   Extra   Spaces   ")

TRIM text in Excel

Compare a value and return a result: IF

the The IF function is a popular tool for comparing values ​​and returning numeric 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 1 for Yes and 2 for No, you would use this formula:

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

IF a value is equal to the function

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

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

IF a value is greater than the function

Search values: XLOOKUP

When you need to find a value or text from another location and enter it into 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 XLOOKUP function in Excel which goes into more detail, here we will use basic examples.

To find a customer’s phone number, 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 a control

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

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

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

XLOOKUP for two fields

Data entry is a sufficient task in itself. I hope you can make it easier to use these Excel data entry functions. Do you have different functions that help you enter data into Excel? Let us know!

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