Excel – Indenting Cell Contents
The contents of a cell can be left-aligned, right-aligned, or centered. By default, numbers are right-aligned, and text is left-aligned. Figure 1 shows a table of data that uses the default alignment. As you can see, entries in the Sales and Region columns appear right next to each other, making it difficult to read.
Figure 1: Default cell alignment can make information difficult to read.
One solution is to change the alignment of the Region column. By making the text centered or right-aligned, the text wouldn’t be next to the numbers in the Sales column. Another option is to indent the Region text.
Most Excel users probably believe that a VBA macro is required in order to display a drop-down list in a cell. But it’s not. You can easily display a drop-down list in a cell, no macros required. Figure: 1 shows an example. Cell B2, when selected, displays a down arrow. Click the arrow, and you get a list of items (in this case, month names). Click an item, and it appears in the cell. The drop-down list can contain text, numeric values, or dates. Your formulas, of course, can refer to cells that contain a drop-down list. The formulas always use the value that’s currently displayed.
A common type of worksheet uses two types of cells: input cells and formula cells. The user enters data into the input cells, and the formulas calculate and display the results.
Figure bellow shows a simple example. The input cells are in the range C4:C7. These cells are used by the formulas in C10:C13. To prevent the user from accidentally typing over formula cells, it’s useful to limit the cursor movement so that the formula cells can’t even be selected.
– This worksheet has input cells at the top and formula cells below –
Setting up this sort of arrangement is a two-step process: Unlock the input cells and then protect the sheet. The following specific instructions are for the example shown in Figure above:
- Select C4:C7.
- Press Ctrl+1 to display the Format Cells dialog box.
- In the Format Cells dialog box, click the Protection tab, deselect the Locked check box, and click OK. By default, all cells are locked.
- Choose Review -> Changes -> Protect Sheet.
- In the Protect Sheet dialog box, deselect the Select Locked Cells check box and make sure that the Select Unlocked Cells check box is selected.
- Specify a password that will be required to unprotect the sheet. (Optional)
- Click OK.
After you perform these steps, only the unlocked cells can be selected. If you need to make any changes to your worksheet, you need to unprotect the sheet first, by choosing Review -> Changes -> Unprotect Sheet.
Although this example used a contiguous range of cells for the input, that isn’t necessary for the steps to work. The input cells can be scattered throughout your worksheet.
When the Data Form dialog box appears, the first record (if any) in the list is displayed. Notice the indicator in the upper right corner of the dialog box; this indicator tells you which record is selected and the total number of records in the list.
To enter a new record, click the New button to clear the fields. Then you can enter the new information into the appropriate fields. Press Tab or Shift+Tab to move among the fields. When you click the New (or Close) button, the data that you entered is appended to the bottom of the list. You also can press Enter, which is equivalent to clicking the New button. If your list contains any formulas, they’re also entered automatically into the new record in the list for you.
One of the most common Excel questions is “How can I prevent Excel from creating automatic hyperlinks?” Normally, Excel watches you type, and if it looks at all like you’re typing an e-mail address or a Web URL, the entry is converted into a hyperlink. Sometimes that capability is helpful, but sometimes it’s not.
Excel 2002 introduced a handy feature: text-to-speech. In other words, Excel is capable of speaking to you. You can have this feature read back a specific range of cells, or you can set it up so that it reads the data as you enter it.
For some reason, this feature appears to be missing in action, beginning with Excel 2007. You can search the Ribbon all day and not find a trace of the text-to-speech feature. Fortunately, the feature is still available, you just need to spend a few minutes to make it available…..
Although most users work with decimal values, some types of data are normally displayed as fractions, not as decimals. This tip describes how to enter noninteger values as fractions. To enter a whole number and a fraction into a cell, leave a space between the whole number and the fractional part. For example, to display 67⁄8, type 6 7/8 and then press Enter. When you select the cell, 6.875 appears on the Formula bar, and the cell entry appears as a fraction…