How would you like to win a ticket to the Global Exist Summit 2023?
Prize
The winner will receive a 3-day conference pass to the Global Excel Summit (worth $195). The conference runs on 6, 7 & 8 February 2023. All the sessions are recorded, so you will still receive access to the recordings even if you can’t attend live.
What’s included:
- 30+ Main Stage sessions
- Access to Company Showroom
- 3-Day Lounge Access
- Microsoft Office Specialist & Microsoft Office Expert Exam Prep
- Lifetime access to all Event Recordings
- 30+ CPD Hours & CPD Certificate of Attendance
Competition is now closed
To enter, participants just needed to answer the following question:
What do you think is the most significant change to happen in Excel in the last ten years?
The winner is…… Zach Prins. Who said “Definitely Power Query!”
Thank You to everybody who took part. Congratulations to Zach!
About the author
Hey, I’m Mark, and I run Excel Off The Grid.
My parents tell me that at the age of 7 I declared I was going to become a qualified accountant. I was either psychic or had no imagination, as that is exactly what happened. However, it wasn't until I was 35 that my journey really began.
In 2015, I started a new job, for which I was regularly working after 10pm. As a result, I rarely saw my children during the week. So, I started searching for the secrets to automating Excel. I discovered that by building a small number of simple tools, I could combine them together in different ways to automate nearly all my regular tasks. This meant I could work less hours (and I got pay raises!). Today, I teach these techniques to other professionals in our training program so they too can spend less time at work (and more time with their children and doing the things they love).
Do you need help adapting this post to your needs?
I'm guessing the examples in this post don't exactly match your situation. We all use Excel differently, so it's impossible to write a post that will meet everybody's needs. By taking the time to understand the techniques and principles in this post (and elsewhere on this site), you should be able to adapt it to your needs.
But, if you're still struggling you should:
- Read other blogs, or watch YouTube videos on the same topic. You will benefit much more by discovering your own solutions.
- Ask the 'Excel Ninja' in your office. It's amazing what things other people know.
- Ask a question in a forum like Mr Excel, or the Microsoft Answers Community. Remember, the people on these forums are generally giving their time for free. So take care to craft your question, make sure it's clear and concise. List all the things you've tried, and provide screenshots, code segments and example workbooks.
- Use Excel Rescue, who are my consultancy partner. They help by providing solutions to smaller Excel problems.
What next?
Don't go yet, there is plenty more to learn on Excel Off The Grid. Check out the latest posts:
Power Query (PQ).
If they say what is true, you spend 40-80% of your time cleaning data. That’s a lot of time.
PQ is easier to learn than you think. (It took me a long time to bite the bullet).
It doesn’t have to stay in the hands of IT.
You can hide much of it from an end user.
You can stick with the ‘basics’ and repeat.
You can copy/paste queries into new workbooks.
From the cleaned data you can easily set up tables, dashboards, reports etc.
Oh, if you have to update weekly, monthly, yearly reports. Spending a little time up front will save you so much time later on.
Generally you’ll only get so many types of data, from so many sources, sure ‘clients’ might be different, but the data isn’t.
Using mapping/translation tables and PQ is a game change.
Already on the mailing list 🙂
Power Query
I think that I agree with Andrew, but to expand upon that
Power Query (PQ), Power Pivot (PV) has changed the way we use computers and databases
With Power Query and Power Pivot, we no longer need to use Access and/or small databases.
They allow us to import tons of data from multiple diverse data inputs and append/merge the data in many different ways, and make relationships possible to provide the data without as much work
It allows for normalizing of data as well, with minimal to no hardships.
PQ allows us to automate so much and then provide the data in pivot tables so that so many worksheets are not necessary, and a data heavy database is needed
If the data is the same from the different data points, updating the data using these too products has become effortless
Excel is growing more and more powerful by adding number of features from time to time. Among the additions are power query, power pivot, power map and other powerful add ins online and offline all aimed at increasing productivity. There is also increase in number of functions based on the needs of the user. VBA seems to be increasing in its popularity. Microsoft Excel is available online and offline based on the needs of the user community. Excel is advancing faster in Microsoft 365 with more functions and other features.
And now Artificial Intelligence is expected to boost its powerful grip on the market, which means excel will remain one of the most important spreadsheet data analysis tools for the years to come. Introduction of Artificial Intelligence in all products was assured by Satya Nadella, Microsoft Office, CEO in an interview recently. ChatGPT from OpenAI is being tested on Microsoft Excel and looks very good, especially, in writing VBA code. Excel will remain in demand among the user community in the years ahead.
Power Query #1
Tables #2 (close second)
The most revolutionizing change in Excel according to me is “Power Query and Power BI”
Power query and power pivot
Power Query by far is the sexiest tool upgraded in Excel, no need to learn VBA, it makes your life so simple..
XLOOKUP
The ability to handle and mine large sets of dat with Power Query
Power query
Power Query, Power Pivot, Dynamic functions and formulas made a significant change.
Dynamic Formulas such as FILTER have added so much functionality to Excel
The most significant and impactful change has been – Power Query and Dynamic Arrays (especially Functional Programming using LAMBDA).
In terms of Excel coding/features, then I say Power Query as well. I abandoned Excel in favor of Access a decade or so ago, and when a new role brought me back to Excel for heavy lifting, I was pleasantly surprised to find PQ.
For Excel users, I would say YouTube and all the tutorials etc. from generous folks like Mark Proctor! I would have been hard pressed to leap ahead to where I am in automations without them.
Hey, you know what I think is the coolest change to happen in Excel in the last decade?
The integration of the Power Features from Microsoft. Can you believe it? Power Query, Power Pivot and the best part, the live connection with the data model in Power BI.
Imagine having one single data source that you can use in so many different places, it’s amazing. O wait, it’s there already 😉
It’s so powerful and super easy to use, and it’s available everywhere, it’s just fantastic!
Rick
Dynamic Array functions.
Power Query is great but this was introduced 2010, however, for dynamic Array functions this was introduced last 2018
Power Query by far!!!
Power Query must be the most significant development of the last ten years. Power Pivot might actually be the most powerful but Power Query is so easy to use and so useful that PQ has to be no. 1.
Dynamic arrays
Get Data Feature
Most significant change to Excel in past 10 yrs: Integration of Power Pivot and Power Query. This has radically increased the availability of data, improving the timeliness of the data, and increased the ease of data scrubbing.
Power Query, Power Pivot and Dynamic Arrays have been the biggest transformations Excel achieved over the past years. However, Power Query seem to be the single biggest transformation compared to all other good introductions made the Excel developers.
Best,
Mohamed
A lot of people are saying power query and other power BI features because their impact is already known in terms of productivity and time saved
This is true but excel 2021 has a number of features which have made a sea change of difference in how we use excel
Dynamic arrays
spilling formulas
Lamda and assiciated functions LET,etc
data types
better co authoring
excel online for free as an alternative to google sheets
better integration with power query and other power BI features
even analyze data as an elementary form of AI integrated with excel
(I’m awaiting the results of microsofts partnership with open AI in the coming decade!)
Power Query
Excel Dynamic Array
Power Query, Power Pivot and Dynamic Array
ETL and Data modeling by far is the best things that happened to MS Excel. Both are game-changer tools and are easy to use at the same time.
Definitely data modeling capabilities and the continuing development/integration of Power Pivot for Excel, Power Query, and Power BI along with additional DAX functions and more native and Microsoft certified 3rd party custom visuals
Dynamic Array formulas
Power Query was introduced in version 2010 which technically speaking is more than 10 years ago – yet, most people have learned (about) it in the last decade – me too.
OTOH, Dynamic Arrays come to mind: much more recent (the oldest YT-video to my knowledge is one by Mr.Excel in September 2018).
And I love both Power Query and Dynamic Arrays, very much – I use ‘m on a daily basis.
My choice for most important change to Excel in a decade: Power Query.
Every one I teach about this tool becomes enthusiastic about it and it greatly empowers its users to a level that before was unimaginable.
Using AI in Excel through Analyse Data, and Geographic Visualization
Dynamic Arrays
No Doubt…. Power Query & Power BI. Like most Analysts, I spent forever cleaning my data manually. Even created some VBA macros to help the process. However, it was still an ordeal. Then PQ arrived. It seemed so complex, and I was intimitaded, but I taught myself and atteded some webinars, and discovered it was more awesome than I could believe. Power BI, wow! I already knew Tableau, so the transition was relatively easy. But before that creating a dashboard on a whim to meet the requirements of an Executive is not an easy task. Power BI, allows me to change on a dime, and showcase the draft, and set the meetings to make changes as needed. I would be los without these tools. And if I could add a 3rd, it would be XLOOKUP. It has allowed me to work easier, but TEACH so many people what they could not understand with VLOOKUP, and could never comprehend with INDEX/MATCH.
The best changes are those that have been integrated by Microsoft as it seems they listened to what the users needed.
All the new builtin functions are a result of user feedback and ideas; and … Microsoft listened and there will be more to come
Power Query and Dax
as others have mentioned, Power Query is not technically eligible (due to date of introduction) – and I’m not sure I’d include it in any case. Yes, it is very useful, but most significant for Excel? Not sure about that. Most ‘power users’ for Power Query eventually move to Power Bi.
For excel, I think that in formulas it’s the suite of dynamic arrays – but for me personally, it’s specifically XLOOKUP – replacing and improving on a measure that almost any Excel data person relied on at one time or another. I started with LOOKUP, then VLOOKUP, and then on to INDEX/MATCH – but XLOOKUP handles 98% of the various work-around scenarios I had to engineer for each of those.
I believe the most significant advancement that Excel has made in the last 10 years is adding the ability to Get Data from other sources and applications. Prior to this addition, Excel was a stand-alone application that required programming to link it with other applications. Now Excel can interact with thousands of other applications. It’s all about communication!
Lambda function.
It allows users to create custom formulas. This makes it very easy to write and understand formulas and save it for future use. It is a kind of introducing programming in excel despite vba.
Power Query.
Hands down.
Second place: Tables
Game changer in Excel for me was Power Query / Get and Transform (however you choose to call it). I started using it as an add-in and have never looked back. I look forward to extending it these days through Power BI.
Excel has had a numerous amount of positive and effective changes over the past ten years. Many of which probably existed, it just took me along time to learn them. However, the most significant change for me is Power Query. It has broadened my horizons to accomplishing tests that I would have never even undertaken just a few years ago. I just wish I had more time to learn more of the intricacies within Power Query so that I could be even more productive in the workplace.
For me its power query
If I had to point one features and only one it would be Power query.
But, to be more exhaustive the combination of power query + power pivot leads to more powerful tool (aka Modern Excel).
And these 2 advanced features are more and more completed by the dynamic arrays formulas, which leads to better dynamic reporting!
ABSOLUTELY Power Query. This tool has the ability to save hundreds to thousands of hours of data preparation work. With it, you can easily extract, transform, and load data with minimal work.
The feature where you can create your ‘recipe’ for transforming your data that is dropped in a folder, then adding new files by dropping them in the folder, and just clicking ‘refresh’ is a true game changer! I could have saved THOUSANDS of hours of data manipulation if I had this tool when I was doing production work with Excel.
Power Query, Dynamic Array Functions and the integration with Power BI.
Apart from retaining dominance in the market despite allegedly superior products and providing universal access through the use of the cloud, it has to be Power Query which sets it apart from its competitors.
Definitely Power Query!
Power Query and Pivot
Definitely power query and power pivot, both of them took my reports to the next level.
All of the above – and Office Scripts.
In the long run, Office Scripts could render VBA redundant.
Power Query and Power Pivot. So many options and ways to handle large data sets and save a ton of time by setting things up at the beginning of the process. Allows an intermediate Excel user like me still make a significant impact at work while I continue to learn additional facets of Excel.
it must be power pivot and power query that emerged and developed over the period.
Power query, Power Pivot & dynamic array..
It is definetly dynamic array formulas.
Power Query
For me, it would be Power BI.
Hello, for me Powerquery and dynamic arrays. Lamda function is great!!
Enjoy your posts, helpful
Power Query
Power Query is the easiest way to connect, extract, transform and load data from a wide range of sources
Hello!
As for me, Power Query was the door to the new era 🙂
Power Query and Power Pivot
Power Query and Pivot Tables
Dynamic arrays formulas have become the new angle of vision!
While the new power functions are a major upgrade they don’t help the larger population of users I believe the changes to simplify the pivot table creation made a beginner feel like an expert
Hello!
LAMBDA + LET = great opportunities have opened
For me, ETL processes with Power Query💪
Hello!
Magical Dynamic Arrays💫
Power Query & Power Pivot: The Two Towers
Hello!
Dynamic Arrays have changed the rules!
Power Query was the breakthrough