* Logo
* Translator Tools
*
* Motto
* *
TransTools suite Knowledge base Feedback Back to Index | Site map
*
*
*
*
**
**
**
***
***
***
***
**
**
*
*
*
*
*
*
Also on:

TransTools on Twitter TransTools on Google Plus TransTools on LinkedIn TransTools on Scoop.It!
*

Cell resize wizard

Excel
Category: Document preparation / FormattingBack to Formatting section

Undo Undo support - YES. See general Excel undo notes.

You are all probably familiar with the following problem: you take a great looking Excel spreadsheet, you translate it in Excel or a CAT program like SDL TagEditor, and when you are finished you cannot see all the text. It simply does not fit within the original dimensions of the cells, but Excel cannot do anything about it. So you have to go through each cell, strain your eyes and resize each row so that the text fits on the screen.

Example of text which is not fully visible
Screenshot: Partly invisible text

Cell Resize Wizard is created so that you can forget about this problem once and for all. The wizard has several options that you can use to find and resize cells interactively, highlight all such cells for manual resizing, or resize all of them automatically.

Screenshot: Cell Resize Wizard dialogue

Why Excel cannot always resize cells automatically?

The problem described above occurs in the following cases:

  • If a cell's Wrap Text property is off, the text will never wrap to a new line. Instead, it will continue to the right until it hits another non-blank cell in the same row. Example >>
    Excel: text with Wrap Text = off
    Here you can see that the cells are not merged (if the cells were merged, the selected area would surround the entire merged area), but the text is still visible. The only exception is the last line - the text is cut off because it hits a non-blank cell.
  • If a cell's Wrap Text property is on, the row will automatically expand to fit the text of all cells in the row. However there are two catches here:
    1. If the document author changes the row height manually, Excel will never try to resize the row even if the text does not fit within a cell. However, you can fix this by using Autofit command (Excel 2003 or earlier: click Format -> Row -> Autofit; Excel 2007 or later: click Format -> Autofit Row Height from the Cells group of the Home tab).
    2. If the row contains at least one merged cell, Excel will be confused and will not resize the cell either.

Key Functionality:

There are three key actions that the Cell Resize Wizard can perform, each giving you a different result:

  1. Option 1: Find cells that need resizing and resize them interactively (active sheet)

    This is the most flexible option offered by the wizard. When it is used, the wizard starts from the current selection and finds the next cell that requires resizing. After you make sure that the cell does need resizing (see the Notes below), you can do one of the following actions:

    1. If you want the text to be within the cell selected by the Wizard, click Resize to resize the cell automatically. Alternatively, without closing the wizard, you can resize rows / columns just like you normally do by dragging row / column borders within the Excel window.
    2. If you want the text to occupy additional cells that are nearby, make a single selection covering all those cells and click Resize button. Make sure that 'Additional cell formatting' in the Options is set to 'Wordwrap + Merge'.
  2. Option 2: Highlight all cells that need resizing (active sheet)

    When this option is used, the wizard determines all the cells on the current sheet that require resizing and highlights them with a specific fill or font color. After this, you can resize these cells manually, or switch to Option 1, select the highlighted cells (including adjacent cells if necessary), and resize them automatically. The advantage of this option is that you will never overlook text that does not fit on the screen, and your client will be happy.
  3. Option 3: Resize all cells (active sheet)

    When you use this option, the wizard resizes all the cells on the current sheet automatically. This is the quickest option, but it should be used with caution (see Notes).

