Excel 2016 – 3.5.7 – Using Calculated 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.
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.
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.
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.
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.
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.
Excel 2016 – 3.2.4 – Apply Pivot Styles
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.
Excel 2016 – 3.2.3 – Change the Pivot Layout
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.
Excel 2016 – 3.2.2 – How to Rename Field Headings
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.
Excel 2016 – 3.2.1 – How to Display Blank Cells
Learn how to display the blank cells that appear in a PivotTable – as zeros.
Excel 2016 – 3.1.11 – Set a PivotTable to Refresh on Open
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.
Excel 2016 – 3.1.10 – Refresh the Data
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 […]
Excel 2016 – 3.1.9 – Insert and Use Slicers
Slicers are an excellent feature for filtering a PivotTable. In this video learn how to create, modify and delete Slicers.
Excel 2016 – 3.1.8 – Create a PivotChart
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.
Excel 2016 – 3.1.7 – Drilldown Into Pages
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.
Excel 2016 – 3.1.6 – Drill into Pivot Data
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.
Excel 2016 – 3.1.5 – Filter and Ungroup Pivot Data
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.
Excel 2016 – 3.1.4 – Group a PivotTable by Date
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.
Excel 2016 – 3.1.3 – Add Currency Style to Pivot Data
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.
Excel 2016 – 3.1.2 – Create a Basic PivotTable
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.
Excel 2016 – 3.1.1 – View and Setup Data
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.
Excel 2016 – 3.12.6 – Move a Macro into the Personal Workbook
This video shows you the steps to take to move a Macro into the Personal Workbook, so that it is available to all Excel Workooks on your computer. This will be done using the VBA Editor. You will also see how to delete a Macro – and at the end of the video, you’ll see […]
Excel 2016 – 3.12.5 – Create a Button for a Macro
We’ll place a button onto the Quick Access Toolbar, then attach our Macro to it, so it is easy for us access, for future use.
Excel 2016 – 3.12.4 – Open a Workbook Containing a Macro
This video shows you the Security message that you should receive when you open a Workbook with a Macro in it. It will also explain why you get this message.
Excel 2016 – 3.12.3 – Save a Macro in a Workbook
Macros cannot be saved into .xlxs Workbooks, for security reasons. This video brings you through the steps required to save the Macro.
Excel 2016 – 3.12.2 – Create a Macro
The Macro that we are going to create involves four separate steps that we find ourselves doing on most of our Excel sheets. This video brings you through the steps required to create that Macro.
Excel 2016 – 3.12.1 – What is a Macro?
This video explains what a Macro is and shows us the Macro that we will be creating throughout these lessons.
Excel 2016 – 3.11.8 – AGGREGATE as a LOOKUP
Another great way to use this fabulous function, with a bit of mixed Absolute/Relative referencing thrown in.
Excel 2016 – 3.11.7 – AGGREGATE Like SUMPRODUCT
This is one for all the Excel nerds out there (you know who you are). This one shows you the structure to use if you want to add Conditions to the calculation.
Excel 2016 – 3.11.6 – Ignore Error Values Using Array Form
Just when you think it can’t get any better – it does! I love it!
Excel 2016 – 3.11.5 – Ignore Error Values Using Reference Form
See how to calculate right through a bunch of errors. This is SO brilliant!
Excel 2016 – 3.11.4 – Using the Array Form with SMALL
We’re looking at using the SMALL function on it’s own, then we’re looking at using it through the AGGREGATE function. This will show you when you would use one above the other.
Excel 2016 – 3.11.3 – Using the Array Form with LARGE
We’re looking at using the LARGE function on it’s own, then we’re looking at using it through the AGGREGATE function. This will show you when you would use one above the other.
Excel 2016 – 3.11.2 – Using the Reference Form with SUM
We’ll look at using 4 functions that do the same thing, and look at why you would use one over the other.
Excel 2016 – 3.11.1 – About the AGGREGATE Function
This function has the ability to replace 19 other Excel functions! Definitely one to know.
Excel 2016 – 3.10.8 – Calculate a Table of Values
Learn here how to combine conditions and calculations in a Table using the SUMPRODUCT Function.
Excel 2016 – 3.10.7 – Using Three Conditions
This video will show how to structure three conditions and two arrays of values – which means there will be five separate selections inside this SUMPRODUCT function.
Excel 2016 – 3.10.6 – Using Two Conditions
We’ll look at working with two text conditions and one set of values (figures).
Excel 2016 – 3.10.5 – How Conditions Work
How can the SUMPRODUCT function multiply and sum, when the conditions being set are Text? This video explains how this very clever function works.
Excel 2016 – 3.10.4 – SUMPRODUCT With Conditions
In this video we’re using the SUMPRODUCT function in a different way. We’re using conditions – and manually typing it out!