For many Excel users, the transition to Power Query can be difficult. Having spent years working with Excel’s functions, moving to Power Query seems like an alien environment. The good news is that many of Excel’s text functions have equivalent functions in Power Query. The purpose of this post is to provide a reference to … Read more
Our ability to manage inputs effectively is critical to building automated processes in Excel. A common reporting scenario is to get data from the latest file. This generally occurs where each file includes the historical data; therefore, we only need the latest file. In this post, we are exploring how to create an efficient solution … Read more
Recently, in our training academy, charting legend Jon Peltier demonstrated the best data structure required for working with Charts in Excel. Following this, one of our academy members asked this question: I am trying to use what Jon was teaching in the charting master class for stacked legends. Is there anyway to get that … Read more
Copying, pasting, and moving cells are simple everyday tasks for Excel users. But what if something already occupies the area we want to paste to? We must perform the move-move-move shuffle by moving something into a temporary location before placing everything into its final destination. Wouldn’t it be better if there were a simple way to swap ranges … Read more
I have never been taught how to use cell ranges in Excel; I doubt you have either. They are just too basic for anybody to teach, right? Select some cells, and there is a : (colon) symbol placed between the start and end cells. Maybe we could throw in a few $ (dollar signs) to fix the position … Read more
No matter where you look, there is a consistent message that it is bad to merge cells in Excel; instead, we should use Center Across Selection. Whenever my friend Richard Sumner posts about his love of merged cells on LinkedIn, he is always met with many replies stating that merging cells is terrible. But here … Read more
Power BI has a really nice way of cross-filtering visuals. When you click on a visual, it highlights the selected item in other charts while also retaining the total value in a lighter color. Can we cross filter visuals in Excel? No. But we can create something pretty close. Power BI Example: This post was … Read more
Power Query is a nightmare when we want to combine data with different column names, and even worse when those columns are not in the same order. Can we even combine that data? You bet we can! We’re looking at that today; using Power Query to rename columns in nested tables based on a list, … Read more
Creating multiple PDFs based on a list is a common use case in Excel. I’m asked about it regularly. A few weeks ago, I received this question about our Office Scripts course: Before I start the course on Office Scripts, I was wondering if it is possible to loop through a spreadsheet and with each … Read more
Over the last few months, I’ve been asked several times how to add a fiscal month, fiscal quarter, or fiscal year column in Power Query. So, I decided to note down the method; therefore, I don’t have to keep reminding myself how I did it last time. The good news is that it’s reasonably straightforward. … Read more