[TSG] Automating Excel Without Coding? That Can't Be True!
Excel Education for the REAL WORLD!
Are you tired of spending hours manually cleaning and formatting data in Excel?
Do you wish there was a faster and easier way to extract and transform data from various sources?
Let’s talk about a feature in Excel that even if you only knew the basics would lead to extreme reverence from your co-workers. That’s because this Excel feature is so powerful in today’s data-centric world, yet only a pinprick of Excel users even know it exists! I personally have yet to meet a co-worker who has even heard of it.
This Excel feature is none other than Power Query.
Power Query lets you automate entire data flows with the click of a (refresh) button without having to write a single line of code (in many cases).
You might be thinking, what is a data flow?
Picture this example deliverable process:
- You need to take 10 separate reports and merge them into a single Excel file
- You then need to change column names, delete blank rows, format text to numbers, and about 10 other "data-cleansing" type activities
- Finally, after many hours of work you are finished (if you remembered to do all the steps properly).
- Now you can breathe a sigh of relief but this deliverable needs to be updated again next week and you have to start the whole process over.
Scenarios like this are very common in the workplace. Many people have heard of the miracles VBA macros can do, but the whole learning-to-code aspect scares away many Excel users from even attempting such a solution.
Since Power Query doesn't require you to write any code by yourself, it is a much more approachable solution to automate tasks in Excel. Especially for those of us who have had no experience coding.
Most Excel users don't even know a tool like Power Query exists. The Microsoft developers somewhat hid it in fear of "scaring" the average Excel user.
They even removed the Power Query name with the concern that the name seemed too advanced/unapproachable. They decided to rename the Power Query group of buttons to Get & Transform Data.
Here's where Power Query resides inside your Excel Ribbon:
The below image shows what the Power Query Editor looks like when opened. It can be accessed within the Get Data menu button.
This editor allows you to connect Excel tables to it and use the buttons on the editor's Ribbon tabs to create automation steps to apply to the data.
While I can't possibly teach you everything there is to know about Power Query in an email, let's look at a few reasons why you should consider learning it.
What's great about Power Query is it follows a click-to-code interface, where you can click buttons in its Ribbon tab and it writes the code for you. Each one of these button clicks creates a "Step" in your automation build.
To modify the automation, you can simply click a Step you've already created and modify it through dialog boxes (again no coding required).
As you get more comfortable with Power Query, you can begin to tackle more advanced features like custom functions or bulk editing by diving into the backend code through the Advanced Editor.
The coding language (called M) is actually pretty easy to pick up if you take a little time to understand it.
One of the biggest advantages of Power Query is that it can automate many of the tedious and time-consuming tasks associated with data cleaning.
For example, you can use Power Query to remove duplicate rows, split values into multiple columns, and filter out irrelevant data with just a few clicks. This can save you hours of manual work and ensure that your data is accurate and consistent.
The editor has all these cleaning actions set up in the Ribbon, so you can quickly throw together powerful data-cleaning combinations with just your mouse.
Once you're done building your automation, going forward all you'll have to do is click a refresh button to prep your data via the right-click menu.
Power Query also makes it easy to connect to a wide range of data sources, including databases, web pages, Excel files, and more. Here's a more extensive list of data sources:
- Table/Named Range/Range Address
- Text/CSV File
- Website
- ODBC/OLEDB
- SQL Server Database
- Access Database
- SharePoint List/Table
- All Files In Folder
- Microsoft Exchange (Outlook)
You can use the intuitive interface to set up connections and import data from these sources directly into Excel. This can save you time and help you make more informed decisions by combining data from multiple sources.
In today's data-driven world, employers are increasingly looking for candidates with strong data analysis and management skills. By adding Power Query to your skillset, you can enhance your resume and stand out from other candidates.
Power Query is also used heavily to prepare the data that feeds into Power BI dashboards.
Power Query on a resume is going to be much more impressive than "Proficient with Vlookups".
If you're looking for a faster, easier, and more powerful way to work with data in Excel with little to no coding, then Power Query is definitely worth exploring. To help you get started, I've put together a free quick start guide to show you the basics.
Trust me, it's easier than you think!
If you are looking for formal training, here are a few courses on Power Query I highly recommend enrolling in:
- Power Query Course - Mynda Treacy (Excel MVP)
- My Excel Online Academy* - John Michaloudas (Excel MVP)
*Johh is currently doing a $1 promo for all his courses
If books are more your thing, here's one of my favorites:
I've written A LOT about Excel over the years. Here are a few articles that might teach you something new today!
Over the years I've solved issues throughout my career by building my own Excel Add-in solutions.
With the launch of my website, I was able to take these tools I had developed for myself over the years and use them to fund operating TheSpreadsheetGuru website.
I tend to never give out discounts except during a new product launch, so this is definitely something that will not be commonplace going forward. As a special welcome gift to you, I'd like to gift you with a 30% off discount code that you can apply to any purchase in my product store (excludes T-shirts and other physical merchandise)
(excludes physical merchandise)
Here's what folks are purchasing the most of to help them take their Excel work to the next level!
Create and save icons to your Excel Ribbon plus many more tools to help auditors, accountants, & finance folks. More >>
Store your favorite custom number formats to buttons in your Excel Ribbon. More >>
Each of my Excel add-ins or spreadsheet templates comes with a 30-day money-back guarantee so please don't feel hesitant in using this gift to make a purchase. Most of my add-in products also have a 2-week trial period that you can utilize to make sure the tool is right for you before making your purchase.
This offer is only valid for the first 7 days after you've subscribed to this newsletter. After that time, you will be locked from the coupon code.
You can check out even more of my product offerings by visiting my online store.
Today's random product recommendation is actually something my wife got me for Christmas one year after accidentally slicing myself with a box cutter.
The Slice box cutter is pretty neat in that it uses a ceramic blade to cut versus the age-old metal blades. They claim a single ceramic blade can last as long as 20 metal blades. I'm not sure how true that is, but I've had the original blade on mine for well over 3 years and it cuts through boxes just fine.
It also auto-retracts its blade, so you never have to worry about safety concerns with an open blade lying around.
If you get a lot of packages mailed to you (like me) or have safety concerns with kids (like me), this will be a perfect purchase for you.
More safe than a regular box cutter and the ceramic blade lasts as long as 20 metal blades.
If this product interests you or you have some shopping to do on Amazon, make sure to use my "Check It Out" button link above. If you buy anything within 24 hours after clicking this link, a 1-2% portion of your sale will go directly to TheSpreadsheetGuru (at no cost to you).
This is a fantastic way for you to support the cost of running this newsletter without having to spend any extra money. I appreciate your willingness to help support this newsletter!
You are more than welcome to invite any one of your friends or colleagues who might also benefit from this newsletter. Simply send them the below link and they can get signed up for free as you did.
Newsletter Link: TheSpreadsheetGuru.com/newsletters
Hopefully, this newsletter brought you some value today. I absolutely love sharing the tips I've come across or developed over the years and also receiving feedback from everyone.
If you have any thoughts on the topics covered in this email, feel free to respond back.
Tomorrow, we're going to be discussing the Quick Access Toolbar and how you might be underutilizing it. Talk to you then!
Chris
Founder, TheSpreadsheetGuru.com
PO Box 150
Lewis Center, OH 43035
United States of America
You received this email because you
signed up on our website or
purchased something from us.