Excel Unplugged

Category Archives: Excel Functions

Remove Blank Rows in Excel

How to remove blank rows in Excel? Let’s look at some examples for simple and complicated tables. Simple Table Let’s start with the following table. The ...

AI in Excel – Part 3 – Analyze Data, the Future of Data Analysis?

Welcome back to Artificial Intelligence in Excel! In Part 1, we looked at a feature called Flash Fill. It works by recognizing patterns of data and filling out the ...

AI in Excel – Part 2 – Column From Examples

Welcome back to Artificial Intelligence in Excel! In Part 1, we’ll looked at a feature called Flash Fill. It works by recognizing patterns of data and filling ...

AI in Excel – Part 1 – Flash Fill

Welcome to the brave new world of Artificial Intelligence in Excel! In this article, we’ll look at a powerful new feature called Flash Fill. It works by ...

Extending the Date Table – Part 2

We’re continuing our quest for beautiful and dynamic dashboards. We make the greatest strides towards that by simply extending the date table with four ...

Extending the Date Table – Part 1

Let’s look at some crucial tools for creating polished dynamic dashboards. We always extend the date table with three important columns: YM Index, Running 13 ...

How to Highlight Weekend Dates in Excel Table

Highlighting Weekends can be hard in Excel. So just for fun, we’ll do this in three different ways: we’ll use conditional formatting to highlight the weekend ...

SEQUENCE function – Add it to your toolbelt today
September 14, 2021

The Excel SEQUENCE function In this article, we’ll introduce the SEQUENCE function. SEQUENCE function is one of the new dynamic arrays functions and when ...

R Language Overview and R Language Examples
September 10, 2021

R Language Introduction and R Language Examples R is a programming language and software environment for statistical computing and graphics. It is free and ...

Consolidate Multiple Excel Sheets From Multiple Excel Files

We often have our data spread out in multiple Excel workbooks and sheets. How do we combine all of them into one table? Let’s walk through an example. We have ...

Turning Product Descriptions into a list of attributes with Power Query

You can also watch the video version of this content here: https://youtu.be/5QDERKmMvZQ or watch the embedded video at the end of this post. It’s true Power ...

Excel FILTER Function – The Function that does Everything

Last year, Microsoft announced the introduction of a new group of functions in Excel, known as dynamic array functions. One of these – the FILTER function – is ...

XLOOKUP is finally here

Today is most definitely one of the most exciting days of this year for all Excel users out there. Today the endless discussions about whether people should use ...

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 a data model 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

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

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. First there’s ...

Search a Range for the closest value to a chosen number

How to find a closest value to a chosen number within an Excel range? Let’s do it through a simple example! We start with a range of values in Excel (A1:G20). ...

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

Alt Enter is known as a manual line break in Excel. It’s what you use when you want to have text show in multiple lines regardless the width of the column. But ...