Related Posts
Anyone have recs for a Nephrologist/Kidney doc?
I’m always pissed 😈
Mentor
Bait and switch!
Additional Posts in Excel Genius
Anyone know what these badges are?
Coach
Would anyone be willing to teach me vlookup?
Still don't have xlookup. #fomo
New to Fishbowl?
Download the Fishbowl app to
unlock all discussions on Fishbowl.
unlock all discussions on Fishbowl.
Importing data, cleaning data, and generating reports can be automated. It can be tricky sometimes, though, to make sure that you don't accidentally create more work for yourself by trying to automate it.
Think of PowerQuery as an excellent way of automating data in columns. Not just columns, this also includes tables and spreadsheets. But generally you will applying a set of rules and actions for that column. How you get that column is also decided on how you approach the data.
You take all the data in that column and you can apply any mathematics equations as well as conditional functions. The rules you apply are as consistent and as rigid as your code. Good code counters any potential errors as well as all possibilities.
If you're doing the same thing over and over, you can probably easily automate it. Similarly you can avoid copying and pasting huge datasets and letting PQ do the heavy lifting for you.
It's hard to know what you need without context. Basically you can pretty much take any data in an excel file or files and combine them into any output you want. It takes a while to get into it but the investment taken by spending a few days learning PQ is offset by the hours saved downstream.
What can be automated: absolutely anything as long as the data is made available and accessible to excel.
A report is basically data from one or different sources transformed and combined to be displayed in a spreadsheet. Excel can do that automatically.
Some data types can be a bit tricky, such as text, but then there are enough functions in excel to handle text (even regular expressions if need be)
To do that there might be needed to use some. VBA (but the less the better) and power Query as @arup1 rightly mentioned.
PM if you want more personal advice
I would think if a process is in excel it is pretty much already manual. Unless it is using VBA or macro.