76

In a Google Doc spreadsheet I would like to have each row the same height no matter how much text is in each cell.

I have tried "resize row" but no matter how many pixels I put in, the row expands downward to the height of the highest cell with full expanded text.

How can I force the row to stay a certain height and insert scrollbars for all text that is longer than that height?

alt text

Here's a video explaining what I mean:

http://screenr.com/Ol4

10
  • I can't find a way to do it. I'd be interested in seeing a solution to this as well.
    – ale
    Sep 22, 2010 at 12:55
  • There may be a way to show a limited amount of content and then expand it or open in a box onclick using a custom script. How tied are you to the particular interface scetched in your image? Oct 1, 2010 at 1:19
  • The solution I need to to be able to paste "endless text/images" into the little cells so that I can zoom in and look at all that text if I want but can at any time, back up and get an overview of all my notes and code. Right now, I cannot get this overview since the long texts and code inserts expand everything so far down the page. Oct 7, 2010 at 7:21
  • I've just joined, so my lack of rep points means I'm not allowed to upvote user554370's solution to this, namely to add the large bit of text as a comment instead. I'm not sure if it would have solved the OP's question or not, but it certainly helped me out, so thank you.
    – user66994
    Apr 25, 2014 at 10:56
  • Took the liberty to upvote !! Apr 25, 2014 at 12:05

17 Answers 17

26
+50

There is no solution for this one. This is just how HTML works. They are using Table for the cells and by default it's not adding scroll bars if the text doesn't fit. Maybe they will change it in the future, but I don't think that this is in their priority list. And it looks like you can merge cells only horizontally, just in case you wanted to merge vertically to make it look nicer.

8
  • 8
    I wonder why they didn't implement overflow:scroll on cells to allow for this.
    – ale
    Oct 6, 2010 at 21:39
  • 8
    Incorrect @Lipis. It's not a limitation of HTML. A simple <div style="overflow:hidden; height: 20px;">Stuff in here</div> would do the trick.
    – Levitikon
    Jun 9, 2012 at 17:26
  • 2
    @Levitikon but can you imagine how ugly would that be with so many scrollbars? and if you don't have scrollbars you wouldn't see the whole text.. and you had to guess that there is something more there to see it after you click on it or something.. I don't think that that is a good solution..
    – Lipis
    Jun 9, 2012 at 19:41
  • 4
    @Lipis: So you have cell { overflow:hidden } cell:hover { overflow:scroll } and then you only see the scrollbar of the cell you've hovering over. May 22, 2014 at 6:35
  • 2
    Now it's possible to merge cells vertically.
    – Rubén
    May 5, 2015 at 23:12
23

I've found that it works to add an additional row under the row you're working on, and then merge the two rows together.

Now, if you paste long text in any of the fields in the "double" row, it will only show you a few lines, keeping your sheet clean and manageable. The text in any field expands for you to read if you double-click on that field.

An example of a spreadsheet excerpt demonstrating the described behavior

Downsides appear when trying to rearrange or sort rows, for instance, since rows work "by two" with this solution, but maybe that's not a big problem.

2
  • Now this is a creative workaround that really works! If you don't mind the extra empty row. Great thinking! One caveat: You cannot enable filters on a range that contains merged rows like this.
    – JD Smith
    Aug 17, 2017 at 15:27
  • great trick !!!!!
    – voxter
    Nov 1, 2018 at 9:00
19

