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
RELATED: How to insert today’s date in Microsoft Excel
The syntax of each function is quite simple. Utilize
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.
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:
With the following formula, you can get the last five characters of the text string in cell A1:
And with this formula, you can get the five characters starting with the seventh character in cell A1:
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:
To change the text in cell A1 to uppercase letters, use the following formula:
To replace the text in that same cell with lowercase letters, use this formula instead:
To change the text in this cell to capitalize the first letter of each word, use this formula:
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:
To round that same number to two digits, you would use this formula:
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:
To remove extra spaces in the sentence
" Extra Spaces " you would use the following formula:
=TRIM(" Extra Spaces ")
Compare a value and return a result: IF
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:
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.
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.
To find a customer’s phone number, you can use this formula:
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.
As another example, you can use this formula to find both the phone number and email address for this customer:
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.
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