Excel is taking an educated guess that you want the result cell formatted the same way as the referenced cell(s). If you were to reference cell A1 along with many other cells that have any standard Excel format-e.g., =SUM(A1:A10)-the result cell would still take on the custom format of cell A1. If you were to reference this cell in a formula-e.g., =A1+20 - the result cell would take on the custom format.
Then click OK.Īlthough the cell displays the word Hello, you can see its true value by selecting the cell and looking in the Formula bar, or by pressing F2. Select Format » Cells » Number » Custom, and using any format as a starting point, type "Hello" (with the quotation marks). For example, type any number into cell A1. It is important to note that formatting a cell's value does not affect its underlying true value. If you enter text into a cell, Excel will display the words "No Text Please," regardless of the true underlying text. Any zero value will have no currency symbol and will show two decimal places. It will do the same for negative values, except they will show up in red. If you enter a positive number as a currency value, Excel will format it automatically so that it includes a comma for the thousands separator, followed by two decimal places. We modified it by adding a separate format for zero values and another one for text. The custom number format shown in the figure is Excel's standard currency format, which shows negative currencies in red. Text is affected by custom formats only when you use all four sections the text will use the last section.ĭon't interpret the word number to mean custom formats applying to numeric data only. If you include only one section, all number types will use that one format. In other words, if you include only two sections, the first section will be used for both positive numbers and zero values, while the second section will be used for negative numbers. When you create a custom number format, you do not have to specify all four sections. Each section is separated by a semicolon ( ). Excel sees a cell's format as having the following four sections (from left to right): Positive Numbers, Negative Numbers, Zero Values, and Text Values. ="Today is " & TEXT(NOW(),"dddd, mmm dd, yyyy")Īgain, the quotation marks are important, as you are constructing a text string.Before you try these hacks, it helps if you understand how Excel sees cell formats. Enter the following formula in some cell: Here's an example that displays "Today is " along with today's date. The different formats you can use with the TEXT function have been covered in other issues of ExcelTips, and you can also find more info in Excel's Help system. To force the above results to display the value to two decimal places, use the following formula. To affect the value formatting, use the TEXT function. (Remember, the result of the formula is text, not a number.) One might naturally display the Format Cell dialog box and then choose a Number format that has two decimal places, but the results would not change.
For example, consider the above formula and the need to display two decimal places. The disadvantage of this approach is formatting the value takes more effort since the result is a text string, numeric cell formatting does not apply. By making the text string part of a formula, you can combine the description and the value within one cell.
Here's a very simple example that displays "1 + 1 is 2." Creating text strings easily accomplishes this feat. For instance, a numeric value could go in cell B3, and the unit description in cell C3, which read together may be something like "3.27 miles."Īnother approach is to put the description text and the numeric value together. One approach is to put the description-a simple text string-near the cell containing the data that needs describing.
Many times, I want a description for my data.
0 kommentar(er)
