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

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

    Last word in a string with an Array formula
  • November 11, 2014

    As promised in the Pragmatism vs. Purity in Excel article from last week, this week’s article will explain the following array formula { =MID(A1,MAX((--(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)=" "))*ROW(INDIRECT("1:"&LEN(A1))))+1,50)} Throughout this article, ...

    Pragmatism vs. purity in Excel
  • November 4, 2014

    One of the greatest things about Excel is that with its endless array of tools and commands it almost always offers multiple ways of achieving a certain goal. Particularly lately with Excel 2013 with the new features that it brought not even mentioning the Add-ins it offers. ...

    Last day of previous month formula in Excel
  • October 28, 2014

    On many occasions we would like to somehow get the dates belonging to the last day of current, previous or any other month, the first day of this month or any day of the current month. This post will show you a nice trick how to do that. Whereas for the last dates of the ...