Forum Discussion
How to sort blank columns with color
Hello mathetes,
On what basis are colors assigned in the first place?
It's a manual process, I have to check other data and then color the row based on the results I find, there's no way to automate it.
Do you do it manually?
Yes, I color each row manually by right-clicking on the row number and selecting a Fill Color.
Perhaps there is a better way to color the rows, so that the color doesn't extend into the empty columns, like a keyboard shortcut? I don't want to have to manually select all the fields in each row because that will add a lot of time to the process, especially when I have very long rows.
Are you familiar with Excel's conditional formatting capabilities?
I have used it a little bit but I don't see how it would apply here, perhaps you can offer me a tip.
Thank you very much for your help!!
Are you familiar with Excel's conditional formatting capabilities?
I have used it a little bit but I don't see how it would apply here, perhaps you can offer me a tip.
On what basis are colors assigned in the first place?
It's a manual process, I have to check other data and then color the row based on the results I find,
there's no way to automate it.
On the contrary, So long as that other data you're checking, and the decisions you make based on what you find, are consistent, then that's exactly what Conditional Formatting is for. So if you can describe some examples....maybe I can illustrate. Is it possible for you to post a copy of the actual spreadsheet, so long as it contains no confidential info? If you can't do it here in the forum, you could post it in OneDrive or GoogleDrive and grant access to it. Or send it to me attached to a private message here in the tech community.
I'm unable to access those sites from my office, are these screenshots helpful?
I also cannot share the data, so this is just an example file.
Colored rows, before running a sort.
After running a sort on Column 5.
The issue is the colored columns starting in Column F, they are not included in the sort.
Typically, the rows are much longer than this, they extend beyond multiple screens wide, lots of columns.
Thank you
- As an aside, I'd recommend not adding fill to cells beyond your data. Formatting beyond the data can cause a workbook to bloat. This tip will come into play with conditional formatting, too. Conditional formatting formulas are single threaded. Keep the sheet tight and optimized.
Patrick2788 wrote:
As an aside, I'd recommend not adding fill to cells beyond your data. Formatting beyond the data can cause a workbook to bloat. This tip will come into play with conditional formatting, too. Conditional formatting formulas are single threaded. Keep the sheet tight and optimized.- Yes, that sounds like what I need! I just don't know how to do that.
- When coloring a row, what's the quickest/easiest way to only fill the fields within a column that has a title/header, and not extend the color into the empty columns on the right?
- Is there perhaps a keyboard shortcut to select those fields within a row, by starting on the left?
- I'm trying to avoid scrolling all the way to the right-most field and doing a SHIFT+HOME combo.
- Is there something similar to that, but reverse... starting on the left and selecting only columns that would be included in a sort (ie clicking on the "Filter" button doesn't include all columns, just those with data).
Please let me know if you need any clarification, I sincerely appreciate your help.
Thank you!!
are these screenshots helpful?
Sadly no. All they're doing is making visible what you described at the very start. They're not answering the questions I asked.
In order to know whether Conditional Formatting could be used, I'd need some details on what different bits of data are Columns 1 through 4 and how you assess them in order to assign the various colors.
Here is a simple example of a set of conditional formatting rules I have in one of my spreadsheets:
The formulas there are relatively simple; they can be made more complex, as needed (within reason). If you can't share the exact conditions for some reason, come up with some analog. The key here is how many different colors you need to reflect what range of different conditions?
I tried to answer your questions again below, please let me know if you need clarification.
The key here is how many different colors you need to reflect what range of different conditions?
That's part of the fun, as the number of colors keeps increasing. Let's say 5 for now, but it could be 6 or 7 next week.
I think I provided an analog in the screenshots, just on a smaller level.
I can describe another analog: consider an exported list of users from a system. There are fields of data for each user, which I update sometimes, and I also edit the colors of the lines for a quick visual reference. The columns would be things like "Name" "ID#" "Description" and a plethora of additional text-based columns beyond that.
- There's nothing within the sheet that contains the info I need in order to color the lines, the magic information is elsewhere.
- Did I miss any of your questions?
On what basis are colors assigned in the first place?
It's a manual process, I have to check other data and then color the row based on the results I find, there's no way to automate it.Based on statuses that can changed outside of the sheet, and not based on any data within the sheet itself. The sheet cannot determine on it's own what colors the lines should be because the answers do not exist within the spreadsheet.
Do you do it manually?
Yes, I color each row manually by right-clicking on the row number and selecting a Fill Color.Perhaps there is a better way to color the rows, so that the color doesn't extend into the empty columns, like a keyboard shortcut? I don't want to have to manually select all the fields in each row because that will add a lot of time to the process, especially when I have very long rows.Yes, I do it manually by right clicking on an row number and selecting a fill color.
Are you familiar with Excel's conditional formatting capabilities?
I have used it a little bit but I don't see how it would apply here, perhaps you can offer me a tip.I'm sorry but I don't have better answer.
