Last Updated on
Last Updated on
It can be a little daunting looking at spreadsheet after spreadsheet of information in a Microsoft Excel workbook. Page after page of names, addresses, wages, inventory, and more can be a lot to manage. Thank goodness for Microsoft Excel Formula.
Every formula represents a handy shortcut for users, which in turn makes spreadsheets and workbooks more efficient are clearer. Having information laid out and clear to see is one thing, but combining said information for the sake of easy-to-find information and clearer charts is a handy feature.
Rather than just talking about it, let’s show how to do it. There are actually two formulae when considering how to combine cells in Excel using the Ampersand symbol (&) and the CONCAT function. Essentially, the Ampersand function is great for combining two cells, with CONCAT being a preferred function for multiple combined cells. Let’s go through both functions and how to implement them:
The following is for combining just two cells, to combine information from more cells, please skip to the next step below. Let’s start by clicking into the cell where the formula result needs to lie. From here we need to tell Excel that formula is about to be entered by typing = followed by the first cell reference. This should then be followed by an Ampersand and a space enclosed in quotation marks, followed by another Ampersand and the second cell reference.
As we can see from the below example, the formula would be =A2&” “&D2.
Finally, if the whole column needs to display this formula, left-click on the bottom-right corner and drag the cell box down to apply the formula to the whole column as displayed below.
This formula is applied in the same way as the Ampersand formula above, only it supports multiple cell inputs, meaning multiple combinations of cells. Click into the cell where the combination needs to lie and start things off with a = symbol to initiate a formula input. Next, write CONCAT followed by a bracket and cells references needed. The cell references need commas in between and the space wrapped in quotation marks to put a space after every comma. Once inputted, close the formula off with a closing bracket.
The example we’ve used below reads as =CONCAT(A2,” “,D2).
Again, if the spreadsheet needs the formula result copied to the whole column, left-click the bottom right corner of the CONCAT cell and drag over any cells needed in the same column.