Excel

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.

DETAIL

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.

DETAIL

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.

DETAIL

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.

DETAIL

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.

DETAIL

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.

DETAIL

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.

DETAIL

Excel 2016 – 3.2.1 – How To Display Blank Cells

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

DETAIL

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.

DETAIL

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 […]

DETAIL

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.

DETAIL

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.

DETAIL

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.

DETAIL

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.

DETAIL

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.

DETAIL

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.

DETAIL

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.

DETAIL

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.

DETAIL

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.

DETAIL

Excel 2016 – 2.9.8 – Remove The Table Feature

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.

DETAIL

Excel 2016 – 2.9.7 – Fill Handling a Calculation Outside a Table

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.

DETAIL

Excel 2016 – 2.9.6 – Calculate Outside Of a Table

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.

DETAIL

Excel 2016 – 2.9.5 – Explanation of Structured References

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.

DETAIL

Excel 2016 – 2.9.4 – Calculate In a Table

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

DETAIL

Excel 2016 – 2.9.3 – Insert & Delete Table Rows and Columns

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.

DETAIL

Excel 2016 – 2.9.2 – Three Quick Tips For Using a Table

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

DETAIL

Excel 2016 – 2.8.6 – Use Visible Cells to Copy Subtotals

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.

DETAIL

Excel 2016 – 2.8.5 – Create a Multi-level Subtotal

This video looks at the steps you need to take to place one Subtotal inside another Subtotal.

DETAIL

Excel 2016 – 2.8.4 – Create Two Separate Subtotals

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

DETAIL

Excel 2016 – 2.8.3 – Remove a Subtotal

Learn how to remove the Subtotal that you previously created.

DETAIL

Excel 2016 – 2.8.2 – Create a Subtotal

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

DETAIL

Excel 2016 – 2.8.1 – Why Use The Subtotal Feature?

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

DETAIL

Excel 2016 – 2.7.4 – Filter and Copy Data To Another Worksheet

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.

DETAIL

Excel 2016 – 2.7.3 – Show Unique Items From a Column

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

DETAIL

Excel 2016 – 2.7.2 – Create An Advanced Filter

When you use a normal Filter, you learn how to set multiple criteria and by default they AND each other. In this video learn how to use the Advanced Filter feature to set two sets of criteria which OR each other.

DETAIL

Excel 2016 – 2.7.1 – Why Use An Advanced Filter?

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.

DETAIL

Excel 2016 – 2.6.7 – Calculating With a 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.

DETAIL

Excel 2016 – 2.6.6 – Create Date Filters

In this video learn how to use some of the pre-defined Date Filtering features, as well as how to use the custom Date Filters.

DETAIL

Excel 2016 – 2.6.5 – Create a Top 10 Filter

In this video use the Top 10 Filter feature to find the top five most expensive cars, then change over to find the bottom five cheapest cars.

DETAIL

Excel 2016 – 2.6.4 – Create a Price Range Filter

In this video see how to Filter for all cars that fall within a specific price range.

DETAIL

Excel 2016 – 2.6.3 – Apply a Filter Using Multiple Criteria

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.

DETAIL

Excel 2016 – 2.6.2 – Create a Quick Filter Using the Right Click

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.

DETAIL

Excel 2016 – 2.6.1 – Create a Basic Filter

This video shows you how to turn on the Filter feature, carry out a single Filter, clear the Filter down, then turn the Filter feature back off again.

DETAIL

Excel 2016 – 2.5.6 – Sort by Row

Excel likes its data laid out in Columns, with Column headings. But what do you do if you have to lay it out in Rows, with Row headings? Excel doesn’t make it easy for you! Watch this video to see how it is done.

DETAIL

Excel 2016 – 2.5.5 – Create a Custom Sort

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.

DETAIL

Excel 2016 – 2.5.4 – Sort by Colour

Sunglasses at the ready? Here’s a different approach to sorting. You may have used a Fill or Font colour to highlight certain information in your data. You can now sort that data by colour.

DETAIL

Excel 2016 – 2.5.3 – Create a Multi-level Sort

In this video learn how to carry out a Sort by two columns, then change to Sorting by four columns, then remove two columns from the sort.

DETAIL

Excel 2016 – 2.5.2 – Create a Single Sort

In this video learn how to create a Single Column Sort using two methods – the right-click and the Ribbon.

DETAIL

Excel 2016 – 2.5.1 – How to Structure Your Data

Important information here! Watch this video and learn how to setup and structure data to get the best from Excel.

DETAIL

Excel 2016 – 2.4.8 – Mixed Reference Absolutes in a SUMIFS Function

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.

DETAIL

Knowledge Market in Your Business

Knowledge Market is a great source for online video tutorials. We have developed a range of topics based on the applications that you use every day.
So whether you wish to refresh your existing knowledge - or you want to learn something new - we've got everything you need when you need it and where you need it.

Membership

Our Membership options have been developed to suit your specific needs. Contact us today to find out how we can be part of your office support infrastructure.

CONTACT

Email: kelly@knowledgemarket.ie

Performance Support and Learning for:
Microsoft Office – Excel, PowerPoint, Project, Word

Privacy & Cookies

This website uses Cookies. Continued use of the site will be deemed as your acceptance of this necessity.

Please read our Privacy Policy