Am I playing it too safe with multiple helper columns instead of one complex formula?
Our take
Hi everyone!
I'm a teacher from PH. I started building an Excel system to automate the heavy paperwork we face esp during the EOSY (End of School Year). What began as a "lazy" way to handle my own tasks turned into a tool I shared with three coworkers. From there, I dove into books and YouTube, eventually building a system using basic VBA and complex formulas that my entire school now uses. I'm proud to say it turned weeks of work into just one hour... and since it's summer here, I'm revamping the whole thing to make it available for Mac users while still being limited to Excel 2019 and older (as most teachers don't have the latest ones). I'm proud of the progress but at the moment, I am feeling self-doubt.
Just earlier, I decided to lean heavily into using helper columns that my main sheets can reference to. I have quite a few, but each one has a clear, single responsibility. This makes the logic easier for me to follow and debug later. Plus there's a lot to consider really and although I know I could compress a lot of this into fewer, more complex formulas (or even one large formula) but I find this approach much easier to maintain. It's a bit like organizing my own room... I prefer to set things up in a way that I understand so I always know where to look if something goes wrong. That said, I’ve started second-guessing myself while looking at how many helper columns I’m using. Am I playing it too safe (I mean this in a negative way) knowing there are better ways to do it?
My question is: In a huge system like this, do you prefer breaking logic into multiple helper columns for clarity, or consolidating everything into fewer, more complex formulas?
(I admit I might just be overthinking it (maybe a bit of imposter syndrome kicking in, comparing myself to how others will probably do the same work I'm doing), but I’d really appreciate hearing how others approach this.)
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- How to deal with a bulky spreadsheet that is starting to hit the limits of Excel?Hello all, I have been venturing on quite the Excel journey the past year or so. I made a corporate spreadsheet that is approaching 500k formulas and that is starting to get serious speed issues at this point. It is 2026, so I conversed with ChatGPT several times regarding the speed issue, but realized I am way better off asking the experts here anyways. What is the problem So, my spreadsheet imports flat databases with specific information regarding objects that need further analysing. The imported flat databases run from say A tot CC or something, from which I probably draw about 12-15 datafields that are used for further analysis. It 'may' be more in the future. Afterwards, said data gets 'enriched' (manually) by things that aren't in the database, also because said data needs a human eye that cannot be automated. So far, so good. Right now, each object gets analysed from several different angles. As it stands, my spreadsheet runs from A until NA or something on the Formula Page. Many columns receive data from preceding columns, that are in the turn the result of many (slightly complex) logical IF or IFS tests, many of which are nested 3 or 4 deep. Often, they work in conjunction with X.LOOKUP to retrieve values, as the columns on the formula page are not equal. For example: A until BC on the Formula Page may analyze 150 objects, BD until DD may analyse 100 objects (from the same dataset, so narrower), and so forths. Thus a lot of X.LOOKUP is required, also because the first 'block' comes up with values that need to be found with X.LOOKUP. Also, values need to be retrieved from the flat database 'import' page with X.LOOKUP. Finally, X.LOOKUP is an insurance compared to FILTER, as I am not fully convinced that empty values in the flat database always contain a space (" "). To get to the point I use many IF, IFS, AND, and if need be, OR, formulas. Thinks: tens of thousands, probably in excess of 100k. These are compounded with X.LOOKUP, or X.LOOKUP gets used copiously without those. Here too, think tens of thousands. These formulas are - as much as possible - in array format, even though I find it controversial to do that as I consider how it can create a chain of updates throughout the spreadsheet. 'Dependencies' is the name of the game, with one object receiving many possible alterations / adjustments due to manual input data, for which the spreadsheet needs to provide. Right now, when I update a value, it may take up to 4 seconds to update the spreadsheet, which is already beyond the annoyance point for me. This leads me to these (hopefully) simple questions: Is it smart to use array formulas, knowing that each thing I change should only impact that one object line (for example, row 488) and none other? It is important to mention that object 1 does not influence object 488, or any other. Any manual data field only effects the object in the row it is in. In my mind, array formulas do not make sense in that regard, as it can result in a cascade of updates, but apparantly array formulas are 'way more efficient'. Is use of a VBA library the way to go to reduce lag and create more of an instant spreadsheet again? I am not able to code in VBA yet, but I am in the slow process of learning it regardless. Alternatively: should I use LET whenever a repeated lookup is needed in the same formula? Really looking for to your answers! submitted by /u/EvolvedRevolution [link] [comments]
- From C++ Hobbyist to Excel: Is the "Programmer Mindset" an edge for freelancing, or is the market too saturated?Hey everyone My life ig: I had a jump that my friends consider "downwards" in my field: I've been a programmer for 5 years, although as a hobby, since I really like everything about developing. Recently, I wanted to see what Excel was about. I didn't know it since I always heard "Excel is hard to learn and what-not" (I'm from Argentina), but once I got into it... what a wonderful program. It has a lot of dynamism in formulas and I like it, it's not super static or anything. I learned nested formulas, their syntax, and how to make dashboards, all in 1 week. I'm not the fastest, since I still don't know the interface 100%, but at least the intermediate stuff until creating a dynamic dashboard with slicers can take me 1-3 hours and while relaxing. Decided to get home office work from this because of how simple it is, I got into Fiverr, but it turns out it's full of offers, especially cheap ones in data entry. However, those who make dashboards put a "limited number" of formulas, which to me makes no sense, since a project, depending on its difficulty, could take more than 3 formulas, or 2, or 5. It makes no sense to limit the work for a few formulas. The main question is, since I don't have projects beyond the one I did about international importers and its dashboard, I have no choice but to keep making more of my own projects. Question: \* For those who have been in Excel for a long time, do you think decent work can be found? * How long could it take until the first one? Since I don't have reviews, my profile looks weird saying I have a decent level without any work haha. And I don't want to limit myself to the OCEAN of data entry. I planned to put about 25 to 40 dollars per project and build them in 1 or 2 days respectively to the difficulty (it seems like a fair price for how simple it can be for me). On the other hand, they offer those jobs but MUCH cheaper, or is that the standard? I plan to raise the price once I exceed about 5-10 sales. Thank you very much for reading me guys, and happy coding. submitted by /u/StatementSuitable830 [link] [comments]
- Is it normal that you struggle with Excel as a newbie? I fear I might not be better at itStupid question, I know, and it's been more than a week since I started learning Excel. I'm an IT-Business Analytics student from PH, and I'm really trying to get better at using Solver. I'm a bit familiar with the controls now, but I still lack in terms of analytical skills, such as what formula should I use for that cell or what constraints, as every spreadsheet problem is situational. Yeah I think I struggle a lot with situational problem solving. Idk, I feel like I'm improving but very slowly with negative thoughts in my head like self-doubt and what if my skills and knowledge actually don't improve over time? What if I lack skills after graduation? Am I gonna be a degree holder yet unemployed? Same what-ifs for coding, math, and other technical skills that require strong analytical skills and critical thinking. I also want to study BSN as my 2nd degree, so I could work as a Nursing Informaticist (and interested in healthcare especially saving lives) from both Nursing and Tech degrees. Same what-ifs too: What if I accidentally kill a patient? I'm just a newbie, but I'm already anxious if I'm able to acquire the skills, especially since the job market is very bad now. I wanna improve, earn, and get out of the country, but it feels impossible at the moment. Sorry if I'm dramatic, but I'm a bit anxious huhu. Finding some comfort here at least. submitted by /u/CurrentEstimate3308 [link] [comments]
- Resources that help you get better at laying out Excel spreadsheets?When I have the opportunity to see someone else's sheets, sometimes I feel like "oh that's a better way to lay out the inputs v data/calcs. I'm comfortable with the data and formulas used, but I always feel like I could use improvement in laying out my information for others to understand. My brain thinks about in one sequence but that may not be the same for others. Are there any resources you've used that helped you get better at synthesizing the building of your workbooks? submitted by /u/brooklyn735 [link] [comments]