6

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

  • The TEXT function will convert any number or date to a forced text representation in the format that you specify. – user385793 Nov 4 '14 at 19:35
  • Would that require that the actual dates and numbers be in a different cell in order to still do math with them? – Suni Nov 4 '14 at 20:27
  • 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 Nov 4 '14 at 21:08
  • Have you tried on the "Format Cells" Dialog, going to the Alignment Tab, and under Text Control choosing "Shrink to Fit"? – JamesTheDev Nov 4 '14 at 21:18
  • 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 Nov 5 '14 at 14:31
3

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.

| improve this answer | |
  • This is the only method I know of that doesn't require converting dates & numbers to text. – I say Reinstate Monica Nov 9 '14 at 2:46
-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.

| improve this answer | |
  • 1
    Can 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 Nov 5 '14 at 16:40
  • 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 May 20 '15 at 7:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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