Selected Answer
Susan
Thanks for uploading a (first) file.
To keep the larger comment visible, you just need to set Autofit on the rows of your table, as follows:
- Select the rows of your table*.
- On the ribbon, go .Home / Cells / Format then
- Click Autofit Row Height..
Now when you sort by anything, the row with the larger comment will be displayed in full anyway.
The first revised file attached has that done and is sorted by Last, Z to A.
(* While you have that selected, personally I would also set the cell vertical alignment to Top on Home / Alignment- the default of Bottom might be fine for Accountants but just feels odd to me with text!)
Revision 21 July 2023
Susan- now you replaced the first file with a quite different one, youi've commneted autofit deep row make others "look scrunched". That kind of inevitable if there's a great disparity between the length of your Notes column and data in other columns.
Without using VBA (e.g. to adjust the font size for verbose cells), if you want to increase the overall legibility of your data, you can do three things (done in the second revised file below):
- Select columns A-G of your table ONLY, autofit the widths - on the ribbon, go .Home / Cells / Format then Click Autofit Column Width (some of your columns will shrink but the headers might still leave them too wide- you might add returns in the longer headers so their width is reduced e.g. see column B, which is now:
Price
Effective
Date
- Use the width saved to expand column H (Notes). For the non-blank entries (including your nonsense data), the average length is about 58 characters (with 271 in the longest). I've set it for about 90 on average
- Limit what a user can enter in that column using data validation- I've used that in cell H8 so users can't use more than 150 characters and can't use returns in the cell (which could make very deep cells)- the Custom data validation formula used is:
=AND(ISERROR(FIND(CHAR(10),H8)), LEN(H8)<=150)
which triggers an error if the user includes a return or exceeds 150 characters in H8. I then used PasteSpecial/ Validation to apply that to other cells in that table column.
The latter doesn't fix existing entries but Excel can show you the offending ones- go riibbon Data/ Data Tools/ Data Validation (icon with green tick/check and red stop sign) then click Circle Invalid Data. Cell H36 (e.g.) will then show a red ellipse. If you go to that cell and try to re-enter the same text, you'll get an error.
Note that as you add to the table, that data validation will be added to each now row.
Hope this fixes your problem. If so, please remember to mark this Answer as Selected.
