Working with dates and time in Excel
One slightly tricky aspect of working with Excel is dates. The key thing to remember is that for Excel the world began on 1/1/1900 which means that every date is a number that represents the number of days that have elapsed since then. If you import data from an external system, you will often see […]
What do those pesky error messages mean?
This blog post is inspired by a participants question. He asked about how to understand some of Excel’s error messages so I have compiled the most common ones into a table below.. You can find a full reference set here. Laila Gharani offers solutions to 7 common Excel errors here Some error messages Function you […]
Filter and separate
One of my favourite sources for blog posts are questions I get asked by customers. And this one falls into this category. It was from an Excel newbie and what he needed to do was extract the results for a specific customer and then email them a copy of those results only. First of all, […]
How to fill in different repeating entries in Excel
A very typical scenario in Excel when you get a data download with multiple entries for the same name/transaction is for the name to be entered once, then multiple transactions to be in another column beside it. However, if you need to filter or apply a pivot table to this data – you have to […]
Cleaning up a messy PDF file… everyone’s favourite job..
A question I get asked from time to time is how to clean up a PDF file. There have been various solutions over the years: copy and paste into Word, use a third party solution but in this tutorial – I want to show you how to clean one up. Here is the file I […]
Why Your Excel Formulas Break When You Copy Them (Relative vs Absolute Referencing) AKA “It was all going so well.”
One of the commonest topics that Excel newbies find challenging is the whole when they copy down a formula and it apparently stops working or at least does weird stuff. Here is the starting file. Here is the finished file Also here are some tips on when to use a fixed (absolute reference) You need […]
Experimenting with some long-term marine data
This week I thought it would be interesting to explore some real data. I have downloaded this from the Irish government data website. It tracks Sea Surface temperature since 1958 at a place called Malin Head (northerly point of Ireland). Here is where I clean it up and I thought it would be interesting to […]
Creating a series of weekday only dates
As is often the case, I get the most interesting questions from participants. This week I got asked how to create a series of weekdays only. In the class I tried my usual technique of giving Excel a pattern of 10 dates following this pattern but it didn’t act as expected. One of those “unexpected […]
5 quick ways to clean up your data – Part Two
In Part One of this blog post I covered 5 ways to clean up your data. In this one I am going to cover 5 more Change Case Concatenate (like many things in Excel – not as exciting as it sound…) Left/Right/Mid Text Before/ Text after Using Len You can download the completed file here. […]
5 quick ways to clean up your data – Part One
Ah, everyone’s favourite Excel job. Data cleansing. I had a Lunch and Learn to do with an organisation and this was part of what I covered. I looked at 5 topics: Trim (to remove unwanted spaces) Text to numbers (when your data comes in looking like a number – but it’s actually text) Flash Fill […]