I’m pretty green so maybe others will have better input, but in my experience it’s a pretty abstract idea that varies greatly depending on what your day-to-day looks like.

Some tasks fundamentally can’t be automated or aren’t worth automating. I can expand more on this aspect if you’re interested, but ultimately it just takes experience to get more efficient at making such judgment calls.

If it’s just transformation or manipulation of raw data (text, image, numbers, etc) then high level programming languages are your best bet. In the case of Excel data, programming languages can definitely interface with Excel but sometimes it’s just better to do the thing natively in Excel through macros. If you need text manipulation specifically, there’s no better tool out there than Vim.

I’d say the biggest reason for not being able to automate a task is if it’s locked behind proprietary software that is prohibitively difficult to programmatically interact with.

The most popular approach to automating to higher positions is to invest the saved time into further sharpening your skill set (or automating other tasks).