Ctrl+Click to Insert Columns

Learn Excel From MrExcel, Podcast Episode 2166: CTRL+CLICK To Insert Multiple Columns.

Hey. Welcome back to the MrExcel netcast. I'm Bill Jelen, and, last night, I was in Lakeland Florida at Florida Southern College doing a seminar there, and I always talk about how in Excel there's 5 ways to do everything. In Excel, there's a slow way and a faster way, and I was talking about the crazy manager request where they want tiny little columns between the columns and so you end up doing these 7 keystrokes over and over again ALT+I and then C to insert column and then ALT+O, C, W, 1, ENTER to change that column width [to 7 – 00:38]. Alright, so, ALT+I, C, ALT+O, C, 1, ENTER over and over and over again.

But there's a faster way and that faster way involves the F4 key and, usually, F4 puts the $ signs in a reference but, when you're not editing a formula, F4 will repeat the last command that you did. So, you can think about this set of steps as two steps: inserting the columns and making the columns narrow. So, if you do the ALT+I C once, alright, now, the last thing I did was insert a column so if I just left arrow, F4, left arrow, F4, left arrow, F4, like that, you can very quickly insert the columns, and then, if we want to do ALT+O, C, W, 1, then it's right arrow twice, F4, right arrow twice, F4, like that.

Alright. Now, anytime I’m doing my seminars, I always talk about how someone in the room is going to have some trick that I've never heard about in Excel, and sure enough, at this point, Lacie -- who's a student at the college, Lacie from Middleburg, Florida -- says, “Hey, no, wait. You could do that, just select all the columns,” alright, and, at that point, I'm like, “No, Lacie, that's not going to work because if I select all of the columns like this and do ALT+I C, it's going to insert all those columns to the left of PRODUCT,” but I jump to a conclusion before Lacie ever finished her sentence. She says, “Hey, that Would work. What if you selected all of the columns with the CONTROL key?” and I’m like, “What? With the CONTROL key?”

Now, I've done this example hundreds of times but I never tried selecting the columns with the CONTROL key. So, I'm holding down CONTROL and clicking each individual column and then -- check this out – ALT+I C, bam. It inserts individual columns, essentially adding a new column between every column, and then of course it would be easy to hold down the CONTROL key while I choose these columns and then do the ALT+O, C, W, 1 once to make everything work. Alright. So, awesome. Kudos to Lacie. [That cool trick that I've done the other way hundreds of times, Lacie won a Excel Guru Mission Patch – 02:44] for that.

Alright. Now, of course, we can take Lacie's trick and turn Lacie's trick sideways. If we needed, just for some reason, to double-space data, CONTROL+CLICK each individual row and then ALT+I R to insert rows, but, again, there's 5 ways to do everything in Excel and the way that I've shown on the podcast before is Bob Umlas’s amazing way of just adding an extra column out here with the numbers 1 through whatever. So I put in the 1, hold down the CONTROL key, and drag the fill handle, alright, and then copy those 9 numbers and paste right there, and then DATA, A to Z, and that will double-space the data. Now, at this point, we can delete everything over there in COLUMN J. Alright. Always multiple ways to do things in Excel.

Now, this book, Power Excel with MrExcel: 617 Excel Mysteries Solved, my book that has a lot of tricks just like the tricks in this book. Click that i in the top right-hand corner to check it out.

Alright. This long episode from Florida Southern College in Lakeland, I was talking about how there's 5 ways to do everything in Excel, and, for example, if you need tiny columns between the columns, you could do the trick that I've done -- ALT+I C ALT+O C W 1 ENTER -- over and over and over, but the F4 key, I was talking about the F4 key, will repeat the last action, and so, by breaking the task into two pieces, you can do ALT+I C once and then repeatedly hit the left arrow F4 until all the columns are inserted, and then the second task, do the ALT+O C W 1 ENTER once and then repeatedly right arrow right arrow F4 until all the columns are smaller.

But Lacie has a faster way: CONTROL+CLICK each column and ALT+I C once. That will insert a column between each column in a click. The trick there is the CONTROL key. That's the thing that makes that work, and then, of course, we can do all the columns at once, make all the columns narrow at once.

You can turn the trick sideways and use it to double-space rows, but if we're double-spacing rows, then Bob Umlas has a great trick. Type the numbers 1 to 9 in a new column, copy and paste to 9 new rows, sort by that column, delete the column, and, bam, we’ve double-spaced the data.

Now, later, in the outtake: why you should never insert tiny columns between the columns. Well, hey. I want to thank you for stopping by and I want to thank Lacie for showing up to my seminar with that great trick. See you next time for another netcast from MrExcel.

Alright. Hey, the outtake. You know, I actually hate this example. This was a real live manager I had that made me do these tiny little columns between the columns, and it was a disaster when it happened, and what's going to happen is someone's going to sort part of this data and not all of this data, but the root cause behind this whole thing was that my manager cared about how things looked, right, and so if we would add an UNDERLINE, you see it only underlines the characters in the cell, not the whole cell, and he wanted the whole cell underlined, alright?

So, that would force us into using the bottom border to solve this problem. So, select each individual item, come here and choose the BOTTOM BORDER, and that would get not just the characters -- not just D-A-T-E -- but all the way across the cell, but then he wanted tiny little gaps between because, of course, when he printed, the grid lines aren't there and, you know, he cared about how things looked, which really drove me crazy. I just wanted the numbers to be right, alright?

So, if you run into this manager who wants the tiny little columns between the columns to make the underscores look better, then here's an awesome trick, right? Instead of using the UNDERLINE or the DOUBLE UNDERLINE, which are the two choices there -- remember there's always more choices hidden in the dialog box behind the ribbon -- then, this arrow, the dialogue launcher, will get you back to those more choices. Alright, so, open this up and now, instead of 2 choices, there's 4 choices, including something called SINGLE ACCOUNTING. Choose the SINGLE ACCOUNTING UNDERLINE and you'll get the underline that goes all the way across, not just the characters in the cell, but leaves the tiny little gaps at each end. So, we get that look that my manager was looking for years and years ago.

Aright, and then, just finally, if you end up working for that manager who demands the tiny little columns between the columns, you know, the hassle is CONTROL * to select the current region is not going to select the whole data set. So, what I do is I sneak in here when the manager’s not looking and type a space bar, and then a space bar here, and then a space bar here, all the way across. Put space bars all the way across -- I've already done that -- and then CONTROL * will select the whole range and we have less of a chance that someone's going to sort part of the data and not all of the data.