Data Entry in Excel

How to Work With Cells in Excel

One of the most fundamental features of Excel is working with cells, which are the individual boxes that make up a spreadsheet. Excel users can input data, format cells, perform calculations, sort and filter data, and even protect cells to prevent unwanted changes.

Here are some ways of how to work with cells in Excel:

• Selecting cells: To work with a cell, you first need to select it. You can select a single cell by clicking on it, or you can select multiple cells by clicking and dragging your cursor over the cells you want to select. You can also select entire rows or columns by clicking on the row or column header.

• Data entry in excel: Once you have selected a cell, you can enter data into it by simply typing. You can enter numbers, text, dates, and more. You can also copy and paste data from other sources, such as a website or another spreadsheet.

• Formatting cells: You can format cells to change the appearance of the data they contain. For example, you can change the font, font size, color, and alignment of the text in a cell. You can also format cells to display numbers or dates in a specific format.

• Using formulas: Excel allows you to perform calculations on data in cells using formulas. Formulas are entered into cells using the "=" sign and can include mathematical operations, such as addition, subtraction, multiplication, and division, as well as more complex calculations using functions.

• Sorting and filtering data: You can sort data in cells by ascending or descending order based on a specific column. You can also filter data to display only the data that meets certain criteria.

• Using conditional formatting: You can use conditional formatting to highlight cells that meet certain conditions, such as cells that contain a certain value or cells that are above or below a certain threshold.

• Protecting cells: You can protect cells in Excel to prevent them from being edited or deleted. This can be useful if you want to share a spreadsheet with others but want to ensure that certain data remains unchanged.

Now we will be looking at the most asked questions when starting to work with cells:

How to Enter a Number in a Cell?

• Click on the cell where you want to enter the number. The selected cell will be highlighted.
• Type the number you want to enter into the cell. You can use the numeric keypad on your keyboard or the number keys at the top of your keyboard.
• Press the Enter key on your keyboard. The number you entered will appear in the cell.

How to Enter Text in a Cell?

• Click on the cell where you want to enter the text. The selected cell will be highlighted.
• Type the text you want to enter into the cell.
• Press the Enter key on your keyboard. The text you entered will appear in the cell. Also to enter a line break we can use ALT+ENTER.

Excel Formulas to Add Text/Character to Cell

To add text or characters to a cell in Excel using a formula, you can use the concatenation function which will concatenate a string with a cell reference. Here are the steps to follow:

• Navigate to the worksheet where you want to add text or characters to a cell.
• Click on the cell where you want to add the text or characters.
• Type the formula in the formula bar at the top of the Excel window. The formula to concatenate or join two or more strings is =CONCATENATE(text1, [text2], [text3], ...)
• Replace "text1" with the first text or character that you want to add to the cell.
  • If you want to add more than one text or character, separate them with a comma and a space. For example, =CONCATENATE("Hello", ", ", "world!")
• Press the Enter key on your keyboard to apply the formula to the cell.
• The cell will now display the combined text or characters

How to Add Text to the Beginning of Cells

To add text to the beginning of cells in Excel, you can use the CONCATENATE function or the ampersand (&) symbol. Here are the steps to follow:

1. Select the cells where you want to add the text.
2. Type the text that you want to add to the beginning of the cells in a blank cell.
3. Copy the text that you just typed by pressing Ctrl + C on your keyboard.
4. Click on the cell where you want to add the text at the beginning.
5. Type the equal sign (=) to begin a formula.
6. Type the text that you copied in step 3, followed by an ampersand (&) symbol, then the cell reference of the cell that you want to add the text to.
   • For example, if you want to add the text "Item - " to the beginning of cell A1, you would type = "Item - " & A1.
   • Press the Enter key on your keyboard to apply the formula to the cell.
   • The cell will now display the combined text

How to Add Text to the End of Cells in Excel

To add text to the end of cells in Excel, you can use the CONCATENATE function or the ampersand (&) symbol. Here are the steps to follow:

1. Select the cells where you want to add the text.
2. Type the text that you want to add to the end of the cells in a blank cell.
3. Copy the text that you just typed by pressing Ctrl + C on your keyboard.
4. Click on the cell where you want to add the text at the end.
5. Type the equal sign (=) to begin a formula.
6. Type the cell reference of the cell that you want to add the text to, followed by an ampersand (&) symbol, then the text that you copied in step 5.
   • For example, if you want to add the text " pcs" to the end of cell A1, you would type = A1 & " pcs".
7. Press the Enter key on your keyboard to apply the formula to the cell.
8. The cell will now display the combined text.

