Excel Unplugged
Importing CSV Files with Changing Start Row
  • May 15, 2018

    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, ...

    The Amsterdam Excel Summit 2018
  • May 10, 2018

    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 – Game Changer
  • May 8, 2018

    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 ...

    Unlock Excel Conference – Australia
  • April 30, 2018

    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 ...

    Ultimate Vlookup Alternative? (Part 2)
  • March 27, 2018

    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 ...

    Ultimate Vlookup alternative? (Part 1)
  • March 20, 2018

    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 ...

    XL Shortcut
  • February 27, 2018

    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. ...

    Conditional Colouring of Excel Sheet Tabs with VBA
  • February 20, 2018

    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 ...

    Making Importing from Folder in Power Query (or Power BI) Bulletproof
  • February 13, 2018

    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. ...

    Custom Power BI Visuals by Zebra BI
  • February 6, 2018

    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 ...

    Dynamic Calendar with Power Query or Power BI – Take 2
  • January 30, 2018

    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, ...

    Modifying Power Query M code with VBA
  • January 23, 2018

    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, […]

    Simulating a “Tabular Form” Pivot Table Layout with Conditional Format
  • January 16, 2018

    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 ...

    Table.Join – The Power Tool of Power Query
  • January 6, 2018

    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 ...

    Welcome to Excel Template
  • June 6, 2017

    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 ...

    Get a random subset of data with Power Query
  • February 14, 2017

    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 ...

    Let’s go to Amsterdam
  • February 2, 2017

    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 ...

    Excel vs. Excel 365
  • November 29, 2016

    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 ...

    Search a Range for the closest value to a chosen number
  • November 8, 2016

    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 ...

    Dynamic Power Query
  • November 1, 2016

    The following post was written as the reply to a great question Dane asked in the Comments section of the Power Query’s Unpivot Function post I wrote back in August 2014. So the question goes like this: Hello – how do you account for new columns of data being added ...

    A great honor for Excel Unplugged
  • June 1, 2016

    Yesterday I got an Email form the guys at spreadsheeto.com notifying me that Excel Unplugged will be featured on their “69 World’s Best Excel Blogs” site (the number might have changed till now J). You can see the whole list of blogs here. I even got a cool ...

    Amsterdam Excel Summit 2016 report
  • May 31, 2016

    This post is a tribute to the event, that is one of a kind in Europe, and gives Excel professionals a rare opportunity to meet brilliant people, that have dedicated their lives to Excel and have a knowledge-base to prove it. Keep in mind that this kind of access is rarely ...

    USE POWER QUERY’S GET DATA FROM FOLDER TO GET DATA FROM MULTIPLE EXCEL FILES
  • May 28, 2016

    I recently posted on how to make a process of getting data from a folder bulletproof. You can read it here. Whereas that post was about getting data from multiple TXT files within one folder, this one will tell you how to  get data from folder full of Excel files. There is ...

    REMOVE, REPLACE OR SEPARATE BY LINE BREAKS (ALT + ENTER) INCLUDING VBA
  • May 24, 2016

    About two months ago, a team assembled to create an Excel AddIn called Xcessories. It will certainly bring Excel users one step closer to eternal happiness J by offering features that Excel lacks or those that are very hidden in Excel (I hope the very hidden joke isn’t ...