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

Fill and Justify in Excel
• June 17, 2014

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 when I’m done so on an Excel Basic training, it just makes me want to rethink the whole career path, in ...

Turn Conditional Formatting On and Off (Show or Hide Conditional Formatting)
• June 3, 2014

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 where you can delete the rules you want. But then there is an elegant way of doing this, that makes you look like ...

15 digit limit in Excel
• May 19, 2014

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 party is IEEE 754. What is IEEE 754 and why can it give you a heart attack in Excel Whenever you work with large ...

Formula to get quarters from dates in Excel
• May 19, 2014

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 will show the simple formula to get quarter number from dates. Then we will show how to add any prefix to […]

Pivot Table vs Power Pivot or what is Power Pivot
• May 16, 2014

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 they are two totally different things altogether. And just like you can cruise around in your Ford Mustang (you ...

PowerPivot Inside Out (Part 2/3)
• May 16, 2014

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 already covered the first three of the ten things you need to know. So we will continue with number four… 4. ...

Do not change Column Width on a Pivot Table Refresh
• May 14, 2014

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 Sheet. Otherwise, it keeps changing the layout of other data or other Pivot Tables. Here Is how you change this. ...

PowerPivot Inside Out (Part 1/3)
• March 24, 2014

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 workbook in Excel. I always believed that understanding is key, and therefore this article is written to deepen your ...

Does a PowerPivot Pivot Table beat a regular Pivot Table
• October 11, 2013

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 the PowerPivot Add-In, or if you just heard it being mentioned in passing, what you should know is that ...

Dynamic ranges in Excel
• October 9, 2013

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 following random data. In real life Ranges like this are very rare and very unkind to the user, since no ...

Taking the Data Validation Dropdown list to the next level
• July 1, 2013

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 called INDIRECT. First let’s take a look at what we are trying to accomplish. We will start with the ...

How do I “separate” Pivot Tables
• June 5, 2013

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 and for this purpose even the creation or better yet the behavior of Pivot Tables has changed since Excel 2003. ...

Removing old Row and Column Items from the Pivot Table
• May 25, 2013

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 soon. One is the automatic resizing of columns on Pivot Table refresh which you can read about here. The second ...

Sorting months chronologically and not alphabetically in a Power Pivot based Pivot Table
• April 16, 2013

Here is our “problem”. When you create a Pivot Table in Excel that has a date field and is based on data in Excel, you can Group that field by month and the sort will be chronological (January, February, …). But when you create a Pivot Table based on Power ...