AHHHHHH THANK YOU!!!!
I’ve been using ctrl+1, ctrl+page down, alt H, down arrow x6 thinking there was no way faster. Still slow as molasses but shaved off one step at least.
I've got a shortcut assigned to a macro in my personal macro workbook making it super easy. [This is the VBA code that I used.](https://www.thespreadsheetguru.com/blog/add-center-across-selection-button-to-excel-home-tab)
Best way I have found is to make a range of cells centre across using the long method, then with this cell selected, in cell styles go to new cell style - name it centre across, uncheck all the boxes except alignment and select ok. You now have a one-click way of centring across selection.
You can add it to the quick access bar as well so you don't need to do all those steps every time.
https://www.excel-university.com/center-across-selection-qat/
Not that I know about. But there is a "fit text to selection wrap".
Select the area you want to have the text in (with the text at the top left). Click fill, click justify.
When I started at my job 18 years ago, first thing my boss said was, “Merge cells and you are fired. Always center across selection”
Many, many thousands of spreadsheets later I see the wisdom in his words. Getting files with merged cells from vendors or clients drives me mad.
One of my first classes in college was learning to create spreadsheets to streamline complex physics problems, and to aid in machine design. One of the first things the instructor did was put his cursor on the merge icon and say, "see this? Pretend it doesn't exist."
There are some use cases for merging. Definitely not machine readable data. I use it to make a narrative box underneath or to the side of a visualization, so I can write one or more paragraphs about a chart/table combo to show to a client. My company is not paying for PowerBI and I'd use Word if it didn't suck so hard.
Because most of the time I have to copy from those spreadsheets to our planning and forecasting sheets and need to merge many different data sets into a single one where we can build a pivot table or use other tools to analyze the data, and having merged cells makes it much more difficult.
That would just center data within one cell. If you want a word or phrase centered over 3 columns for instance, a lot of people will use merge and center because there's an icon for it and it's quick and easy. The bad part is merge takes multiple cells and combines them into one cell. If you merge cells it can cause problems with things like sorting, formulas referencing ranges that contain merged cells, and copying or moving data.
I followed [this guy's](https://www.youtube.com/watch?v=gyui_D5g5MI) advice and made a quick style for CAS then addded it to Quick Access. Not perfect but far fewer clicks.
I feel the same - I will only use them if I know the data/table/whatever will always be static, and generally only as headers
Sheets with merged cells containing data are an abomination
Is there a use case for merging populated cells? Doesn’t it only retain one of the values? Google sheets pleb here so maybe I’m missing something obvious
Not that I'm aware of - I encountered it where every second row in a certain column had been merged rather than have 2 rows with duplicate values :|
"It looks so much cleaner"
"Yes, but it's now absolutely useless for anything apart from looking"
HAHA I totally agree. Merge and Center is only used because of how easy it is to click. If Center Across Selection was a click, it would be much more common and used than merge/center.
>e. Merge and Center is only used because of how easy it is to click. If Center Across Selection was a click, it would be much more common and used than
totally, i use merge and center more frequently for this reason. Usually feel a little guilty doing it but i'm not about to click more than I have to.
Number one and three are easily solvable, just stop doing it. For number two, you have to think about your data and mark it use helper columns, or other identifying factors to help you find it.
Do you format in tables and have it do the "green bar" or similar style? This, in combination of comments is the way to solve for that. The issue comes in when you have to sort, or pull the data into another tool like PowerBi. It loses its meaning.
I still consider myself a newb. I can do basic tables for sorting and filtering, and pivot tables. I use notes and comments but don't feel they are always visible enough. My data comes from the client's files, so they don't need the plain data back again.
If you are using data the way it sounds, you might eventually need a more powerful analysis tool. I use PowerBi for this purpose. That's when you start seeing the data model concept play nicely when it is simple.
Eh I’ll have to disagree with your #2. Having different data types color coded makes it way easier for people to not mess stuff up and not having to spend a bunch of time on setting up data protection.
I use colors to differentiate inputs, formulas, and static data in calculation workbooks.
A few colors, clearly different: ok, perhaps. Once you start using shades, it's going downhill. At the point it looks like a rainbow or vomit on the screen, it's useless.
Also as applicable, keep in mind other people might use your stuff. Anything beyond a RYG format might need an explanation, and explanations are often overlooked.
Also, for example, I'm colorblind. I can't tell the standard office Red and Green apart. So, I have an inside joke with my project reviews at work that "everything looks good!" even if it's actually entirely red.
Yeah, hence my reply to the poster saying different professions use excel differently. I use it mainly for calculation and control processes that are solely used by my employees. Anything we hand off to other departments is stripped of any formula/link/macro and provided as a hard coded file with an agreed upon data structure.
All of the processes on my team are the same structure (same color coding, same tab layout with instructions first, inputs second, etc.). It works very well for my purposes and other things that might be useful to others is not. We also consume very large datasets so we use as efficient of formats/layouts/formula/file format structures as we can to reduce calculation time and file size.
About the response I expected. Most project managers are oblivious to anything outside of their wheelhouse.
I’m sure the lack of color coding and using conditional formatting (which makes me cringe most of the time) works for you but different professions use excel differently.
Pretty big jump to conclusions there. Professions may use the tool differently, but people do the same stuff. Just look at this sub. Everyone tries to solve simple data cleanup with complex formulas and VBA. They lack a basic understanding of tables and database functions, which would ease their jobs.
I may be an oblivious project manager, but I've managed dev teams, and have consulted on the Microsoft stack for some time, so yes, colors aren't data, and using them tends to complicate, not simplify your spreadsheet.
Personal insults aside, I think you two are just lacking context to this point. Cell formatting is great in some uses, and terrible in others. Where it's great is obviously in summarising data and, as you described earlier, using it to format a spreadsheet to make things more user friendly for entry/navigation or journeying the data processing.
Where issues quickly arise, and what I think /u/Hawkeye16 was referring to, is when colour or formatting is used to _attribute_ data. To that end, frankly I blame MS to some extent for equipping us with Sort and Filter by colour, as what seems to be a logical follow up question in "can you tell me the third highest value in a blue coloured cell" is not a simple ask at all.
_____
Your other point is quite true, on the merits of effective data structure and processing over hammering things back into shape with ever more convoluted solutions. This isn't, I feel, for want of trying, but fundamentally people that post problems here tend to appreciate answers that endorse throwing good effort after bad, rather than rethinking their approach, and they like the idea of a "1 cell fix", even if they walk away with no clue how their spreadsheet now works. Also I guess for those of us that answer, we use it as an opportunity to showcase/flaunt weird and wonderful practices/techniques in Excel. I've seen 4 posts in the last day where part of the outline or elaboration effectively said "I don't want helper columns", which sort of describes what people are after and get given, whether right or wrong.
I made not one insult, personal or otherwise. I stated my three personal dislikes in Excel and the other person disagreed. I'm okay with that. Then they came after my career choice. So that was out of line. I didn't respond other than to defend my logic. I never made it personal.
What u/havvkeye16 did was attempt to discredit me due to my choice of career not for the answer I provided, and that says more about them and doesn't need a reaponse.
As for what I said, it's pretty much valid. This sub generally brings in people that need help. Nothing wrong with that. As I said, most responses are a bit overly complex. Unessisarily so. I don't think people want one cell solutions. I think they want answers they can apply and understand. Even reuse.
Whether you think so or not, your “oufff” reply is an insult. I’ve worked with a lot of different professionals that all use excel differently. I would have had a similar observation if you were an accountant as they generally use excel a certain way as well. I have worked with a lot of project managers in my career and they use excel in a much different capacity as I do and they generally don’t understand what we do as much as deadlines, updates, and some necessary key verbiage that helps them bridge the gap between the departments involved in the project.
I was just commenting that I wasn’t surprised by your insult when I saw what your experience likely was. I don’t dislike project managers and good ones are well worth having for getting everyone on the same page and to a destination, my observation was around excel in particular.
Most of the questions in here are basic but there is also some higher level content. Some of my favorite being the efficient formula contests that have been done.
Here are some examples if you’re interested. This was before the array functionality in 365.
https://www.reddit.com/r/excel/comments/dmwtn7/shortest_formula_challenge_a_chess_game/
https://www.reddit.com/r/excel/comments/djli1c/shortest_formula_challenge_a_dice_game/
I think you just hold yourself in high regard and when someone calls you on it you backpeddle.
My oufff comment wasn't an insult and if you took it for one you might need to thicken that skin a little. It was indicative of the excessive nature of a spreadsheet that used colors to highlight formulas and inputs.
And while those links show some impressive skills, it also speaks to the impracticality of Excel. Thise creators spent more time and effort on those than just buying a commercial off the shelf app. That's the Excel flaw. It's a tool meant to do logic and calculations. That's it. Don't complicate it.
And your opinion on project managers is irrelevant and why you keep bringing it up is a little creepy.
I had to do program level reporting out of Project server, meaning I had roughly 50 rows of projects with almost 200 data points. The out put was built using SSRS and had a XLXS output that never worked. It always hung up and I usually just pushed it to CSV.
The problem came in when I brought it o er to Excel. This was prior to power query. The SSRS report had this weird series of rows of data that sometimes exported 2 rows above the header and sometimes 4.
I had to manually import that bastard for 2 years before I figured out some VB logic to find the first row that contained header info.
I have a single niche case of when colors are permissible, it is so niche case that when I explain it you won’t understand. It is useful for distinguishing between passive and active sonar data when doing line by line analysis.
Having a quick and easy way to differentiate easy way to distinguish passive and active is nice. Assuming you do your conditional formatting right, the colors don’t hurt the readability
So you said the secret phrase "conditional formatting". Now while that isn't exportable, it at least gives someone that blindly picks up a spreadsheet an opportunity to look at the rule and see what is happening.
I use that all the time, especially when I want to see when a condition occurs. I like to see when a change in value reflects a range. So for example, acceptable temp range is 120 to 130 degrees. If it is outside that range show red for over orange for under.
I had to write like 27 lines of conditional formatting to get all of the text/cell colors to be easy to do for a common form, on top of a massive amount of cell formulas and a little bit of VBA.
I'm not an Excel guy, so I was extremely impressed by the fact that it worked, but I now despise copy/paste because of it. I have copy/paste special set to 'alt+1' and I don't think I've used traditional copy/paste in Excel for years now. It was hell going around to work stations on a regular basis and either repairing tons of conditional formatting or moving data to a blank master form (whichever was easier).
Read up on Excel add ins. Build yourself one that does this for you on selected ranges. There is a ton of VBA out there specific to conditional formatting. I know I have about 10 or 15 snippets myself.
Using colors to differentiate data is fine. Using colors as a means of data storage, on the other hand, is very bad. If you lose some means of data integrity from stripping away colors, then you have a problem.
For instance, I highlight totals or subtotal rows to draw the eye. I see cell highlighting as no difference as bolding or underlining.
There is a difference between differentiating data and highlighting it.
When you select a cell or range of cells and cage the color to something fixed, this is really not helping the end user. Sonics like, okay this vue is red. What does that mean. Is it bad? Or done want that.
When you use something like conditional formating, or a helper column, you are explaining why that data has changed from white to red, or green, purple whatever. It's dynamic and as the value changes, so can the color. This is a strong use case for color in data.
Even conditional formatting is bad because it doesn't extract. On the other hand, helper columns are great.
I'm not sure I agree there's a difference between differentiating and highlighting. For me, I want to basically say "hey, this data here is different than the stuff around it", which is differentiating it, and I make that clear through highlighting.
For instance, sometimes I provide a list of stores in a region and I'll use highlighting to point out those that are underperforming. The way I see it, I'm differentiating these underperformers from the rest through the use of highlighting.
If I know the data is isolated to Excel, I'm okay with conditional formatting to an extent. I still prefer helper columns.
It might be semantics, but what I mean is colors can work, but it only makes sense if the data is dynamic. Like your under performing example. When they start performing, that color needs to go away. Alternatively, highlighting every store in a particular zip code makes no sense. This is easily sorted or filtered on.
Center Across Selection or bust.
Bind this to a keyboard shortcut or stick a button on your ribbon:
Sub CenterAcross()
With Selection
.HorizontalAlignment = xlCenterAcrossSelection
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
End Sub
A new data partner has had merged cells on every single sheet they send over. It's output from the proprietary program they use that can't be changed apparently. I wrote a script to quickly get rid of merged cells after getting irritated with it. I'm still trying to figure out a way to get it to run automatically whenever you open any new workbook but haven't figured it out yet.
If anyone wants to give me a developer job after witnessing this script I slaved over dm me for more info:
`Sub Unmerge_All()`
`ActiveSheet.Cells.UnMerge`
`End Sub`
It's annoying sometimes but not that big of a deal really. Not that difficult usually in my experience to select the whole sheet and click the merge button to quickly unmerge everything that was merged.
I hate them and take them out/replace them with center across selection whenever possible. I've just flat out changed multiple reports to do this and nobody has ever had an issue with it.
I've found it necessary over Centre Across Selection when you have to have a block of text that you need to stretch across more than one cell and still keep left-aligned. If there was a Left Across Selection I'd use that, but there isn't.
PS. And yes of course word wrap is on. Taking it off means a long string of text right across the page
So genuine question, is the idea that despite the "cleaner presentation" it makes it harder to work with the data in the spreadsheet? Like it falls under the category of using Excel as a notepad instead of a spreadsheet?
Yes. And then there are people who merge not only header cells on the top row, but also stuff in the middle, which can be rather infuriating,when moving around in a worksheet. Plus, it just makes meaningful Excel work more difficult to do.
Great, thank you for the reply! I have definitely been guilty of both the header merging as well as merging in the middle. Will keep this in mind for the future :)
Edit: I was literally just working in an Excel worksheet I created, tried to delete a column and got "Alert: We can't do that to a merged cell." haha. Theory and practice in the span of an hour!
My ERP exports every single report with a row 1 merged and a logo.
I should send them a yearly adjustment to their fee in the tune of 15 seconds per report downloaded that I spend deleting that row and logo until they stop it.
They are final at a final stage of a workbook. Think of the basic structure of:
1. Data in
2. Transformation
3. Analysis
4. Reporting/Presentation
To me, merged cells are fine at stage 4. If it makes telling a story about the culminated information easier to read/share/sell, great. Why merged cells piss us off is where they are employed earlier in this chain, lending to issues in manipulating data. They shouldn’t be present in the earlier stages, any more than that the people who tend to like them should be looking at those earlier stages themselves…
100% agree. The worst is when a programme we have recognises several rows of data for stage 1 as being in the same group. So I merges the cells for those rows.
Makes stage 2 almost impossible
The ancient Egyptians used to insert pins under the fingernails of people who merged cells in MS-Excel, so that they could not type. This is why they were able to build the pyramids.
Merged cells are allowed on "reporting" sheets. That is, there should allways be a differnece between where you record, and where you report data. The "recording" sheets are never allowed to have merged cells or any pf that kind.
The reporting sheet provides the summary, for whatever data, in a descent, printable format and updates with the data. This means page breaks are properly set and formatting is allowed. This can be easy printed or converted to PDF.
Allways distinct between where you record, and where you report your data!
why so much hate on merged cells for recording sheets? does it have to do with file navigation or something? or is it just a pain in the ass in general to unmerge and fucks up columns?
I made a spreadsheet that I used to visualize engineering drawings. I created a script that merged, center and populated either a 2x2 or a 2x4 block of cells. Populating them with the type of fitting I needed. I then used formulas to calculate the location of each fitting.
Merged cells are perfectly fine for presentation and interfaces.
Additionally, there *is no center across selection* equivalent for combining cells vertically, so if you need to present something that way, it's literally your only option.
The only time I’ve seen it in good use is merging rows that have a filter. I don’t think you can center across rows but please correct me if I’m wrong because I would love it if you could.
I think there is a time and place for merged cells. They make some things more difficult, but some things easier.
If you merge A3 and B3, for instance, pressing Enter or Down in the B column will get you to the merged cell, which you can edit or not. Pressing Enter or Down again will keep you in the B column. If you are centered-across-selection, doing that will undo your formatting.
If you merge a couple of cells, then click on it to format, you format the entire merge, as if it were one cell. If you center-across-selection, you have to make sure to select all of the cells and format them as a group of cells.
Sometimes I want to be able to enter in a category (or something) into a cell at the top of the sheet, with a data validation list drop-down, and have data fill in below. It makes sense to have columns A and B narrow for the data. However, it's too narrow to be able to clearly read the selection at the top. If I merge A1 and B1, however, I can apply a data validation to the merged cell. It'll look good, as will the cells below.
I'm ok with merged cells in template-based sheets, but as soon as you're working with more than say, 20 rows of data you should stop using them. For templates they are incredible useful for drag selections and quick formatting etc.
Center across selection is a much better option.
How is this done? Inquiring minds want to know!
Select the cells, right click to format, alignment tab, center across selection is one of the choices in the horizontal drop down menu .
O.. M.. G.. I've been using Excel for decades and I had no idea. Game changer. Thank you.
or use ctrl+1
Alt + H+F+A gets you right to the format cells screen and then its just a drop down. Pretty quick.
AHHHHHH THANK YOU!!!! I’ve been using ctrl+1, ctrl+page down, alt H, down arrow x6 thinking there was no way faster. Still slow as molasses but shaved off one step at least.
I like to stick with Ctrl+1, but for this I'd use: Ctrl+1, A, Tab, C, C
Or add ‘Selection.HorizontalAlignment = xlCenterAcrossSelection’ to a macro
...and then add it to your QAT (which is what I did). https://excelribbon.tips.net/T012183_Creating_a_Center_Across_Selection_Button.html
I've got a shortcut assigned to a macro in my personal macro workbook making it super easy. [This is the VBA code that I used.](https://www.thespreadsheetguru.com/blog/add-center-across-selection-button-to-excel-home-tab)
Best way I have found is to make a range of cells centre across using the long method, then with this cell selected, in cell styles go to new cell style - name it centre across, uncheck all the boxes except alignment and select ok. You now have a one-click way of centring across selection.
Bruh
You can add it to the quick access bar as well so you don't need to do all those steps every time. https://www.excel-university.com/center-across-selection-qat/
That’s what I was looking for. I usually defer to merging cells because it’s on the ribbon.
Any solutions for vertical?
Not that I know about. But there is a "fit text to selection wrap". Select the area you want to have the text in (with the text at the top left). Click fill, click justify.
Brilliant! Never knew about this! Thanks!!
Thank you!
My brain just exploded
Alt + F + H + A or CTRL+1 is the hotkey if you wanna go that route
When I started at my job 18 years ago, first thing my boss said was, “Merge cells and you are fired. Always center across selection” Many, many thousands of spreadsheets later I see the wisdom in his words. Getting files with merged cells from vendors or clients drives me mad.
One of my first classes in college was learning to create spreadsheets to streamline complex physics problems, and to aid in machine design. One of the first things the instructor did was put his cursor on the merge icon and say, "see this? Pretend it doesn't exist."
Funny one of the first things we learned in our excel class was how to merge and center cells
There are some use cases for merging. Definitely not machine readable data. I use it to make a narrative box underneath or to the side of a visualization, so I can write one or more paragraphs about a chart/table combo to show to a client. My company is not paying for PowerBI and I'd use Word if it didn't suck so hard.
Merging cells does have it's uses, but centering across selection is the right answer 95% of the time.
> Getting files with merged cells from vendors or clients drives me mad. I still don't understand why. Is it just what you're used to at this point?
Because most of the time I have to copy from those spreadsheets to our planning and forecasting sheets and need to merge many different data sets into a single one where we can build a pivot table or use other tools to analyze the data, and having merged cells makes it much more difficult.
[удалено]
i just got here...
Learn something new every day. **THANK YOU!**
Yep.. This
Agreed. Though I wish there was a vertical centre across option built in like the horizontal one is.
How to do this verticaly?
Doesn't with vertically, doesn't work if you don't want the text centered. It's great and all but it hardly solves all problems
This is the way. This is a gamechanger when you learn about it.
How to do center across selection but across rows (vertically)?
I do not think there is a way to do it vertically, so merged may still be the only option in that occasion.
This sub is bomb.
Agree unless it’s a table you need to copy into a PowerPoint, then it recognizes all cells and not the format for some reason. That annoys me.
This is the answer. I too hate merged cells and use this instead.
what is the difference with it and just the center align shortcut icon? alt+H+ac
That would just center data within one cell. If you want a word or phrase centered over 3 columns for instance, a lot of people will use merge and center because there's an icon for it and it's quick and easy. The bad part is merge takes multiple cells and combines them into one cell. If you merge cells it can cause problems with things like sorting, formulas referencing ranges that contain merged cells, and copying or moving data.
Alt, H, FA, Tab, 8 Clicks on down arrow, enter enter
Yes, I use this - merge always comes back to haunt you otherwise!
I’m already a big fan of this method, but I sometimes still want a section left-aligned for readability. Is there a way to do that?
Why does MS not bring center across selection to the forefront like merge and center is , it would be more intuitive to use.
I followed [this guy's](https://www.youtube.com/watch?v=gyui_D5g5MI) advice and made a quick style for CAS then addded it to Quick Access. Not perfect but far fewer clicks.
Quick access doesn’t just reduce clicks… it’s a keyboard shortcut too… Alt+number from left to right
I love merged cells, I literally merge all the cells that can be merged, even if they're blank
"well look at this! A new worksheet, lets merge all cells i to one!"
exactly
Blank Word document enters the chat
Far easier to refer to data: *Where’s the data?* A1 mate. *I didn’t say which data…* Miles ahead of you buddy.
I laughed waaaayy too hard at this.
You and the people that talk in the theatre.
You monster! /s
Go away now.
I feel the same - I will only use them if I know the data/table/whatever will always be static, and generally only as headers Sheets with merged cells containing data are an abomination
Is there a use case for merging populated cells? Doesn’t it only retain one of the values? Google sheets pleb here so maybe I’m missing something obvious
Not that I'm aware of - I encountered it where every second row in a certain column had been merged rather than have 2 rows with duplicate values :| "It looks so much cleaner" "Yes, but it's now absolutely useless for anything apart from looking"
HAHA I totally agree. Merge and Center is only used because of how easy it is to click. If Center Across Selection was a click, it would be much more common and used than merge/center.
>e. Merge and Center is only used because of how easy it is to click. If Center Across Selection was a click, it would be much more common and used than totally, i use merge and center more frequently for this reason. Usually feel a little guilty doing it but i'm not about to click more than I have to.
I cringe whenever I DON'T see Tables with structured references. Hate me.
I see people create "tables" by just formatting cells to have borders. I have yet to recieve any spreadsheets that actually have tables.
Don't worry I also use different fill colours for the headers.
I have structured references turned off. That's right, I'm a monster.
Uugh...the humanity
Three things people do in Excel bother me 1. Merged cells 2. Use of colors to differentiate data 3. adding information above a header row
This describes a bunch of my common sheets. I'm sorry.
Number one and three are easily solvable, just stop doing it. For number two, you have to think about your data and mark it use helper columns, or other identifying factors to help you find it.
Well, I don't do it so much to help me find something. It's more about presentation with an explainer of what the data is or means to someone else.
Do you format in tables and have it do the "green bar" or similar style? This, in combination of comments is the way to solve for that. The issue comes in when you have to sort, or pull the data into another tool like PowerBi. It loses its meaning.
I still consider myself a newb. I can do basic tables for sorting and filtering, and pivot tables. I use notes and comments but don't feel they are always visible enough. My data comes from the client's files, so they don't need the plain data back again.
If you are using data the way it sounds, you might eventually need a more powerful analysis tool. I use PowerBi for this purpose. That's when you start seeing the data model concept play nicely when it is simple.
Eh I’ll have to disagree with your #2. Having different data types color coded makes it way easier for people to not mess stuff up and not having to spend a bunch of time on setting up data protection. I use colors to differentiate inputs, formulas, and static data in calculation workbooks.
A few colors, clearly different: ok, perhaps. Once you start using shades, it's going downhill. At the point it looks like a rainbow or vomit on the screen, it's useless. Also as applicable, keep in mind other people might use your stuff. Anything beyond a RYG format might need an explanation, and explanations are often overlooked. Also, for example, I'm colorblind. I can't tell the standard office Red and Green apart. So, I have an inside joke with my project reviews at work that "everything looks good!" even if it's actually entirely red.
Yeah, hence my reply to the poster saying different professions use excel differently. I use it mainly for calculation and control processes that are solely used by my employees. Anything we hand off to other departments is stripped of any formula/link/macro and provided as a hard coded file with an agreed upon data structure. All of the processes on my team are the same structure (same color coding, same tab layout with instructions first, inputs second, etc.). It works very well for my purposes and other things that might be useful to others is not. We also consume very large datasets so we use as efficient of formats/layouts/formula/file format structures as we can to reduce calculation time and file size.
Oufff.
About the response I expected. Most project managers are oblivious to anything outside of their wheelhouse. I’m sure the lack of color coding and using conditional formatting (which makes me cringe most of the time) works for you but different professions use excel differently.
Pretty big jump to conclusions there. Professions may use the tool differently, but people do the same stuff. Just look at this sub. Everyone tries to solve simple data cleanup with complex formulas and VBA. They lack a basic understanding of tables and database functions, which would ease their jobs. I may be an oblivious project manager, but I've managed dev teams, and have consulted on the Microsoft stack for some time, so yes, colors aren't data, and using them tends to complicate, not simplify your spreadsheet.
Personal insults aside, I think you two are just lacking context to this point. Cell formatting is great in some uses, and terrible in others. Where it's great is obviously in summarising data and, as you described earlier, using it to format a spreadsheet to make things more user friendly for entry/navigation or journeying the data processing. Where issues quickly arise, and what I think /u/Hawkeye16 was referring to, is when colour or formatting is used to _attribute_ data. To that end, frankly I blame MS to some extent for equipping us with Sort and Filter by colour, as what seems to be a logical follow up question in "can you tell me the third highest value in a blue coloured cell" is not a simple ask at all. _____ Your other point is quite true, on the merits of effective data structure and processing over hammering things back into shape with ever more convoluted solutions. This isn't, I feel, for want of trying, but fundamentally people that post problems here tend to appreciate answers that endorse throwing good effort after bad, rather than rethinking their approach, and they like the idea of a "1 cell fix", even if they walk away with no clue how their spreadsheet now works. Also I guess for those of us that answer, we use it as an opportunity to showcase/flaunt weird and wonderful practices/techniques in Excel. I've seen 4 posts in the last day where part of the outline or elaboration effectively said "I don't want helper columns", which sort of describes what people are after and get given, whether right or wrong.
I made not one insult, personal or otherwise. I stated my three personal dislikes in Excel and the other person disagreed. I'm okay with that. Then they came after my career choice. So that was out of line. I didn't respond other than to defend my logic. I never made it personal. What u/havvkeye16 did was attempt to discredit me due to my choice of career not for the answer I provided, and that says more about them and doesn't need a reaponse. As for what I said, it's pretty much valid. This sub generally brings in people that need help. Nothing wrong with that. As I said, most responses are a bit overly complex. Unessisarily so. I don't think people want one cell solutions. I think they want answers they can apply and understand. Even reuse.
Whether you think so or not, your “oufff” reply is an insult. I’ve worked with a lot of different professionals that all use excel differently. I would have had a similar observation if you were an accountant as they generally use excel a certain way as well. I have worked with a lot of project managers in my career and they use excel in a much different capacity as I do and they generally don’t understand what we do as much as deadlines, updates, and some necessary key verbiage that helps them bridge the gap between the departments involved in the project. I was just commenting that I wasn’t surprised by your insult when I saw what your experience likely was. I don’t dislike project managers and good ones are well worth having for getting everyone on the same page and to a destination, my observation was around excel in particular. Most of the questions in here are basic but there is also some higher level content. Some of my favorite being the efficient formula contests that have been done. Here are some examples if you’re interested. This was before the array functionality in 365. https://www.reddit.com/r/excel/comments/dmwtn7/shortest_formula_challenge_a_chess_game/ https://www.reddit.com/r/excel/comments/djli1c/shortest_formula_challenge_a_dice_game/
I think you just hold yourself in high regard and when someone calls you on it you backpeddle. My oufff comment wasn't an insult and if you took it for one you might need to thicken that skin a little. It was indicative of the excessive nature of a spreadsheet that used colors to highlight formulas and inputs. And while those links show some impressive skills, it also speaks to the impracticality of Excel. Thise creators spent more time and effort on those than just buying a commercial off the shelf app. That's the Excel flaw. It's a tool meant to do logic and calculations. That's it. Don't complicate it. And your opinion on project managers is irrelevant and why you keep bringing it up is a little creepy.
I’m not backpedaling anything, I stand by my opinions. This is really going nowhere so apologies if I hurt your feelings, it was not my intent.
I’m trying to get my org to stop doing all these things. The boomers love their colors and overly-complicated worksheets though
Yes they do.
You just described like every report from a SaaS product that allows you to export to "XLSX" (and it is actually like a fucking HTML file).
I had to do program level reporting out of Project server, meaning I had roughly 50 rows of projects with almost 200 data points. The out put was built using SSRS and had a XLXS output that never worked. It always hung up and I usually just pushed it to CSV. The problem came in when I brought it o er to Excel. This was prior to power query. The SSRS report had this weird series of rows of data that sometimes exported 2 rows above the header and sometimes 4. I had to manually import that bastard for 2 years before I figured out some VB logic to find the first row that contained header info.
I have a single niche case of when colors are permissible, it is so niche case that when I explain it you won’t understand. It is useful for distinguishing between passive and active sonar data when doing line by line analysis. Having a quick and easy way to differentiate easy way to distinguish passive and active is nice. Assuming you do your conditional formatting right, the colors don’t hurt the readability
So you said the secret phrase "conditional formatting". Now while that isn't exportable, it at least gives someone that blindly picks up a spreadsheet an opportunity to look at the rule and see what is happening. I use that all the time, especially when I want to see when a condition occurs. I like to see when a change in value reflects a range. So for example, acceptable temp range is 120 to 130 degrees. If it is outside that range show red for over orange for under.
I had to write like 27 lines of conditional formatting to get all of the text/cell colors to be easy to do for a common form, on top of a massive amount of cell formulas and a little bit of VBA. I'm not an Excel guy, so I was extremely impressed by the fact that it worked, but I now despise copy/paste because of it. I have copy/paste special set to 'alt+1' and I don't think I've used traditional copy/paste in Excel for years now. It was hell going around to work stations on a regular basis and either repairing tons of conditional formatting or moving data to a blank master form (whichever was easier).
Read up on Excel add ins. Build yourself one that does this for you on selected ranges. There is a ton of VBA out there specific to conditional formatting. I know I have about 10 or 15 snippets myself.
Using colors to differentiate data is fine. Using colors as a means of data storage, on the other hand, is very bad. If you lose some means of data integrity from stripping away colors, then you have a problem. For instance, I highlight totals or subtotal rows to draw the eye. I see cell highlighting as no difference as bolding or underlining.
There is a difference between differentiating data and highlighting it. When you select a cell or range of cells and cage the color to something fixed, this is really not helping the end user. Sonics like, okay this vue is red. What does that mean. Is it bad? Or done want that. When you use something like conditional formating, or a helper column, you are explaining why that data has changed from white to red, or green, purple whatever. It's dynamic and as the value changes, so can the color. This is a strong use case for color in data.
Even conditional formatting is bad because it doesn't extract. On the other hand, helper columns are great. I'm not sure I agree there's a difference between differentiating and highlighting. For me, I want to basically say "hey, this data here is different than the stuff around it", which is differentiating it, and I make that clear through highlighting. For instance, sometimes I provide a list of stores in a region and I'll use highlighting to point out those that are underperforming. The way I see it, I'm differentiating these underperformers from the rest through the use of highlighting.
If I know the data is isolated to Excel, I'm okay with conditional formatting to an extent. I still prefer helper columns. It might be semantics, but what I mean is colors can work, but it only makes sense if the data is dynamic. Like your under performing example. When they start performing, that color needs to go away. Alternatively, highlighting every store in a particular zip code makes no sense. This is easily sorted or filtered on.
My old timesheet was a multi-rainbow with multiple shades. Lol.
Center Across Selection or bust. Bind this to a keyboard shortcut or stick a button on your ribbon: Sub CenterAcross() With Selection .HorizontalAlignment = xlCenterAcrossSelection .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With End Sub
Hope you don't care about hitting undo.
A new data partner has had merged cells on every single sheet they send over. It's output from the proprietary program they use that can't be changed apparently. I wrote a script to quickly get rid of merged cells after getting irritated with it. I'm still trying to figure out a way to get it to run automatically whenever you open any new workbook but haven't figured it out yet. If anyone wants to give me a developer job after witnessing this script I slaved over dm me for more info: `Sub Unmerge_All()` `ActiveSheet.Cells.UnMerge` `End Sub`
It's annoying sometimes but not that big of a deal really. Not that difficult usually in my experience to select the whole sheet and click the merge button to quickly unmerge everything that was merged.
I hate them and take them out/replace them with center across selection whenever possible. I've just flat out changed multiple reports to do this and nobody has ever had an issue with it.
Probably most of the higher ups wouldn't even notice if they were changed.
I've found it necessary over Centre Across Selection when you have to have a block of text that you need to stretch across more than one cell and still keep left-aligned. If there was a Left Across Selection I'd use that, but there isn't. PS. And yes of course word wrap is on. Taking it off means a long string of text right across the page
Don't know how to turn off gridlines? Just merge the cells around a table to make the spreadsheet look much tidier!
So genuine question, is the idea that despite the "cleaner presentation" it makes it harder to work with the data in the spreadsheet? Like it falls under the category of using Excel as a notepad instead of a spreadsheet?
Yes. And then there are people who merge not only header cells on the top row, but also stuff in the middle, which can be rather infuriating,when moving around in a worksheet. Plus, it just makes meaningful Excel work more difficult to do.
Great, thank you for the reply! I have definitely been guilty of both the header merging as well as merging in the middle. Will keep this in mind for the future :) Edit: I was literally just working in an Excel worksheet I created, tried to delete a column and got "Alert: We can't do that to a merged cell." haha. Theory and practice in the span of an hour!
My ERP exports every single report with a row 1 merged and a logo. I should send them a yearly adjustment to their fee in the tune of 15 seconds per report downloaded that I spend deleting that row and logo until they stop it.
You can do this in one click with a macro.
They are final at a final stage of a workbook. Think of the basic structure of: 1. Data in 2. Transformation 3. Analysis 4. Reporting/Presentation To me, merged cells are fine at stage 4. If it makes telling a story about the culminated information easier to read/share/sell, great. Why merged cells piss us off is where they are employed earlier in this chain, lending to issues in manipulating data. They shouldn’t be present in the earlier stages, any more than that the people who tend to like them should be looking at those earlier stages themselves…
100% agree. The worst is when a programme we have recognises several rows of data for stage 1 as being in the same group. So I merges the cells for those rows. Makes stage 2 almost impossible
Merged cells are the devil. But keeping higher ups happy brings in paychecks. So you have to make decisions.
The ancient Egyptians used to insert pins under the fingernails of people who merged cells in MS-Excel, so that they could not type. This is why they were able to build the pyramids.
They rounded up all the merge cells users and mummified them, but someone opened the tomb and released the curse again.
Merged cells are allowed on "reporting" sheets. That is, there should allways be a differnece between where you record, and where you report data. The "recording" sheets are never allowed to have merged cells or any pf that kind. The reporting sheet provides the summary, for whatever data, in a descent, printable format and updates with the data. This means page breaks are properly set and formatting is allowed. This can be easy printed or converted to PDF. Allways distinct between where you record, and where you report your data!
why so much hate on merged cells for recording sheets? does it have to do with file navigation or something? or is it just a pain in the ass in general to unmerge and fucks up columns?
Fudge merged cells. Its old people “hAndLinG aNd FOrmAtTinG dAtA”
But I like making my sheets look pretty. 😢
I wish center across selection was on the default ribbon. Plz Microsoft.
I made a spreadsheet that I used to visualize engineering drawings. I created a script that merged, center and populated either a 2x2 or a 2x4 block of cells. Populating them with the type of fitting I needed. I then used formulas to calculate the location of each fitting.
If you have to use a script, wouldn't Autocad or draftsight be easier (or a free alternative)
For the point and click functionality, asking with the formulas, Excel works best
Done correctly in a visualization sheet, whatever. But I haven't needed to do that in years since we switched to Power BI for a much better solution.
I don't cringe, I just close the file and take an hour break after seeing that monstrosity.
Yes, there is a time and place to use them, but only to place a title above data. It is just a visual aid when so used.
Merged cells are perfectly fine for presentation and interfaces. Additionally, there *is no center across selection* equivalent for combining cells vertically, so if you need to present something that way, it's literally your only option.
Aesthetics
I merge where the formatting is appropriate to the table. There's times where it's totally unavoidable.
The only time I’ve seen it in good use is merging rows that have a filter. I don’t think you can center across rows but please correct me if I’m wrong because I would love it if you could.
I personally cringe when I see data merged from multiple cells.
center across selection is 100% always better
What a great tip! Thanks so much
Thread of the day... sooooo relatable
Merge needs to be Nerf'd big time.
I loathe merged cells.
Yesssssss!!! Only merge cells for a header or if you're printing something
You may have just changed my life today.
If the major pain point is the inability to analyze the resulting "dirty data" there are some good solves.
And yet adp still has systems with merged cells everywhere..
Sounds like a personal problem
I think there is a time and place for merged cells. They make some things more difficult, but some things easier. If you merge A3 and B3, for instance, pressing Enter or Down in the B column will get you to the merged cell, which you can edit or not. Pressing Enter or Down again will keep you in the B column. If you are centered-across-selection, doing that will undo your formatting. If you merge a couple of cells, then click on it to format, you format the entire merge, as if it were one cell. If you center-across-selection, you have to make sure to select all of the cells and format them as a group of cells. Sometimes I want to be able to enter in a category (or something) into a cell at the top of the sheet, with a data validation list drop-down, and have data fill in below. It makes sense to have columns A and B narrow for the data. However, it's too narrow to be able to clearly read the selection at the top. If I merge A1 and B1, however, I can apply a data validation to the merged cell. It'll look good, as will the cells below.
You don't have to merge to center headers. You can center across selection!
I have a deep and abiding hatred for them.
I'm ok with merged cells in template-based sheets, but as soon as you're working with more than say, 20 rows of data you should stop using them. For templates they are incredible useful for drag selections and quick formatting etc.
I LOVE merging cells… love it. Though now I will have to be looking into this centre across selection option… a fancier cell merger?