In the real world, sometimes the user would like to present two or more values in a single cell. For eg in the sales report, we might need sales and the respective %share in the same cell. So in this article, we will learn how to display count and percentage in the same cell with the help of an example. In the below, sample data, have given sales data in $ and market share in Percentages. We need to add a column to show both Sales $ (Share %) in a cell as in (Img2).
So to do this task, we use the following methods:
1. CONCAT() Function: CONCAT function is an Excel built-in function and allows to join 2 or more text/strings. Or we can say it joins two or more cells in a single content. This function must contain at least one text as an argument and if any of the arguments in the function is invalid then it will return an error.
Example: When we execute CONCAT(“geeks”, “-“, “for”, “-“, “geeks”) we get geeks-for-geeks as a output.
2. TEXT() function: The TEXT function is also an Excel built-in function and returns a text with a specified format. It is useful when you want to combine text or symbols or when you want to display numbers in a more readable form.
Here, the value parameter is used to convert to text, and the format_text parameter is used to format to apply the result.
Example: When we execute this TEXT(1000, “$0”) we get $1000 as an output.
Now follow the following steps to put count and percentage in one cell:
Step 1: Type column header “ $ Sales ( % Share)” in cell E2.
Step 2: We use the Excel TEXT() function to retain excel format and the CONCAT() function to join four texts.
- Text 1 – Sales $
- Text 2 – Open bracket
- Text 3 – Share %
- Text 4 – Close bracket
Type the formula in cell “E3”
=CONCAT(TEXT(C3,"$ 0")," (", TEXT(D3,"0.0%"),")")
Step 3: Drag formula E3 to E8 to fill the same formula to all other cells
This is how we can easily put count and percentage in one cell.