Stop manual work in Excel with this blueprint.

[@kdfarmery]

As a member of the Excel Off the Grid Academy for nearly a year, I can confidently say it has transformed my approach to problem-solving in Excel, significantly improving both my efficiency and focus. The academy provides a wealth of knowledge through comprehensive training courses covering a wide range of topics, regular videos introducing innovative techniques, single-topic webinars presented by experts from around the globe, and invaluable direct support from Mark. I wholeheartedly recommend joining the academy-it has not only enhanced my skills but also afforded me more quality time with my family and friends. P.S. I am not on commission!!

[@ExcelOffTheGrid]

Wow! Thanks Keith. That is so kind of you to say. It just makes me happy to know what it’s made such a big difference. 😁

[@IvanCortinas_ES]

Excellent presentation. The important thing is not just to start working on a project, but how you are going to approach the work. There are many questions to ask yourself at the beginning. The initial management work is essential. Thanks, Mark.

[@ExcelOffTheGrid]

Exactly - understanding the process of how inputs pass through tools into the output is the key to achieving the benefits.

[@Star-zk8ne]

One of the biggest issues not talked about is the move from unstructured data types to structured. So these are pdfs or images with tables that are used as templates or manually filled in (yes handwritten) and then either imaged (because of ipads and phones being the best mobile platform and easiest for field work or data entry). This is a big issue that most offices that have legacy data and therefore a massive history of data to mine informatively. The blue print is great of course but getting around this is issue is tough and easier to ignore, as it is in most cases.

[@ExcelOffTheGrid]

Yes - if you are dealing with images / hand writing then you will need some specific software to deal with that before you even think about Excel. That’s common in Accounts Payable where supplier invoices are scanned and the key details extracted using other software. It is that database of that software, or the reports from the software that becomes the Input.

[@sledgehammer-productions]

Thank you for validating the path that I'm already taking. I have a template somewhere that also includes the parameters for sending an email - I just forget to use it when starting a new "model", and then I find out my prototype/proof of concept is going to be used (or is used without me really knowing about it) and then I have to add all that stuff. I am now even contemplating on having the flexible input for power queries for all the flavours (csv, xlsx/m with sheet/defined name, xlsx/m with table, databases), to the point of having the raw data in PQ.

[@ExcelOffTheGrid]

Sounds like you’re in the right path. Good work 👍

[@RonDavidowicz]

Great video Mark. I try and follow these steps myself, but it’s great to see it laid out like your blueprint.

[@ExcelOffTheGrid]

Good stuff - sounds like you’re in the right path. 👍

[@liquidapathy82]

Really great video, I was paying so much attention. I need to implement this into a more consistent basis. Already do it, but not completely.

[@ExcelOffTheGrid]

Be consistent and you will reap the rewards. 👍

[@grahamc5531]

Been using this pattern for years now with just slight tweaks based on requirements....the only thing I would change is the file naming format - keep the file date at the start of the filename and use yyyy-MM-dd format, then power query can extract the first 10 characters and convert to a proper date in one step

[@ExcelOffTheGrid]

I always use the last characters for the date. It’s a useful way to get a date into the dataset. I used to be quite dogmatic about it, but it became a blocker to some applying the blueprint. Provided it’s possible for PQ to easily find the file, that is the main thing. So now I view it as good practice rather than a requirement.

[@ObedieNicksonTheraiePhakati]

Thank you Mark. I really love your eloquent explanation. However I would be good to charge or tailor the training to specific member. Do you do that? I am really willing to see that and happen to me. Let me know if individualized framework/blueprint can be taught to me. We can agree the cost coz the most important is the knowledge. Kindly let me know this arrangement

[@ExcelOffTheGrid]

We used to do that in the past, but it never worked that well. The level of knowledge required about your specific situation to customize a program ends up becoming more like consulting, and leaves too many skills gaps for the member. It works better were members have sufficient base knowledge to solve a variety of scenarios (as covered in our courses). Then through the Q&A session and Support Forum we can provide advice to apply the techniques in to specific scenarios.

