Excel Unplugged
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 and I recently followed up with a post on how to make the process bulletproof and you can read that here. It seems that lately Excel users can be separated ...

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

    Another formula to get quarters from dates
  • January 20, 2015

    A while back I wrote about multiple ways to get quarters from dates. Even “manual” quarters (starting with any months you like). You can read that post here. But now I wish to show you yet another way to get quarters from dates, this time by using the CHOOSE ...

    Holiday chart in Excel
  • December 28, 2014

    This is what happens when someone has too much time and Excel 🙂 Happy holidays and all the best in 2015 to all Excel lovers!

    Difference between two times formula in Excel
  • December 23, 2014

    In short, times in Excel are decimal numbers between 0 and 1 that show you the percentage of the day that has past. Now with that in mind let’s calculate two differences between Arrivals and Departures. Most Excel users will use a simple Arrival – Departure ...

    Case sensitive/insensitive Sort in Excel (bug or a feature)
  • December 16, 2014

    This post will talk about the way that Excel sorts data and the Case Sensitive sorting it offers. If we look at this data And sort it in ascending order (A-Z), we get It’s easy to see that there is no case sensitivity here, but there is a way to turn it on. You go […]

    Create a Date Series of all weekdays in Excel (omits Saturdays and Sundays)
  • December 9, 2014

      Let’s say we want to create a series of dates from 1/1/2013 to 12/31/2013 but with a catch. We want to leave out all dates that occur on weekends. So all dates that represent Saturdays and Sundays. We can do this in Excel quite easily by using the Fill/Series… ...

    Making your Min Max chart in Excel truly shine
  • December 2, 2014

    Just so we know where this is heading, this is the end result And here is the file with the end result, you can download it and play around with pressing F9. Here’s how we make it work. We start with the data. Now we need to add two columns that will only show the […]

    What Excel should learn from Word
  • November 25, 2014

    I’m only guessing, but I think that anybody who wants to use Excel at an above average level, will have to learn at least a few keyboard shortcuts to get there. The math goes that the more shortcuts you know, the more efficient you are in Excel. So what does Word offer ...

    Give Excel it’s voice (The Speak Cells Command)
  • November 18, 2014

    Very often, we will conduct a more or less pointless discusions with our Excel :), but not many people are aware that Excel in fact can talk. It can do so from Excel 2003. It’s a function that can be as entertaining as it is useful. The main purpose of the tool is to ...