add-in combo box date excel 2000 excel 2002 excel 97 excel XP format formatting formula grouping loan - amortization macros subtotal sum excel tutorial workbook worksheet vba
Cells in an Excel worksheet can contain different types of characters, including text characters, numbers and dates.
Entering basic text into Excel cells is simple. It can seem complicated if you want to do any of these tasks:
© Enter a large amount of text, perhaps a few paragraphs in a cell.
© Edit text once it is entered.
© Mix formatting in a single cell, i.e., having bold and non-bold text in a single cell.
© Join multiple text entries into a single cell.
© Join text in a cell with the results of a formula.
© Create a header above columns and prevent text from overflowing into certain cells.
Many Excel users do not view Excel as a platform for editing text, but only as a pure spreadsheet. If you are still using Word to create financial statements, you should not be. Excel offers you all of the text editing tools needed to create financial statements, including efficient handling of text. This chapter will try to convince you to create those financial statements in Excel.
Text and Toolbars
Excel offers a large variety of toolbars with a wide range of formatting icons. The following is a list of the toolbars available in Excel 97 and Excel 2000 that are helpful for dealing with text:
© Formatting
© Drawing
© Picture
© Shadow Settings
© 3-D Settings
© WordArt
NEW
IN 2002
2002
Excel 2002 includes 3 new toolbars:
© Borders
© Drawing Canvas
© Power Formatting
A significant addition to the set of toolbars is the Border Toolbar. The icons on the toolbar make it easy for you to draw borders, fill cells in different colors, and use different line styles to mark cell borders. You can also erase borders quickly by turning your mouse into a pencil, just like in graphics programs.
Entering Text in Cells
Excel offers a number of tools to help you enter text in cells:
Using AutoComplete
When you enter text in a cell, Excel searches the column to see if the characters you have entered match an existing entry in that column. If it finds matching text, it will automatically fill in the remaining characters for you. This saves you the trouble of typing in the entire entry (only if you want to take advantage of it).
Selecting from list of Previous Entries
This feature works after you have entered several entries in a column. You can right-click a cell and select Pick from list from the shortcut menu. The shortcut is to press ALT + Down Arrow. You can then select from a unique sorted list of text that has been entered in that column.
Validating data
By selecting validation criteria, you can prevent entry of incorrect data or data that does not meet the selected criteria. This technique is discussed in more detail later in the chapter.
Moving selection after Enter
Depending on the nature of your data entry, you may want the cellpointer to automatically move to the right or down after entering a value. This is easy to control.
1. Choose Tools, Options and click the Edit tab.
2. Be sure the box next to Move selection after Enter Direction is checked.
3. Change the direction of Move selection after Enter as desired.
Tips
Prevent moving to the next cell when you finish entering data
Press Ctrl+Enter.
To temporarily override the move selection after enter direction, use the Down Arrow key instead of Enter to move the cellpointer down one row. Use the Right Arrow key instead of Enter to move the cellpointer to the right.
Selecting a range
Selecting a range will help you enter data efficiently. Select a range of cells, and begin entering data. Use Enter to move from cell to cell. The data will be entered in the range of cells selected. When you have entered data into the last cell selected in a column, the cursor will automatically move to the first cell in the next column of the selected range.
Example: Select cells A1 through D5. Assuming the Enter direction is set to down, begin entering data. After you have entered data into cell A5, press Enter, and cell B1 will be selected automatically.
Using AutoCorrect
Choose Tools, AutoCorrect Options (in Excel 97, select AutoCorrect).
AutoCorrect allows you to correct common typos; exchange text shortcuts for full text, such as ‘between’ instead of ‘betwen’ (see figure); and to quickly insert frequently used text.
Example: You can save The Best Excel Book with the shortcut Book. When you type Book, AutoCorrect will automatically replace the entry with the full text, as defined in the AutoCorrect settings.
Tip – Use AutoCorrect to enter special symbols
Example – enter the euro sign in a cell
In the cell, enter the formula =CHAR(168), press F2, and then F9 (Paste Special, Values). Copy the euro sign from the formula bar by using the Ctrl+C shortcut. From the Tools menu, select AutoCorrect. In the Replace box, type “euro”, and in the With box, press Ctrl+V (Paste). Click Add, and click OK.
To check this – in any cell, type the word “euro.” The text changes to the euro sign.
Note
AutoCorrect shortcuts for frequently used text defined in Excel can be used in Word. Likewise, AutoCorrect text you define in Word will be available in Excel.
Validating Data
When you enter data into a cell, Excel validates the data against validation criteria that you defined. If the data does not validate, it will not be entered into the cell.
Example: You can set criteria for a range of cells to only allow dates for 2002.
1. Select cells A1:A15.
2. Choose Data, Validation.
3. Select the Settings tab, and in the Allow box, click Date.
4. In the Start date box, enter 1/1/2002.
5. In the End date box, enter 12/31/2002.
6. Select the Input Message tab.
7. In the Title box, enter Date validation.
8. In the Input message box, enter the criteria or any message you like.
9. Error Alert – Select the Error Alert tab, and in the Title box, enter the title of the alert. In the Error message box, enter the date validation criteria. This error alert will appear when the date entered into the cell is found to be invalid and cannot be validated against the validation criteria.
The Error Alert tab has three different options for alerts about errors – Stop, Warning and Information.
The Stop option prevents you from typing data into a cell if the data does not meet the criteria. The Warning option allows you to enter invalid data, after acknowledging the error in the warning box shown above. The Information option only presents information about the criterion, but allows the user to enter invalid data.
Caution
Validation is only performed when data is entered into a cell manually. The validation technique does not work on data that is pasted into cells.
In Excel 97, you cannot use validation in cells of the frozen titles section of the worksheet.
List
Validation by list allows you to attach lists to cells. By doing this, you can select text from a list or enter text manually. The text will be validated against the list, which actually serves as the validation criteria for the text.
You can create several types of validation lists, such as a list of company employees, customer list, account list, inventory list and others.
In the Data Validation dialog box, select the Settings tab. In the Allow box, select List.
See the customer list in the figure below.
1. Select the customer list in column A.
2. Press Ctrl+F3 to define a name for the list. In the Names in workbook box, enter the name CustomerList and click OK.
3. Select cells D1:D10.
4. Choose Data, Validation.
5. Select the Settings tab, and in the Allow box, select List.
6. In the Source box, press F3 and paste the name CustomerList.
7. Click OK.
8. Select cell D1 and open the customer list (click the small arrow on the right side of the cell). Select one of the customers.
Preventing duplicate data entry
Use Validation to enter a formula to catch entry of duplicate data.
1. Select cells A2:A20.
2. Choose Data, Validation.
3. Select the Settings tab.
4. In the Allow box, select Custom.
Note – The title of the third box in the dialog box changed to Formula.
Enter the following formula into the box: =COUNTIF($A$2:$A$20,A2)=1
5. Select the Error Alert tab.
6. In the Title box, enter duplicate.
7. In the Error message box, enter The value you entered already appears in the list.
Validating text entries
The Allow box in the Settings tab does not include criteria for validating text. You can validate a text entry, but you cannot check whether the entry is text or not.
Solution: Enter a formula that will check whether the data is text.
1. Choose Data, Validation.
2. Select the Settings tab.
3. In the Allow box, select Custom.
4. In the Formula box, enter =IsText(D4) (D4 is the first cell in the range).
5. Click OK.
Copying validation
When copying a cell that contains validation criteria to a different cell, the criteria are copied along with the text, formula and format.
If you want to copy only the validation criteria, use Paste Special.
To copy only the validation criteria, first copy the cell which contains the validation, and then select a different cell and right-click. From the shortcut menu, select Paste Special, click Validation, and click OK.
Deleting validation criteria
Locate, select and delete validation criteria defined for cells.
1. Press F5, and click Special. Select Data validation, and click OK.
2. Choose Data, Validation.
3. In the Data Validation dialog box, click Clear All.
Wrapping Text
Text wrapping allows you to display multiple lines of text within a single cell and prevents the text from spilling over into the neighboring cell.
Aside from the visual importance of keeping the text in a single cell, text wrapping is also important when working with data. See Chapter 15, Data.
Text wrapping techniques
© Automatic text wrapping.
© Manual text wrapping.
© Text wrapping of text that extends beyond the selected range.
Wrapping text automatically
1. Type the following text into a cell A1: This is the best Excel book ever published.
2. Press Enter.
3. Select the cell.
4. Press Ctrl+1 (Format Cells).
5. Select the Alignment tab.
6. Select Wrap text.
7. Click OK.
If you cannot see the full text, just resize the cell to make the row or column wider or smaller.
Canceling automatic text wrap
1. Select a cell that is formatted with text wrap.
2. Press Ctrl+1.
3. Select the Alignment tab.
4. Clear the Wrap text check box.
5. Click OK.
Wrapping text manually
1. In the Formula bar, place the cursor after the word “Excel.”
2. Press Alt+Enter.
Canceling manual text wrap
1. In the Formula bar, place the cursor where you caused the text to wrap, that is, after the word “Excel.”
2. Press Delete.
Wrapping text beyond the data range
When you enter a long string of text into cell A1, the text spills over into cells that are outside the area of the table (column E) or the print area. You do not want the text to extend into column E. See the example below.
1. Be sure that the text you typed was only entered into cell A1.
2. Select cells A1:E1.
3. Choose Edit, Fill, Justify (ALT+E+ I+J).
4. Click OK and the following message will appear: Text will extend below selected range.
Caution
Before you click OK, check if there is data or text in the rows below. Allowing the text to extend below that will overwrite the existing data.
Adding a wrap text shortcut
Wrap text is a useful function that you will use frequently when working in Excel. You add the shortcut by adding a style in the Style box. For more information, see Chapter 5, Styles.
Adding the Style box to the Formatting toolbar
Right-click one of the toolbars, and select Customize. Select the Commands tab, and then select Format.
Drag the Style icon from the Customize dialog box to the Formatting toolbar, and drop it next to the Font Size box (or anywhere else you choose). Then click Close.
Adding wrap text to the Style box
Enter text or several words into a cell in the worksheet, and press Ctrl+1. Select the Alignment tab, check the wrap text check box, and click OK.
In the Style box, enter the text wrap text.
Changing the Indentation in a Cell
In the figure, note the text in Column A.
The cells contain a list of expenses for each department, with an indentation of a few characters.
1. Type the text in the cells.
2. Select the list of expenses below each division. For example, select cells A2:A6 (group of expenses for Department 1) and so forth for each group of expenses for each department.
3. Click the Increase Indent icon on the Formatting toolbar several times, until you reach the indentation you want. Alternatively, select A2:A6; press Ctrl+1; select the Alignment tab; and in the Indent box, change the number of characters to indent. Click OK.
Dividing a Title in a Cell
See cell A1 in the figure below.
1. In cell A1, enter the text Title Number.
2. Select cell A1. In the Formula bar, place the cursor after the word Title.
3. Press Alt+Enter twice (text wrap and an additional row).
4. Press Ctrl+1 (Format Cells dialog box).
5. Select the Border tab.
6. Select the left diagonal border.
7. Select the Alignment tab.
8. In the Horizontal box, select Justify.
9. In the Vertical box, select Justify.
10. Click OK.
Transposing Data
Transpose is used to change a vertical range of cells to a horizontal range or vice versa.
1. Copy a range of cells (see cells A1:C9 in the figure).
2. Select a cell in the current or any other worksheet and right-click.
3. From the shortcut menu, select Paste Special.
4. Check the Transpose check box.
The result:
See the Array Formula section of Chapter 7, Formulas for a method of transposing using a formula.
Changing a Numeric Heading to Text
When editing reports, you occasionally use numerals in headings. For example, in reports that are organized according to year, you might enter the year 2001 in the heading cells as a number and so forth.
You will want to prevent these numbers from being calculated along with the other numeric data.
The solution: enter the ‘ (apostrophe) symbol before the number. The result: ‘2001 as a text entry.
Selecting Cells That Only Contain Text
By selecting cells that only contain text, you can distinguish between cells containing different types of data, which allows you to delete, fill or lock cells by type.
Technique 1
1. Press F5, or choose Edit, Go To…
2. In the Go To dialog box, click Special.
3. In the Go To Special dialog box, select Constants.
4. Click OK.
Technique 2 – Conditional Formatting
1. Select the data area.
2. From the Format menu, select Conditional Formatting.
3. In Condition 1, select Formula Is.
4. In the Formula Box, enter the formula =Istext(A1).
5. Click Format..., choose any format from the Format Cells dialog box, and click OK.
6. Click OK.
Searching and Replacing
To search for text, use the keyboard shortcut Ctrl+F or choose Edit, Find. To search and replace text, use the keyboard shortcut Ctrl+H or choose Edit, Replace.
Using wild cards in the Text Search
Use * as a wild card for any number of characters. The * may appear before or after text.
Example: Searching for *CO will find Cisco or Telco.
Use ? as a wild card for a single character.
Example: searching for R?N will find Ron or Ran but not Rain.
To search for an asterisk, enter ~* in the search box.
Searching All the Sheets in the Workbook
The shortcut Ctrl+F allows you to search only in the active sheet.
To search the entire workbook, you must activate a macro by attaching the macro to a button or icon, or to a keyboard shortcut. (See more Chapter 26, Running a Macro.)
Using Text Formulas
In the figure below, you can see a list of text formulas, including explanation, details (Column E) and the results of the formula’s calculation (Column C).
Joining Text
You can create new text by joining text from several cells, including a combination of linked and formatted numbers.
Joining text using a keyboard shortcut
A simple technique for combining, clearing and cutting text is using the keyboard shortcuts Ctrl+C, Ctrl+V, and Ctrl+X.
Example: Select a cell containing text. In the Formula bar, select characters or full words and press Ctrl+C. Select a different cell, and in the Formula bar, enter the location where you want to paste the text you copied. Press Ctrl+V.
Joining text using a manual formula
1. In cell A1, enter the text This is the.
2. In cell A2, enter the text Best Excel book ever published.
3. In cell A3, enter the formula =A1&" "&A2.
Explanation
The ampersand symbol (&, Shift+7) joins text the same way the + symbol joins numbers. The quotation marks are used to add empty spaces between them.
In the example above, a space is added between the words combined. In cell A3, you can see the combined sentence. Combining text from different cells results in merged text.
Joining text using the concatenate function
Much like writing a formula to join text, as described, the Concatenate function allows you to merge values from multiple cells into a single cell.
In a formula, you create a space between words by pressing the Spacebar. In the second box in sample figure, pressing the Spacebar will automatically add the quotation marks when you move to the next box.
Joining text with a linked number
Example: Format a number without decimal places, with a thousands separator, and combine it with text.
1. In cell A1, enter the text You still owe the sum of.
2. In cell A2, enter the number 5434.
3. In cell A3, enter the text for invoice # 2232 from 6/15/2001.
4. In cell A4, enter the formula =A1&""&TEXT(A2,"#,##0")&""&A3.
Formatting a number with the thousands sign – the letter K
The formula =A1&” “&TEXT(A2,”#,K”)&” “&A3
The sentence appearing in the cell – You still owe the sum of 5K for invoice # 2232 from 6/15/2001.
For an explanation of formatting numbers, see Chapter 3, Formatting Numbers.
Formatting a date containing text
The formula =A1&” “&TEXT(A2,”mm/dd/yyyy”)&” “&A3
The sentence appearing in the cell – On 10/22/2000 you had a cup of coffee…
For more information on the various formatting options, see Chapter 3, Formatting Numbers.
Extracting Characters from Text
Using formulas to extract characters from text
See figure – A budget item number or an index number in accounting is made up of three parts:
© a department number – the first three digits.
© the expense item number – the next three digits.
© and secondary description of the expense – the last two digits.
Use the formulas shown in Row 6 to extract these portions from the text. You can find the formulas for extracting characters in the Text category, under the Paste function.
Parsing characters from text without formulas
Use Text to Columns to parse text.
1. Select column A.
2. Choose Data, Text to Columns.
3. In step 1 of 3, select Fixed width.
4. Click Next.
5. In step 2 of 3, you parse the data in the column by clicking the mouse between columns you want to parse.
6. Click Next.
7. In step 3 of 3, in the Destination box, enter cell B1.
8. Click Finish.
Separating first name and last name
Formulas for separating first name and last name
Cell A1 contains a name – John Smith.
The formula for extracting the first name is =LEFT(A1,FIND(“ “,A1)).
The formula for extracting the last name is
=MID(A1,FIND(“ “,A1)+1,LEN(A1)).
Separating first name and last name without using formulas
The cells in Column A contain a list of names, first name and last name.
1. Select Column A or the range of cells containing the list of names.
2. From the Data menu, select Text to Columns.
3. In Step 1 of 3, select Delimited.
4. In Step 2 of 3, select the Space option.
5. In Step 3 of 3, in the Destination box, select Cell B1, and click Finish.