Excel 2016 Level 3

Excel 2016 – 3.9.8 – Create a Risk Register

In project management we assess the probability of a risk happening and the impact if it occurs. In this video we show you how to apply Conditional Formatting to provide focus to Risk Scores in a Risk Register.

DETAIL

Excel 2016 – 3.9.7 – CF With Banded Rows

This video will introduce the MOD and ROW functions and then show you how to use them in Conditional Formatting to create a banded row effect.

DETAIL

Excel 2016 – 3.9.6 – CF With VLOOKUP

e a VLOOKUP in Conditional Formatting to highlight the exams that students have failed.

DETAIL

Excel 2016 – 3.9.5 – CF With Text

Use Conditional Formatting to display the status of an issue as a different colour depending on whether that issue has been Raised, is In Progress or has been Closed.

DETAIL

Excel 2016 – 3.9.4 – CF With Sales Target

Use a Formula within Conditional Formatting to highlight the rows where the sales meet, or exceed, the target.

DETAIL

Excel 2016 – 3.9.3 – Show Any Range of Dates

Use a Formula within Conditional Formatting to show entire rows being highlighted, if they are within a range of selected dates

DETAIL

Excel 2016 – 3.9.2 – Show Today’s Date Plus 7 Days

Use Conditional Formatting with a Formula to highlight today’s date (whatever date that may be) plus the next week.

DETAIL

Excel 2016 – 3.9.1 – Display Icons Using a Formula

Learn how to use a basic Formula in Conditional Formatting to display a green tick if a value is met, or is higher, and a red x if the value is not met.

DETAIL

Excel 2016 – 3.8.7 – Left LOOKUP With Naming

The final step is to Name all the relevant parts – the table, the row headings and the lookup column – so that we don’t need to move off the sheet on which we are creating the Left LOOKUP.

DETAIL

Excel 2016 – 3.8.6 – Use INDEX and MATCH Across Worksheets

Now that we’re more comfortable with these functions, We’ll use the INDEX function with two MATCH functions, across Worksheets, as you would in a real-life scenario.

DETAIL

Excel 2016 – 3.8.5 – Nest 2 MATCH Functions Into INDEX

We’re now going to select a full table to work with. This means that we have to nest two MATCH functions into the INDEX function. This is a real ‘Left Lookup’.

DETAIL

Excel 2016 – 3.8.4 – Combine INDEX and MATCH

In this video we will nest the MATCH function into the INDEX function. This is a basic Left Lookup as the selection for each function will be a single column.

DETAIL

Excel 2016 – 3.8.3 – How INDEX Works

We’re going to look at the INDEX function on its own so that you can see what it does and how it works – before we combine it with the MATCH function to create a ‘Left Lookup’.

DETAIL

Excel 2016 – 3.8.2 – How MATCH Works

We’re going to look at the MATCH function on its own so that you can see what it does and how it works, before we combine it with the INDEX function to create a ‘Left Lookup’.

DETAIL

Excel 2016 – 3.8.1 – Why Do You Need a Left LOOKUP?

This video gives you an explanation and example as to why a VLOOKUP will not work with our data, and why we need to carry out a ‘Left Lookup’. There is no function called ‘Left Lookup’ – we simply work with 2 different functions, INDEX and MATCH, to create the impression of a Left Lookup.

DETAIL

Excel 2016 – 3.7.10 – Create a VLOOKUP to Another Workbook

All our LOOKUPs have taken place within the same Workbook – now we’ll see how to go about creating a VLOOKUP to another Workbook.

DETAIL

Excel 2016 – 3.7.9 – Create an Array LOOKUP

An Array LOOKUP is also rarely used these days. It can take a table as its selection, but can only ever return a result from the last column of the table, whatever that might be. But just in case you need to know it…

DETAIL

Excel 2016 – 3.7.8 – Create a Vector LOOKUP

A Vector LOOKUP is rarely used these days, as it cannot take a table as its selection, so the VLOOKUP is used instead. But just in case you need to know how to use it…

DETAIL

Excel 2016 – 3.7.7 – Create a HLOOKUP

If your data has Row Headings instead of Column Headings, then a VLOOKUP won’t work. This is where you turn to the HLOOKUP function instead.

DETAIL

Excel 2016 – 3.7.6 – Create a Drop-down Menu for the Lookup Value

Now that we’re comfortable with the VLOOKUP function, we’re going to make our Invoice sheet more dynamic by creating drop-down menus that we can pick values from, rather than typing them in.

DETAIL

Excel 2016 – 3.7.5 – Combine VLOOKUP and Naming

If you find that you are regularly selecting the same table to look up – then you can save yourself some time by ‘Naming’ the table, so that you don’t have to physically select it.

DETAIL

Excel 2016 – 3.7.4 – Create a VLOOKUP That Doesn’t Require an Exact Result

When should you leave the Range_lookup box blank? This video shows you an example of when you would do this.

DETAIL

Excel 2016 – 3.7.3 – How VLOOKUP Can Go Wrong

