Excel Merging: Avoid Data Loss and Sort Errors (Full Transcript)

[00:00:00] Speaker 1: Here's how to merge cells in Excel and do it without losing data or breaking your spreadsheet. I'm Kevin, and let's fix this. Let's start with the absolute basics here in Excel. To merge cells, simply highlight all the different cells that you would like to combine, A1 here through E1. Then go up to the Home tab and on the Home ribbon right here in the center in the Alignment category, you'll see an icon to Merge and Center. Let's click on that, and there, Excel combines them into one cell and it censors it. Let's take a look at another example where I have three different values in different cells. Here, if I highlight all of them and up above, if I click on Merge and Center, you'll notice that I get a warning message telling me that it'll only keep the upper left value. If I click on OK, it keeps Kevin, but it discards Cookie Company. Merge and Center only keeps a TOFLA value. Everything else is removed. Now, if you lost data that you needed, you can always undo to bring it back. In the top left-hand corner, you can click on this icon or you can press the shortcut key, Control-Z, and that brings it all back. Hopefully that relieves some stress if you thought you lost your data. Right up on top next to the Merge and Center icon, you'll notice that there's a dropdown arrow, and if we click on that, we have a few different options. At the very top, we have Merge and Center, which is what we just looked at. It merges the cells and centers the content. Right underneath that, we have something called Merge Across, and this merges cells across each row individually instead of creating one large merged block. Let's take a look at what that does. To do that, let's jump to this next sheet called the Merge dropdown. Right up on top, I want to merge and center each one of these rows. Now here, if I simply click on Merge and Center, it'll just keep the top left value, and that's not what I want. I lost all that other data, so let's undo that by pressing Control-Z. Instead, let's click on this dropdown, and we have the option to merge across. When I click on that, it's now merged each one of these rows. Now, it's not currently centered, and if I want to center it, I can simply highlight the three rows, and then right up on top, I can use all the different alignment tools. So over here, I could center a line. Here, we could right align, but I like to place it in the center, so I'll go with that. Let's now scroll down just a little bit more where I have another header for this sales report. I could highlight all of these cells. Let's click on the dropdown, and here I could simply merge the cells. This is similar to merge and center, but it doesn't actually center it. It just left aligns it. Of course, I could also take these merged cells, and right up above, I could apply all of the different alignment tools. Now, let's say you want to turn off the merging of the cells, or maybe you'd like to turn them back into separate cells. You could simply click on the merged cells, then go right up on top, and over here, you could unmerge the cells. So I could click there, and that then returns it back to what it originally was. Now, right up on top, you could also click on these, and you could simply click on this icon, and that toggles off the merged cells. So you could either go through this dropdown or simply toggle this icon. So that's how merging works in Excel, but this is also where things can start to break if you're not careful. Here, I have a simple table with headers and also data underneath. Now, you'll notice I have some merged cells included in the data table. Now, if I try to sort the data, I'll click right here. You'll notice that Excel throws an error. It tells me it can't sort because merged cells need to be the same size. I'll click on OK right here. This is one of the biggest issues with merging. Excel relies on a consistent grid structure. When you merge cells, you're changing that structure. So merging might look harmless for formatting, but once you start analyzing, sorting, filtering, or building reports, it can actually create problems quickly. In many cases, the cleanest solution is to not merge at all. So right over here, I'll select this cell and let's turn off the merged cells. Now, instead of entering no cells, I could simply type in a zero across each one of these cells. But if you want that centered visual look, here's the pro method. This will give you the same visual effect as merge and center, but without actually merging the cells. Let's remove all of these zeros and over here, let's type in no cells. Then I'll select all of these cells. Now we could right click and then go down to format cells, but here's a shortcut key that's even easier. Simply press control one and that opens up format cells. Right over here under the alignment tab, we have text alignment for horizontal. Click on that dropdown and right here near the bottom, we have an option called center across selection. Let's click on that and then click on okay. Now look at that. The text appears centered across the selected cells, but the cells themselves are still separate. That means I can sort. Right up on top, let's sort and there I no longer get an error message. I could also filter and work with my data normally. Nothing breaks. Visually, it looks the same, but structurally it behaves much better. This is why many experienced Excel users prefer center across selection for both headers and also layout formatting instead of merging. You get the appearance you want, but without changing the grid structure of your spreadsheet. If your goal is to combine values in cells, don't merge, instead use a formula. Let's take a quick look. Here I have two cells that I want to combine. We could use a variety of different formulas and functions or we could use something called flash fill to combine them. Let's start with the simple one using the ampersand symbol. To enter a formula, I'll start with the equal sign and over here, I'll select the first cell that I would like to merge together. Then let's enter in an ampersand and I would like to include a space between the first name and the last name. So over here, I'll open quotes, space, close quotes and then enter in another ampersand and I want to combine it with the last name. Then let's press enter and here I could see the first name and the last name combined together. Now, if this looks good over here, I could click on this icon in the bottom right-hand corner and that applies it to all the other rows. Now you could also use the concatenate function. Here I'll enter it in. You simply specify which cells you would like to concatenate and I'd also like to have a space between them. Here, I'll press enter. This is an older Excel function and I'll copy it all the way down. It works in a similar way. Lastly, we also have a newer function called text join. Now this is especially helpful if you're combining many cells or adding separators like commas. Here, I'll enter in the function. Here, I could specify the delimiter, what to do if there are any empty cells and then which cells I would like to combine and here, I'll press enter and again, I could copy it all the way down. Lastly, you can use flash fill. Here in the first cell, I'll type in how I want Excel to combine the data. I'll type in Kevin Stratford, including that space in between the first name and the last name, then I'll press enter. Now, I'll select this cell and you can go to the fill menu. Down at the bottom, you have the option for flash fill. If you hover over, you'll also notice that the shortcut key is control E. So let's try that. I'll press control E and here, it automatically fills it down, applying that same pattern to all of the remaining rows. Now, the one downside of flash fill, this is not a live formula. So over here, if any of the data changes, let's say I remove the last name, you'll notice all of these update, but not flash fill. And there you go. Now remember, merge is for looks, not for data analysis. I'll see you in the next one.