Sometimes in Excel, we are just an innocent bystander when enthusiasm gets the better of people and they start drifting around Excel, inserting data in places where they weren’t supposed to. And even if they are so kind to remove any trace of their wayward journey, a ...
Following a wide demand, I also wrote a post about getting data from Excel files in a folder. You can read it here and I recently followed up with a post on how to make the process bulletproof and you can read that here. It seems that lately Excel users can be separated ...
In my work as a Trainer and also as a Data Scientist (I love that term), Shortcuts in Excel are a vital part of doing things efficiently. This shortcut helps you when you select data. Let’s assume this is our data. A bunch of dates from A1 to Z12000. Working with this ...
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 Excel, the rigidness of the weekends and holiday exceptions only were not working for me. On many occasions I ...
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 here. But now I wish to show you yet another way to get quarters from dates, this time by using the CHOOSE ...
This is what happens when someone has too much time and Excel 🙂 Happy holidays and all the best in 2015 to all Excel lovers!
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 differences between Arrivals and Departures. Most Excel users will use a simple Arrival – Departure ...
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 get It’s easy to see that there is no case sensitivity here, but there is a way to turn it on. You go […]
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 dates that represent Saturdays and Sundays. We can do this in Excel quite easily by using the Fill/Series… ...
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. Here’s how we make it work. We start with the data. Now we need to add two columns that will only show the […]
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 there. The math goes that the more shortcuts you know, the more efficient you are in Excel. So what does Word offer ...
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 2003. It’s a function that can be as entertaining as it is useful. The main purpose of the tool is to ...
As promised in the Pragmatism vs. Purity in Excel article from last week, this week’s article will explain the following array formula { =MID(A1,MAX((--(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)=" "))*ROW(INDIRECT("1:"&LEN(A1))))+1,50)} Throughout this article, ...
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. Particularly lately with Excel 2013 with the new features that it brought not even mentioning the Add-ins it offers. ...
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 the current month. This post will show you a nice trick how to do that. Whereas for the last dates of the ...
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 kind of data (text, numbers, errors, objects, formulas…). The Go To… command which you get by either ...
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 Microsoft’s Power BI (get it here). It is proving to be a tool Excel Users have been (unknowingly) waiting on ...
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 two Data Validation related posts: Taking the Data Validation List to the next level Dependent Data ...
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 few new features were introduced, but none as sweet as the new Pivot option. To get it you must update your ...
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 vegetables are in season or most of the time whatever you have in your fridge. Now when you’re reading the title ...
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 of cells from A1 to A100. You can see this in action here. It went by as it did a million times […]
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. Here’s an example… Now there are two dropdowns here, one for the Band and another for the Song Title. But ...
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 Sheet you have all the data, and on the other, all the calculations or a chart based on that data. Now we wish ...
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 proves once more that it is a brilliant tool that can handle Unpivot with ease. Let’s first elaborate on ...
