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.
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 […]
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.
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.
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.
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
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.
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.
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.
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.
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.
In this video we show you how – using mixed references to cells – you can perform multiple criteria and range calculations to create a Table or Matrix of new data.