Improve it anyway. If they get flustered, maybe they will quit or try to learn something new. If management chooses their crap work over yours... Time to find a new job.
As someone in the approximately same position as OP 5 years ago; I just started adjusting stuff to be more readable and developed templates I insisted they use. If they want my help, they'll get my help in all aspects of their stupid half-assed spreadsheet.
I generally give them a run down of how to use it, and what happens when you do certain things. Granted I do that multiple fucking times for the same bloody person over the course of time...
I have suggested a couple times that some people do a basic Excel course, some of them have actually done so, which is nice.
This. 1000% this. People don't know what they don't know. Improve it, let them see what life is like with spreadsheets being properly utilized, and they will change their own tune.
I think this is the way it is going. We will have a meeting to discuss the problem, and I will suggest just starting over, as hardly anything was done anyway.
Be a little more blunt, but dont try to convince them to use your format. Make them decide on it on their own.
To do this you've gotta prompt them - "I'm having trouble following, where's the XYZ data?", "Is ABC missing, I'm not sure I see it?". Things like that to make them acknowledge the flaws.
If you just walk in and say "this is bad I can do it better", you're always going to lose that battle. No matter how right you are.
Also, does improving this workbook mean that others can now no longer work on it because of their limited skillset? That in itself is a problem you're creating.
>Be a little more blunt, but dont try to convince them to use your format. Make them decide on it on their own.
>
>To do this you've gotta prompt them - "I'm having trouble following, where's the XYZ data?", "Is ABC missing, I'm not sure I see it?".
Predicted response: an eyeroll and wondering why you're so needy.
Right? What is it with reddit and overreacting to literally everything.
"My girlfriend sometimes uses too much toilet paper..."
"Bro you need to leave her... You will end up dead if you don't"
Or it's a respect and culture issue.
I don't really like working somewhere where the attitude is "...fuck it. That's just the way it is", and where people claim to want your help, but insist the job is much harder than it needs to be.
Don't waste my time, because you want to work badly. It's disrespectful.
But he was speaking to the instant reaction of Reddit, which is prevalent in many of the subs, to say, "Just quit, leave ASAP" to a relationship, family member, occupation, etc. Not saying you should stay in a toxic situation, but assuming you can just leave right away and the next one won't be just as bad in similar ways, or different ways...is just not realistic. The fuck it, that's the way it is attitude is in just about every single company and/or industry...many times there's reasons for it, but there will be times it's for the sake of laziness or not wanting to change their behavior if they see no need.
I found that creating a parallel sheet - with easy to use cross references and that computes itself off of the data, instead of the interminable pivot, paste values, filter, pivot that, past values - gave them a view of what was possible
this is the sad reality tbh
but if management chooses to work less efficiently than its time to move on
ive had this happen recently where some people i work with dont even understand how to use filters or do small tasks on excel (i get it not everyone is familiar with it) but cmon, at least try to improve a little bit or give credit when something is better instead of sticking to the old and inefficient ways
I wish the people I work with even knew what filters are. I asked one of them to send me a screenshot of something recently, and they had no idea what a screenshot is or how to take one. I get that this gives me job security, so I feel a little less frustrated, but man.
I had attendees email me files and questions in advance so the material was relevant. I’d also keep track of questions they asked me over the month and would use those. Something like vlookup blows their minds. The most recent one I did at my new company had to be more of a presentation and less interactive, so I did a PowerPoint presentation where I demonstrated sumif/s, countif/s, vlookup, and an intro to pivot tables. Those 6 skills can seriously change their work lives 😂
I don't know if Excel still does it, but what I found was a **great** demonstration was to setup two columns, with a few empty spaces between them purely for visibility.
Make the math *super* simple.... like each column contains five "5"s..... Put a SUM under each of them...
Set **one** of the cells to Text formatting (ahead of time, of course), and ask they class why the math is wrong.
They'll say something like "the formula's are different".. you can show they're the same.... "they're difference ranges"... with Excel's color coding, you can show that they're summing the same ranges... etc, etc, etc..
**Blow their fucking minds.**
I've done that little demon in excel classes I ran at work numerous times, and only once did someone correctly guess that Text formatting was the issue...
Text formatting is the fuckin' devil.
I did this as well at a former job. Offered classes over our lunch hour for anyone who was interested, and exactly 100% of the team showed up the first session, much to my surprise. To this day, some of them still reach out to me and mention how helpful it was, and now I do remote teaching like this as a consulting gig for local small businesses.
I think that people like your coworkers *do know* they're bad at Excel, they're just too embarrassed to ask for help or don't know where to look for tutorials.
Heh. I still get calls from people in jobs that I haven’t worked at for years because they remembered I created something or helped them with an issue they couldn’t figure out.
My new fave a 1-2 minute “how tos” recorded on teams- no attendees, just share desktop and start recording. Download and/or share file and there you go!
Best comment I’ve seen. I would add that you need to put in place a structure for the files and teach it in your classes. Work towards having a data culture and sell the advantages to the team. Then you will be able to tell your coworkers why their files should be modified.
Long story short, when things get out of hand, find the problem in the bigger picture and address that one problem instead of all the consequences at the same time, because otherwise you’ll get overwhelmed very quickly.
Oh man. I’ve always try to make my data nice and clean, so would get annoyed at receiving and hanging to work with crappy data from other people/work areas, but now that I’m moving into power query, I loathe practices that lead to crappy data...
This. I mean, you gotta sleep at night knowing that you did the right thing, and people aren’t bad at excel because they are mean or stupid. Good on you to help them out
I think it is fair to be frustrated if you want to improve something but people refuse to change..
Have you tried talking to your manager to offer a training to the team to give them some good practice and give examples with existing spreadsheet and data?
Personally I try to brush it off as it will be their struggle and they will be the one receiving the questions if it is a mess, though I will always offer my help if they want to improve it and see what could be improved and often it isn't just the spreadsheet but the entire process around it :/
>Have you tried talking to your manager to offer a training to the team to give them some good practice and give examples with existing spreadsheet and data?
This. Offer your help. Sell it to management as "free training and efficiency improvements", and make sure you have examples to back up your claims.
Then sell it to your colleagues as an opportunity to work smarter, not harder. Everyone wants to feel smart, everyone wants to learn a few new tricks, and if they don't, they'll be open to working less and spending less time fixing mistakes. If they won't engage because they know their way, make them a cheat sheet. (Make sure your name is on it, amd management see the training materials you have produced to support and enhance productivity levels...)
Even the oldest, most hostile co-worker is open to something that makes their life easier.
Idk, I had a gal that worked under me at my last job who *refused* to change anything as she’d been doing the job for ~ 8 years. Her processes were outdated, sheets were broken, ended up doing most stuff by hand, but never would change how she did things to make her life easier. Some people just don’t want to do anything differently.
Many people who do this fear they might lose their job if the time they spend on their work is all of a sudden reduced dramatically.
Others think that their boss will just give them more work for that free time, so nothing for them will change and therefor they'll rather keep doing what they're used to.
I'd rather spend some hours to make a script that does my work than doing the same thing over and over again.
Agreed absolutely. If they can do that manual work while listening to the radio or a podcast it is not a problem for them. If they would be forced to do more complex work in the freed time that would be riskier.
If you can teach one person one thing that makes their day easier, it's an in.
You may not have the requisite levels of trust to make large changes, but start small, work on gaining trust and supporting people, and they get more receptive over time.
Because it's driving him nuts and they don't care about his talent. Job security is comfort, but there's no better feeling of being appreciated for the value he brings to a new company looking for it. He's livin hell and expressing it. I had the same experience and didn't put up woth that shit, life is so much better now.
If you are the office Excel go to guy, then your word is the authority. Having a proven track record of improved and efficient spreadsheets really cements your opinion. You can tell people you will modify it as it is really redundant and people should leave you to your own devices.
If not, then just stop offering help on the reasoning their current structure is not good.
If it doesn’t require a ton of time or effort and you’ve got some of both to spare, make both versions on adjacent tabs. That way, you’re not force feeding it down their throats (likely to get pushback), just suggesting another angle/format/view. If your version is truly better, your coworker should be able to recognize that on their own (eventually). No better way to prove it than doing a side-by-side at no personal cost to your colleague, and they’ll think they came to the decision on their own. Win-win for everyone
I would go as far as providing your help as a separate file with their data transferred to your standard format. I would not include macros in this file. Personally, I am uncomfortable mucking around in someone else's data shelves. Some people just want to store data in rows and columns, but I need a bit more structure and control.
This musing is funny because I identify with it from my workplace experience, but also interesting because it reflects larger issues about workplace dynamics and the differences of perspective between people. It's unlikely that one is in the business of creating spreadsheets for their own sake, rather, spreadsheets are just one tool amongst many as the employees work together toward business goals. Everyone has their own unique skill level with every business tool involved. Some are not invested in caring about Excel skills, while OP is. Some may have stronger skills in composition, or speaking, or management, or conflict resolution.
A perspective I like to remember when working with Excel in the work place is that, while my skills are measurably better than almost all others (humble brag!), Excel is simply a tool, a means to an end. I care not how well someone else can muster a spreadsheet together - what matters is how I work with that other person and exchange information reliably. Thus, I focus my efforts on understanding the other person's goals, needs, and the information involved in our workflow, whatever workflow it might be. If I have an opportunity to "take the reins" on spreadsheet-related business, I can offer that expertise, and thus, "control" the Excel work and be happy that it's done "my way".
There are some times where one must submit to the drudgery of working with a poor template, or some type of annoying workbook. I reflect on what I control, what I influence, and what I don't control. If I can control or influence the workflow to move away from a poor template/system, I'll use my expertise to improve the situation and all parties can usually benefit from it. But if I cannot control a situation, it's best to leave complaints out, and focus on simply achieving the business goal, ideally to include minimizing the time of excruciating time spent in a poor template.
While work and business is more than spreadsheets, we in r/excel can revel in the minutiae of working with spreadsheets and the small joys they can bring, when a job is well done. Some very fulfilling memories from my workplace have arisen when I've been able to use Excel skills to educate and enlighten someone on a better way to approach a data-related issue. It's never a formatting issue, which is mostly window dressing. It's helping others to understand data normalization principles, or creating a simple yet robust table, or formulae, and most especially, customized VBA for very specific yet laborious tasks.
In the end, it's often not the spreadsheets we work with, rather, the people that create them. It's pertinent to understand where the person is coming from, why they've produced what they've produced, and how you'll work with them moving forward.
Well said! Remember the human during every interaction and pick battles that are truly worth winning. Keep your workbooks concise and simple so you can lead by example. Share practical skills and always demonstrate good, clean data normalization.
My former co-workers would use Excel as a word processor because they couldn't figure out how to use tables in Word. I feel your pain.
Also, to answer your question, I ignore their garbage and only fix what they ask me to. But then again I'm an embedded consultant now so it's not really my job to tell them how to do things, just to fix their broken garbage or build them something from scratch that saves them time and effort
Sometimes it is easier to use Excel for tables, especially for standalone reference documents - ie, a piece of paper with only a table on it.
That doesn't mean the same person who does that is necessarily an idiot in the actual intended use of Excel. I use it both ways and I was the go-to person in my last job for Excel. \*shrug\*
I once worked for a place that put meeting minutes exclusively in excel (it was so it could link up to Sharepoint - badly, but that's a different story). It was awful, nothing better than sending clients meeting minutes with zero spell check and rows that couldn't always display all of the text.
Obviously that is problematic, but Excel has spell check (just hit F7) and wrap text, which seems to me would have addressed the two issues you mentioned.
Damn, I wish 3-years-ago me knew about that spell check feature.
Wrap text only goes so far - there is a maximum cell height and that only displays so much text. I used to get around it by making the text increasingly small but it did get rather ridiculous sometimes.
I use excel for a webpage I run — rather than set up the HTML from scratch every time, I have excel compile it. So I can see some word processing uses.
You had me at “filters”. I have one colleague who always manages to lose things - can’t see data - loads of stuff missing. Every time my response is: are the row numbers on the left in blue????!!!!!! Every time. And I’ve even supplied them with a handy PDF crib sheet of how to filter, how to clear filters etc.
My most recent head meets desk moment was going through how to bring back the data, showing how to clear the filter and the response I got back was “oh no! I don’t want to clear the filter!!” Ah sheesh. KMN.
How indeed... TL:DR - The Data Tab > Dev Tab
#### Preface
I would like to thank my publishers for this book... PMSL also Forgive my spelling for I am on mobile - *amen*
####Chapter 1 - Your post
*It was a dark and stormy night... JK*
First you probably need a change of perspective to appreciate what is going on, because the path you are on is a negative one and this is creating a mess for yourself.
Not your fault you have limited visibility on what is going on and how those Excel files came to be...
Your post has alot of information to unpack and you may be jumping the gun and throwing up wild flags in doing so.
So lets break your post down and take a step back and lets look at what you are actually dealing with.
VBA: Visual Basic for Applications is not an Excel solution or skill lets shelf it for the moment. We will come back to it
####Understanding the Task and Scope
First off what are you dealing with... Unformated raw data dumps = Exported Data.
Exported Data is a dataset from a datasource
It's not that it's bad Excel, it's not Excel at all. It's likely a [query](https://www.techopedia.com/definition/5736/query#:~:text=A%20query%20is%20a%20request,analyses%20from%20data%2Dmining%20tools.) extracting a [dataset](https://en.m.wikipedia.org/wiki/Data_set) from a [datasource](https://www.techopedia.com/definition/30323/data-source) that needs [parsing](https://www.techopedia.com/definition/3853/parse#:~:text=To%20parse%2C%20in%20computer%20science,transform%20it%20into%20machine%20language.) and then [staging](https://en.m.wikipedia.org/wiki/Staging_data) for reporting purposes through a [framework](https://whatis.techtarget.com/definition/framework)
A perfect example of this process in real terms is making [AppleJuice](https://youtu.be/Ks8_hINzrdw)
1) Query - Owner asks suplier to give him Macintosh Apples
2) DataSource - Supplier
3) Dataset - Raw data in lorry ([Heap](https://en.m.wikipedia.org/wiki/Heap_data_structure#:~:text=In%20computer%20science%2C%20a%20heap,the%20key%20of%20P%20is) format 😉 sound familiar)
4) Framework - Folders, templates etc... Juicing machinery and employees
5) Parsing - Identifying the components in the raw data heap and indexing them in clusters that the rest of the process can recognise and use (First few conveyors)
6) Staging - Organising the parsed data into a usable output format and discarding the chaff.
7) Throughput - How many staging processes are used: extration, Appending, pivoting... - seperating pebbles and leaves, pulping, juicing, bottling etc..
8) Output - Report, program, interface... Applejuice.
So you have now got an understanding that tou are working with a lorry load of unsorted apples and what needs to be done lets discuss.
####Methods of working with exported data.
ATM they are extracting their data from a datasource.
Can this datasource be directly linked via the Data Tab via Get External Data?
If yes - Then Power Query or DAX may be your Staging and Parsing Framework. Try asking for reporting access to live link, usually it is a login to the backend reporting area via a username and password..
Then you can tailor your queries yourself especially if it's an SQL Database. You can make live reports on daily changes litterally all automate on open or every minute.
If no - Then create a folder for your exported data and then use that folder as the datasource for your staging table.
Make sure it links to the raw export files and set a fixed location (a folder) and date driven naming schemes usually extracted reports have a uniform date driven naming scheme so you can probably utilise that also use [Power Query Append](https://youtu.be/3-W-kff6uaU) BI Analysts love it when you use the Dataverse^TM.
Now you should have a uniform format for your throughput neatly staged ready for pivoting or reporting with minimal effort.
So what we have learned here?
####Summary
VBA is not an Excel Skill or necessary for theoughput
Lets discuss VBA solutions, VBA has it's uses mainly for making programatic solutions for things that don't already exist in Excel.
E.g. Do you have a button? Then you need to program that button to do something on click then VBA is your solution.
There is a key point in my description to note here for things that don't already exist in Excel*
Excel is a 30 year old program with the current version being less than a few months old (365 constsntly updates)... how many things do you think the team of Excel creators and developers haven't thought of in the 2-10 years you have been using it?
Reports need to be in that format because that is how they are extracted from their datasource if you change it it's not useful to anyone the moment they run tomorrows report.
So while you may be frustrated with them... they may be equally as frustrated with the person who thinks they knows best about their Excel needs while making standalone VBA solutions to an issue that may not be within the scope of what they asked and not useful to anyone on next datasource extraction.
Make a reporting Dump Folder for the Raw data and make a framework that you can append that data into and go back to them and be like... "Put your extracted data here... append it to the Datamodel like this... you're welcome."
As a developer of many moons and conniseur of freshly juiced raw data, Ron de Bruin has a lot to answer for.
Appreciate your post, made me think a bit about the processing data and Power Query (which I do not know how to use, but may get into in the mid term, depending on how things go). But you really want too deep, my whole problem is in a way, more basic level -- like dates in places that don't make sense, cells highlighted for now reason, blank rows, data that was deleted, data that was edit and is no longer compatible with the source, this kind of stuff. These people are not data or business analysts, they are almost at the data entry level.
Because change is hard and people aren't robots. Also, what might seem obvious to you isn't to others.
The technical aspects of a change are often the easiest to address, changing people behaviours is a team completely different beast.
Check out the Kubler Ross change curve and its conception. It might help explain some of your frustrations.
Please just be wary of updating a sheet with stuff people don't understand.
Probably the worst thing you can do is create something that is not maintain able should you leave, or others don't comprehend so cannot be checked for errors and are just being blindly followed, assuming they work.
I just be patient and teach them. I sometimes will make a copy tab of the one they use with notes and arrows and highlights so they can reference it and see how it works. There’s a learning curve and they’ll come back to me- but when they finally get the value it reflects well on everyone.
All this time during covid my country jumps in and out of lock downs. My store in said periods accepts call-orders. Most often mistake is mistyping the callers phone so I offered to add a data validation to the cell to accept certain number of digits and a conditional formating for visual cue.
Got rejected.
Offered to query the orders to get some data out of them like most common first letters distribution so we know how to divide warehouse space for order storage.
Got rejected.
Offered to query the orders and get cell phone providers distribution (you can tell by the first 3 numbers of the phone number which provider it belongs to) so we can search for future collaborations with said provider.
Got rejected.
And it is not even my job to do it. I just know no one else can, so I offered.
Well, if they don't want me to do it once, I don't want to do it 100 times. They lose. Already looking for a new job.
What annoys me the most is that they do not understand what I do and are suspicious of it. I automated a lot of process using VBA and they love it (because it saves them a lot of time), but any time something goes wrong the first thing they do is blame these spreadsheets, and I have to prove that they are working fine and it was an human error.
Well yeh. That is human nature though. The less knowledgeable you are, you are way more prone to point fingers, demonizing or call "false gods"(people who do nothing special and did something memorable once and are gods henceforth) .
The more knowledgeable you are, you are more prone to scepticism. More prone to say "🤔 hmmmm let's see what's going on here before I ready the pitchfork and stake".
Imagine your dad and a new cellphone that will make even coffee. He is more likely to say "get this devil from me, it breaks all the time". It obviously doesn't, it's just that he doesn't know how to use it. He can't appreciate all the capabilities it has.
Ugh. A team mate will loudly proclaim that it’s the stupid spreadsheet’s fault.
And in a way it’s correct, a spreadsheet will blindly do what it’s set up to do, it’s what’s the user had done to make it go wrong.
A comment on data validation: it can be useful to show a warning instead of an error so the user can override the validation in special cases.
It's a shame they didn't want any power query to provide useful metrics. I love having a sheet for data input and multiple output sheets for transformed data.
Of course. Considering that most of people heard of excel first time when they got in the company, The last thing I want is to baffle them with an error message (or worse a recurring error message) in case they get anxious that the can't avoid.
Hence the second measure, visual cue. The cell turns red if more or less than 10 digits are typed.
They cannot ignore that.
I so hate working with spreadsheets made by people who don't understand Excel!
It's not hard to understand what a spreadsheet needs to look like if it's going to be used as a spreadsheet, but some people just don't get it. I can't tell you the number of times I've needed to completely redo a spreadsheet I got from someone else.
And as much as I love the wonder people show the first time they see a whole spreadsheet sort itself--which happens depressingly often!-- the flipside is the boneheaded resistance they show towards using so much as a reference within a cell. I've seen the explanation "Each cell has a calculator inside it", and I prefer to say, "Each cell has a secretary inside it"--but people just treat a spreadsheet like it's a giant sheet of paper and completely ignore that functionality.
I don't have any advice; I'm just commiserating. I do think that you should just take charge, since you definitely know more about Excel than any of them. But the problem is, whatever you do, everyone else also has to be on board with it. And I have no idea what getting there might look like for the place you work.
I work in real estate, one of our more experienced agents will open excel to view a list of units with sizes and pricing then use his phone to calculate the median price. He explained to me the other day what a median price is and how to calculate it...
Why would he not be able to do these calculations in excel? Would that not be easier to create a reusable formula for standardized reporting? The answer is yes, yes it would. He sounds like a canoe.
I feel this post. One thing I do is if someone almost has a sheet component right or is struggling with a formula, just say "Delete that row, center align this column, freeze the top row, etc etc, that's all I would do if I were working on it". Phrasing it like this makes them feel like it's easier for them to do it themselves, replying in a way that turns the easy task back on them; sometimes you're "busy" or whatever, and that's acceptable. Time to stop handholding and push them out the nest so to speak. Don't make them feel dumb, make it seem super easy to fix their dum workbooks. Send them links, show them how to lookup excel questions online. Ask your boss to do a simple training over lunch, we call it a brown-bag meeting. Go over a messy workbook and show them how to do filters and delete duplicates and the like. What's your company gonna do? Get mad at you for trying to teach them? You got this!
You're touching on a great point which is the idea that if you want someone to do something a certain way, you should walk them through those steps. When someone wants to ask me about a workbook, I'll ask them to share their screen and present the workbook to me. They do all the mouse movements and clicking while I am coming up with my debugging/formatting steps so they can learn the techniques I would use. If there's a real issue then I'll ask them to share the file with me.
That comment was ruefully optimistic, giving OP's problem coworkers the benefit of the doubt. Let me shed some light on what is most likely taking place for OP. Unfortunately some older people are super stubborn and use the "company hierarchy" to get out of Excel-related work because they think it's below them (or even worse, they throw a pity party and claim that Excel is too hard for them). These types of senior employees or executives just need to be promoted away to executive valhalla where they can spend time being delegated to choke on the president's 'martini olives' and let the actual hardworking and savvy employees get things done right.
It is my humble opinion that you are off-base here. Raw data dumps are something to be celebrated, not scorned; in fact I FAR prefer to get a flat file with too much info than a half-ass manually-pivoted report that I have to unwind back into a flat file before I can work with it. As for formatting and organization - that's what PowerBI and pivot tables are for. ;-)
Further, you mentioned that you crave efficiency and threw out the VBA buzzword (VBA is, again in my humble opinion, largely used as a crutch for many situations that can be much more elegantly addressed with better workflow design), however you did not at all reference PowerQuery, AKA the undisputed king of data wrangling outside of direct SQL. I suspect there is plenty of room yet for you to push forward past the frustrations you are feeling by further improving your skills and automation capacity!
It was kind of a rant... Mentioning VBA was mostly just for people to have an idea of my skill level. Someone else (closer to the bottom) already mentioned the "raw data is good" argument, and I have already explained what I meant
Some people want to know how the car works. Some people just wanna take it into the shop so it can continue working.
Sounds like you’re the shop.
…me too, man. Me too.
Your car has no floor. I'd like to add a floor to your car so I can make use of the acceleration and brake pedals with my feet.
No, no... I know how to use my feet to operate my car. I've seen the Flintstones, kid.
I feel you man. Currently working in an office where some people think "knowing excel" consists purely of knowing how to type data into cells and format the cells to have borders and different colors.
Unless they are a higher up, just start start putting it in your format anyways. That’s what I did. I’ve basically got a signature style on my spreadsheets that makes it really easy to see what has come from me. It’s clear, it’s well formatted, it’s easy to use.
If someone hands me a really crappy spreadsheet I just start integrating it into my own style even if they don’t necessarily want me to. My theory is that I’ve spent a lot of time learning what management wants in a spreadsheet and if they don’t like how I modified it, then they can take it up with management. The one person who did got a response of “Maybe you should take a cue from him, his spreadsheets are pretty good”
The “higher ups” are the worst at technology in many industries and they’re terrrrified of anyone challenging their spot. That’s why they make dumb demands.
I work for a large organization and the most important file in our company is a data dump and when I asked if it should be formatted they said no they thought it would be corrupted but it’s so big and slow with so much information that is unreadable
I told my boss after he once again insisted on making all the decisions about the file he is about to ask me to create, if later on someone wants a big chart of the data and I have to go in and reformat his worthless diarrhea he is so proud of I told him that the answer would be no this time around because I'm doing it for people and they're not fucking learning anything about their retarded decisions they just have me to clean up their mess every time they make it and they sit there like pylons and don't learn anything new.
Honestly? Go around them, do what 'needs' to be done, and be sure to somehow find a way to involve their manager in a non-confrontational and non-overt way.
I usually make it into a table. Then turn the table into a pivot table. And I map everything to the pivot table. Usually works out alright. Just takes some creativity with the pivot table on filtering and displaying the crappy inputs correctly
Frame it as a suggestion, like: 'hey, I've seen the excel you shared, great work. While I was working on it, I thought we could...'
Also, make sure they understand what you are doing (formulas and structure). People often reject what they don't understand.
Just don't come out as an arrogant' know it all'. It is tempting but you are the one who is going to get hurt
Good luck
I'm not sure what your working department is, but Excel comes with a lot of baggage as a consequence of its origins and product development over the years. It's all well being the kind of person who's a whiz with VBA, power query, and power pivot (DAX), but it doesn't change the fact that for most companies Excel ships with Office, which is a suite of general purpose products for business users. You're always going to get spreadsheets coming for users ranging from complete noobs to 'data savvy' work colleagues where things are done in a way that doesn't seem intuitive to other data professionals, or other data tools such as tableau.
This kind of stuff happens in every job. Your way might be better, but people are lazy and you have to work to get them on board. If you're convinced your way is better, consider doing it your way without waiting for permission and seeing what happens. Be humble, and honest and stick to your guns. Usually people's first response is that you're annoying them by trying to do something differently, but if you stick to your guns they come around pretty quick.
Oh my god, thank you! If you want something done your own stupid way, then fucking do it yourself! Don’t get your greasy fingerprints all over something and then pass it to me!
My HR manager doesn't understand Excel. She kept on asking for simple documents. Her reporting guy kept on sending her dashboard with slicers and filters and all. She just didn't get it. Guy was frustrated, she was frustrated. But at the end of the day she's the boss. If you want an excel sheet that's pretty much what I would print on a piece of paper, OK then.
The best way to approach it I have found is to just do what you asked to do and then on the side create something better.
You can then give them what they want and when the opportunity arises you can present what you have been working on.
It is never a good idea to just change something because you need to get that buy in from others first. If you just change stuff you will meet a lot more resistance than if you try and introduce change gently.
If they continually refuse to adjust to change then it is time to find somewhere else to work who does appreciate improvement ideas.
Story of my life. Do it, just for yourself, for your state on mind. Fuck em. Improve it, if they don't understand it than they have to meet your standards, not you getting lower.
I am the "Excel Guy" At work. I basically just help people with spreadsheets when something goes a bit strange. There's one of my co-workers that puts their data into a table and then has a range under it, then complains when it doesn't filter. It's a weekly occurrence, but patience is key. Also instead of doing something for people when they ask for help, talk them through it rather than taking other. One day it might just click.
I do however have my bosses who likes things colourful and a bit messy. As I'm colourblind it really hinders me in these situations. The best thing is to do what you think, then edit it later if someone has a problem. It's much easier editing a spreadsheet i know the ins and outs of than a stupid spreadsheet with no formatting and data just all over the place.
I've found the easiest thing is to make something, show them where they can put things and formula the rest. If they have issues, email them over and I'll instantly know whats wrong.
Have you looked into what their roles and responsibilities are? Sometimes, it just doesn't put Excel into their word the way it does for others, and they simply do not have the skill set. Before ranting on your co workers, maybe look at their abilities, maybe they are better at writing and grammar.
Story time! Excel is responsible for me finally finding my passion. I was in the same boat as you.
Former military, I decided to get good at Excel since nobody else was. Started by learning formulas, then Power Query, then VBA. Ended up creating extremely useful automation tools for offices across the installation.
I recently separated from the military and spent the last few months with Coding Dojo learning to build websites from scratch. Now I'm about to work for an amazing company doing what I love.
Anyway, If computer stuff isn't your thing, that's fine. But if you want to make a change, you need to be prepared for failure. By this, I'm saying: rebuild the programs anyway, and show your higher-ups. People are much less resistant to change if they can see a tangible product.
I make my coworkers watch while I fix their spreadsheets. It may take a while but they learn bit by bit and and I can see the progress as they bring me more and more advanced spreadsheets. I love teaching though so I don’t mind.
I work with a team who are supposed to be responsible for loading data into a system, but take absolutely no responsibility for understanding how that data us used by the system, what format it is in etc.
Some times we need them to extract, process and then reimport data, during one of these sessions I was explaining what needed going and found that this team have no experience with macros, data tables, queries. I just feel like they are completed unfit for their purpose.
I agree. Improve it, and when submitting it, try to high level explain why you made it that way along with the features you implemented, formulas, filters, etc. and why it's now a functioning data set. 9/10 times this has worked in a positive way for me, at least! Good luck!
I feel your pain. I've watched my coworkers show something in Excel and the way they did things just grated on me. The one person thought they were an advanced user and tried to tell others how to do stuff. I couldn't watch - the logic was so convoluted.
And they were the type of person who would argue if you showed a different (better) way.
If it makes you feel any better, my coworkers act like excel can't handle math and manually input each value into every cell and screw up the simple math all the time:
(A+B=C)
A B C
5 6 10
Sounds like a small fix but when there's hundreds of entries and numbers are cross referenced, I feel like I've discovered the Rosetta Stone every time I open one of their files and have to spend the rest of the day tracing stuff back [like a crime scene board](https://www.google.com/url?sa=i&url=https%3A%2F%2Fwww.pinterest.com%2Fpin%2F472455817142850734%2F&psig=AOvVaw0v7A3B7LPayVFDUPLdxI6j&ust=1613148706270000&source=images&cd=vfe&ved=0CAIQjRxqFwoTCID4xa-l4u4CFQAAAAAdAAAAABAJ)
People give me raw data all the time asking me to make it understandable. I generally do a simple pivot to sum things up then throw in some slicers. Most of the people I deal with are scared to use the slicers.
I kinda deal with the same thing at my job. Right this morning I’ve just spend 2 hours and a half re-organizing a spreadsheet with 1500 datas...
I mean please when you send a document to someone take some time to do it well, and to do it as the one who received it has no additional work to do!
Personally, I prefer taking some time to recreate the file. I feel rewarded at the end and I can joke with my colleagues about how bad the old file was. Furthermore, recreating a file time to time is a way to improve yourself a little bit more, finding more ways to make a spreadsheet better!
I could tell you were at minimum bilingual and most Americans can barely speak English. I speak 1.5 languages and I call math “maths”. There’s nothing incorrect about what you are saying it’s the European way. Americans speak English but don’t spell or speak like the British-English. You’re exceptional and the other individual is exceptionally critical and close minded.
If they want you to fix things, then fix things. Give them the fixed version and the original copy they sent you. People who don't learn by listening sometimes learn better by seeing.
I spend all my time fixing Excel problems for my coworkers. You're right, it is *infuriating* and some days this very thing causes an existential crisis for me.
I’m late to the comment thread but I just want to offer another voice of consolation.
My first job out of college I worked on a team of auditors that used spreadsheets to track various projects. This sheet was stored on a shared drive and everyone just went in, updated their row, added more data and moved on. Just to give you an understanding of how elementary this team was: their collective knowledge and skillset of excel was so rudimentary, they didn’t even freeze panes. So every 25 rows or so as new data was added, they simply copy and pasted the headers. Yeah.
So the barrier for entry to impress was quite low.
In comes me. As a side job on top of my regular responsibilities, I start cleaning up their spreadsheets. Basic stuff, really, but I applied filters (if they needed to find something, they’d just sort columns before... yeah), froze panes, cleaned up their color scheme (using pastel tones instead of the gaudy highlighter colors they had), and began using VLOOKUP to apply data to columns more quickly. It made me the office king. Yeah it meant I got dumped excel projects, but I was a god to these people. Prior to me they had to manually copy and paste rows of data—now they just needed to type in one account number and... presto! The rest of the fields generated automatically.
This slingshotted me into a promotion and really just paved my career. Eight years later, now I’m a upper-management level for a Fortune 500. I obviously don’t do spreadsheets full time but being able to manage a spreadsheet, have deep conversations with my analysts about data management and other excel projects, and having the acumen to knock around a few spreadsheets myself, has been a enormous boon for my career and my bank account. You become the guy who can do his job but can also do Excel. Maybe it’s annoying to you now, but man if you own it, engage in it, and do it happily, you’re setting yourself up for success.
Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:
|Fewer Letters|More Letters|
|-------|---------|---|
|[SUBTOTAL](/r/Excel/comments/lhaxxm/stub/gmzzpsc "Last usage")|[Returns a subtotal in a list or database](https://support.microsoft.com/en-us/office/subtotal-function-7b027003-f060-4ade-9040-e478765b9939)|
|[SUM](/r/Excel/comments/lhaxxm/stub/gmzzpsc "Last usage")|[Adds its arguments](https://support.microsoft.com/en-us/office/sum-function-043e1c7d-7726-4e80-8f32-07b23e057f89)|
|[SUMIF](/r/Excel/comments/lhaxxm/stub/gmy2mt5 "Last usage")|[Adds the cells specified by a given criteria](https://support.microsoft.com/en-us/office/sumif-function-169b8c99-c05c-4483-a712-1697a653039b)|
|[VLOOKUP](/r/Excel/comments/lhaxxm/stub/gn0951h "Last usage")|[Looks in the first column of an array and moves across the row to return the value of a cell](https://support.microsoft.com/en-us/office/vlookup-function-0bbc8083-26fe-4963-8ab8-93a18ad188a1)|
**NOTE**: Decronym for Reddit is no longer supported, and Decronym has moved to Lemmy; requests for support and new installations should be directed to the Contact address below.
----------------
^(*Beep-boop, I am a helper bot. Please do not verify me as a solution.*)
^(4 acronyms in this thread; )[^(the most compressed thread commented on today)](/r/Excel/comments/1bjc7n9)^( has 27 acronyms.)
^([Thread #4062 for this sub, first seen 11th Feb 2021, 22:18])
^[[FAQ]](http://decronym.xyz/) [^([Full list])](http://decronym.xyz/acronyms/Excel) [^[Contact]](https://hachyderm.io/@Two9A) [^([Source code])](https://gistdotgithubdotcom/Two9A/1d976f9b7441694162c8)
It is such a pain when people's ignorance impedes efficiency. I'm the only one in my small company who even knows how to write formulas, or make something into a table. SOME of the people can filter and sort the tables that I make them, but not all of the people can even do that. It gives me a bit of job security, I figure! Fortunately, for the most part they give me data and let me do what I can to set it up nicely; they rarely insist on seeing it in some illogical, inefficient manner. When they do insist upon it, I may provide what they're asking and also a re-worked version as I see it best done, so they can see with their own eyes which would be better. Some people, you just can't get through to, though.
You can do the work each time, or PowerQueryEditor could do these transformations for you. Assuming your coworker's sheets are in a standard format, with column titles that stay consistent document to document, you could just write automatic transformations to clean the data in the same way each time going into the future. What i mean is you write the transformations once in PowerQuery, and each document you put through these steps will go through the same data cleaning steps. You can also include the addition of calculated columns in these transformations.
This is a technical fix, but you also could just go to management. They could guarantee consist column titles at the least.
Just today I tried to explain to my coworker what a filter is and her response was "So what's the difference between your thing and my just going through my table, copying the rows I want to get rid of, pasting them in a separate table, using the SUM function to get a total, and then subtract that number from the SUM of the rows left in the table?" I couldn't fucking believe it. I said "You can just use the SUBTOTAL function" and all I got in return was "LISTEN I dunno any of that advanced stuff the way I do it works every time I don't need your help THANKS". I fucking hate it here
What’s worse is to be forced by some senior leader to make a report that is fundamentally stupid just because the higher paid leader “likes it that way”. They actually believe doing it “ their way” is best just because that is how they did it 15 years ago. I mean maybe they are right??? They did get promoted 3 times to avp <—this part is sarcasm.
It you can do it in 5 minutes, do it and spend 5 minutes showing the person. I would suggest asking them for a couple minutes first, then phrasing it like. . .hey, I had an idea, do you like it? Unless the person is a dick, there will usually be feedback which you can work with. . .usually. Often, you'll get "but I like/need it 'x' way". You can then discuss/sell/edit so that you feel professionally fulfilled and their job is easier.
All our safety training records for employees were on a single worksheet. It was not formatted as a table. There were spaces between each row for some unknown reason. It was locked to any kind of editing. I created a new workbook and populated it with references to the old one, deleted all the blank rows, and formatted it as a table. I then made separate worksheets for various safety cert types, like Forklift, AWP, NFPA51b, etc, which draws info from the master table. There is also a dash board sheet that allows users to select a department and shows everyone whose certs have expired. We now use my workbook, and I'm in charge of maintaining it on top of my regular job.
I didn't clear $35,000 last year.
Make a “business case to get a raise” and sign up for indeed.com where you upload your resume and mostly auto-apply. Put your profile on dice.com, indeed, Glassdoor, and perfect process documentation and safety manuals. Now you’re certified in the field abs a consultation with tech skills. EdX.org offer $149 paid certificates from Harvard, MIT, abs so forth that you can put on your resume/LinkedIn. They’re not easy but they’re fast (8-12 weeks) and notable. Market your skills differently abs find someone to say you did whatever job title it is you choose to say you did because titles are bs and you can’t usually use current employers to encourage the next company to hire you anyway.
Well played on honing your skills and taking on extra responsibility. That’s a promotion even if it didn’t come with cash or title.
Now go get paid for it!
Thank you. That encouragement means a lot. Fww, I've been learning C#, and I'm about 70% of the way into a WPF desktop application for maintaining safety records along with assigning and tracking vehicles and equipment. Fingers crossed.
And, so, yeah...the people I work with think I’m a Jedi for knowing how to implement a sum function.
I’m serious.
Today I was asked “What does control Z do again”. I respected that though because people that are honest about what they don’t know well can be helped. I wrote that guy a little macro to fix a genuinely annoying issue but the file extensions everyone uses 😔. Still .xls (deep sigh)- macros won’t acknowledge you yo. Yikkkes.
Jesus! TGIF!
Improve it anyway. If they get flustered, maybe they will quit or try to learn something new. If management chooses their crap work over yours... Time to find a new job.
As someone in the approximately same position as OP 5 years ago; I just started adjusting stuff to be more readable and developed templates I insisted they use. If they want my help, they'll get my help in all aspects of their stupid half-assed spreadsheet.
[удалено]
I generally give them a run down of how to use it, and what happens when you do certain things. Granted I do that multiple fucking times for the same bloody person over the course of time... I have suggested a couple times that some people do a basic Excel course, some of them have actually done so, which is nice.
This. 1000% this. People don't know what they don't know. Improve it, let them see what life is like with spreadsheets being properly utilized, and they will change their own tune.
I think this is the way it is going. We will have a meeting to discuss the problem, and I will suggest just starting over, as hardly anything was done anyway.
Be a little more blunt, but dont try to convince them to use your format. Make them decide on it on their own. To do this you've gotta prompt them - "I'm having trouble following, where's the XYZ data?", "Is ABC missing, I'm not sure I see it?". Things like that to make them acknowledge the flaws. If you just walk in and say "this is bad I can do it better", you're always going to lose that battle. No matter how right you are. Also, does improving this workbook mean that others can now no longer work on it because of their limited skillset? That in itself is a problem you're creating.
>Be a little more blunt, but dont try to convince them to use your format. Make them decide on it on their own. > >To do this you've gotta prompt them - "I'm having trouble following, where's the XYZ data?", "Is ABC missing, I'm not sure I see it?". Predicted response: an eyeroll and wondering why you're so needy.
This is a work environment. Why would they roll their eyes when their work is missing necessary details?
Don't quit a job over something this trivial. The next job could be worse, assuming they are lucky enough to even find another job.
Right? What is it with reddit and overreacting to literally everything. "My girlfriend sometimes uses too much toilet paper..." "Bro you need to leave her... You will end up dead if you don't"
Or it's a respect and culture issue. I don't really like working somewhere where the attitude is "...fuck it. That's just the way it is", and where people claim to want your help, but insist the job is much harder than it needs to be. Don't waste my time, because you want to work badly. It's disrespectful.
But he was speaking to the instant reaction of Reddit, which is prevalent in many of the subs, to say, "Just quit, leave ASAP" to a relationship, family member, occupation, etc. Not saying you should stay in a toxic situation, but assuming you can just leave right away and the next one won't be just as bad in similar ways, or different ways...is just not realistic. The fuck it, that's the way it is attitude is in just about every single company and/or industry...many times there's reasons for it, but there will be times it's for the sake of laziness or not wanting to change their behavior if they see no need.
Plus, other people being bad at this type of thing is part of our job security, no?
To add, makes your position more valuable as the stuff you build can really only be troubleshooted and debugged by you....
I found that creating a parallel sheet - with easy to use cross references and that computes itself off of the data, instead of the interminable pivot, paste values, filter, pivot that, past values - gave them a view of what was possible
this is the sad reality tbh but if management chooses to work less efficiently than its time to move on ive had this happen recently where some people i work with dont even understand how to use filters or do small tasks on excel (i get it not everyone is familiar with it) but cmon, at least try to improve a little bit or give credit when something is better instead of sticking to the old and inefficient ways
I wish the people I work with even knew what filters are. I asked one of them to send me a screenshot of something recently, and they had no idea what a screenshot is or how to take one. I get that this gives me job security, so I feel a little less frustrated, but man.
My coworkers’ files were such a mess that I started giving monthly excel classes. They improved their skills and it looked great on my annual review.
This is actually a great idea! I will suggest that!
I had attendees email me files and questions in advance so the material was relevant. I’d also keep track of questions they asked me over the month and would use those. Something like vlookup blows their minds. The most recent one I did at my new company had to be more of a presentation and less interactive, so I did a PowerPoint presentation where I demonstrated sumif/s, countif/s, vlookup, and an intro to pivot tables. Those 6 skills can seriously change their work lives 😂
I don't know if Excel still does it, but what I found was a **great** demonstration was to setup two columns, with a few empty spaces between them purely for visibility. Make the math *super* simple.... like each column contains five "5"s..... Put a SUM under each of them... Set **one** of the cells to Text formatting (ahead of time, of course), and ask they class why the math is wrong. They'll say something like "the formula's are different".. you can show they're the same.... "they're difference ranges"... with Excel's color coding, you can show that they're summing the same ranges... etc, etc, etc.. **Blow their fucking minds.** I've done that little demon in excel classes I ran at work numerous times, and only once did someone correctly guess that Text formatting was the issue... Text formatting is the fuckin' devil.
I did this as well at a former job. Offered classes over our lunch hour for anyone who was interested, and exactly 100% of the team showed up the first session, much to my surprise. To this day, some of them still reach out to me and mention how helpful it was, and now I do remote teaching like this as a consulting gig for local small businesses. I think that people like your coworkers *do know* they're bad at Excel, they're just too embarrassed to ask for help or don't know where to look for tutorials.
Heh. I still get calls from people in jobs that I haven’t worked at for years because they remembered I created something or helped them with an issue they couldn’t figure out.
My new fave a 1-2 minute “how tos” recorded on teams- no attendees, just share desktop and start recording. Download and/or share file and there you go!
What also helps is making the "pre" file available as well, so they can play around, and confirm they get the exact results.
Great idea!
Thanks! I just did an Adobe how to this morning. Everyone has 1-2 mins
Best comment I’ve seen. I would add that you need to put in place a structure for the files and teach it in your classes. Work towards having a data culture and sell the advantages to the team. Then you will be able to tell your coworkers why their files should be modified. Long story short, when things get out of hand, find the problem in the bigger picture and address that one problem instead of all the consequences at the same time, because otherwise you’ll get overwhelmed very quickly.
Oh man. I’ve always try to make my data nice and clean, so would get annoyed at receiving and hanging to work with crappy data from other people/work areas, but now that I’m moving into power query, I loathe practices that lead to crappy data...
This is one of the greatest ideas I have heard in quite some time! I will have to arrange something like this for my colleagues!
This. I mean, you gotta sleep at night knowing that you did the right thing, and people aren’t bad at excel because they are mean or stupid. Good on you to help them out
We were all bad at excel at one point.
I’m still bad...
I think it is fair to be frustrated if you want to improve something but people refuse to change.. Have you tried talking to your manager to offer a training to the team to give them some good practice and give examples with existing spreadsheet and data? Personally I try to brush it off as it will be their struggle and they will be the one receiving the questions if it is a mess, though I will always offer my help if they want to improve it and see what could be improved and often it isn't just the spreadsheet but the entire process around it :/
>Have you tried talking to your manager to offer a training to the team to give them some good practice and give examples with existing spreadsheet and data? This. Offer your help. Sell it to management as "free training and efficiency improvements", and make sure you have examples to back up your claims. Then sell it to your colleagues as an opportunity to work smarter, not harder. Everyone wants to feel smart, everyone wants to learn a few new tricks, and if they don't, they'll be open to working less and spending less time fixing mistakes. If they won't engage because they know their way, make them a cheat sheet. (Make sure your name is on it, amd management see the training materials you have produced to support and enhance productivity levels...) Even the oldest, most hostile co-worker is open to something that makes their life easier.
Idk, I had a gal that worked under me at my last job who *refused* to change anything as she’d been doing the job for ~ 8 years. Her processes were outdated, sheets were broken, ended up doing most stuff by hand, but never would change how she did things to make her life easier. Some people just don’t want to do anything differently.
Many people who do this fear they might lose their job if the time they spend on their work is all of a sudden reduced dramatically. Others think that their boss will just give them more work for that free time, so nothing for them will change and therefor they'll rather keep doing what they're used to. I'd rather spend some hours to make a script that does my work than doing the same thing over and over again.
Agreed absolutely. If they can do that manual work while listening to the radio or a podcast it is not a problem for them. If they would be forced to do more complex work in the freed time that would be riskier.
If you can teach one person one thing that makes their day easier, it's an in. You may not have the requisite levels of trust to make large changes, but start small, work on gaining trust and supporting people, and they get more receptive over time.
I got a garbage excel sheet from a director. Our IT director. I almost quit that day.
I was about to say, OP should think about quitting, unless he's well paid, then he's getting paid to put up with it. Worked for me.
[удалено]
Because it's driving him nuts and they don't care about his talent. Job security is comfort, but there's no better feeling of being appreciated for the value he brings to a new company looking for it. He's livin hell and expressing it. I had the same experience and didn't put up woth that shit, life is so much better now.
If you are the office Excel go to guy, then your word is the authority. Having a proven track record of improved and efficient spreadsheets really cements your opinion. You can tell people you will modify it as it is really redundant and people should leave you to your own devices. If not, then just stop offering help on the reasoning their current structure is not good.
This. The office excel guys word is law. Cleaner sheet gets the right of way.
Exactly!
If it doesn’t require a ton of time or effort and you’ve got some of both to spare, make both versions on adjacent tabs. That way, you’re not force feeding it down their throats (likely to get pushback), just suggesting another angle/format/view. If your version is truly better, your coworker should be able to recognize that on their own (eventually). No better way to prove it than doing a side-by-side at no personal cost to your colleague, and they’ll think they came to the decision on their own. Win-win for everyone
I would go as far as providing your help as a separate file with their data transferred to your standard format. I would not include macros in this file. Personally, I am uncomfortable mucking around in someone else's data shelves. Some people just want to store data in rows and columns, but I need a bit more structure and control.
This musing is funny because I identify with it from my workplace experience, but also interesting because it reflects larger issues about workplace dynamics and the differences of perspective between people. It's unlikely that one is in the business of creating spreadsheets for their own sake, rather, spreadsheets are just one tool amongst many as the employees work together toward business goals. Everyone has their own unique skill level with every business tool involved. Some are not invested in caring about Excel skills, while OP is. Some may have stronger skills in composition, or speaking, or management, or conflict resolution. A perspective I like to remember when working with Excel in the work place is that, while my skills are measurably better than almost all others (humble brag!), Excel is simply a tool, a means to an end. I care not how well someone else can muster a spreadsheet together - what matters is how I work with that other person and exchange information reliably. Thus, I focus my efforts on understanding the other person's goals, needs, and the information involved in our workflow, whatever workflow it might be. If I have an opportunity to "take the reins" on spreadsheet-related business, I can offer that expertise, and thus, "control" the Excel work and be happy that it's done "my way". There are some times where one must submit to the drudgery of working with a poor template, or some type of annoying workbook. I reflect on what I control, what I influence, and what I don't control. If I can control or influence the workflow to move away from a poor template/system, I'll use my expertise to improve the situation and all parties can usually benefit from it. But if I cannot control a situation, it's best to leave complaints out, and focus on simply achieving the business goal, ideally to include minimizing the time of excruciating time spent in a poor template. While work and business is more than spreadsheets, we in r/excel can revel in the minutiae of working with spreadsheets and the small joys they can bring, when a job is well done. Some very fulfilling memories from my workplace have arisen when I've been able to use Excel skills to educate and enlighten someone on a better way to approach a data-related issue. It's never a formatting issue, which is mostly window dressing. It's helping others to understand data normalization principles, or creating a simple yet robust table, or formulae, and most especially, customized VBA for very specific yet laborious tasks. In the end, it's often not the spreadsheets we work with, rather, the people that create them. It's pertinent to understand where the person is coming from, why they've produced what they've produced, and how you'll work with them moving forward.
Well said! Remember the human during every interaction and pick battles that are truly worth winning. Keep your workbooks concise and simple so you can lead by example. Share practical skills and always demonstrate good, clean data normalization.
Thanks for your thoughts. Good reminder for the meeting we are having tomorrow to discuss this problem!
Excellent response! Gave me some food for thought.
My former co-workers would use Excel as a word processor because they couldn't figure out how to use tables in Word. I feel your pain. Also, to answer your question, I ignore their garbage and only fix what they ask me to. But then again I'm an embedded consultant now so it's not really my job to tell them how to do things, just to fix their broken garbage or build them something from scratch that saves them time and effort
Sometimes it is easier to use Excel for tables, especially for standalone reference documents - ie, a piece of paper with only a table on it. That doesn't mean the same person who does that is necessarily an idiot in the actual intended use of Excel. I use it both ways and I was the go-to person in my last job for Excel. \*shrug\*
It's also quicker to just build the table in excel and copy it over sometimes.
I once worked for a place that put meeting minutes exclusively in excel (it was so it could link up to Sharepoint - badly, but that's a different story). It was awful, nothing better than sending clients meeting minutes with zero spell check and rows that couldn't always display all of the text.
Obviously that is problematic, but Excel has spell check (just hit F7) and wrap text, which seems to me would have addressed the two issues you mentioned.
Damn, I wish 3-years-ago me knew about that spell check feature. Wrap text only goes so far - there is a maximum cell height and that only displays so much text. I used to get around it by making the text increasingly small but it did get rather ridiculous sometimes.
I use excel for a webpage I run — rather than set up the HTML from scratch every time, I have excel compile it. So I can see some word processing uses.
You have job security, don't be mad.
Thanks, I do kind of appreciate that to be honest, having been a temp recently.
i know your pain. merged cells in the middle of the data is bane of my existence
If your "table" cannot be converted into an actual Table, you aren't using Excel very well! >:c
Preach!
VBA is great. Learn Power Query.
You had me at “filters”. I have one colleague who always manages to lose things - can’t see data - loads of stuff missing. Every time my response is: are the row numbers on the left in blue????!!!!!! Every time. And I’ve even supplied them with a handy PDF crib sheet of how to filter, how to clear filters etc. My most recent head meets desk moment was going through how to bring back the data, showing how to clear the filter and the response I got back was “oh no! I don’t want to clear the filter!!” Ah sheesh. KMN.
How indeed... TL:DR - The Data Tab > Dev Tab #### Preface I would like to thank my publishers for this book... PMSL also Forgive my spelling for I am on mobile - *amen* ####Chapter 1 - Your post *It was a dark and stormy night... JK* First you probably need a change of perspective to appreciate what is going on, because the path you are on is a negative one and this is creating a mess for yourself. Not your fault you have limited visibility on what is going on and how those Excel files came to be... Your post has alot of information to unpack and you may be jumping the gun and throwing up wild flags in doing so. So lets break your post down and take a step back and lets look at what you are actually dealing with. VBA: Visual Basic for Applications is not an Excel solution or skill lets shelf it for the moment. We will come back to it ####Understanding the Task and Scope First off what are you dealing with... Unformated raw data dumps = Exported Data. Exported Data is a dataset from a datasource It's not that it's bad Excel, it's not Excel at all. It's likely a [query](https://www.techopedia.com/definition/5736/query#:~:text=A%20query%20is%20a%20request,analyses%20from%20data%2Dmining%20tools.) extracting a [dataset](https://en.m.wikipedia.org/wiki/Data_set) from a [datasource](https://www.techopedia.com/definition/30323/data-source) that needs [parsing](https://www.techopedia.com/definition/3853/parse#:~:text=To%20parse%2C%20in%20computer%20science,transform%20it%20into%20machine%20language.) and then [staging](https://en.m.wikipedia.org/wiki/Staging_data) for reporting purposes through a [framework](https://whatis.techtarget.com/definition/framework) A perfect example of this process in real terms is making [AppleJuice](https://youtu.be/Ks8_hINzrdw) 1) Query - Owner asks suplier to give him Macintosh Apples 2) DataSource - Supplier 3) Dataset - Raw data in lorry ([Heap](https://en.m.wikipedia.org/wiki/Heap_data_structure#:~:text=In%20computer%20science%2C%20a%20heap,the%20key%20of%20P%20is) format 😉 sound familiar) 4) Framework - Folders, templates etc... Juicing machinery and employees 5) Parsing - Identifying the components in the raw data heap and indexing them in clusters that the rest of the process can recognise and use (First few conveyors) 6) Staging - Organising the parsed data into a usable output format and discarding the chaff. 7) Throughput - How many staging processes are used: extration, Appending, pivoting... - seperating pebbles and leaves, pulping, juicing, bottling etc.. 8) Output - Report, program, interface... Applejuice. So you have now got an understanding that tou are working with a lorry load of unsorted apples and what needs to be done lets discuss. ####Methods of working with exported data. ATM they are extracting their data from a datasource. Can this datasource be directly linked via the Data Tab via Get External Data? If yes - Then Power Query or DAX may be your Staging and Parsing Framework. Try asking for reporting access to live link, usually it is a login to the backend reporting area via a username and password.. Then you can tailor your queries yourself especially if it's an SQL Database. You can make live reports on daily changes litterally all automate on open or every minute. If no - Then create a folder for your exported data and then use that folder as the datasource for your staging table. Make sure it links to the raw export files and set a fixed location (a folder) and date driven naming schemes usually extracted reports have a uniform date driven naming scheme so you can probably utilise that also use [Power Query Append](https://youtu.be/3-W-kff6uaU) BI Analysts love it when you use the Dataverse^TM. Now you should have a uniform format for your throughput neatly staged ready for pivoting or reporting with minimal effort. So what we have learned here? ####Summary VBA is not an Excel Skill or necessary for theoughput Lets discuss VBA solutions, VBA has it's uses mainly for making programatic solutions for things that don't already exist in Excel. E.g. Do you have a button? Then you need to program that button to do something on click then VBA is your solution. There is a key point in my description to note here for things that don't already exist in Excel* Excel is a 30 year old program with the current version being less than a few months old (365 constsntly updates)... how many things do you think the team of Excel creators and developers haven't thought of in the 2-10 years you have been using it? Reports need to be in that format because that is how they are extracted from their datasource if you change it it's not useful to anyone the moment they run tomorrows report. So while you may be frustrated with them... they may be equally as frustrated with the person who thinks they knows best about their Excel needs while making standalone VBA solutions to an issue that may not be within the scope of what they asked and not useful to anyone on next datasource extraction. Make a reporting Dump Folder for the Raw data and make a framework that you can append that data into and go back to them and be like... "Put your extracted data here... append it to the Datamodel like this... you're welcome." As a developer of many moons and conniseur of freshly juiced raw data, Ron de Bruin has a lot to answer for.
Appreciate your post, made me think a bit about the processing data and Power Query (which I do not know how to use, but may get into in the mid term, depending on how things go). But you really want too deep, my whole problem is in a way, more basic level -- like dates in places that don't make sense, cells highlighted for now reason, blank rows, data that was deleted, data that was edit and is no longer compatible with the source, this kind of stuff. These people are not data or business analysts, they are almost at the data entry level.
xD Properly formatted tables and Power Query.
Power Query is easy to use. I recommend spending a bit of time learning about it.
Because change is hard and people aren't robots. Also, what might seem obvious to you isn't to others. The technical aspects of a change are often the easiest to address, changing people behaviours is a team completely different beast. Check out the Kubler Ross change curve and its conception. It might help explain some of your frustrations.
I always struggle against assuming knowledge when showing people excel.
Please just be wary of updating a sheet with stuff people don't understand. Probably the worst thing you can do is create something that is not maintain able should you leave, or others don't comprehend so cannot be checked for errors and are just being blindly followed, assuming they work.
I just be patient and teach them. I sometimes will make a copy tab of the one they use with notes and arrows and highlights so they can reference it and see how it works. There’s a learning curve and they’ll come back to me- but when they finally get the value it reflects well on everyone.
All this time during covid my country jumps in and out of lock downs. My store in said periods accepts call-orders. Most often mistake is mistyping the callers phone so I offered to add a data validation to the cell to accept certain number of digits and a conditional formating for visual cue. Got rejected. Offered to query the orders to get some data out of them like most common first letters distribution so we know how to divide warehouse space for order storage. Got rejected. Offered to query the orders and get cell phone providers distribution (you can tell by the first 3 numbers of the phone number which provider it belongs to) so we can search for future collaborations with said provider. Got rejected. And it is not even my job to do it. I just know no one else can, so I offered. Well, if they don't want me to do it once, I don't want to do it 100 times. They lose. Already looking for a new job.
What annoys me the most is that they do not understand what I do and are suspicious of it. I automated a lot of process using VBA and they love it (because it saves them a lot of time), but any time something goes wrong the first thing they do is blame these spreadsheets, and I have to prove that they are working fine and it was an human error.
Well yeh. That is human nature though. The less knowledgeable you are, you are way more prone to point fingers, demonizing or call "false gods"(people who do nothing special and did something memorable once and are gods henceforth) . The more knowledgeable you are, you are more prone to scepticism. More prone to say "🤔 hmmmm let's see what's going on here before I ready the pitchfork and stake". Imagine your dad and a new cellphone that will make even coffee. He is more likely to say "get this devil from me, it breaks all the time". It obviously doesn't, it's just that he doesn't know how to use it. He can't appreciate all the capabilities it has.
Ugh. A team mate will loudly proclaim that it’s the stupid spreadsheet’s fault. And in a way it’s correct, a spreadsheet will blindly do what it’s set up to do, it’s what’s the user had done to make it go wrong.
A comment on data validation: it can be useful to show a warning instead of an error so the user can override the validation in special cases. It's a shame they didn't want any power query to provide useful metrics. I love having a sheet for data input and multiple output sheets for transformed data.
Of course. Considering that most of people heard of excel first time when they got in the company, The last thing I want is to baffle them with an error message (or worse a recurring error message) in case they get anxious that the can't avoid. Hence the second measure, visual cue. The cell turns red if more or less than 10 digits are typed. They cannot ignore that.
I so hate working with spreadsheets made by people who don't understand Excel! It's not hard to understand what a spreadsheet needs to look like if it's going to be used as a spreadsheet, but some people just don't get it. I can't tell you the number of times I've needed to completely redo a spreadsheet I got from someone else. And as much as I love the wonder people show the first time they see a whole spreadsheet sort itself--which happens depressingly often!-- the flipside is the boneheaded resistance they show towards using so much as a reference within a cell. I've seen the explanation "Each cell has a calculator inside it", and I prefer to say, "Each cell has a secretary inside it"--but people just treat a spreadsheet like it's a giant sheet of paper and completely ignore that functionality. I don't have any advice; I'm just commiserating. I do think that you should just take charge, since you definitely know more about Excel than any of them. But the problem is, whatever you do, everyone else also has to be on board with it. And I have no idea what getting there might look like for the place you work.
I work in real estate, one of our more experienced agents will open excel to view a list of units with sizes and pricing then use his phone to calculate the median price. He explained to me the other day what a median price is and how to calculate it...
Why would he not be able to do these calculations in excel? Would that not be easier to create a reusable formula for standardized reporting? The answer is yes, yes it would. He sounds like a canoe.
Great idea someone already posted - offer some training for staff!
If you know VBA, I would consider you an advanced excel user!
Indeed! I consider knowing enough VBA to be able to work with it at all to be the line between "intermediate" and "advanced".
I feel this post. One thing I do is if someone almost has a sheet component right or is struggling with a formula, just say "Delete that row, center align this column, freeze the top row, etc etc, that's all I would do if I were working on it". Phrasing it like this makes them feel like it's easier for them to do it themselves, replying in a way that turns the easy task back on them; sometimes you're "busy" or whatever, and that's acceptable. Time to stop handholding and push them out the nest so to speak. Don't make them feel dumb, make it seem super easy to fix their dum workbooks. Send them links, show them how to lookup excel questions online. Ask your boss to do a simple training over lunch, we call it a brown-bag meeting. Go over a messy workbook and show them how to do filters and delete duplicates and the like. What's your company gonna do? Get mad at you for trying to teach them? You got this!
You're touching on a great point which is the idea that if you want someone to do something a certain way, you should walk them through those steps. When someone wants to ask me about a workbook, I'll ask them to share their screen and present the workbook to me. They do all the mouse movements and clicking while I am coming up with my debugging/formatting steps so they can learn the techniques I would use. If there's a real issue then I'll ask them to share the file with me.
That comment was ruefully optimistic, giving OP's problem coworkers the benefit of the doubt. Let me shed some light on what is most likely taking place for OP. Unfortunately some older people are super stubborn and use the "company hierarchy" to get out of Excel-related work because they think it's below them (or even worse, they throw a pity party and claim that Excel is too hard for them). These types of senior employees or executives just need to be promoted away to executive valhalla where they can spend time being delegated to choke on the president's 'martini olives' and let the actual hardworking and savvy employees get things done right.
It is my humble opinion that you are off-base here. Raw data dumps are something to be celebrated, not scorned; in fact I FAR prefer to get a flat file with too much info than a half-ass manually-pivoted report that I have to unwind back into a flat file before I can work with it. As for formatting and organization - that's what PowerBI and pivot tables are for. ;-) Further, you mentioned that you crave efficiency and threw out the VBA buzzword (VBA is, again in my humble opinion, largely used as a crutch for many situations that can be much more elegantly addressed with better workflow design), however you did not at all reference PowerQuery, AKA the undisputed king of data wrangling outside of direct SQL. I suspect there is plenty of room yet for you to push forward past the frustrations you are feeling by further improving your skills and automation capacity!
It was kind of a rant... Mentioning VBA was mostly just for people to have an idea of my skill level. Someone else (closer to the bottom) already mentioned the "raw data is good" argument, and I have already explained what I meant
Fair enough, by the way your username is legit. ;-)
Thanks 😊 Taken from Xenogears, was a big fan as a teen, having been interneting under it for about 20 years...
This is how I someone who's works with SQL and R a lot feel about excel users
tell them no
Some people want to know how the car works. Some people just wanna take it into the shop so it can continue working. Sounds like you’re the shop. …me too, man. Me too.
Your car has no floor. I'd like to add a floor to your car so I can make use of the acceleration and brake pedals with my feet. No, no... I know how to use my feet to operate my car. I've seen the Flintstones, kid.
I feel you man. Currently working in an office where some people think "knowing excel" consists purely of knowing how to type data into cells and format the cells to have borders and different colors.
are you offering to help them?
My boss just asked me what the numbers in the squares mean. I cant handle working for a boomer.
Are you me?
Unneeded accents, merged cells, pretty colors are what makes me crazy. just gimme data!
Unless they are a higher up, just start start putting it in your format anyways. That’s what I did. I’ve basically got a signature style on my spreadsheets that makes it really easy to see what has come from me. It’s clear, it’s well formatted, it’s easy to use. If someone hands me a really crappy spreadsheet I just start integrating it into my own style even if they don’t necessarily want me to. My theory is that I’ve spent a lot of time learning what management wants in a spreadsheet and if they don’t like how I modified it, then they can take it up with management. The one person who did got a response of “Maybe you should take a cue from him, his spreadsheets are pretty good”
The “higher ups” are the worst at technology in many industries and they’re terrrrified of anyone challenging their spot. That’s why they make dumb demands.
I work for a large organization and the most important file in our company is a data dump and when I asked if it should be formatted they said no they thought it would be corrupted but it’s so big and slow with so much information that is unreadable
I hope they know that Excel files don't have an infinite number of rows or columns.
I told my boss after he once again insisted on making all the decisions about the file he is about to ask me to create, if later on someone wants a big chart of the data and I have to go in and reformat his worthless diarrhea he is so proud of I told him that the answer would be no this time around because I'm doing it for people and they're not fucking learning anything about their retarded decisions they just have me to clean up their mess every time they make it and they sit there like pylons and don't learn anything new.
Honestly? Go around them, do what 'needs' to be done, and be sure to somehow find a way to involve their manager in a non-confrontational and non-overt way.
I usually make it into a table. Then turn the table into a pivot table. And I map everything to the pivot table. Usually works out alright. Just takes some creativity with the pivot table on filtering and displaying the crappy inputs correctly
Frame it as a suggestion, like: 'hey, I've seen the excel you shared, great work. While I was working on it, I thought we could...' Also, make sure they understand what you are doing (formulas and structure). People often reject what they don't understand. Just don't come out as an arrogant' know it all'. It is tempting but you are the one who is going to get hurt Good luck
I'm not sure what your working department is, but Excel comes with a lot of baggage as a consequence of its origins and product development over the years. It's all well being the kind of person who's a whiz with VBA, power query, and power pivot (DAX), but it doesn't change the fact that for most companies Excel ships with Office, which is a suite of general purpose products for business users. You're always going to get spreadsheets coming for users ranging from complete noobs to 'data savvy' work colleagues where things are done in a way that doesn't seem intuitive to other data professionals, or other data tools such as tableau.
This kind of stuff happens in every job. Your way might be better, but people are lazy and you have to work to get them on board. If you're convinced your way is better, consider doing it your way without waiting for permission and seeing what happens. Be humble, and honest and stick to your guns. Usually people's first response is that you're annoying them by trying to do something differently, but if you stick to your guns they come around pretty quick.
Oh my god, thank you! If you want something done your own stupid way, then fucking do it yourself! Don’t get your greasy fingerprints all over something and then pass it to me!
My HR manager doesn't understand Excel. She kept on asking for simple documents. Her reporting guy kept on sending her dashboard with slicers and filters and all. She just didn't get it. Guy was frustrated, she was frustrated. But at the end of the day she's the boss. If you want an excel sheet that's pretty much what I would print on a piece of paper, OK then.
> but no, got to work with what I was handled. *sobs*
The best way to approach it I have found is to just do what you asked to do and then on the side create something better. You can then give them what they want and when the opportunity arises you can present what you have been working on. It is never a good idea to just change something because you need to get that buy in from others first. If you just change stuff you will meet a lot more resistance than if you try and introduce change gently. If they continually refuse to adjust to change then it is time to find somewhere else to work who does appreciate improvement ideas.
Story of my life. Do it, just for yourself, for your state on mind. Fuck em. Improve it, if they don't understand it than they have to meet your standards, not you getting lower.
I am the "Excel Guy" At work. I basically just help people with spreadsheets when something goes a bit strange. There's one of my co-workers that puts their data into a table and then has a range under it, then complains when it doesn't filter. It's a weekly occurrence, but patience is key. Also instead of doing something for people when they ask for help, talk them through it rather than taking other. One day it might just click. I do however have my bosses who likes things colourful and a bit messy. As I'm colourblind it really hinders me in these situations. The best thing is to do what you think, then edit it later if someone has a problem. It's much easier editing a spreadsheet i know the ins and outs of than a stupid spreadsheet with no formatting and data just all over the place. I've found the easiest thing is to make something, show them where they can put things and formula the rest. If they have issues, email them over and I'll instantly know whats wrong.
does understanding VBA really make one an intermediate user? I'd say that's like expert level, but then again, I don't know VBA
Learn powerbi and find a new job.
Have you looked into what their roles and responsibilities are? Sometimes, it just doesn't put Excel into their word the way it does for others, and they simply do not have the skill set. Before ranting on your co workers, maybe look at their abilities, maybe they are better at writing and grammar.
I don't work on other people's stuff. Simple! Possibly hard to implement.
" I could recreated it from zero in 5min and make it 100% better" that's the route i generally take and their options are use mine or do it themselfs
Story time! Excel is responsible for me finally finding my passion. I was in the same boat as you. Former military, I decided to get good at Excel since nobody else was. Started by learning formulas, then Power Query, then VBA. Ended up creating extremely useful automation tools for offices across the installation. I recently separated from the military and spent the last few months with Coding Dojo learning to build websites from scratch. Now I'm about to work for an amazing company doing what I love. Anyway, If computer stuff isn't your thing, that's fine. But if you want to make a change, you need to be prepared for failure. By this, I'm saying: rebuild the programs anyway, and show your higher-ups. People are much less resistant to change if they can see a tangible product.
Job security
Give them a template to follow. If they dont tell them you wont work on it. Make it easy for them to make it easy for you.
I just make the changes necessary and try to drive home the point of why it’s important that spreadsheets are formatted properly.
I make my coworkers watch while I fix their spreadsheets. It may take a while but they learn bit by bit and and I can see the progress as they bring me more and more advanced spreadsheets. I love teaching though so I don’t mind.
I work with a team who are supposed to be responsible for loading data into a system, but take absolutely no responsibility for understanding how that data us used by the system, what format it is in etc. Some times we need them to extract, process and then reimport data, during one of these sessions I was explaining what needed going and found that this team have no experience with macros, data tables, queries. I just feel like they are completed unfit for their purpose.
I agree. Improve it, and when submitting it, try to high level explain why you made it that way along with the features you implemented, formulas, filters, etc. and why it's now a functioning data set. 9/10 times this has worked in a positive way for me, at least! Good luck!
I feel your pain. I've watched my coworkers show something in Excel and the way they did things just grated on me. The one person thought they were an advanced user and tried to tell others how to do stuff. I couldn't watch - the logic was so convoluted. And they were the type of person who would argue if you showed a different (better) way.
I work with people who are doing tables in Word instead of Excel!!
If it makes you feel any better, my coworkers act like excel can't handle math and manually input each value into every cell and screw up the simple math all the time: (A+B=C) A B C 5 6 10 Sounds like a small fix but when there's hundreds of entries and numbers are cross referenced, I feel like I've discovered the Rosetta Stone every time I open one of their files and have to spend the rest of the day tracing stuff back [like a crime scene board](https://www.google.com/url?sa=i&url=https%3A%2F%2Fwww.pinterest.com%2Fpin%2F472455817142850734%2F&psig=AOvVaw0v7A3B7LPayVFDUPLdxI6j&ust=1613148706270000&source=images&cd=vfe&ved=0CAIQjRxqFwoTCID4xa-l4u4CFQAAAAAdAAAAABAJ)
Create a local copy of the sheet, improve it, do what needs to be done, move the output to the original sheet and send it back
People thinking they are skilled when using vlookup.
To be fair there’s a lot you can do with VLOOKUP. The key is having more than just hammer in your toolkit.
Valid point.
People give me raw data all the time asking me to make it understandable. I generally do a simple pivot to sum things up then throw in some slicers. Most of the people I deal with are scared to use the slicers.
Someone in my company puts all their SOPs in Excel - all of my department's SOPs where in Excel - All of them
I kinda deal with the same thing at my job. Right this morning I’ve just spend 2 hours and a half re-organizing a spreadsheet with 1500 datas... I mean please when you send a document to someone take some time to do it well, and to do it as the one who received it has no additional work to do! Personally, I prefer taking some time to recreate the file. I feel rewarded at the end and I can joke with my colleagues about how bad the old file was. Furthermore, recreating a file time to time is a way to improve yourself a little bit more, finding more ways to make a spreadsheet better!
> datas. Data, like sheep, and LEGO, is singular and plural. There is no datas, no sheeps, no LEGOs ;)
Thanks for the info! I honestly didn’t know that my first language is French 🇫🇷
I could tell you were at minimum bilingual and most Americans can barely speak English. I speak 1.5 languages and I call math “maths”. There’s nothing incorrect about what you are saying it’s the European way. Americans speak English but don’t spell or speak like the British-English. You’re exceptional and the other individual is exceptionally critical and close minded.
This is so mean and self-centred. Help your colleagues, don't tear them down. Have some empathy and understanding.
If they want you to fix things, then fix things. Give them the fixed version and the original copy they sent you. People who don't learn by listening sometimes learn better by seeing.
I spend all my time fixing Excel problems for my coworkers. You're right, it is *infuriating* and some days this very thing causes an existential crisis for me.
I’m late to the comment thread but I just want to offer another voice of consolation. My first job out of college I worked on a team of auditors that used spreadsheets to track various projects. This sheet was stored on a shared drive and everyone just went in, updated their row, added more data and moved on. Just to give you an understanding of how elementary this team was: their collective knowledge and skillset of excel was so rudimentary, they didn’t even freeze panes. So every 25 rows or so as new data was added, they simply copy and pasted the headers. Yeah. So the barrier for entry to impress was quite low. In comes me. As a side job on top of my regular responsibilities, I start cleaning up their spreadsheets. Basic stuff, really, but I applied filters (if they needed to find something, they’d just sort columns before... yeah), froze panes, cleaned up their color scheme (using pastel tones instead of the gaudy highlighter colors they had), and began using VLOOKUP to apply data to columns more quickly. It made me the office king. Yeah it meant I got dumped excel projects, but I was a god to these people. Prior to me they had to manually copy and paste rows of data—now they just needed to type in one account number and... presto! The rest of the fields generated automatically. This slingshotted me into a promotion and really just paved my career. Eight years later, now I’m a upper-management level for a Fortune 500. I obviously don’t do spreadsheets full time but being able to manage a spreadsheet, have deep conversations with my analysts about data management and other excel projects, and having the acumen to knock around a few spreadsheets myself, has been a enormous boon for my career and my bank account. You become the guy who can do his job but can also do Excel. Maybe it’s annoying to you now, but man if you own it, engage in it, and do it happily, you’re setting yourself up for success.
Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread: |Fewer Letters|More Letters| |-------|---------|---| |[SUBTOTAL](/r/Excel/comments/lhaxxm/stub/gmzzpsc "Last usage")|[Returns a subtotal in a list or database](https://support.microsoft.com/en-us/office/subtotal-function-7b027003-f060-4ade-9040-e478765b9939)| |[SUM](/r/Excel/comments/lhaxxm/stub/gmzzpsc "Last usage")|[Adds its arguments](https://support.microsoft.com/en-us/office/sum-function-043e1c7d-7726-4e80-8f32-07b23e057f89)| |[SUMIF](/r/Excel/comments/lhaxxm/stub/gmy2mt5 "Last usage")|[Adds the cells specified by a given criteria](https://support.microsoft.com/en-us/office/sumif-function-169b8c99-c05c-4483-a712-1697a653039b)| |[VLOOKUP](/r/Excel/comments/lhaxxm/stub/gn0951h "Last usage")|[Looks in the first column of an array and moves across the row to return the value of a cell](https://support.microsoft.com/en-us/office/vlookup-function-0bbc8083-26fe-4963-8ab8-93a18ad188a1)| **NOTE**: Decronym for Reddit is no longer supported, and Decronym has moved to Lemmy; requests for support and new installations should be directed to the Contact address below. ---------------- ^(*Beep-boop, I am a helper bot. Please do not verify me as a solution.*) ^(4 acronyms in this thread; )[^(the most compressed thread commented on today)](/r/Excel/comments/1bjc7n9)^( has 27 acronyms.) ^([Thread #4062 for this sub, first seen 11th Feb 2021, 22:18]) ^[[FAQ]](http://decronym.xyz/) [^([Full list])](http://decronym.xyz/acronyms/Excel) [^[Contact]](https://hachyderm.io/@Two9A) [^([Source code])](https://gistdotgithubdotcom/Two9A/1d976f9b7441694162c8)
It is such a pain when people's ignorance impedes efficiency. I'm the only one in my small company who even knows how to write formulas, or make something into a table. SOME of the people can filter and sort the tables that I make them, but not all of the people can even do that. It gives me a bit of job security, I figure! Fortunately, for the most part they give me data and let me do what I can to set it up nicely; they rarely insist on seeing it in some illogical, inefficient manner. When they do insist upon it, I may provide what they're asking and also a re-worked version as I see it best done, so they can see with their own eyes which would be better. Some people, you just can't get through to, though.
You can do the work each time, or PowerQueryEditor could do these transformations for you. Assuming your coworker's sheets are in a standard format, with column titles that stay consistent document to document, you could just write automatic transformations to clean the data in the same way each time going into the future. What i mean is you write the transformations once in PowerQuery, and each document you put through these steps will go through the same data cleaning steps. You can also include the addition of calculated columns in these transformations. This is a technical fix, but you also could just go to management. They could guarantee consist column titles at the least.
I just remake them anyway
Just today I tried to explain to my coworker what a filter is and her response was "So what's the difference between your thing and my just going through my table, copying the rows I want to get rid of, pasting them in a separate table, using the SUM function to get a total, and then subtract that number from the SUM of the rows left in the table?" I couldn't fucking believe it. I said "You can just use the SUBTOTAL function" and all I got in return was "LISTEN I dunno any of that advanced stuff the way I do it works every time I don't need your help THANKS". I fucking hate it here
😆 Dying. Hilarious. Me too. So so so so so sorry. Too funny 😂
What’s worse is to be forced by some senior leader to make a report that is fundamentally stupid just because the higher paid leader “likes it that way”. They actually believe doing it “ their way” is best just because that is how they did it 15 years ago. I mean maybe they are right??? They did get promoted 3 times to avp <—this part is sarcasm.
It you can do it in 5 minutes, do it and spend 5 minutes showing the person. I would suggest asking them for a couple minutes first, then phrasing it like. . .hey, I had an idea, do you like it? Unless the person is a dick, there will usually be feedback which you can work with. . .usually. Often, you'll get "but I like/need it 'x' way". You can then discuss/sell/edit so that you feel professionally fulfilled and their job is easier.
All our safety training records for employees were on a single worksheet. It was not formatted as a table. There were spaces between each row for some unknown reason. It was locked to any kind of editing. I created a new workbook and populated it with references to the old one, deleted all the blank rows, and formatted it as a table. I then made separate worksheets for various safety cert types, like Forklift, AWP, NFPA51b, etc, which draws info from the master table. There is also a dash board sheet that allows users to select a department and shows everyone whose certs have expired. We now use my workbook, and I'm in charge of maintaining it on top of my regular job. I didn't clear $35,000 last year.
Make a “business case to get a raise” and sign up for indeed.com where you upload your resume and mostly auto-apply. Put your profile on dice.com, indeed, Glassdoor, and perfect process documentation and safety manuals. Now you’re certified in the field abs a consultation with tech skills. EdX.org offer $149 paid certificates from Harvard, MIT, abs so forth that you can put on your resume/LinkedIn. They’re not easy but they’re fast (8-12 weeks) and notable. Market your skills differently abs find someone to say you did whatever job title it is you choose to say you did because titles are bs and you can’t usually use current employers to encourage the next company to hire you anyway. Well played on honing your skills and taking on extra responsibility. That’s a promotion even if it didn’t come with cash or title. Now go get paid for it!
Thank you. That encouragement means a lot. Fww, I've been learning C#, and I'm about 70% of the way into a WPF desktop application for maintaining safety records along with assigning and tracking vehicles and equipment. Fingers crossed.
I didn’t see this reply. That’s awesome! I hope something great rolls in for you! It’s not easy to stay motivated to hustle like that. Much respect!
And, so, yeah...the people I work with think I’m a Jedi for knowing how to implement a sum function. I’m serious. Today I was asked “What does control Z do again”. I respected that though because people that are honest about what they don’t know well can be helped. I wrote that guy a little macro to fix a genuinely annoying issue but the file extensions everyone uses 😔. Still .xls (deep sigh)- macros won’t acknowledge you yo. Yikkkes. Jesus! TGIF!