Add Characters to Beginning and End of a String

We can do both, adding to the beginning and adding to the end, simultaneously in one formula. Let us see how we can do that:

1. Click on the cell where you want to add the characters.
2. Type the equal sign (=) to begin a formula.
3. Type the character or text that you want to add to the beginning of the string, followed by an ampersand (&) symbol, then the cell reference of the cell that contains the string.
4. Next, type another ampersand (&) symbol, followed by the character or text that you want to add to the end of the string.
5. Press the Enter key on your keyboard to apply the formula to the cell.
6. The cell will now display the combined text.
   • For example, if you want to add a dollar sign ($) to the beginning and end of the string in cell A1, you would type = "$" & A1 & "$"
7. To apply the formula to other cells, click on the cell that you just entered the formula in, and drag the fill handle down to apply the formula to the other cells.

Combine Text From Two or More Cells

To combine text from two or more cells in Excel, you can use the CONCATENATE function or the "&" operator. Here are the steps:

1. Select the cell where you want to combine the text.
2. Type the following formula: =CONCATENATE(cell1, cell2, cell3, ...) or =cell1&cell2&cell3&... where cell1, cell2, cell3, etc. are the cells that you want to combine.
3. Press Enter.
   • For example, if you want to combine the text from cell A1 and cell B1, you can use the following formula: =CONCATENATE(A1,B1) or =A1&B1
4. If you want to include a space or any other character between the text, you can add it inside the formula using quotes. For example, if you want to add a space between the text from cell A1 and B1, you can use the following formula: =CONCATENATE(A1," ",B1) or =A1&" "&B1
5. Once you have entered the formula, the combined text will appear in the cell you selected.

How to Add Special Character to Cell in Excel

To add a special character to a cell in Excel, you can follow these steps:

1. Select the cell where you want to add the special character.
2. Click on the "Insert" tab in the ribbon.
3. Click on the "Symbol" button, which is located in the "Symbols" group.
4. Choose the font that contains the special character you want to add from the "Font" drop-down menu.
5. Scroll through the list of symbols and click on the one you want to use.
6. Click the "Insert" button to add the symbol to your cell.
7. If you need to use the special character in other cells, simply copy and paste it.

It's important to note that not all ASCII codes will produce visible characters, and some may produce characters that are not supported by your font or operating system. Therefore, it's a good idea to test the character in your spreadsheet before using it extensively.

How to Add Text to Formula in Excel

To add text to a formula result in Excel, you can follow these steps:

1. Type your formula into the cell where you want the result to appear. For example, you might have a formula that calculates the sum of a range of cells: =SUM(A1:A10)
2. After the formula, type an ampersand (&) followed by the text you want to add. For example, if you want to add the text "Total" to the sum of the range, your formula would look like this: =SUM(A1:A10)&" Total"
3. Press Enter to see the result.

The ampersand (&) is used to concatenate, or join, text and values together in a formula. In the above example, the result of the SUM formula is joined with the text " Total" to give a final result that reads "55 Total", assuming the sum of the range A1 is 55.

How to Insert Text After Nth Character

To insert text after the Nth character in a cell in Excel, you can use a combination of the LEFT and RIGHT functions. Here are the steps:

1. Select the cell where you want to insert the text.
2. Type the following formula: =LEFT(cell, N) & "text" & RIGHT(cell, LEN(cell)-N) where "cell" is the cell containing the text you want to modify, "N" is the number of characters before the insertion point, and "text" is the text you want to insert after the Nth character.
   • Replace "cell", "N", and "text" with the appropriate values for your data.
3. Press Enter.

For example, if you want to insert the text "abc" after the 5th character in cell A1, you would use the following formula: =LEFT(A1, 5) & "abc" & RIGHT(A1, LEN(A1)-5)

Note that the LEN function is used to calculate the length of the original cell contents, so that the RIGHT function can return the correct number of characters.

How to Add Text Before/After a Specific Character

To add text before or after a specific character in a cell in Excel, you can use the SUBSTITUTE function in combination with the CONCATENATE function. Here are the steps:

Adding text before a specific character:

1. Select the cell where you want to add the text.

2. Type the following formula: =CONCATENATE("text", SUBSTITUTE(cell, "specific character", "text"&"specific character")) where "cell" is the cell containing the text you want to modify, "specific character" is the character before which you want to add the text, and "text" is the text you want to add before the specific character.

3. Replace "cell", "specific character", and "text" with the appropriate values for your data.

