# OK, Excel gurus, step up to the plate **URL:** \ **Category:** Factual Questions\ **Created:** [April 12, 2000, 8:43pm UTC](https://boards.straightdope.com/t/ok-excel-gurus-step-up-to-the-plate/10194 "2000-04-12T20:43:00Z")\ **Posts on this page:** 11\ **Page:** 1
**Author:** ![mouthbreather](https://avatars.discourse-cdn.com/v4/letter/m/a698b9/32.png) [@mouthbreather](https://boards.straightdope.com/u/mouthbreather)\ **Post date:** [April 13, 2000, 5:23am UTC](https://boards.straightdope.com/t/ok-excel-gurus-step-up-to-the-plate/10194/1 "2000-04-13T05:23:00Z")
IS there a “compress” feature in excel? What I mean is If I have a documnt that has 3000 rows, but only with data on every 3rd row, is there a function I can run to make it a 1000 row document with no blank lines? ---
**Author:** ![sdimbert](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/sdimbert/32/20204_2.png) [@sdimbert](https://boards.straightdope.com/u/sdimbert)\ **Post date:** [April 13, 2000, 5:34am UTC](https://boards.straightdope.com/t/ok-excel-gurus-step-up-to-the-plate/10194/2 "2000-04-13T05:34:00Z")
> [@](#): > > Originally posted by mouthbreather: > **IS there a “compress” feature in excel? What I mean is If I have a documnt that has 3000 rows, but only with data on every 3rd row, is there a function I can run to make it a 1000 row document with no blank lines?** Yes there is, and I’m no Guru. 😉 Just use the AutoFilter. Select all of your columns and then select Data -\> Filter -\> AutoFilter. You will see a little drop-down menu arrow in each collumn header. Go to the collumn with the black cells and choose “non-blanks” from the drop-down. Tada! ---
**Author:** ![mouthbreather](https://avatars.discourse-cdn.com/v4/letter/m/a698b9/32.png) [@mouthbreather](https://boards.straightdope.com/u/mouthbreather)\ **Post date:** [April 13, 2000, 5:40am UTC](https://boards.straightdope.com/t/ok-excel-gurus-step-up-to-the-plate/10194/3 "2000-04-13T05:40:00Z")
thanks, Sdimbert. one gripe with this method…it hoses the row #'s on the side. So, in my case of: 1 2 3 xxxx 4 5 6 xxxx your suggesstion yields: 3 xxxx 6 xxxx when what I want is: 1 xxxx 2 xxxx anyone know how to do this? ---
**Author:** ![starfish](https://avatars.discourse-cdn.com/v4/letter/s/87869e/32.png) [@starfish](https://boards.straightdope.com/u/starfish)\ **Post date:** [April 13, 2000, 5:41am UTC](https://boards.straightdope.com/t/ok-excel-gurus-step-up-to-the-plate/10194/4 "2000-04-13T05:41:00Z")
Not that I know of. Select your column [click the A, B, etc] at the top. [If you have several columns, click the leftmost box on the header line to select the spreadsheet.] On the menu bar, go to Data, Sort. Have it sort by the column you want. All the rows with blanks in that column will be moved together. If you need to keep your data in the original order, do this FIRST. Create a new column. fill it with sequential numbers [1,2,3, etc, Excell can generate them, but you have to mark the range. Hint, hide all but one column to reduce the time spent redrawing the screen as you scroll.] Now sort as above. Then mark the portion of the spreadsheet with “good” rows. Now sort again by your sequential column. ---
**Author:** ![Mr.Zambezi](https://avatars.discourse-cdn.com/v4/letter/m/7cd45c/32.png) [@Mr.Zambezi](https://boards.straightdope.com/u/Mr.Zambezi)\ **Post date:** [April 13, 2000, 5:46am UTC](https://boards.straightdope.com/t/ok-excel-gurus-step-up-to-the-plate/10194/5 "2000-04-13T05:46:00Z")
OR do the auto-filter function, then cut and paste the data into a new sheet. THe row numbers will be sequential. ---
**Author:** ![mouthbreather](https://avatars.discourse-cdn.com/v4/letter/m/a698b9/32.png) [@mouthbreather](https://boards.straightdope.com/u/mouthbreather)\ **Post date:** [April 13, 2000, 5:49am UTC](https://boards.straightdope.com/t/ok-excel-gurus-step-up-to-the-plate/10194/6 "2000-04-13T05:49:00Z")
starfish, I would do this, except Excel can’t effectively sort my column. I have number headings in each of format 1.1.1.1.1 1.1.1.2 1.1.1.2.1 1.1.2.1.1 And excel can’t sort those sequentially. ---
**Author:** ![mouthbreather](https://avatars.discourse-cdn.com/v4/letter/m/a698b9/32.png) [@mouthbreather](https://boards.straightdope.com/u/mouthbreather)\ **Post date:** [April 13, 2000, 5:50am UTC](https://boards.straightdope.com/t/ok-excel-gurus-step-up-to-the-plate/10194/7 "2000-04-13T05:50:00Z")
That’s it, Mr. Z. That’ll work. thanks to all who contributed. ---
**Author:** ![sdimbert](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/sdimbert/32/20204_2.png) [@sdimbert](https://boards.straightdope.com/u/sdimbert)\ **Post date:** [April 13, 2000, 5:50am UTC](https://boards.straightdope.com/t/ok-excel-gurus-step-up-to-the-plate/10194/8 "2000-04-13T05:50:00Z")
> [@](#): > > Originally posted by starfish: > \*\*Not that I know of. > > If you need to keep your data in the original order, do this FIRST. Create a new column. fill it with sequential numbers [1,2,3, etc, Excell can generate them, but you have to mark the range. Hint, hide all but one column to reduce the time spent redrawing the screen as you scroll.] > \*\* Just one note, 'cause it’s cool… Enter a 1 in the first row and a 2 directly beneath it. Then, select both cells. See the little “grab-sqare” in the bottom right-hand corner of the selection? Grab it and drag straight down… Excell will fill in 3,4,5,6…! It works for days of the week, months of the year, even a list of names you input. Excell can identify a pattern and repeat it. Cool 🆒 cool 🆒 cool! ---
**Author:** ![starfish](https://avatars.discourse-cdn.com/v4/letter/s/87869e/32.png) [@starfish](https://boards.straightdope.com/u/starfish)\ **Post date:** [April 12, 2000, 8:43pm UTC](https://boards.straightdope.com/t/ok-excel-gurus-step-up-to-the-plate/10194/9 "2000-04-12T20:43:00Z")
> [@](#): > > Originally posted by mouthbreather: > \*\*starfish, I would do this, except Excel can’t effectively sort my column. > > I have number headings in each of format > > 1.1.1.1.1 > 1.1.1.2 > 1.1.1.2.1 > 1.1.2.1.1 > > And excel can’t sort those sequentially.\*\* Do it using my second method. Make a new column with sequencial numbers. Select the spreadsheet. Sort by your column. You don’t care what order the rows come out in. All the blank values in the column being sorted will be grouped. Delete them, or just copy the rows you want to keep - including the value in the new sequential column. Now sort these by the sequntial column and your rows end up back in their original order. You can then delete the sequential column. ---
**Author:** ![starfish](https://avatars.discourse-cdn.com/v4/letter/s/87869e/32.png) [@starfish](https://boards.straightdope.com/u/starfish)\ **Post date:** [April 12, 2000, 8:47pm UTC](https://boards.straightdope.com/t/ok-excel-gurus-step-up-to-the-plate/10194/10 "2000-04-12T20:47:00Z")
I tried sorting numbers like 1.1.1 1.1.2 1.2.1 1.1.2.1 and they came out correct 1.1.1 1.1.2 1.1.2.1 1.2.1 ---
**Author:** ![mouthbreather](https://avatars.discourse-cdn.com/v4/letter/m/a698b9/32.png) [@mouthbreather](https://boards.straightdope.com/u/mouthbreather)\ **Post date:** [April 12, 2000, 9:44pm UTC](https://boards.straightdope.com/t/ok-excel-gurus-step-up-to-the-plate/10194/11 "2000-04-12T21:44:00Z")
Starfish, I stand corrected. I could have sworn that I tried that last tiem I was faced with the same problem, and it didnt work. You were right, thanks for the info. * * * That movie taught me some important lessons in life. 1. I can build a robot that loves me. 2. I can reanimate my dead girlfriend by jamming bits of metal and silicon into her skull. Both are lessons I use on a daily basis…