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

Enter the current date and time: TODAY and NOW

You may want see the current date with or without the time with each opening of its leaf. The TODAY function provides the current date and the NOW function provides the date and time.

The syntax of each function is quite simple. use TODAY()Y NOW()no arguments or characters in parentheses.

Just enter the following formula for the function you want, press Enter or Return, and every time you open your sheet, you’re up to date.

=HOY()
=AHORA()

TODAY and NOW functions in Excel

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

If you’re 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 of each function is as follows:

  • LEFT(text, number_characters)first required argument
  • RIGHT(text, number_characters)first required argument
  • 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:

=IZQUIERDA(A1,5)

LEFT function in Excel

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

=DERECHA(A1,5)

RIGHT function in Excel

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

=MEDIO(A1,7,5)

MID function in Excel

Change case: UPPER, LOWER and PROPER

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

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 all uppercase letters, use the following formula:

= SUPERIOR (A1)

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

=INFERIOR(A1)

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

=PROPIO(A1)

CORRECT function in Excel

Round your numbers: ROUND UP and DOWN

You may have a spreadsheet that contains decimal numbers that you prefer round up or down, instead of displaying the entire string. The ROUNDUP and ROUNDDOWN functions in Excel allow you to easily round numbers.

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

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

=REDONDEAR(A1,2)

ROUND function in Excel

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

=REDONDEAR HACIA ABAJO(A1,2)

ROUND function in Excel

Use a positive number like number_digitsargument 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 remove spaces.

The syntax of 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 of cell A1, use the cell reference as in this formula:

=RECORTAR(A1)

TRIM function in Excel

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

=RECORTAR(" Espacios adicionales ")

TRIM text in Excel

Compare a value and return a result: YES

The IF function is a popular tool for comparing values ​​and returning numeric or textual results. You can then parse 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 a 2 for No, you would use this formula:

=SI(A1="Sí",1,2)

IF a value is equal to a function

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

=SI(A1>B1,"Sobre","Debajo")

IF a value is greater than the function

Lookup Values: LOOKUPX

When you need to look up 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 complete tutorial on the XLOOKUP function in Excel which provides more details, we will use basic examples here.

To find a customer’s phone number, you can use this formula:

=BUSCARX(G2,A2:A10,C2:C10)

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

XLOOKUP for a field

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

=BUSCARX(G2,A2:A10,B2:C10)

Here, we simply expand the returnargument 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 by using these Excel data entry features. Do you have different functions that help you enter data in excel ? Let us know!

RELATED:   How to clean a drive in Windows 10 or Windows 11

Leave a Reply

Your email address will not be published. Required fields are marked *