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.
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()
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 requiredRIGHT(text, number_characters), first argument requiredMID(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)
With the next formula, you can obtain the last five characters in the text string in cell A1:
=RIGHT(A1,5)
And with this formula, you can obtain the five characters starting with the seventh character in cell A1:
=MID(A1,7,5)
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)
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.
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)
To round that same number down two digits, you’d use this formula:
=ROUNDDOWN(A1,2)
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)
To remove the additional spaces in the phrase " Extra Spaces " you would use the following formula:
=TRIM(" Extra Spaces ")
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.
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)
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")
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(G2,A2:A10,C2:C10)
To break down the formula, G2 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 the email address for that customer:
=XLOOKUP(G2,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.
Data entry is enough of a task on its own. Hopefully, you can make it easier using these Excel data entry functions.