If you require a LOOKUP function to look for something specific, then you must use the Range_lookup box. This video will show you what can happen if you don’t use it – and explain what is going on.

DETAIL

Excel 2016 – 3.7.2 – Create a VLOOKUP

The VLOOKUP function is one of those functions you simply must know. This video brings you through the correct steps to create a VLOOKUP.

DETAIL

Excel 2016 – 3.7.1 – About LOOKUPs

This video explains the LOOKUP functions and the table layout you need to have, to use them.

DETAIL

Excel 2016 – 3.6.8 – Chart With Scroll Bar

If you liked the previous video, you’ll fall head over heels for this one! It’s a longer video, but it’s worth it!

DETAIL

Excel 2016 – 3.6.7 – Chart With Checkbox Control

For all you chart nerds, you’re going to love this one!

DETAIL

Excel 2016 – 3.6.6 – Trending Trick 2

We make a slight adjustment to the structure of the data we used previously, to be able show it as a Line Chart.

DETAIL

Excel 2016 – 3.6.5 – Trending Trick 1

Learn how to structure your data to clearly show a trend over a 3 year period, using Columns.

DETAIL

Excel 2016 – 3.6.4 – Create a Bar Chart

For this data a Bar Chart is more suitable than a Column Chart. Discover why that is.

DETAIL

Excel 2016 – 3.6.3 – Create a Trending Chart

Learn how to show movement over time, with this Trending Chart. Take particular notice of how this data is laid out.

DETAIL

Excel 2016 – 3.6.2 – Chart Two Datasets

This data is poorly constructed so we’ll have a problem charting it. We’ll see how to deal with that and how to 0chart our two sets of figures different, but without using a secondary axis.

DETAIL

Excel 2016 – 3.6.1 – Create a Secondary Axis

We have two different types of data in our data set, that can’t be charted in the normal way. This is where a Secondary Axis should be used.

DETAIL

Excel 2016 – 3.5.9 – Pivot Sorting Issue

If you have found that – seemingly out of the blue – a PivotTable refuses to sort in either Ascending or Descending order for you then hopefully, this video will ‘sort’ out your frustration!

DETAIL

Excel 2016 – 3.5.8 – Using Calculated Item

This calculates one item in a field, with another item in that same field.

DETAIL

Excel 2016 – 3.5.7 – Using Calculated Field

We’re going to use the Calculated Field feature to show VAT at 23%. This will create a new Field specific to the PivotTable as it does not exist in the underlying data.

DETAIL

Excel 2016 – 3.5.6 – Using % of Row Total

Use this function to show what % of the months sales – each Product amounts to.

DETAIL

Excel 2016 – 3.5.5 – Using % Of

We’re going to see what % difference there is between our highest sales in Dublin, and each of the other Locations.

DETAIL

Excel 2016 – 3.5.3 – Using % of Grand Total

We want to know what % of the Grand Total are the first years sales. We can easily work this out using this pre-created feature.

DETAIL

Excel 2016 – 3.5.2 – Using % Difference From

We want to see the % difference in Sales between one year and another. This video shows you how to use this pre-created feature.

DETAIL

Excel 2016 – 3.5.1 – Using Average and Count

We know how to create a SUM within a PivotTable, but now we’ll create two more very common calculations.

DETAIL

Excel 2016 – 3.4.3 – Group Text Fields

This video brings you through the steps required to create a Regional Grouping in the PivotTable, even though there is no Region field, in the underlying data source.

DETAIL

Excel 2016 – 3.4.2 – Group Dates by Week

We’ve already seen how to Group by Date in Lesson 1 – but to Group Dates by Week takes a bit more work. This video shows you how it is done.

DETAIL

Excel 2016 – 3.4.1 – Group by Numbers

This feature is used to show how you can Group employees in a company, by age.

DETAIL

Excel 2016 – 3.3.6 – Using Slicers With Multiple Pivots

If you have multiple PivotTables created from the same data source, then you can attach the same Slicers to all of them, so that they are all Filtered together. Particularly useful for Dashboards.

DETAIL

Excel 2016 – 3.3.5 – Show Top 5 Locations

We’re going to use the Top 10 Filter feature to show the Locations generating the Top 5 sales revenues.

DETAIL

Excel 2016 – 3.3.4 – Filter By Date

PivotTables provide many pre-created date selections, which we’ll look at, but we’re going to see how to Filter between one date and another.

DETAIL

Excel 2016 – 3.3.3 – Custom List Sort

A manual Sort is great for small amounts of information, but if you want to create your own Sort Order for large amounts of data, then look at carrying out a Custom List Sort instead. Another advantage of this type of Sort is that it can be re-used in other PivotTables.

DETAIL

Excel 2016 – 3.3.2 – Create a Manual Sort

Is it possible to sort the data in a different order to A-Z and Z-A? Yes it is, and this video shows you how.

DETAIL

Excel 2016 – 3.3.1 – Basic Sorting

Sorting can be done through the Filter drop-downs or by using the right-click. We’re going to carry out 3 Sorts using the right-click.

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