Currently Being Moderated

Rotate cell contents in Numbers 3: Workaround

Acknowledgement to co-author SGIII (see Method 1 on how to automate this using AppleScript).

 

 

Overview of this User Tip

 

A frequently-asked question In Numbers is how to rotate cell contents to vertical (or some other angle). The short answer is that you can't. However, there is a workaround using Text Boxes that can be rotated.

 

There are two methods described in this User Tip:

  • Method 1: Use AppleScript to automate the process (Recommended if you want it to "just work'";
  • Method 2: Rotate the Text Boxes manually (Recommended if you want to see how the process works).

 

Method 2 (Rotate the Text Boxes manually) gives two options for "hiding" the underlying cells:

  • A: Use a colour (color) fill in the Text Boxes that matches the fill in the underlying table cells; or
  • B: Use the default 'No Fill' in the Text Boxes and "hide" the contents of the underlying table cells by changing the text colour (color) to match the cell fill (Recommended).

 

Be aware that formulas will not reference the contents of the Text Boxes. The contents of the underlying cells must correctly reflect what the formulas are searching for.

 

 

Method 1: Use AppleScript to automate the process

 

This script:

  • creates Text Boxes from Header Row cells;
  • the Text Boxes have No Fill (recommended);
  • text in the Header Row cells is hidden by making the text colour match the cell background fill;
  • The Row Height of the Header Cells is automatically adjusted to accommodate  the longest Text Box.

 

Steps for you to do:

  • Manually adjust the column widths;
  • Select any cell in the table;
  • Copy the script, paste it into the AppleScript editor and Run the script.

 

Here is the script by SGIII.


--generates "vertical" or "slanted" text boxes from contents of header cells

--set columns to desired width, click a table cell, click 'run' button

property textRotation : 90 --> 90 is vertical, 1-89 slanted

property shiftRight : 2 --> moves a box right from table column's left edge

property lenFactor : 6 --> adjust as needed to fit font/size

property lenPadder : 10 --> adjust as needed to fit font/size/text length

tell application "Numbers"

 

  --put focus on the selected table

  try

  set s to front document's active sheet

  set t to s's first table whose selection range's class is range

  on error

  display alert "First click in a cell of the table." buttons "Ok"

  return

  end try

 

  --get horizontal and vertical offsets (on sheet) for first text box

  set {hOffset, vOffset} to t's position

  set hOffset to hOffset + shiftRight

 

  tell t's first row

 

  --get font and hide the text

  set headerFont to font name

  set text color to background color

 

  --collect content of header cells and place in AppleScript list

  set headerValues to cells's value

 

  --estimate length for longest text box:

  set maxLen to 0

  tell s to repeat with i from 1 to count headerValues

  set thisLen to (headerValues's item i's character count) * lenFactor + lenPadder

  if thisLen > maxLen then set maxLen to thisLen

  end repeat

 

  --set row height to accomodate longest text box:

  set height to maxLen

 

  end tell

 

  --create text box for each item in the list headerValues:

  tell s to repeat with i from 1 to count headerValues

  set myTextItem to make new text item with properties ¬

  {object text:headerValues's item i's contents, width:maxLen, rotation:textRotation, position:{hOffset, vOffset}}

  set myTextItem's object text's font to headerFont

  set hOffset to hOffset + (t's column i's width)

  end repeat

 

end tell

--end of script

 

 

Example of using the script with a wide Column A and narrow Columns B, C, D.

Screen Shot 2014-08-05 at 1.50.19 am.png

 

Manually adjust column widths of the table.

Screen Shot 2014-08-05 at 1.52.06 am.png

 

Run the script, then delete the text box in A and change the font colour back to what it was (to see Cell A1).

Screen Shot 2014-08-05 at 2.28.28 am.png

 

 

Method 2: Rotate the Text Boxes manually

 

Here is a table in Numbers 3.2 with long text strings in Row 1 (the Header Row):

Screen Shot 2014-08-05 at 1.57.15 am.png

 

Select the contents of each Header cell (one at a time) and Menu > Edit > Copy (command c).

 

Paste (command v) each cell content, one at a time, to a blank piece of the 'canvas' to create Text Boxes.

 

Screen Shot 2014-08-05 at 1.58.08 am.png

 

Note: selecting the cell and then copying and pasting to the canvas will create a new one-cell table. Not what you want! Select the cell contents (click twice in a cell and drag to select the contents)

 

There are two ways to "hide" the Header Cells (in the table) behind the Text Boxes:

  • A: Use a colour (color) fill in the Text Boxes that matches the fill in the underlying table cells; or
  • B: Use the default 'No Fill' in the Text Boxes and "hide" the contents of the underlying table cells by changing the text colour (color) to match the cell fill (Recommended).

 

A. Change the background fill of each Text Box to match the Cell Fill of your Header Row.

 

Bring up the Colour Window with Format Panel > Style > Fill > Color Fill and click on the rainbow wheel:

Screen Shot 2014-08-05 at 2.21.04 am.png

 

Hint: command shift c is the quick way to bring up the Colours window with all those rich options.

 

I use 'Mercury' from the Crayons Palette because that matches my default Header Row fill. Drag the matching colour from the Colour Window to each of the Text Boxes.

Screen Shot 2014-08-05 at 2.01.55 am.png

 

Select, then rotate the Text Boxes to 90 degrees:

Screen Shot 2014-08-05 at 2.05.24 am.png

 

Screen Shot 2014-08-05 at 2.07.43 am.png

Now those Text Boxes are ready to slide over the Header Cells. You will have to resize the table Columns/Rows and/or the Text Boxes to to get a good appearance. Use Format Panel > Arrange > Align or Distribute, or fine tune with the arrow keys on your keyboard.

Screen Shot 2014-08-05 at 2.08.28 am.png

 

Done.

 

 

B. Give Text Boxes No Fill and "Hide" the contents of the Header Row by changing the Text Colour (color) to match the Cell Fill. (Recommended).

 

This method of No Fill in the Text Boxes makes it easier to align the Text Boxes. The cell contents are "hidden" by making the cell text match the cell fill. No need to worry about how far to "stretch" the text boxes. No need to worry about accidentally covering the cell borders.

 

Text Boxes with No Fill ready to slide over the table cells:

Screen Shot 2014-08-05 at 2.09.07 am.png

 

Set the text colour of the table cells to match the cell fill.

Screen Shot 2014-08-05 at 2.09.53 am.png

 

Now slide the Text Boxes over the table. Use Format Panel > Arrange > Align or Distribute, or fine tune with the arrow keys on your keyboard.

 

Done.

Replies

Delete User Tip

Are you sure you want to delete this user tip?