Last year, Microsoft announced the introduction of a new group of functions in Excel, known as dynamic array functions. One of these – the FILTER function – is possibly the best of the lot. The FILTER function will filter a list and return the results that meet the ...

Today is most definitely one of the most exciting days of this year for all Excel users out there. Today the endless discussions about whether people should use VLOOKUP or INDEX-MATCH are finally OVER! I will admit that I thought it should have been over back when Power ...

One of the most popular posts on Excel Unplugged is Formula to get the Start and End Date of the Week. For the last two years I’ve been a strong advocate for Power Query. Now whereas Power Query is brilliant and can do amazing things, I like to point out the little ...

In Slovenia (where I come from) there is a significant shortage of Excel and Power BI conferences. Basically, there are none (I’m planning to change that in November). Furthermore, there is a shortage of Excel, and Power BI themed conferences in Europe in general. ...

I wrote a blog post on the New Excel Data Types in May and stated that “the Sky is the limit” in the way the New Excel Data Types can affect the way we work with Excel… And one month later, here is a first Excel feature that is greatly enhanced by The New ...

In this post, we’re going to talk about importing CSV files with power query! This is a guest post written by John MacDougall, an Excel MVP from Canada. John runs a famous Excel blog called How To Excel where you can find tons of useful tips and tricks. In this post, ...

Jan Karel Pieterse and Tony de Jonker will give Excel enthusiasts reason for joy on the 7th and 8th of June 2018. This longest running Excel Conference in Europe is returning for the fifth time in a row! I have very fond memories of the Amsterdam Excel Summit and ...

New Data Types in Excel will undoubtedly be THE talking point of the near future Excel evolution. That being said, they are also the reality in Office 365 Fast Insiders. That’s where they were recently introduced by Microsoft. This post will first explain what the new ...

The Unlock Excel Conference  took place in April in three cities across Australia. I joined Excel MVP’s Liam Bastick (SumProduct LLC), Jon Peltier (Peltier Tech), Ken Puls (Excel Guru), Mynda Treacy (My Online Training Hub) and Tim Heng (SumProduct LLC), and we all ...

This is part 2 of the Ultimate Vlookup Alternative post and a continuance from last week’s post. We will be using the same Excel workbook as we did in part one. You can download it here (or just continue working in the one from last week). And before we begin, the ...

Almost a year ago, more precisely in May of 2017 a site called defeat excel invited 27 Excel Experts (including myself) to end the endless debate of Vlookup vs. Index-Match combination once and for all. Needless to say, the goal was not achieved and to be fair, I think no ...

Readers of Excel Unplugged know that I am a big believer in using keyboard shortcuts to be fast and more efficient in Excel. There are a lot of good resources for learning shortcuts but the Excel Add-in from David Hoppe called XL Shortcut is just what the doctor ordered. ...

This post will feature VBA code that will recolour the Worksheet tab based on a set of predefined conditions. Let’s imagine we have a Workbook where each Sheet represents one active project in a company. Cell A1 gives a short status of the project from the task ...

I’ve always been a big fan of the Import from Folder feature in Power Query. So much so, that I wrote my first post on the topic way back in February of 2015. It’s still one of most popular posts on Excel Unplugged and has been commented on 57 times and counting. ...

This weeks post will be a short testament to a development venture by a few of my countrymen. Like most of Excel fans, I’m also a big fan of Power BI and it’s reporting capabilities. But having said that, the built-in visuals have often left me wishing I was in ...

I guess every data professional that has ever taken on the challenge of creating data models either in Power Pivot or in Power BI knows the importance of the Calendar table. It gives you the ability to use Time Intelligence functions in DAX like DATESYTD, DATEADD, ...

The core of this blog post is a VBA code that will create a copy of a Power Query M code, modify it, create a new sheet and load the result of the modified Query to the new Sheet. This code was written and tested in Excel 2016. And if you want to follow along, […]

This is a follow up post on the final result of last week’s post Table.Join function in Power Query. So basically, we want to get from this To this Only by using Conditional Formatting. First, we need to make sure, that the column in which we will be simulating blank ...

The basic explanation of Table.Join function in Power Query is to use Merge Queries on two steps of one single query. This will be a step by step tutorial on how to use Power Query to get a list of duplicate files that could be interesting for System Administrators and the ...

It’s the newest “Quick Excel Guide” template in Excel and all I can say is BRAVO Microsoft. As the name implies, the target audience are Excel beginners, but it’s also quite a comprehensive short guide from A to Z or better said, it takes you from ...

The other day I made a Dashboard with a classic by time and relevant dimensions analysis. The data was pulled from Azure SQL DB, so Power Query was the method I used, to get the data into Excel. The dashboard build was very straightforward, until the customer desired to see ...

This Post is a Call To Action for all European (and UK 🙂 ) Excel Lovers… Whereas the reasons for going to Amsterdam are clear to anyone, I’d like to give Excel lovers out there the greatest reason of all, to visit Amsterdam on the 18th and the 19th of April ...

Whatever side of the Cloud vs. On Premise debate you take, it’s a fact that at this point there are two Excel 2016 versions out there, and I’m not talking about Professional, Home and so on… First there’s Excel 2016 with constant updates but mostly those ...

We start with a range of values in Excel (A1:G20). Now we are looking for a formula to get the closest value to the value we input in cell I1. First we’re looking for the closest value lower than the selected one. Closest value lower then selected Here’s the ...