Excel 2016 Level 2

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.

DETAIL

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.

DETAIL

Excel 2016 – 2.11.4 – The TEXTJOIN Function

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

DETAIL

Excel 2016 – 2.11.3 – The CONCAT Function

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

DETAIL

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.

DETAIL

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.

DETAIL

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.

DETAIL

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.

DETAIL

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.

DETAIL

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.

DETAIL

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.

DETAIL

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!

DETAIL

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.

DETAIL

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.

DETAIL

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.

DETAIL

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!

DETAIL

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.

DETAIL

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.

DETAIL

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!

DETAIL

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.

DETAIL

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.

DETAIL

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.

DETAIL

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.

DETAIL

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.

DETAIL

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.

DETAIL

Excel 2016 – 2.9.8 – Remove The Table Feature

Look at how to remove the Table feature and return your data to a ‘normal’ data set. See also the effects this will have on the calculations that we created previously.

DETAIL

Excel 2016 – 2.9.7 – Fill Handling a Calculation Outside a Table

If you calculate outside a Table, then want to Fill Handle, like you would normally do, you’ll find that you run into a problem. Absolute (F4) doesn’t work! This video will show you this not working, will explain why it doesn’t work, and will show you how to fix the issue.

DETAIL

Excel 2016 – 2.9.6 – Calculate Outside Of a Table

This video shows you why you shouldn’t use the SUM function to create a total when calculating a field from a Table. We then look at using the SUBTOTAL function to carry out the same task and see why it works when the SUM function doesn’t.

DETAIL

Excel 2016 – 2.9.5 – Explanation of Structured References

Tables use a different type of calculation called Structured References. When you see these for the first time it can be daunting, but they are quite understandable once they are explained. This video helps you to understand what they are actually saying.

DETAIL

Excel 2016 – 2.9.4 – Calculate In a Table

Watch this video and discover some of the in-built calculation features in a Table – and also get an introduction to ‘Structured Reference’.

DETAIL

Excel 2016 – 2.9.3 – Insert & Delete Table Rows and Columns

Learn how to insert a Table column or row without affecting another dataset on the Worksheet. Sounds unbelievable? Check it out! Another great feature of Tables.

DETAIL

Excel 2016 – 2.9.2 – Three Quick Tips For Using a Table

This video shows you 3 quick tips for working with the Tables feature: Tip 1: How to quickly select the entire Table Tip 2: How the built in ‘Freeze Panes’ works in the Table Tip 3: How to quickly add in a new row

DETAIL

Excel 2016 – 2.9.1 – Create a Table

Learn what the word ‘Table’ really means in Excel – and how to create one.

DETAIL

Excel 2016 – 2.8.6 – Use Visible Cells to Copy Subtotals

When you Copy and Paste Subtotaled data you’ll find that the hidden data also comes with it! This video shows you how to deal with this issue.

DETAIL

Excel 2016 – 2.8.5 – Create a Multi-level Subtotal

This video looks at the steps you need to take to place one Subtotal inside another Subtotal.

DETAIL

Excel 2016 – 2.8.4 – Create Two Separate Subtotals

If you want to carry out two separate Subtotals, one after the other, then you must remember to Sort prior to each Subtotal.

DETAIL

Excel 2016 – 2.8.3 – Remove a Subtotal

Learn how to remove the Subtotal that you previously created.

DETAIL

Excel 2016 – 2.8.2 – Create a Subtotal

Learn how to use this very simple ‘must have’ feature – you’ll wonder how you ever did without it.

DETAIL

Excel 2016 – 2.8.1 – Why Use The Subtotal Feature?

This video will demonstrate why everyone should know how to use the Subtotals feature.

DETAIL

Excel 2016 – 2.7.4 – Filter and Copy Data To Another Worksheet

In this video learn the work-around that allows you to Copy and Paste the unique items from a column, into a different sheet, as part of the Advanced Filter.

DETAIL

Excel 2016 – 2.7.3 – Show Unique Items From a Column

In this video learn how to use the Advanced Filter feature to obtain a list of each unique item contained in a column.

DETAIL

Excel 2016 – 2.7.2 – Create An Advanced Filter

When you use a normal Filter, you learn how to set multiple criteria and by default they AND each other. In this video learn how to use the Advanced Filter feature to set two sets of criteria which OR each other.

DETAIL

Excel 2016 – 2.7.1 – Why Use An Advanced Filter?

In this video see an example of why you might need to use an Advanced Filter, when a normal Filter is unable to give us the result we are looking for.

DETAIL

Excel 2016 – 2.6.7 – Calculating With a Filter

Did you know that there are Functions in Excel that do not understand Filters? Learn what Functions they are and how they should be calculated. Also discover that if you carry out a Filter on data that already has Hidden Rows in it, this also causes a problem with those Functions.

DETAIL

Excel 2016 – 2.6.6 – Create Date Filters

In this video learn how to use some of the pre-defined Date Filtering features, as well as how to use the custom Date Filters.

DETAIL

Excel 2016 – 2.6.5 – Create a Top 10 Filter

In this video use the Top 10 Filter feature to find the top five most expensive cars, then change over to find the bottom five cheapest cars.

DETAIL

Excel 2016 – 2.6.4 – Create a Price Range Filter

In this video see how to Filter for all cars that fall within a specific price range.

DETAIL

Excel 2016 – 2.6.3 – Apply a Filter Using Multiple Criteria

In this video, learn how to Filter using more than one column. Also see how to quickly clear down a multi-filter, without having to clear each one individually.

DETAIL

Excel 2016 – 2.6.2 – Create a Quick Filter Using the Right Click

Another way to carry out a basic Filter, this time showing how you can use the right-click not only to carry out a Filter, but to actually turn the Filter feature on, at the same time.

DETAIL

Knowledge Market in Your Business

Knowledge Market is a great source for online video tutorials. We have developed a range of topics based on the applications that you use every day.
So whether you wish to refresh your existing knowledge - or you want to learn something new - we've got everything you need when you need it and where you need it.

Membership

Our Membership options have been developed to suit your specific needs. Contact us today to find out how we can be part of your office support infrastructure.

CONTACT

Email: kelly@knowledgemarket.ie

Performance Support and Learning for:
Microsoft Office – Excel, PowerPoint, Project, Word

Privacy & Cookies

This website uses Cookies. Continued use of the site will be deemed as your acceptance of this necessity.

Please read our Privacy Policy