Forum Discussion
Excel changes cell format from no wrap to wrap on its own without my asking it to
I'm going to start with the observation that your question has been here in this forum for three days now with zero replies. That suggests to me that what you're asking doesn't totally sound like an Excel problem per se, certainly not an Excel problem that the experts here are particularly interested in. I'm not that much of an exception, but that you asked piqued my curiosity, so I thought I'd dive in with a question or two for a more complete description.
- First question: what is the task, the job, that you're asking Excel to do. Not at the cellular level; the bigger picture.
- What's the nature of this text that you're entering? What's its source?
- Why is word wrap such a bother in your context?
- WHY are you doing it in Excel? Are there some other features of Excel, some functions, some reporting, that you plan to use, tasks or functions that other packages (such as Word) can't perform?
I ask, because Excel, though it can handle text, isn't primarily a text editor. Text can be there as part of a database, or report, and can play a crucial role in very valid Excel applications, but it's rarely the heart and soul of the application.
- Here is a full description of the problem.
I have a large table, hundreds of lines long and about 8 columns wide. I want to be able to select (filter) rows on various criteria, sort rows on multiple criteria, and search. All of these are fine. The problem is that in one column, the each cell is a series of comments on the row that it is in. If these cells were always fully visible, only two to four logical rows would display on the screen at once. I want to display just the top line of the comments in the cell, so I can view a lot of rows at once.
In the original source (more on that later), the comment cells were a list of comments, separated by carriage returns, within the cell. In table format, only the top line of comments showed, but when I clicked on a comments cell, the full list of comments was shown over the table, and I could edit that list.
With that table in Excel, the comments cells are all formatted as no wrap. That means they are all displayed on one line, and go off to the right until there is an occupied cell, then the rest of it is just not shown. But if I click on a comments cell, it shows up in the formula bar, and if I expand the formula bar window, all of the comments show up as a list, on separate lines.
The problem occurs when I want to add another comment. I go to the comment cell as above, and insert a new line by typing <cntl><option> <carriage return> and the text of the comment. In the formula bar, this looks perfect. But when I got back to the table, the comments cell has been reformatted as wrap, and may now be 10 or 15 lines long. If I
reformat the cell as no wrap, it goes back to the way it was.
The question is what is causing Excel to change the format of the cell, and how can I stop that from happening.
Background on the source document. Apple used to have a productivity suite called AppleWork, which had a very simple database component (really just tables like I described above). That suite was replaced by iWorks, then Numbers, etc. But the replacements did not include a database component, and Apple’s recommended db product did not do what I needed. MacOS only supported the AppleWork until OS 10.6.8 (Snow Leopard), which was replaced about 2013. I kept an old Mac with that OS just to keep running the app, but now I am trying to get the functionality I want on a 2020 iMac, so I have experimented with various ways to export/save as from the old Mac, paste/import to Excel, and format things in Excel to get to where I am now.
Thanks for any suggestions you can provide.
BillOh, my! So you are using Excel really as a database manager. Excel does work for that, has some really good tools for it, in fact. You might want to look into a full database manager instead. Access (sadly) isn't available for Apple (I'm on Apple's various computers as well; iMac, MacBook, Mac Mini).... I used to have Access, at work, on a Windows system. It's powerful.
There are alternatives, but I've not had any real need. Airtable is one, and I do know folks have had good results with it.
In Excel, I'm going to defer to somebody else on your formatting/re-formatting frustrations. I have no magic bullet there.
One thought I have, though, is to separate that column with all the comments from the rest of your table. Make sure there's a project code or some other unique identifier so that you can join the two tables whenever you want to (joining in much the way you would with a relational database).
But that may not be functional with how you actually USE your data.
Are you familiar with the new Dynamic Array functions? FILTER in particular, and how it could help in your searching of all those rows based on multiple criteria? If not, here's a helpful introduction:
https://www.youtube.com/watch?v=9I9DtFOVPIg
I'm sorry I can't be more helpful....it really does sound more like an application calling for a more robust database manager.
- Thanks for your suggestions.
Although the data was from a simple database manager, I only need to display, filter and sort on multiple (but simple) criteria and search in one column for a single term. I do not use any of the other features of a database manager.
Excel seems to do everything I need except for the frustrating formatting issue.
A few years ago I did look into Mac based DBMS, but did not find one that would let me export the data with embedded CRs into a cell. I even spent a while at the Apple Genius Bar trying to move data from Appleworks to the then Apple recommended database product, Benneto (sp?), and they could not figure out how to do it. And, of course, Benneto is now gone too, so I would still be in the same place if I switched to Benneto. At least Excel looks like it will stay around indefinitely, so I will be a cautious about small company products.
I have only used Excel for simple spread sheets in the past, and my next task is to find a way to set up frequently used filters and sorts as one or two click tasks and not go thru menus. And there is probably a way to make the cell (or whole column) reformatting a one click operation instead of going thru menus. I’m hoping your pointer to dynamic arrays will help with that.