4. Press Enter.

   • For example, if you want to add the text "ABC" before every comma in cell A1, you would use the following formula: =CONCATENATE("ABC", SUBSTITUTE(A1, ",", "ABC,"))

This formula replaces every comma in A1 with the text "ABC,". The CONCATENATE function then adds the text "ABC" before each comma.

Adding text after a specific character:

1. Select the cell where you want to add the text.
2. Type the following formula: =CONCATENATE(SUBSTITUTE(cell, "specific character", "specific character"&"text"), "text") where "cell" is the cell containing the text you want to modify, "specific character" is the character after which you want to add the text, and "text" is the text you want to add after the specific character.
3. Replace "cell", "specific character", and "text" with the appropriate values for your data.
4. Press Enter.
   • For example, if you want to add the text "XYZ" after every hyphen in cell A1, you would use the following formula: =CONCATENATE(SUBSTITUTE(A1, "-", "-"&"XYZ"), "XYZ")

This formula replaces every hyphen in A1 with the text "-XYZ". The CONCATENATE function then adds the text "XYZ" after each hyphen.

How to Add Space Between Text in Excel Cell

To add a space between text in an Excel cell, you can use the CONCATENATE function. Here are the steps:

1. Select the cell where you want to add the space.
2. Type the following formula: =CONCATENATE(cell1, " ", cell2) where "cell1" and "cell2" are the cells containing the text you want to combine, and the space between the quotation marks is the space you want to add between the text.
3. Replace "cell1" and "cell2" with the appropriate values for your data.
4. Press Enter.
   • For example, if you have the first name in cell A1 and the last name in cell B1, and you want to add a space between them, you would use the following formula: =CONCATENATE(A1, " ", B1)

This formula combines the text in cell A1, a space, and the text in cell B1.

How to Add the Same Text to Existing Cells With VBA

You can use VBA to add the same text to existing cells in Excel. Here are the steps:

1. Open your Excel workbook and press ALT + F11 to open the VBA editor.
2. In the VBA editor, click on "Insert" from the menu bar, then select "Module". In the new module, type the following code:

3. Replace "your text here" with the text you want to add.
4. Replace "A1" with the range of cells you want to modify.
5. Press F5 or click on "Run" from the menu bar to execute the code.

This VBA code adds the specified text to the beginning of each cell in the range you specified.

Add Text or Character to Multiple Cells with Ultimate Suite

You can use the Ultimate Suite add-in for Excel to add text or characters to multiple cells at once. Here are the steps:

1. If you do not have the Ultimate Suite add-in installed, you can download a trial version from the official website.
2. Open your Excel workbook and select the range of cells where you want to add the text or characters.
3. Click on the "Ablebits" tab in the Excel ribbon.
4. Click on the "Text" drop-down menu and select "Add Text":
   • In the "Add Text" dialog box, type the text or characters you want to add to the selected cells in the "Text to add" field.
   • Choose whether you want to add the text before or after the existing text in the cells by selecting the appropriate radio button.
   • Select the "Selected cells" radio button under "Apply to" to apply the changes only to the selected cells, or select the "Entire column" or "Entire row" radio buttons to apply the changes to the entire column or row.
   • Click on the "Add" button to apply the changes.

How to Enter a Number That has a Fixed Decimal Point ?

To enter a number that has a fixed decimal point in Excel, you can use the "Number" data type and set the number of decimal places to be displayed. Here are the steps:

1. Select the cell or cells where you want to enter the number.
2. Right-click on the selected cell or cells and select "Format Cells" from the context menu.
3. In the "Format Cells" dialog box, select the "Number" tab.
4. In the "Category" list, select "Number" or "Currency" (depending on whether you want to display a currency symbol or not).
5. In the "Decimal places" field, enter the number of decimal places you want to display. For example, if you want to display a number with two decimal places, enter "2".
6. In the "Symbol" list, select the currency symbol you want to display (if applicable).
7. Click on the "OK" button to apply the changes.

How to Enter a Date or a Time in a Cell?

To enter a date or time in a cell in Excel, you can follow these steps:

1. Select the cell where you want to enter the date or time.
2. Type the date or time in the cell in the format that you want to use. For example, if you want to enter the date January 1, 2022, you can type "1/1/2022" or "01/01/2022" (depending on your regional settings).

Custom Excel Date and Time Formatting

If you want to change the format of the date or time, you can use the "Format Cells" dialog box. To access the "Format Cells" dialog box:

• right-click on the cell
• select "Format Cells"
• then choose the "Date" or "Time" category from the list on the left side of the dialog box.
• You can then select the desired format from the list of options or create a custom format using the available options.