Fourth option from the right (Menu bar - once you're in the spreadsheet with the results, etc.). There is an option "WRAP TEXT", select the rows you want and click it.

This sorted it out for me. Once wrap text was on and all rows went to a single line height, I could then also select all rows and resize them all when resizing one, just like in Excel.

** The 'Wrap Text' isn't in any of the dropdown menus, it is a button on the ribbon underneath (same section as horizontal and vertical align).

5
  • 2
    This answer is just much more useful than the accepted one!
    – freitass
    Mar 29, 2014 at 19:03
  • 24
    @freitass: For the OP, this isn't 'more useful'. He has code in his cell which has many linebreaks. Turning off the wrapping wont fix his (or my) issue as you just end up with the cell being as long as the number of newlines in your content. It would work for paragraph text though. But that isn't the OP's problem. May 22, 2014 at 6:33
  • 4
    Although this answer could be helpful, it doesn't address the OP's problem.
    – Rubén
    May 5, 2015 at 23:10
  • This doesn't work if you have linefeeds in the text such as lyrics or poetry.
    – Wes Modes
    Nov 19, 2017 at 23:07
  • This doesn't address the question Feb 19 at 15:21
14

When cells contain linebreak characters, the 'Overflow', 'Wrap', and 'Clip' formatting options do not have the desired effect of limiting the height of the row.

To work around this, one can create an adjacent column that uses a formula to strip out the linebreak characters, then hide the original column. This approach preserves the ability to properly filter and sort the data.

One such formula is: =arrayformula( iferror( SUBSTITUTE(A:A,char(10), " " ) )). Paste it into row 1 (the header row) of an adjacent column, replacing A:A with the label of the source column.

Hide the original column, then apply [overflow|wrap|clip] formatting to the newly created column.

Showing the undesirable tall row

Showing a conveniently sized row, and the formula that enables it

10

How about using notes?

I had newline characters in my text that I wanted to show somehow in a single row, too - what I ended up doing was adding the long text into a note. These can be many lines and show when you float - which is about the functionality I was looking for. The only remaining problem really is that the note box is very narrow!

Here's what it looks like now when floating on the cell:

Data with newlines in notes (Google Sheets)

(I realise this doesn't quite fix the OP's problem as stated, but there's no way to achieve what they ask for, currently in Google Sheets.)

2
  • 2
    Fantastic idea, and still relevant in 2018, thank you very much!
    – Liran H
    Mar 19, 2018 at 23:36
  • yes works as expected. we can add some context like click here to view more for easy understanding.
    – Mukundhan
    Jul 11, 2020 at 16:29
6

The new Google Sheets allow to merge cells vertically. Doing this, makes that the row height is keeped; when the cell is in edit mode, the vertical scrollbar is displayed.

Example

Instructions - To merge cells vertically, and then resize the raw, you need to select the cell + one cell bellow, and than merge:

  1. Do not use CTRL key to select multiple cells to merge, but instead use SHIFT + Down Arrow key (perhaps it's a bug in Google Sheets, since selecting adjacent cells with CTRL, makes merge option unavailable).

  2. Press on the Merge Button (also available under menu: Format > Merge Cells > Merge Vertically).

2

There is no actual way, wrapping will effect cell width not it's height. Whenever it bothers me I add the text as a comment block. Simply right click on a relevant cell and select "comment" off the context menu; delete the auto generated text and paste yours. This way you'll be able to maintain a fixed row height for all your rows.

2

I am able to achieve this in two ways:

  • Turn off word-wrap on every cell in the row (as everyone has already mentioned)
  • Replace all line breaks inside the text with 1000 spaces (The 1000 spaces will appear as a line break)
  • Replace all tabs inside the text with 4 spaces (The 4 spaces will appear as a tab)

I have written a script to be run from Tools->Script Editor:

function myFunction() {

  var r=SpreadsheetApp.getActiveSheet().getDataRange();
  var rws=r.getNumRows();
  var cls=r.getNumColumns();
  var i,j,a,find,repl;
  find="\n";
  repl="                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                ";
  for (i=1;i<=rws;i++) {
    for (j=1;j<=cls;j++) {
      a=r.getCell(i, j).getValue();
      if (r.getCell(i,j).getFormula()) {continue;}
      try {
        a=a.replace(find,repl);
        r.getCell(i, j).setValue(a);
      }
      catch (err) {continue;}
    }
  }
}

This is what is looks like collapsed: enter image description here

This is what it looks like when cell is double clicked and expanded with vertical scroll bar: enter image description here

1

I agree, that so far this is impossible but have you tried using different hex characters like NBSP for your text? This would make things ugly and lose your formatting I noticed without carriage returns the length of the entry has no bearing on the height.

Edit: You should also submit your (very well presented idea) here

1

Firstly, remove all the filters from your sheet if you have any. Then, just add this script in your script editor and run.

function myFunction() {
   var ss = SpreadsheetApp.getActiveSpreadsheet();
   var sheet = ss.getActiveSheet();
   var maxRows = sheet.getMaxRows(); 
   //Logger.log(maxRows);
   for (var i=2; i<=maxRows;)
   {
     sheet.insertRowAfter(i);
     range = 'A'+ i.toString() + ':Z' + (i+1).toString();
     sheet.getRange(range).mergeVertically();
     //sheet.setRowHeight(i+1, 1);
     i += 2;
   }
}

Note: if you want to resize your rows too then uncomment the second last line.

0

If they're not offering the feature your only option may be to create a Greasemonkey script, assuming you want it that bad.

0

I was having this issue today and found that the following workaround worked best for keeping my spreadsheet organized:

  1. Right click on the row number
  2. Click "resize the row"
  3. Click "Specify row height" and enter a height in pixels
  4. Click "OK"

This will not provide a scrollbar as requested in the original question, but for me this was the best way to keep the spreadsheet organized and easy to look at.

-1

Simple. Select all cells in the spreadsheet, then unselect the WRAP TEXT button. See button highlighted in the below screenshot. Every row and column will revert back to identical sizes.

enter image description here

2
  • 6
    This only works if the text is wrapped. If the text has carriage returns or new lines (as does the text posted in the question) that button doesn't do anything.
    – zeh
    Feb 6, 2014 at 20:48
  • 1
    this helped me for a similar issue where the heights of rows would not work properly. also note that the icon is slightly different in the newest google docs (released late 2014).
    – meklarian
    Apr 14, 2015 at 22:16
-1

It has to do with the fact that you're copying and pasting from somewhere else. If you're pasting, copy the material to Notepad first and get rid of any unwanted spaces. Then paste into Google Spreadsheet. Then the wrap will work correctly.

-1

Wrap text worked for me. Here is why: After unwrapping the particular row, I noticed that there was a column for which someone had reduced the width drastically. Not even one character would fit properly in that column (but there were couple of lines of text in that column) and that had caused the row to grow in height tremendously. Since the column was so small, none of the text was visible and even after resizing, the row height did not change. Once unwrapped, all it needed was to change the column width and wrap the row again.

-3

Fourth option from the right (Menu bar - once you're in the spreadsheet with the results, etc.). There is an option "WRAP TEXT", select the rows you want and click it.

1
  • 3
    How does this answer the question? Nov 6, 2012 at 14:32
-4

Try this link below

https://support.google.com/docs/answer/58183?hl=en

This should help you resize the columns and rows.

1
  • 2
    Always include important content of link in your answer.
    – serenesat
    Jul 23, 2015 at 10:45

Not the answer you're looking for? Browse other questions tagged or ask your own question.