Excel 2016 Level 2

This shows a clever trick to display data from columns that are not beside each other – and only that data.

A normal sort doesn’t work with this function. See how we deal with this issue.

If you don’t want to return data from all the columns, just specific columns, then this is the video for you!

Off-topic – but why not! Shows how to use Conditional Formatting on our list of dates.

We have a list of dates. Can we use the FILTER Function to filter them? Yes we can!

Find out what causes the #Spill Error and how to prevent/fix it.

Using the FILTER Function and the > (Greater Than) sign together.

How to use the FILTER Function to return one value or another.

How to use the FILTER Function to return from one column and from another column.

Can we use multiple delimiters in the CONCAT() or TEXTJOIN() Functions? Yes we can and this video shows you how.

So we can see there are two Functions replacing CONCATENATE(). Now let’s take a look and see the differences between them.

Another new Function! This is also replacing the CONCATENATE() Function!

CONCAT() is a new function and is replacing CONCATENATE(). However, it’s not the only one…

You’ve seen how to concatenate manually, now you’ll see how to do it using the CONCATENATE Function. IMPORTANT INFO: This Function is being deprecated – but it’s important to know, for backward compatibility.

If you need to ‘join’ things together such as combining First Name and Surname into the same cell, then you need to concatenate.  This is how you do it manually.

Do you have Dates that are actually Text? Well, if you do, then it’s worth using the TIMEVALUE Function, to convert them so that they become calculable.

In topic 5 ‘Calculate Date and Time’ we saw that once you cross over 24 Hours, you have to use a special format. Well, it’s exactly the same when Time crosses over 24 Hours.

We’ll take it a step further and show Excel that we’re calculating with Minutes rather than Hours.

If you’ve ever tried it, you know you can get strange results! You just need to let Excel know that it is Time that it is calculating.

Now we’re looking at addition with Time.

Now we know how to subtract time, but what if we want to subtract the lunch time from a Timesheet? This video shows you one way to achieve this.

We’re going to look at this calculation working, but we’re also going to look at how it can go wrong, and why!

Just like Dates, Times can also be formatted to show them the way you wish to see them.

Sometimes, when you download data from systems, like SAP, Dates actually come in as Text – which means they are non-calculable. However, if Excel recognises their structure, then it can convert them into Dates that can be calculated, using the DATEVALUE function.

You know the date that a project is going to start. You know how many days you have to complete it. What you don’t know is the date that it’s going to end, because you need to exclude Saturdays and Sundays – and Holidays. Well, that’s what this function does.

Do you need to know the number of working days between two dates – i.e. excluding Saturdays and Sundays? Do you also need to exclude holidays? Look no further, this is the function for you!

Do you need to be able to show what day of the week it is, on your spreadsheet? The WEEKDAY function and some custom codes can do the job for you.

You won’t find the DATEDIF function in the Help menu for Excel because it is no longer supported. Yet we can still use it, once we know how.

Do you have a project that is due in 2 months time, but it’s due at the end of the month? If so, the End of Month is the one that will work out that date for you!

If you need to know what the date will be a month from now, or 3 months from now – then this is the function that will give you that.

If you’ve ever tried to calculate with Dates and Times that run over the 24 hour period, then you’ll know that you get some very strange results! This video shows you the custom code that you need to use, to overcome this issue.

This video is going to show you how to find a future date by using addition with dates.

Dates are easy to calculate with because they are actually numbers. However, sometimes you’ll come across some formatting issues. This video will show you one way to deal with this.

This video shows you the codes that you can use to customise Dates to look a different way in the cell, while keeping the correct format in the background, so that we can calculate with our Dates.

In this video we’re going to look at the way Excel likes Dates to be entered. We’re also going to look at some shortcuts for putting in Dates.