Custom Date Formatting Characters

To use custom date formatting characters in Excel, you can follow these steps:

1. Select the cell or cells that you want to format.
2. Right-click on the cell or cells and select "Format Cells" from the context menu.
3. In the "Format Cells" dialog box, select the "Number" tab if it's not already selected.
4. In the "Category" list, select "Custom".
5. In the "Type" field, enter the custom formatting code that you want to use. The following are some commonly used custom date formatting codes:
   • "d" or "dd": Displays the day of the month, with or without leading zeros.
   • "m" or "mm": Displays the month as a number, with or without leading zeros.
   • "mmm" or "mmmm": Displays the month as an abbreviated or full name, respectively.
   • "yy" or "yyyy": Displays the year as a two-digit or four-digit number, respectively.
6. You can also use separators such as "/" or "-" to separate the different parts of the date.

Custom Time Formatting Characters

To use custom time formatting characters in Excel, you can follow these steps:

1. Select the cell or cells that you want to format.
2. Right-click on the cell or cells and select "Format Cells" from the context menu.
3. In the "Format Cells" dialog box, select the "Number" tab if it's not already selected.
4. In the "Category" list, select "Custom".
5. In the "Type" field, enter the custom formatting code that you want to use. The following are some commonly used custom time formatting codes:
   • "h" or "hh": Displays the hour in a 12-hour format, with or without leading zeros.
   • "H" or "HH": Displays the hour in a 24-hour format, with or without leading zeros.
   • "m" or "mm": Displays the minute, with or without leading zeros.
   • "s" or "ss": Displays the second, with or without leading zeros.
   • "AM/PM" or "A/P": Displays the AM/PM indicator in uppercase or lowercase.
6. You can also use separators such as ":" or "." to separate the different parts of the time

How to Enter the Same Data into Several Cells at the Same Time ?

1. Type the data that you want to enter into the active cell.
2. Hold down the "Ctrl" key and select the cells where you want to enter the same data. You can select multiple cells by clicking and dragging over them or by holding down the "Ctrl" key and clicking on each cell.
3. Press the "Ctrl" key and the "Enter" key at the same time.

How to Enter the Same Data on Several Worksheets at the Same Time?

We can use the "Group Sheets" feature in Excel to enter the same data on multiple worksheets at the same time:

1. Click on the first worksheet where you want to enter the data.
2. Hold down the "Ctrl" key and click on the tab for each additional worksheet where you want to enter the data.
3. Right-click on one of the selected worksheet tabs and choose "Group Sheets" from the context menu. This will group the selected worksheets together.
4. Type the data that you want to enter into a cell on any of the grouped worksheets. The same data will be entered into the corresponding cell on all of the other grouped worksheets.
5. When you are finished entering the data, right-click on one of the grouped worksheet tabs and choose "Ungroup Sheets" from the context menu to ungroup them.

Using the "Group Sheets" feature can be a quick and efficient way to enter the same data on multiple worksheets, particularly if the data needs to be entered into the same cell on each worksheet.

How to Adjust Worksheet Settings and Cell Formats ?

To adjust worksheet settings and cell formats in Excel, you can follow these steps:

1. Adjusting Worksheet Settings:
   • To change the name of a worksheet, right-click on the worksheet tab and select "Rename". Type in a new name and press "Enter".
   • To insert a new worksheet, click on the plus sign (+) icon next to the last worksheet tab.
   • To delete a worksheet, right-click on the worksheet tab and select "Delete". Confirm the deletion if prompted.
   • To change the color of a worksheet tab, right-click on the tab and select "Tab Color". Choose a new color from the drop-down menu.
2. Adjusting Cell Formats:
   • To change the number format of a cell, select the cell or cells you want to format. Click on the "Number Format" drop-down menu in the "Home" tab and select the format you want to use.
   • To add or remove decimal places, select the cell or cells you want to format. Click on the "Decrease Decimal" or "Increase Decimal" icons in the "Number" group of the "Home" tab.
   • To change the font size or style, select the cell or cells you want to format. Click on the "Font Size" or "Font Style" drop-down menus in the "Home" tab and select the size or style you want to use.
   • To add borders or shading to cells, select the cell or cells you want to format. Click on the "Borders" or "Fill Color" icons in the "Font" group of the "Home" tab.

These are some of the basic adjustments you can make to worksheet settings and cell formats in Excel. There are many more formatting options available in Excel that you can explore by clicking on the various tabs and options in the ribbon.