Excel 2016 – 3.10.3 – Using Four Arrays
This is the final video showing how to use the SUMPRODUCT, in the Microsoft way, this time using four arrays.
Excel 2016 – 3.10.2 – Using Three Arrays
Still showing the Microsoft way of using SUMPRODUCT, this video shows you how to use three arrays in the calculation.
Excel 2016 – 3.10.1 – Introduction to SUMPRODUCT
There are two ways of using the SUMPRODUCT function and this video explains the default, or Microsoft way, of using this function.
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.
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.
Excel 2016 – 3.9.6 – CF With VLOOKUP
e a VLOOKUP in Conditional Formatting to highlight the exams that students have failed.
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.
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.
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
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.
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.
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.
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.
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’.
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.
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’.
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’.
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.
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.
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…
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…
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.
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.
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.
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.
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.
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.
Excel 2016 – 3.7.1 – About LOOKUPs
This video explains the LOOKUP functions and the table layout you need to have, to use them.
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!
Excel 2016 – 3.6.7 – Chart With Checkbox Control
For all you chart nerds, you’re going to love this one!
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.
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.
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.
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.
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.
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.
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!
Excel 2016 – 3.5.8 – Using Calculated Item
This calculates one item in a field, with another item in that same field.
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.
Excel 2016 – 3.5.6 – Using % of Row Total
Use this function to show what % of the months sales – each Product amounts to.
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.
Excel 2016 – 3.5.4 – Using Rank
We’re going to use this pre-created PivotTable function to rank our Sales in order, by Location.
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.
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.
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.
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.
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.
Excel 2016 – 3.4.1 – Group by Numbers
This feature is used to show how you can Group employees in a company, by age.
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.
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.