Excel Unplugged
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. This post is obviously not regular, since it’s not Tuesday (now I feel like Julianne Moore in Laws of Attraction) J but it’s a spinoff of a post I wrote in ...

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

    Power BI publisher for Excel (The Times They Are A-Changin)
  • April 1, 2016

    The title of this post is stolen from the legendary Bob Dylan song, but rightfully so since it’s one of the biggest game changers in the Power BI game (and with their constant updating and adding of brilliant abilities into the product that is saying a lot, even made ...

    Custom Power Map to show Mick Jagger’s stage presence during Gimme Shelter
  • March 1, 2016

    This article will show you how you can use that nobody’s child called Power Map or 3D Map to solve a problem many of us wish to run into one day. We get tickets for the Rolling Stones and are unable to decide where to set up camp, to be closest to Mick Jagger. That ...

    Sum up the diagonal of your data anywhere on a Sheet
  • January 26, 2016

    The title of this post is actually a question I got on one of the last trainings I gave in 2015. It struck me as a great little sample of using array formulas, and simply a demonstration of how array formulas can be a great time saver. So let’s start with a sample data ...

    Creating a Dynamic Calendar for Your Data Model
  • November 24, 2015

    This article will take care of the most fundamental need when it comes to Power Pivot or even Power BI and Time Intelligence. Having a Date Table (Calendar Table) is vital to any such endeavors. Mostly this Calendar tables were created in Excel using a Fill/Series command ...