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

    Get data from Folder in Power Query
  • February 10, 2015

    Following a wide demand, I also wrote a post about getting data from Excel files in a folder. You can read it here. It seems that lately Excel users can be separated into two groups. One group that gets the new Excel (2010 or 2013) but still uses the application as if ...

    My Excel Shortcut of the Year
  • February 3, 2015

    In my work as a Trainer and also as a Data Scientist (I love that term), Shortcuts in Excel are a vital part of doing things efficiently. This shortcut helps you when you select data. Let’s assume this is our data. A bunch of dates from A1 to Z12000. Working with this ...

    WORKDAY and WORKDAY.INTL functions in Excel
  • January 27, 2015

    I can still remember when I learned about the WORKDAY function. I found it very useful and used it regularly but since I also do a lot of project calculating in Excel, the rigidness of the weekends and holiday exceptions only were not working for me. On many occasions I ...