Main Options:

  1. Option 1: Find cells that need resizing and resize them interactively (active sheet)

    Screenshot: Option 1 interface

    Direction: Choose the direction of the search - "Right, then Down" or "Left, then Up". In both cases the search is done row by row.

    Find Next button: Click this button to find the next cell on the active worksheet that requires resizing, starting from the currently selected cell.

    Resize button: Click this button to resize the current cell (or a selection of severall cells) in accordance with the Options. The text will be wrapped, and, optionally, merged to occupy the selection.

    Modify Selection buttons:

    Expand Selection to the Right Side – click this button to expand the selection before clicking Resize button.

    Expand Selection to Unused Cells on the Right (Smart Expand) – click this button to expand the selection to the right until a non-empty cell is encountered. This is very convenient if the text has Word Wrap = off and extends beyond the right border of the cell.

    Shrink Selection to the Left Side – click this button to shrink the selection by one cell before clicking Resize button.

    Advanced Options button: Click this button to display and modify additional search and resize options.

  2. Option 2: Highlight all cells that need resizing (active sheet)

    Screenshot: Option 2 interface

    Highlight Colour: Choose a fill or font color to apply to the cells that the Wizard finds on the active worksheet.

    Highlight All button: Click this button to highlight all the cells on the active worksheet that require resizing.

    Advanced Options button: Click this button to display and modify additional search and resize options.

  3. Option 3: Resize all cells (active sheet)

    Screenshot: Option 3 interface

    Highlight Colour: Choose a fill or font color to apply to the cells that the Wizard finds on the active worksheet.

    Resize All button: Click this button to resize all the cells on the active worksheet that require resizing.

    Advanced Options button: Click this button to display and modify additional search and resize options.

Advanced Options:

Screenshot: Cell Resize Wizard Advanced Options

  1. Scope:
    Choose 'Text cells only' (default) to search in textual cells, or 'All cells' to search all cells (including dates, numbers, etc.).
  2. Cells with vertical text:
    When the Wizard encounters cells with vertical text (rotated 90 or 270 degrees) which does not fit within the cell, you have the option of resizing the last column of the cell instead of the last row. In other words, the cell will be expanded horizontally, not vertically. Choose 'Resize column' (default) to do this. Otherwise, choose 'Resize row' to expand the cell vertically. View example of vertical text >>
    Excel: Vertical text example
  3. Do not resize if difference smaller than:

    Usually, when Excel determines the required height or width of a cell, it needs to leave a small distance between the text and the border of the cell. However, you may encounter spreadsheets where the rows or columns were resized in such a way that this condition is not observed, but the text is still visible enough. In such cases you can tell the Wizard to ignore cells whose height or width is different from the ideal height/width by the specified value. For cells which will be resized vertically, this value is measured in points, for cells resized horizontally it is measured in characters (abbreviated as 'chars').

    Note: The column setting only applies to cells with vertical text and will be used only when 'Cells with vertical text' is set to 'Resize column'.

  4. Use consistent sizes:
    From time to time, you will come across spreadsheets where rows and columns have standard heights / widths. For example, you can see this in engineering datasheets describing equipment. In this case, you probably want to use row heights or column widths that are consistent with the original document. View example >>
    Excel: Grid-like document
    Text in this document is entered in a grid of equally sized rows and columns. You may want to keep this consistency in the translated document.

    Use the controls to select up to 6 standard row heights or column widths.

    For example, if the current height of a cell is 20 points, but it needs to be expanded to 36 points, if you set 'Row Height 2' to 40, the cell will be expanded to 40 points instead of 36.

    For convenience, use the Set button to pick height / width from the current spreadsheet. Select one or several cells whose total height or width is equal to the value you want to assign to the level selected from the list, and click Set.

    To disable this option, click Clear.

    Note: The column width settings only apply to cells with vertical text and will be used only when 'Cells with vertical text' is set to 'Resize column'.

  5. Additional cell formatting (Option 1 only):
    When you resize cells in the interactive mode (using Option 1), you can select more than one cell so that they are merged before resizing. This is very convenient when the original Excel spreadsheet contains cells with Wrap Text = off. Although such cells are not merged, visually their text occupies several cells, and you may want to merge the cells so that the text in the translated document occupies the same cells. View example >>
    Excel: text with Wrap Text = off
    Here you can clearly see that the cells are not merged (if the cells were merged, the selected area would surround the entire merged area), but the text is still visible.

    Select 'Wordwrap + merge' to merge cells if several cells are selected (cells will be merged even when no resizing is required), and 'Wordwrap only' if you do not want to merge cells (a notification will be displayed if you try to resize a selection containing several cells).

