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

Why Excel thinks “Time is on my side” by the Stones is a joke on him…

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

Sum of ten largest values

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

Fill and Justify in Excel

In my work as a trainer I’m usually the one serving the Excel goodies to the audience. But every so often I’m the one getting served. Whereas sometimes ...

Turn Conditional Formatting On and Off (Show or Hide Conditional Formatting)

Simply put, there are two ways to turn Conditional formatting On and Off. The manual way will take you to the Home/Conditional Formatting/Manage Rules… window ...

15 digit limit in Excel

This is something everyone should know about Excel since it both enables Excel to do lightning speed calculations and limits Excel in a great way. And the guilty ...

Formula to get quarters from dates in Excel

We have a column of dates in Excel, and our goal is to get Q1, Q2,… or QTR1, QTR2,… or Quarter 1, Quarter 2,… . I think you get the picture 🙂 First we ...

Pivot Table vs Power Pivot or what is Power Pivot

This question or rather comparison never seizes to amaze me, since it’s just like comparing Eric Clapton to a Ford Mustang. It just can’t be done since ...

PowerPivot Inside Out (Part 2/3)

This is the second article of three. If you hadn’t read the first part yet, it is recommended that you do so. It talked about the basics of PowerPivot and ...

Do not change Column Width on a Pivot Table Refresh

One of the most annoying things a Pivot Table does on refresh is it auto fits the column width of all columns. This is great if a Pivot Table is all you have on a ...

PowerPivot Inside Out (Part 1/3)

10 things you need to know The following article is kind of a baseline knowledge, one should possess, before attempting the creation of their first PowerPivot ...

Does a PowerPivot Pivot Table beat a regular Pivot Table

Hands down, YES! If you would like to read about the comparison of definitions of the two, you can do so in the Pivot Table vs Power Pivot post. If you are new to ...

Dynamic ranges in Excel

For the purposes of this article (if you wish to follow long and I strongly suggest you do so) we will be using a workbook that has the A2:E10 Range filled with the ...

Taking the Data Validation Dropdown list to the next level

Creating a dependent or linked Dropdown list In this article I will show you how to create dependent Dropdown lists using Data Validation and an Excel function ...

How do I “separate” Pivot Tables

During the creation of Office 2007, 2010 and 2013, a great emphasis was given to the file size. Of course you would like to make the file size as small as possible ...

Removing old Row and Column Items from the Pivot Table

Pivot tables still remains the go to solution in Excel for Data Analysis. And for those who do work with them on a regular basis, three things begin to bother them ...

Sorting months chronologically and not alphabetically in a Power Pivot Pivot Table

Here is our problem. When you create a Pivot Table in Excel that has a date field, you can Group that field by month and the sort will be logical (January, February, ...