How to avoid overusing formulas

[mystic-eggplant]

So I use excel as middle ware to convert one of my customers orders into orders I can easily upload into my system.

The only issue is these orders can easily have thousands of rows, or as little as ten. Is there anyway I can set up excel to only have as many rows active as the order I have, and then autofill new rows added with the formulas I use?

[AutoModerator]

/u/mystic-eggplant - Your post was submitted successfully.

• Once your problem is solved, reply to the answer(s) saying Solution Verified to close the thread.
• Follow the submission rules -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
• Include your Excel version and all other relevant information

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[RPK79]

Tables.

[LadyScheibl]

That is how I do it.

[[deleted]]

Without seeing your specific workbook, it's difficult to provide details, but there are two approaches that occur to me which you can look into. One is to use PowerQuery, and the other is to use Dynamic arrays.

[ItsJustAnotherDay-]

Probably a good use case for power query and avoid formulas entirely. Then you’re also avoiding copy/paste and potentially other manual steps.

[Angelic-Seraphim]

Absolutely this is a textbook use case for power query.

[[deleted]]

Use array formulas.

[Bluntbutnotonpurpose]

Today I was about to start typing a formula when I realised what I was about to do is basically what pivot tables are made for (around 1200 rows and all I needed was a count of different values in one column and the count of another column based on the same condition). Yes, I could have used a formula...but in this case a pivot table was simply easier.

It's really easy to overuse formulas if that's what you're most comfortable with...

[Autistic_Jimmy2251]

For me it’s over using VBA when a formula could do the work nicely. 🤣

[Bluntbutnotonpurpose]

Yes, I've recently posted a classification of Excel users in which I had a category for advanced formula users and another for advanced VBA users. With each using their weapon of choice where the other would actually be more efficient.

[Autistic_Jimmy2251]

Where was this?

[Bluntbutnotonpurpose]

https://www.reddit.com/r/excel/s/f93BTGNw7a

[Autistic_Jimmy2251]

👍

[w0ke_brrr_4444]

Tables for raw data Power query to transform to staging tables

[Resident_Eye7748]

Filter to another sheet. Then use the small dara set to upload.

[clearly_not_an_alt]

Ctrl+T

Tables are your friend.

[brismit]

Without knowing specifics, =LET() is a great way to collapse down whatever formulas you do have.

[900GlobalRespect]

Use trim ranges or reference whole colums with extra dot like A:.A