Notes:

  • The Cell Resize Wizard determines the required height / width of a cell based on the following:
    1. There should be a bit of space between the text and the cell border (called 'margin').
    2. The text should not extend past the borders of the cell. This means that the text should wrap within the cell (Wrap Text = on).
    3. Cells with horizontal text and non-vertical rotated text (i.e. not rotated 90 or 270 degrees) will always be resized vertically (i.e. by resizing the last row of the cell).
    4. If 'Cells with vertical text' property is set to 'Resize column' (under Advanced Options), and the cell contains vertical rotated text (i.e. rotated 90 or 270 degrees), then it will be resized horizontally (by resizing the last column of the cell).
    5. If 'Cells with vertical text' property is set to 'Resize row' (under Advanced Options), and the cell contains vertical rotated text (i.e. rotated 90 or 270 degrees), then it will be resized vertically just like a cell with horizontal text.

    However, the text may be perfectly visible even when Cell Resize Wizard decides that it should be resized. Using Option 1, you can make a decision whether to resize a cell
    1. by checking that the contents of the cell visually fit on the screen (e.g. the contents of a cell with Wrap Text = off may fit on the screen, but not within the current dimensions of the cell). View example >>
      Excel: text with Wrap Text = off
      Here you can see that the cells are not merged (if the cells were merged, the selected area would surround the entire merged area), but the text is still visible.
    2. by checking whether the cell contents displayed in the formula bar fully fit within the cell.
  • Always save the spreadsheet before using Cell Resize Wizard. Whenever you click Find Next, Resize, Highlight All or Resize All buttons, you will not be able to undo any previous actions. However, you can undo the most recent Resize, Highlight All or Resize All actions.
  • If you use any of the Wizard's commands and no cells are found/resized, and then you close the spreadsheet, you may still get a dialogue asking you whether you want to save the file even if you saved it before using the Wizard. The reason for this is that the Cell Resize Wizard creates a hidden sheet which is used to calculate cell dimensions, and deletes it before you close the file, therefore the file becomes modified. This hidden sheet is deleted any time you close or switch workbooks while the Wizard dialogue is on the screen or when the Wizard is closed, so this hidden sheet will never be seen by anybody (and it's empty anyway). When you see the Save dialogue, you can click 'No' if you are sure that you previously saved the file, or click 'Yes' to save the current state of the spreadsheet (without the hidden sheet).
  • Option 3 (Resize all cells) should be used with caution. Some spreadsheets have a lot of cells whose text is not wrapped (Wrap Text is off), but the text visually occupies several cells. For now, the Wizard is unable to make an intelligent decision about merging such cells, so you should use Option 1 in such cases, or change cell dimensions suggested by the Wizard using Option 3.
  • Sometimes the worksheet or workbook may be protected. A notification will be displayed in such cases. You will need to remove protection as follows:
    • If the current worksheet is protected, try to remove protection with an empty password (In Excel 2003 or earlier: click Tools -> Protection -> Unprotect Sheet...; in Excel 2007 and later: click Unprotect Sheet located in the Changes group on the Review tab). Some worksheets have an empty password, so try this.
    • If the workbook is protected, you will need to remove protection (In Excel 2003 or earlier: click Tools -> Protection -> Unprotect Workbook...; in Excel 2007 and later: click Protect Workbook located in the Changes group on the Review tab, and uncheck 'Protect Structure and Windows'). Some workbooks have an empty password, so try this.
Developed by Stanislav Okhvat, 2007–2016

Microsoft Word®, Excel®, PowerPoint® and Visio® are registered trademarks of Microsoft Corporation.
Autocad© is copyright of Autodesk, Inc.
SDL Trados® (including SDL Trados Studio, Trados Workbench, TagEditor and Microsoft Word Addin) is a registered trademark of SDL plc.
memoQ is copyright of Kilgray Translation Technologies.
Wordfast© is copyright of Yves Champollion.

Software disclaimer