MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Ctrl+Click to Insert Columns


November 02, 2017 - by Bill Jelen

Ctrl+Click to Insert Columns

Excel fast way to insert many columns using the F4 key to Redo.

Watch Video

  • I was in Lakeland Florida at Florida Southern College for an Excel seminar
  • There are five ways to do everything in Excel.
  • For example: If you need tiny columns between the columns, you could do
  • Alt + I C Alt + O C W 1 Enter over and over and over
  • But the F4 key will repeat the last action. By breaking the task in to two pieces, you
  • can do Alt + I C once and then repeatedly hit Left Arrow F4 until all the columns are inserted
  • Then, do Alt + O C W 1 Enter once and repeatedly hit Right Arrow Right Arrow F4 until all columns are smaller
  • But Lacie had a faster way:
  • Ctrl + Click each column and Alt + I C once
  • That will insert a column between each column
  • Ctrl+Click each new column and Alt + O C W 1 once
  • You could turn this trick sideways and use in to double-space the rows
  • But a faster way is the Bob Umlas trick to double space rows
  • Type the numbers 1-9 in a new column. Copy those numbers and paste to 9 new rows.
  • Sort by that column and then delete the column
  • Later, in the outtake - why you should never insert tiny columns between the columns

Auto-Generated Transcript

  • Learn Excel from MrExcel Podcast
  • episode 2166 control click to insert
  • multiple columns hey welcome back to the
  • MrExcel in that cast I'm Bill Jeleng
  • with last night I was Lakeland Florida
  • at Florida Southern College doing a
  • seminar there I always talked about how
  • in Excel there's five ways to do
  • everything in Excel there's a slow way
  • in 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
  • seven keystrokes over and over again all
  • I and then see the insert column and
  • then all o cw-1 enter to change that
  • column with the seven all right sold I
  • see OCW one enter and over and over and
  • over again but there's a faster way nice
  • and and that faster way involves the f4
  • key and usually f4 puts the dollar 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 these this set of steps is
  • two steps inserting the columns and
  • making the columns narrow so if you do
  • the alt I see once all right now the
  • last thing I did was insert a column so
  • if I just left arrow f4 left arrow f4
  • left everyone for like that right you
  • can very quickly insert the columns and
  • then if we want to do all OCW one then
  • it's right arrow twice f4 right arrow
  • twice f4 like that
  • alright now anytime i doing why seminars
  • i always talked about how some of the
  • rooms gonna have some trick that I've
  • never heard from heard about in Excel
  • and sure enough at this point lacy who's
  • a student at the college they see from
  • Middleburg Florida says hey no way you
  • could do that it just select all the
  • columns alright and at that point I'm
  • like Nellie see that's not gonna work
  • because if I select all of the columns
  • like this and do all I see it's gonna
  • insert all those columns to the left of
  • product but I jump to a conclusion
  • before lacy ever finished her sentence
  • she says hey that would work what if you
  • selected all of the columns with the
  • ctrl key with a ctrl key now I've done
  • this example hundreds of times but I
  • never tried selecting the
  • with the control keys I'm holding down
  • control and clicking each individual
  • column and check this out all I see BAM
  • it inserts individual columns
  • essentially adding a new column between
  • every column and then of course would be
  • easy to hold down the control key like
  • choose these columns and then do the alt
  • OCW one once to make everything work
  • alright so awesome
  • kudos to LaCie that cool trick that I've
  • done the other way hundreds of times I
  • see one a excel guru mission patch for
  • that all right now of course we can take
  • Lacey's trick and turn Lacey's tricks
  • sideways if we needed just for some
  • reason to double space data control
  • click each individual row and then all I
  • our to insert rows but again there's
  • five ways to do everything in Excel and
  • the way that I've showed on the podcast
  • before is Bob I'm Melissa mazing 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 all right and
  • then copy those nine numbers and paste
  • right there and then data a to Z and
  • that will double space the data is now
  • at this point we can delete everything
  • over there and column J all right always
  • multiple ways to do things in Excel now
  • this book power excel with MrExcel 617
  • excel mystery 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 and Lakeland I was
  • talking about how there's 5 ways to do
  • everything Excel and for example if you
  • need tiny columns between the columns
  • you could do the trick that I've done
  • all I see all OCW 1 enter over and over
  • and over but the f4 key I was talking
  • about the f4 key I will repeat the last
  • action and so by breaking the tasks into
  • two pieces you can do alt IC once and
  • then repeatedly hit the left arrow f4
  • until all the columns are inserted and
  • then the second to ask do the alt OCW
  • one enter once and then repeatedly right
  • arrow right arrow f4 until all the
  • columns are smaller but lacey has a
  • faster way ctrl-click each column and
  • all I see 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 I make the columns
  • narrows once you can turn the trick
  • sideways and use it to double spaced
  • rows but if we're double spacing rows
  • then Bob 'unless has a great trick type
  • the numbers 1 to 9 and you call him copy
  • and paste at 9 new rows sort by that
  • column delete the column and bam with
  • double spaced the data now later in the
  • outtake why should never insert tiny
  • columns between the columns ok I want to
  • thank you for stopping by and I want
  • like lazy for showing of my seminar with
  • that great trick see you next time for
  • another net cast from MrExcel all
  • right hey I'll take you know I I
  • actually hate this example this was a
  • real live manager that I had that made
  • me do these tiny little columns between
  • the columns and it's a disaster wouldn't
  • happen and what's gonna happen is
  • someone's gonna start part of this data
  • and not all of this data but the root
  • cause behind this whole thing was that
  • my manager I 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 all right so that would force
  • us into using the bottom border to solve
  • this problem so select each individual
  • item and come here and choose the bottom
  • border and that would get not just the
  • characters not just da-te 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 wanted he
  • cared about how things looked which
  • really drove me crazy I just wanted the
  • numbers to be right all right so if you
  • run into this manager he wants the tiny
  • little columns between the columns to
  • make the underscores look better and
  • 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 and this arrow the dialogue
  • launcher will get you back to those more
  • choices
  • all right so open this up and now
  • instead of two choices there's four
  • choices including something called
  • single accounting choose a single
  • accounting underline and you'll get the
  • the underlined 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 all right and then just finally if
  • you end up working for that manager who
  • once demands the tiny little columns
  • between the columns you know the hassle
  • is control asterisk 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 managers 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 asterisk 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

Download File

Download the sample file here: Podcast2166.xlsm

Title Photo: RenRan / Unsplash