Excel 2016 – 3.7.11 – VLOOKUP with IFERROR
When VLOOKUP can’t find the value it is looking for, it produces the #N/A error. We’re going to see how we can use that error value in the IFERROR function.
Excel 2016 – 2.9.1 – Create a Table
Learn what the word ‘Table’ really means in Excel – and how to create one.
Excel 2016 – 2.12.12 – FILTER to Show Non-contiguous Columns
This shows a clever trick to display data from columns that are not beside each other – and only that data.
Excel 2016 – 2.12.11 – How to Sort with FILTER
A normal sort doesn’t work with this function. See how we deal with this issue.
Excel 2016 – 2.12.10 – FILTER to Show Specific Columns
If you don’t want to return data from all the columns, just specific columns, then this is the video for you!
Excel 2016 – 2.12.9 – Conditional Formatting to Highlight Dates
Off-topic – but why not! Shows how to use Conditional Formatting on our list of dates.
Excel 2016 – 2.12.8 – FILTER Due Dates
We have a list of dates. Can we use the FILTER Function to filter them? Yes we can!
Excel 2016 – 2.12.7 – FILTER Function with Tables
See the advantages of setting your data up as a Table. This feature allows the FILTER function to refresh and update, without having to press Refresh!
Excel 2016 – 2.12.6 – FILTER #Spill Error
Find out what causes the #Spill Error and how to prevent/fix it.
Excel 2016 – 2.12.5 – FILTER Function Using > (Greater Than)
Using the FILTER Function and the > (Greater Than) sign together.
Excel 2016 – 2.12.4 – FILTER Function with Multiple OR Criteria
How to use the FILTER Function to return one value or another.
Excel 2016 – 2.12.3 – FILTER Function with Multiple AND Criteria
How to use the FILTER Function to return from one column and from another column.
Excel 2016 – 2.12.2 – Basic FILTER Function
Carry out a basic one column filter with the FILTER Function.
Excel 2016 – 2.12.1 – About the FILTER Function
This video will explain this wonderful new function and introduce you to the syntax.
Excel 2016 – 2.11.6 – Multiple Delimiters
Can we use multiple delimiters in the CONCAT() or TEXTJOIN() Functions? Yes we can and this video shows you how.
Excel 2016 – 2.11.5 – TEXTJOIN Over CONCAT
So we can see there are two Functions replacing CONCATENATE(). Now let’s take a look and see the differences between them.
Excel 2016 – 2.11.4 – The TEXTJOIN Function
Another new Function! This is also replacing the CONCATENATE() Function!
Excel 2016 – 2.11.3 – The CONCAT Function
CONCAT() is a new function and is replacing CONCATENATE(). However, it’s not the only one…
Excel 2016 – 2.11.2 – The CONCATENATE Function
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.
Excel 2016 – 2.11.1 – Manual Concatenation
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.
Excel 2016 – 2.10.20 – The TIMEVALUE Function
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.
Excel 2016 – 2.10.19 – Time Exceeds 24 Hours
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.
Excel 2016 – 2.10.18 – Calculate Minutes With Currency
We’ll take it a step further and show Excel that we’re calculating with Minutes rather than Hours.
Excel 2016 – 2.10.17 – Calculate Time With Currency
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.
Excel 2016 – 2.10.15 – Timesheet Removing Break Times
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.
Excel 2016 – 2.10.14 – Subtract Time
We’re going to look at this calculation working, but we’re also going to look at how it can go wrong, and why!
Excel 2016 – 2.10.13 – Time Formatting
Just like Dates, Times can also be formatted to show them the way you wish to see them.
Excel 2016 – 2.10.12 – DATEVALUE Function
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.
Excel 2016 – 2.10.11 – WORKDAY 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.
Excel 2016 – 2.10.10 – NETWORKDAYS Function
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!
Excel 2016 – 2.10.9 – WEEKDAY Function
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.
Excel 2016 – 2.10.8 – DATEDIF Function
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.
Excel 2016 – 2.10.7 – EOMONTH Function
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!
Excel 2016 – 2.10.6 – EDATE Function
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.
Excel 2016 – 2.10.5 – Calculate Date and Time
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.
Excel 2016 – 2.10.4 – Addition with Dates
This video is going to show you how to find a future date by using addition with dates.
Excel 2016 – 2.10.3 – Subtract Dates and Formatting Issues
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.
Excel 2016 – 2.10.2 – Custom Date Formatting
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.
Excel 2016 – 2.10.1 – About Dates and Formatting
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.
Excel 2016 – 1.12.6 – Create Custom Views
If you’re in the position that you regularly have to print out information from a file for others and each time you do you have to carry out certain tasks, such as hiding some columns or rows, then this feature is definitely for you!
Excel 2016 – 3.13.12 – Use Solver and Reports
This video shows you how to use Solver and how to create the different reports that it makes available.
Excel 2016 – 3.13.11 – Add Solver to Excel
Solver is NOT part of the default set-up of Excel, which means we need to add it in. This video shows you how.
Excel 2016 – 3.13.10 – About Solver
There are few things you need to be aware of, to use Solver. This video explains them.
Excel 2016 – 3.13.9 – Goal Seek
A lovely simple little feature, that is always handy to know.
Excel 2016 – 3.13.8 – Create a Summary Report
This video shows you the print out that you can get, or Summary Report, from the Scenario Manager.
Excel 2016 – 3.13.7 – Named Cells with Scenarios
This video shows you how to use Named Cells with your Scenarios, which means they are much easier to read and makes for a better print out.
Excel 2016 – 3.13.6 – The Scenario Manager
This video shows the Scenario Manager and how it’s commonly used.
Excel 2016 – 3.13.5 – Two-input Data Table
This video shows how to use two inputs into a Data Table.
Excel 2016 – 3.13.4 – Start-up Data Table
We are still looking at a One-input Data Table but this time we’re looking at a Start-up company. We want to show the Revenue, Profit and Variable Costs associated with differing sales amounts.