Hi, this is Wayne again with a topic “Better Than Merge and Center: Excel’s Center Across Selection”.
Foreign to merge and center in your Excel spreadsheets and you’ll be able to see that it’s better in at least a couple of ways. So here we have a spreadsheet with some sales information from first quarter. Second quarter, third quarter and fourth quarter, and I have a duplicate of this information here at the right just so that we can compare and contrast, and here at the left, let’s set up a merge and center. So here we have some text. First quarter album sales that I would really like to Center above this data, because it applies to all of this data so many times in Excel when we want to do this, what we tend to do is click and drag to select the range that we would Like to merge together and then we go here to the Home tab home ribbon here in the alignment group, you should see this symbol and or the text merge and center. So I click that and you can see it’s now merged together. This is now what used to be five separate cells now has been merged together into what looks like one cell and the text is centered inside that one big cell and I can do the same thing here – click and drag merge in center and so on. But let’s look at some of the potential problems that happen when you merge in Center. So let’s say I decide.
I want to create a formula here and I’d like to figure out what was the highest number of album sales in a particular quarter for a particular album, so I’ll, just type in equals Max left parenthesis, and then here I’d like to select all of the copy Sold data for the year, so I could click and drag to highlight this and then go down here. But look as soon as I hit the merged and centered area here. Excel is not allowing me to just select column D, and so when I go all the way down here to the bottom, I can then tap enter and I get a number, but this isn’t the number of copies sold. This is just the highest number in the range that I had selected and I wasn’t allowed to just select column D and the reason why is because I had merged and centered this area here.
So the fact that I had merged and centered this area really ruined my formula. It caused some serious problems for this formula and made it so I couldn’t get the information that I wanted easily. Another example, as you may already know, when you click and drag to select a range, you get some automatic calculations down here at the bottom of your Excel screen.
So, for example, I can just quickly see that there’s 11 records selected, the smallest number in that range is 15.. The biggest number in that range is 132 and the sum of all of those numbers is 661.. So that’s some great automatic calculations that have been done in Excel, but look what happens when I click and drag across a merged area, same problem as we had with the formula right now. The automatic calculations down here are not really relevant. They’Re looking in the entire range, not just the column that I’m interested in, so let’s look at a better instead of using merge and center.
I can click and drag to select the range that I’d like to Center, very similar to what I did with merge and center, but instead of clicking merge and center, I’m going to click here on this alignment launch button in the lower right corner of the alignment Group and I’m simply going to go here to horizontal General and I’ll switch it to Center across selection click that click, OK and look, what happens? It looks identical to merge and center, but with Center across selection. Look, I still have my separate cells, and so it’s not going to interfere with my formulas or calculations or anything else. Let’S do the same thing here: click and drag to select the range now this time, instead of going up here to the alignment group and clicking the launch button, I want to show you a shortcut. You can just hold the control key and tap the number one and it brings up the same format, cells, dialog, box and again here I’ll just switch to Center across selection. Okay, a and it worked beautifully.
Give me a second to do the same thing with these other two quarters and then I’ll resume the video now that I’ve done Center across selection for all four quarters. Let’S take a look, let’s say I want to sum up all of the sales for the entire year. I can just click and drag to select that data and look there’s no problem at all, selecting all the way down the spreadsheet. Why? Because each of the columns, each of the cells is still intact, there’s no merging going on here at all. Instead, the text is simply centered across the selection. So I can look down here and see the sum of all the orders is 2803. What about my formula? Let’S try the same formula again equals Max. I want to see the highest number of sales in a particular quarter for a particular album, and I can now simply click and drag all the way down to select the range tap enter and the highest number of sales. It looks like was 150 for this Depeche Mode album and, of course, instead of clicking and dragging I can could have simply typed in equals. Max left parenthesis and then selected, column, J and Excel lets me do that, because these cells are not merged and centered. I can now tap enter, so I hope that you’ll consider using Center over selection instead of merge and center.
Now it’s still important to be aware of merge and center to know how it works to know where it’s found for one reason, because sometimes you’re using other people’s spreadsheets. So, even if you have decided to always use Center across selection for some very valid reasons, you’re going to be using spreadsheets in most cases that other people have created as well and if they’ve done immersion Center and you want to turn it off, you need to Be aware of it, you need to know how to select the merge area, go to the Home, tab home ribbon in the alignment group and just deselect the merge and center option, and that range now returns to the typical unmerged State and then, of course, at that Point you could switch it to Center across selection click, okay and you’re good to go. So, while it’s important to be aware of both options, I highly recommend Center across selection. Thank you for watching.
I hope you found this video to be helpful. If you did please like follow And subscribe, and when you do click the Bell so you’ll be notified. When I post another video, if you’d like to support my channel, you can do that by clicking the thanks button below the video or you can support me through. My patreon account, or by buying Channel merch and you’ll, see information about those options in the description below the video speaking of patreon. I want to give a quick shout out to my five dollar patreon supporters.
Thank you. So much to Kurt Michelle and David they’ve been supporters of my channel for so long, and I appreciate so much all of their support and I appreciate really all of my patreon supporters, including Gita and Peter who have been supporters for several years as well as David Ann and so many others, and even my most recent patreon supporter Barbara, thank you all so much. You may not realize how much your support means to me.
Thank you. .