Excel 2016 Level 3

This video shows you how to use Solver and how to create the different reports that it makes available.

Solver is NOT part of the default set-up of Excel, which means we need to add it in. This video shows you how.

There are few things you need to be aware of, to use Solver. This video explains them.

A lovely simple little feature, that is always handy to know.

This video shows you the print out that you can get, or Summary Report, from the Scenario Manager.

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.

Data to be used in a PivotTable must be structured in a way that Excel understands, or it just won’t work properly. This video will show you how to correctly set up your data, in the ‘Tabular’ way, that PivotTables understand.

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!

This calculates one item in a field, with another item in that same 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.

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

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

We’re going to use this pre-created PivotTable function to rank our Sales in order, by Location.

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.

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

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

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.

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.

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

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.

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

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.

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.

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.

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.

The default PivotTables Style is based on a blue colouring – but if blue isn’t your colour, then you can easily change the Style to any of the other pre-created Styles that Microsoft provides.

Maybe you’d like to remove the Grand Totals or add Subtotals to each Group. Maybe you’d like to add space between each Group to make it easier to read. Learn how to quickly and simply alter the layout of your PivotTable.

This video shows you how to change the field headings to be more understandable. It will also show you how to overcome a very common problem that you will encounter when renaming those field headings.

Learn how to display the blank cells that appear in a PivotTable – as zeros.

By default, PivotTables have to be manually Refreshed. However, there is a way that you can set them up to automatically Refresh whenever the Workbook is opened. In this video we’re going to see how you turn on that feature.

Slicers are an excellent feature for filtering a PivotTable. In this video learn how to create, modify and delete Slicers.

This video will show you how to quickly drilldown the Location field to show a separate Worksheet and PivotTable for each of the individual Locations. A very powerful feature of PivotTables.

This video shows you how easy it is to create a PivotChart. PivotCharts, like PivotTables, are dynamic and you’ll see that reflected in this video.

This video shows you how you can drill into any of your figures and get a breakdown of the sales that make up a figure. We’ll show a single drill-down, but you can drill-down on as many figures as you like.

This video shows you filtering and how it works differently with Grouped information. You’ll also see that Grouping stays with data, even when removed from the PivotTable, so if you want data to be Ungrouped, you have to manually carry it out.

The dates in the Date column span 2 years. This is the correct way to input dates in your dataset, if you want to create a PivotTable. However, to display the information by Year we need to know how to Group those dates. We’ll take it a step further and Group by Quarter, within Year.

Believe it or not, there is a correct way and a wrong way, to turn on the Currency style in a PivotTable. Learn the correct way, but also discover why the wrong way – is wrong.

This video shows you how easy it is to create a basic PivotTable. We’ll then build on this PivotTable over the rest of videos in this lesson.

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!

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

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

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

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