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

    Boost your Excel efficiency with one key – F5 (Go To and Go To Special)
  • October 21, 2014

    Very often you will find yourself in a position where you wish to either move efficiently through the workbook or you wish to select only cells containing certain kind of data (text, numbers, errors, objects, formulas…). The Go To… command which you get by either ...

    Creating a dynamic Dropdown List with Data Validation from another workbook with a little help from Power Query
  • October 14, 2014

    Probably the longest title of all times, but it leads to something great. First let’s explain Power Query. Power Query is an AddInn for Excel and part of Microsoft’s Power BI (get it here). It is proving to be a tool Excel Users have been (unknowingly) waiting on ...

    Creating a Data Validation dropdown list from another Workbook
  • October 7, 2014

    Can you create a Data Validation Dropdown list that uses data from another workbook as a source? This question has been proposed to me continuously since I published two Data Validation related posts: Taking the Data Validation List to the next level Dependent Data ...

    The New Pivot Column option in Power Query
  • September 30, 2014

    If some pictures are hard to view, you can get the PDF of the article The Pivot option in Power Query. With the latest update of Power Query AddInn for Excel quite a few new features were introduced, but none as sweet as the new Pivot option. To get it you must update your ...

    Option Button Control on the Developer Tab, Top Bottom 5 in Pivot Table and CHOOSE Function in Excel
  • September 23, 2014

    We have a dish where I come from (Slovenia), called Minestrone. It’s interesting since there is no single recipe for it. It’s made from whatever vegetables are in season or most of the time whatever you have in your fridge. Now when you’re reading the title ...

    Who knew (Just another day with Excel)?
  • September 12, 2014

    One day as I was giving a basic(!!!!) Excel training, my mind got sort of blown. We were discussing the use of A1:A100 in the Name Box for the selection of a range of cells from A1 to A100. You can see this in action here. It went by as it did a million times […]

    Dependent Data Validation dropdown lists
  • September 2, 2014

    Our goal here, is to create a dropdown list by using Data Validation, where the contents of the list will be determined by another Data Validation Dropdown list. Here’s an example… Now there are two dropdowns here, one for the Band and another for the Song Title. But ...

    Smile for Excel’s Camera
  • August 26, 2014

    There are many more or less unknown tools in Excel. One of those is definitely the Camera Tool. Imagine the following scenario. You have two sheets in Excel. On one Sheet you have all the data, and on the other, all the calculations or a chart based on that data. Now we wish ...

    Unpivot with Power Query
  • August 19, 2014

    One might think that analyzing data with a Pivot table is hard, but Unpivot a Pivot table proves to be even harder. But Power Query or better yet a Query editor proves once more that it is a brilliant tool that can handle Unpivot with ease. Let’s first elaborate on ...

    Get Todays Date in Cells A1:A15000
  • August 12, 2014

    In my work as an Excel Trainer, I do love to challenge the audience. And one of my favorite challenges to extend is the focus of this post. So fill all the cells in a range from A1 to A15000 with today’s date. I also like this challenge because if you do it correctly ...

    Create a Factor Growing Series in Excel
  • August 5, 2014

    If someone was to tell you, do a 1;2;3;4;5;6;7;8;… series in Excel, there are many that would not hesitate and go for one of the two options Write 1 and 2 in the first two cells. Select both values, go to the bottom right corner and drag to wherever the heart desires. ...

    Convert Text to Numbers in Excel
  • July 29, 2014

    A task that comes about every so often in Excel and can be quite tricky. Now our ultimate goal is to get a number out of something like (text 1 text) or even (text123text). This is the main focus of this post but just to cover all the bases, let’s start with the easy ...

    Custom Cell Format You Must Know in Excel
  • July 22, 2014

    Custom cell formats are a very important steppingstone to eternal happiness in Excel. And among all Custom Formats, one takes precedence for both, its usability and versatility. We will get to understand it through two examples. Four for the Price of One The following Custom ...

    Rounding numbers to a specific multiple in Excel
  • July 15, 2014

    Excel can be very tricky about rounding numbers. Almost half of Excel users still believe that if they format a cell to round to two decimals, that what they see is what they are calculating with. This of course is not the case. For the purpose of rounding numbers in Excel ...

    Fill All Blank Cells in an Excel Range With a Desired Value
  • July 8, 2014

    Here’s a nifty little trick I use every day while working in Excel. It’s a two part trick. Part one will be selecting all the blank cells in a range or rather reducing a selection or a range to blank cells within that range. Part two will be how to enter a value ...

    Why Excel thinks “Time is on my side” by the Stones is a joke on him…
  • July 1, 2014

    Excel has quite a few funny perks. Once you get over the 15 digit limit in Excel and the fact it does not calculate exactly as it should, you will sooner or later arrive at the point where you will feel the need to calculate time. In this case time stands for time and date ...

    Sum of ten largest values
  • June 24, 2014

    This will be a short lesson on three great and sadly unknown functions and how to use them as an array function. The eighth LARGEst value One of my favorite questions to propose to listeners is “Give me the eighth largest value from an array”. This is one of the ...