Excel 2016

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.

This video shows you that if you increase your original dataset you then need to Refresh the PivotTable so that it picks up the new data. If your original dataset was turned into a Table before being made into a PivotTable we’ll see that the Refresh is very simple. However, if the dataset was not […]

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

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 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 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.

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.

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.

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.

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.

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.

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

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.

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

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

Learn how to remove the Subtotal that you previously created.

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

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

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

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.

You know how to Sort A to Z or Z to A. But what if you want to Sort using your own Custom Sort Order? Well this video will show you how.