In this article you will learn the use of the Hash symbol (#) and At the Rate symbol (@) in Excel. Actually, there are different-different use if symbols. it depends on conditions and data structure. so i have explained deeply about the use of these 2 symbols in Excel.
Use of the “#” Symbol in custom formatting in Excel
In Excel, the # symbol is used as a placeholder character in custom number formatting to represent a digit or a space. Custom number formatting allows you to control how numbers are displayed in cells without changing their actual values. You can use various symbols and characters in custom number formatting to achieve the desired formatting.
Here’s how # is typically used in custom number formatting:
- Single
#: It represents a digit placeholder. When you use a single#, Excel displays a digit if one exists in the corresponding position, and if not, it displays nothing (a space). For example, if you format a cell as#,#, and you enter the number 12345, it will be displayed as “12,345” with the comma as a thousand separator. - Multiple
#characters: You can use multiple#characters in sequence to represent multiple digit placeholders. For example, if you format a cell as###, and you enter the number 12345, it will be displayed as “123.”
Here are some more examples of how # can be used in custom number formatting:
0.00: This format displays two decimal places and includes leading zeros if necessary. For example, 12.345 would be displayed as “12.35.”#,##0: This format uses commas as thousands separators and displays at least one digit. For example, 1234 would be displayed as “1,234.”##0%: This format displays a number as a percentage with no decimal places. For example, 0.456 would be displayed as “46%.”# ?/?: This format is used to display fractions. It displays the whole number with a space and then the fraction. For example, 2.75 would be displayed as “2 3/4.”#,###;(#,###): This format uses parentheses to display negative numbers in a different format. For example, -1234 would be displayed as “(1,234).”
The # symbol is just one of the placeholder characters you can use in custom number formatting in Excel. Other common placeholder characters include 0, ?, and *, each with its own specific meaning and use in formatting numbers. You can create complex custom number formats by combining these characters to achieve the desired formatting for your data.
Use of the “@” Symbol in custom formatting in Excel
In Excel, the “@” symbol is used in custom formatting as a placeholder for text or alphanumeric characters. When you include the “@” symbol in a custom number format, it tells Excel to display any text or characters that are in that position in the cell’s value.
Here’s how you can use “@” in custom formatting:
- Displaying Text: You can use “@” to display text or labels alongside numeric values. For example, if you have a cell with a value of 42 and you apply the custom format
"@ units", the cell will display “42 units.” The “@” symbol serves as a placeholder for the numeric value, and any text you specify is displayed as is. - Mixing Text and Numbers: You can combine “@” with other custom number format codes to mix text and numbers in a single cell. For instance,
"Product: @###"would display “Product: 123” if the cell contains the number 123. - Displaying Alphanumeric Codes: If you have cells with alphanumeric codes, you can use “@” to ensure that the entire code is displayed. For example, if you have a cell with the value “ABC123” and you apply the custom format
"@-###", it will display “ABC123-“. The “@” symbol ensures that the alphanumeric code is shown as it is. - Displaying Text Based on Conditions: You can use “@” in conditional custom formats to display different text based on the cell’s value or conditions. For example,
[@>0]"Positive";[@<0]"Negative";"Zero"could be used as a custom format to display “Positive” for positive values, “Negative” for negative values, and “Zero” for zero.
In essence, “@” in custom formatting is a versatile tool for incorporating text or labels into the display of cell values, allowing you to create custom representations that combine numbers and text as needed for your specific use cases.
Join Our WhatsApp Group
Nazim Khan (Author) 📞 +91 9536250020
[MBA in Finance]
Nazim Khan is an expert in Microsoft Excel. He teaches people how to use it better. He has been doing this for more than ten years. He is running this website (TechGuruPlus.com) and a YouTube channel called "Business Excel" since 2016. He shares useful tips from his own experiences to help others improve their Excel skills and careers.
