Excel Unplugged

Category Archives: Excel Functions

Start And End Date Of The Week With Power Query

One of the most popular posts on Excel Unplugged is Formula to get the Start and End Date of the Week. For the last two years I’ve been a strong advocate for ...

Ultimate Vlookup Alternative? (Part 2)

This is part 2 of the Ultimate Vlookup Alternative post and a continuance from last week’s post. We will be using the same Excel workbook as we did in part ...

Ultimate Vlookup alternative? (Part 1)

Almost a year ago, more precisely in May of 2017 a site called defeat excel invited 27 Excel Experts (including myself) to end the endless debate of Vlookup vs. ...

Making Importing from Folder in Power Query (or Power BI) Bulletproof

I’ve always been a big fan of the Import from Folder feature in Power Query. So much so, that I wrote my first post on the topic way back in February of 2015. ...

Dynamic Calendar with Power Query or Power BI – Take 2

I guess every data professional that has ever taken on the challenge of creating data models either in Power Pivot or in Power BI knows the importance of the ...

Table.Join – The Power Tool of Power Query

The basic explanation of Table.Join function in Power Query is to use Merge Queries on two steps of one single query. This will be a step by step tutorial on how to ...

Get a random subset of data with Power Query

The other day I made a Dashboard with a classic by time and relevant dimensions analysis. The data was pulled from Azure SQL DB, so Power Query was the method I ...

Excel vs. Excel 365

Whatever side of the Cloud vs. On Premise debate you take, it’s a fact that at this point there are two Excel 2016 versions out there, and I’m not ...

Search a Range for the closest value to a chosen number

We start with a range of values in Excel (A1:G20). Now we are looking for a formula to get the closest value to the value we input in cell I1. First we’re ...

Amsterdam Excel Summit 2016 report

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

REMOVE, REPLACE OR SEPARATE BY LINE BREAKS (ALT + ENTER) INCLUDING VBA

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

Power BI publisher for Excel (The Times They Are A-Changin)

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

Sum up the diagonal of your data anywhere on a Sheet

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

My three favorite things in Excel 2016

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

Formula to get the Start or End Date of the week

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 create Yearly, Quarterly, Monthly, ...

WORKDAY and WORKDAY.INTL functions in Excel

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

Another formula to get quarters from dates

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

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

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

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

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