Excel Unplugged
Difference between two times formula in Excel

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

Case sensitive/insensitive Sort in Excel (bug or a feature)

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

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

Making your Min Max chart in Excel truly shine

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

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

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

Last word in a string with an Array formula

As promised in the Pragmatism vs. Purity in Excel article from last week, this week’s article will explain the following array formula ...

Pragmatism vs. purity in Excel

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

Last day of previous month formula in Excel

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

Boost your Excel efficiency with one key – F5 (Go To and Go To Special)

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

Creating a dynamic Dropdown List with Data Validation from another workbook with a little help from Power Query

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

Creating a Data Validation dropdown list from another Workbook

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

The New Pivot Column option in Power Query

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

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

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

Who knew (Just another day with Excel)?

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

Dependent Data Validation dropdown lists

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

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

Unpivot with Power Query

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

Get Todays Date in Cells A1:A15000

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

Create a Factor Growing Series in Excel

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

Convert Text to Numbers in Excel

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

Custom Cell Format You Must Know in Excel

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

Rounding numbers to a specific multiple in Excel

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

Fill All Blank Cells in an Excel Range With a Desired Value

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