•1 min read•from Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community
Sorting from A-Z Suddenly Not Working
Our take
It sounds like you're encountering a frustrating issue with sorting in your spreadsheet after cell 566. Despite your efforts to format the cells uniformly, it seems that the program is treating anything beneath that line as a separate entity, disrupting your sorting process for numbers 567-569 in the "C" column. It’s understandable to feel overwhelmed, especially after trying various solutions without success. Thank you for your patience and for utilizing the community’s resources, including the option to share images for clarity.
After cell 566, my spreadsheet stopped automatically formatting. I highlighted a bunch of cells underneath #566 and finally got it to format uniformly, but now it won't sort numbers 567-569 from A-Z in the "C" column. It appears that the program thinks anything underneath #566 is a new spreadsheet.
I've Googled and tried a bunch of stuff. It's maddening. I do not want to accidentally delete this.
I also want to thank the mods here for having the option to post images when you actually can't. I had to rewrite my entire post, which has taken up more time.
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- Anomalous Sorting of COUNTIF ColumnsEDIT: Resolved as I was trimming the worksheet to upload a version with data redacted with replacement text. As I was removing extraneous worksheets, the formula broke with a #REF value. When I fixed them, the problem resolved. Looks like I was actually connected to another set of the same data, but since it as outside of the table, it was creating the anomaly inside of it. Essentially the issue outlined in this blog article, except A) I am not using the unnecessary sheet reference that fixes the problem if it's removed and B) the formula displays correctly: https://excelcharts.com/excel-sort-countif-function-mess-fix-it-how-to/ (Unfortunately, the data I'm working on is proprietary, so I can't share specifics. :( Images are mockups that simulate the results I'm getting.) I have a large table on Sheet1. Column A is a Date column that runs back several years. I would like to count instances in text column B going one year back as of the most recent update and then sort that from largest to smallest, so I can make a simple line graph with the top ten items. Updated iterations of this graph will be used in a routine report, I'm ultimately trying to draft a plug and play tool so my Excel-deficient coworkers can just Copy/Paste into Powerpoint. https://preview.redd.it/ad56jbjp0oog1.png?width=364&format=png&auto=webp&s=8fc6b43dfe338b2c93d3ce7945d5fe9b7e45e0e5 On Sheet2 I create a secondary table that will have 2 columns. The text column A and the Count column B. I have entered each unique Data value from Sheet1 into the Data column in Sheet2. In the Count column I enter the following formula: =COUNTIFS('Sheet1'!A:A,">="&MAX('Sheet1'!A:A)-365,'Sheet1'!A:A,">="&MAX('Sheet1'!A:A),'Sheet1'!B:B,A#) (Where # equal the row) The table on Sheet1 has over 20 columns and I ultimately want to create line graphs for the data in several columns. Since there will be several of these graphs, and the data in Sheet1 is appended with new, up to date data at least once a week, it makes more sense to list the range as 'Sheet1'!A:A rather than 'Sheet1'!A1:A# The formula works just fine. https://preview.redd.it/0gpltacq2oog1.png?width=260&format=png&auto=webp&s=0ba8b74005b82fa9f37813dde8f838af29d49b96 But when I try to sort by Count, it does what's pretty much spelled out in the above linked article, moving the Data cell to the correct row, with the formula adjusting to reflect it's new replacement, but returning the value associated with the Data in the row it was in before the sort. So we see "Item27" in row 28 has the highest count, with 56 occurrences. The formula in row 28 column B, as expected, is: =COUNTIFS('Sheet1'!A:A,">="&MAX('Sheet1'!A:A)-365,'Sheet1'!A:A,">="&MAX('Sheet1'!A:A),'Sheet1'!B:B,A28) After the sort, "Item27" is in row 2 (as expected) but is returning a count of 4, the amount of occurrences "Item17" (now sitting in "Item27"'s old position in row 28) returned before the sort. Of course, "Item17" is returning the wrong count as well. "Item1" was obviously displaced by "Item27" and is in it's expected row 19 but returning a count of 56, which is "Item47"'s actual count. Essentially, each "Item#" is returning a count of whatever "Item#" replaced them in their old position in the table. https://preview.redd.it/aknjet2x5oog1.png?width=249&format=png&auto=webp&s=d069620eed3867df4eabae42532654c089f1cef7 HOWEVER, unlike the example in the article, the formula sitting in B2 next to "Item47" is still correct: =COUNTIFS('Sheet1'!A:A,">="&MAX('Sheet1'!A:A)-365,'Sheet1'!A:A,">="&MAX('Sheet1'!A:A),'Sheet1'!B:B,A2) So.... what's the issue and how do I resolve it? Version: Microsoft Excel for Microsoft 365 MSO (v2508) Environment: Desktop My Knowledge: Intermediate submitted by /u/LikeCCClockwork [link] [comments]
- Font formatting of a calculation dashboard KEEPS reverting every time the data in the other sheets is updatedI am at my wits end, every time I open this spreadsheet the formatting of these cells (supposed to be large and extremely readable to low-tech users on same team) reverts back to Calibri size 11 for some reason. I even created a custom cell style to try and force it to stay that way and I can see that it's selected in the menu, but not reflected whatsoever. Is it just impossible to format cells with a constantly changing calculation? What can I do here?? I feel like I have gone through every formatting constraint/rule there is to try and force it to behave but it always changes back, even if its not right away. It's a spreadsheet stored on sharepoint primarily but I don't know if that makes a difference. Advice appreciated. 😞 submitted by /u/vailette [link] [comments]
- Spreadsheet suddenly turned all white/without any grids, rest of workbook is normalI'm not sure what I did exactly but suddenly the spreadsheet I am working on is completely white (screenshot here). I must have lazily hit a random hot key trying to quickly toggle between sheets. The rest of the spreadsheets are normal with visible data (screenshot here - sample of new spreadsheet for privacy). I don't think anything is erased since I can see there is data behind the white (the formula bar shows contents). Please tell me this is fixable. I'm in the middle of itemizing 1600 lines and I really don't want to have to start over :( Edit: gridlines are currently turned on in the view ribbon Edit 2: I'm currently working on a Mac if that helps with hotkey suggestions. Final Edit: SOLVED! Turns out I somehow hid the columns via hotkey. If you look at my screenshots, the row numbers on the left are visible but the column letters on the top are not. Addendum to final edit: So I selected an entire row and then instead of hitting "CMD -", I hit "CMD 0," which will hide the selected columns. submitted by /u/beermeupscotty [link] [comments]
Tagged with
#modern spreadsheet innovations#machine learning in spreadsheet applications#enterprise-level spreadsheet solutions#digital transformation in spreadsheet software#collaborative spreadsheet tools#AI-driven spreadsheet solutions#cloud-based spreadsheet applications#Excel alternatives for data analysis#automation in spreadsheet workflows#no-code spreadsheet solutions#AI-powered spreadsheet#spreadsheet API integration#rows.com#natural language processing for spreadsheets#generative AI for data analysis#Excel compatibility#real-time data collaboration#real-time collaboration#Excel alternatives#spreadsheet