Question

I have an excel document that is being used for reports. For readability I currently have all the columns fairly narrow and am using cell overflow a lot visually. I want excel to recognize numbers and dates as such, I just want them to visually overflow like text does.

Key complications

-Non technical users will need to make versions off this document for their own reports
-this document will be passed around as a report in excel format

EDIT

I've added an image of the section that's a problem here to clarify the problem a little. You'll notice how H is shortening the date, it contains information almost identical to M. M will do so once I click out of the cell. I want it to display like J50 or H51, both of which are flowing out beautifully.
enter image description here

Suni

Posted 2014-11-04T19:28:46.403

Reputation: 171

The TEXT function will convert any number or date to a forced text representation in the format that you specify.

– Jeeped – 2014-11-04T19:35:21.483

Would that require that the actual dates and numbers be in a different cell in order to still do math with them? – Suni – 2014-11-04T20:27:40.293

This doesn't answer the question you asked, but something I've found useful in those situations is to use a narrow font (like Arial Narrow, which is very readable), and drop the point size. This lets you fit more in a cell when the columns need to be narrow. – fixer1234 – 2014-11-04T21:08:56.883

Have you tried on the "Format Cells" Dialog, going to the Alignment Tab, and under Text Control choosing "Shrink to Fit"? – JamesTheDev – 2014-11-04T21:18:30.817

Maybe how narrow my columns are is part of the problem? I have the cells almost perfectly square with the default row height. Before you yell too much, it allows me to lay out the text really quite nicely while still using a lot of excels functionality simply by knowing the leftmost cell information will occupy. Just numbers and don't won't flow out while still being numbers and dates. – Suni – 2014-11-05T14:31:28.917

Answers

It's not clear why you need the extra columns (for example, I, K, and L), rather than just changing the column widths for what are now H and J. However, Excel will overflow a cell to display text if there is nothing in the next cell.

A simple way to accomplish what you want is to merge cells, for example H52:L52. This joins the cells to act like one big cell that uses what is in the left-most cell (or upper left cell if you are merging multiple rows). Highlight the cells you want to merge. Then click on the Merge & Center button. If you want the contents left-justified as it currently appears, use the left-align button.

You can also do this from the context menu. Select the cells to merge and right-click. Select Format Cells, then Alignment. Click the Merge Cells checkbox. The result will align the content within the merged cells according to how it is normally aligned (i.e., text is left-aligned, numbers are right-aligned, etc.). Change the alignment as you would in any cell if you want it different.

fixer1234

Posted 2014-11-04T19:28:46.403

Reputation: 24 254

This is the only method I know of that doesn't require converting dates & numbers to text. – I say Reinstate Monica – 2014-11-09T02:46:18.377

-1

you have to re format the table, you have to use one column for the entries you have "Start Date" etc, you will have a lot of problems using this format on the future use.

emirjonb

Posted 2014-11-04T19:28:46.403

Reputation: 610

1Can you expand this answer a little to make it clearer? How should the table be reformatted to handle the table and heading structure as well as the cell entries (what should be done with specific cells/columns)? What potential problems might there be in future use? – fixer1234 – 2014-11-05T16:40:09.397

the way it is formatted this table is wrong. Start date is limited only on column H and the "cell" he is using is 5 column wide and 2 row high, it is not how a normal cell is formatted. and if he want to use this value as a reference on a formula it will fail – emirjonb – 2015-05-20T07:50:00.247

How do I stop excel from turning numbers and dates to # in short columns

Question

Key complications

EDIT

Answers