[@kapil4757]

This is all good. It can be even better if you were to cover a detailed example covering all the steps you mentioned as part of your unique framework. Would you be kind enough to do that soon? Thanks in advance

[@ExcelOffTheGrid]

Take a look at this: kzbin.info/www/bejne/iKSWg6Vse9asp7c

[@willmartin4234]

Do you have a video to show how to turn a pdf remittance into an excel import for certain software? Also, how to set this up to be automated by a few clicks?

[@willmartin4234]

Recently subbed and you have been super helpful btw!

[@ExcelOffTheGrid]

We don’t have any public videos about importing PDF into Excel. We have some in our membership program. How easy / hard is very dependent on the specific PDF. So would definitely be a case-by-case basis.

[@DrRosik]

I have to admit, I was a bit confused at the beginning of the video when you listed all the manual steps that one can encounter during a "normal" Excel task. I think that every single step you described is exactly the steps I do! And I mean it literally! Have you looked at my Excel sheets, or am I just sooo boringly average? :) Anyway, great video. Although, I was aware of most of the tips in this video I didn't use them, now I will. You earned a sub from me now as well! Side note...can you please stop pronouncing H as "Haitch", it's driving me crazy! 🤪

[@ExcelOffTheGrid]

Ha ha ha - I don’t need to see your spreadsheets, it’s the natural end point from incrementally learning Excel. It’s time to reset and re-learn using the tools the right way. In terms of (h)aitch - I’ve clearly been saying it wrong for the past 40+ years. Interestingly everybody I know who grew up in my local area says it wrong too. So maybe I was never taught correctly in the first place. But it’s now in my radar, so there is a chance.

[@DrRosik]

@@ExcelOffTheGrid kzbin.info/www/bejne/mWTcYXZ6Z6iiedU 😂

[@ExcelOffTheGrid]

🤣 love it. Don’t look too closely at my website… there will definitely be some gramer errors there. (Typo intended 😀)

[@rodneyplunkett6688]

I have run into issues with too many new rows being added to a table and it is not automatically expanded to cover that new data. Is there a fix for this?

[@ExcelOffTheGrid]

I’ve not experienced this. It sounds like a bug to me. You should definitely use the Feedback section of Excel to report the issue - then there is a chance Microsoft will fix it.

[@warrenanderson412]

Power Query & Automate anyone😊?

[@ExcelOffTheGrid]

Power Automate Online doesn’t currently support refresh of Power Query, even with Office Scripts, it’s not supported. Hopefully this will come as it will open up a lot of doors. Power Automate Desktop can use the UI and RPA to refresh Power Query, but it’s not as easy as VBA, and it’s much slower than VBA. PQ and PA is definitely the future. I just hope Microsoft continue to develop this area, as it’s so close to giving exactly what we need.

[@rwfrench66GenX]

This sounds great but how do you validate the cleaning process? Where I work I deal with external feeds to update pricing in Oracle. Sometimes the feed gets truncated so not all items were properly updated compared to our parent company who gets a feed from the same third party. Other reports I have access too are read only reports in OBIEE and the person who wrote the parameters for the report used fixed variables and it didn’t populate enough cells on the report. Then there are conversion errors when IT receives a file from a Windows based server system to upload into our Unix based system and information gets shifted over a column. Then reports that come from manual data entry might have duplicate records, have missing records, have information in wrong columns or wrong dates. Reports from data warehouses only have information from the last refresh, same for API’s. If the data isn’t clean first no matter how automated your processing and analysis is you’ll be chasing ghosts in the data.

[@ExcelOffTheGrid]

Power Query can perform some level of data cleaning, but everything has to be available in the various inputs. If it is, use the Visualize step to validate the data cleaning. However it sounds like your issue is in your source systems. If the source doesn’t provide the data you need then Excel can’t help with that. Your problem exists before you even get to Excel.

[@WaqasAli-dq9wg]

Luftwaffe 😁