Excel Unplugged
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

    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 February of 2015 called Get Data From Folder with Power Query. Almost all comments referring to the ...

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

    Format/Default Width…
  • June 30, 2015

    I should give a subtitle of »Oh, so that’s what it does« to this post. So the command we are talking about is HOME/Format/ Default Width… First I will tell you what it does and then I will show you a trick you can do with it. What It does What it basically does is ...

    New Charts in Excel 2016
  • May 26, 2015

    Up to Excel 2016, if you wanted to create advanced and special charts, your best option was Jon Peltier. He created great Excel charting add-ins. My favorite is the advanced edition of his charting utility. Now there are still many things and fine-tuning that you can only ...

    My three favorite things in Excel 2016
  • May 12, 2015

    First off, this post is written on basis of a public preview of Office 2016 which you can get here. The official version will contain a few more goodies like many new charts including Waterfall Chart… But as I said, this is what can be seen in the preview. Let’s ...

    Using Named Ranges in Excel
  • May 5, 2015

    There will be three different views of named ranges this post. First all different techniques of creating named ranges. After that a few techniques of using named ranges will be given and finally a few magical tips and tricks on operating with those names. Even if you think ...

    Amsterdam Excel Summit Report
  • April 21, 2015

    Whereas this post does not contain any excel shortcuts or commands, it does contain plenty of information I wish I had when I was trying to expand my Excel horizons. It also mentions quite a few EXCELebrities (that’s how I call them), that I had the honor to meet at ...

    Excel Shortcut of the year
  • April 14, 2015

    On February 3rd this year I published a post titled My Excel Shortcut of the Year. Now, only two months later, I’m passing the flattering title on to another shortcut. Ctrl + ‘ Ctrl + ‘ If you haven’t heard of this already, brace yourselfJ. In cell A1 ...

    Add data to your Chart (the easy way)
  • April 7, 2015

    (as simple as Copy and Paste) This is our sample data. First we select the data for all days but only for the first three months and press Alt + F1. That is the best Chart Shortcut you can know. Whereas in Excel 2003 both F11 (which creates the chart on its own sheet then ...

    test = zzyw = BBAb
  • March 31, 2015

    The title might make you think that I lost my mind, but this is actually one of the best tricks to show your colleagues and also a transparent demonstration that all versions of Excel before Excel 2013 were lacking a great deal in security. Ok so as the introduction ...

    Formula to get the start or end of the week date
  • March 17, 2015

    On many occasions and in many dashboards, time will be the main factor of your analysis and reports. As far as time goes, you can do a Yearly, Quarterly, Monthly, Weekly or daily reports. For each and every one of those except for the Weekly reports, the dates are known and ...

    Custom Views or Excel Tables (Sophie’s Choice in Excel)
  • March 10, 2015

    This is really a lose – lose situation in Excel and therefore a true Sophie’s Choice. Let’s elaborate on the two commands. Excel Table A table is the best way to make Excel dynamic. If you format your range as a table, all your charts that would take data ...

    Allow users to edit ranges in Excel
  • March 3, 2015

    One simple command that elevates Excel Sheet protection to a whole new level. Imagine wanting to protect a Worksheet in Excel but with a few requirements. all coworkers can view but they must not be allowed to edit the Worksheet four special coworkers (John, Paul, George and ...

    Learn a foreign language with Excel
  • February 24, 2015

    Surprisingly, this is not a post about the Translate feature in Excel. Rather we will take an in depth look into Cell Format and a great feature of Excel that allows you to change the names of days and months into any language you prefer, by using either the Cell Format or ...

    Scroll trouble in Excel (scrolling beyond your data)
  • February 17, 2015

    Sometimes in Excel, we are just an innocent bystander when enthusiasm gets the better of people and they start drifting around Excel inserting data in places where they weren’t supposed to. And even if they are so kind to remove any trace of their wayward journey, a ...