Excel

Excel 2016 – 3.13.2 – Create a One-input Data Table

This video brings you through the steps required to create a One-input Data Table.

DETAIL

Excel 2016 – 3.13.1 – About Data Tables

This video explains what a One-input Data Table and a Two-input Data Table are. It also shows the results after the Data Table feature has been applied.

DETAIL

Excel 2016 – 1.7.11 – Group Worksheets

This feature is one of those ‘must haves’. It allows you to carry out multiple actions, to multiple sheets, at the same time.

DETAIL

Excel 2016 – 1.12.5 – Print Comments or Notes

Back in Lesson 2 you learned how to create Comments or Notes; however, they don’t print automatically. This video shows you how to make them print.

DETAIL

Excel 2016 -1.13.6 – Delete a Style

We’ve created a Style that, I’m sure, you don’t want to keep. So now we’ll look at how to remove a Style, completely.

DETAIL

Excel 2016 – 1.13.5 – Return to the Normal Style

This shows you how to return all your data back to the Normal Style.

DETAIL

Excel 2016 – 1.13.4 – Copy a Style to Another Workbook

When you create your own Style, or edit an existing Style, that change takes place within the current Workbook only. This video shows you how to copy those changes to other Workbooks.

DETAIL

Excel 2016 – 1.13.3 – Create a Style by Example

Do you want to create your own Styles rather than use the built-in Styles? No problem, this video shows you how.

DETAIL

Excel 2016 – 1.13.2 – Edit a Style

This shows you how to edit a default Style and how that change automatically updates on all pages in the Workbook, that are using that Style.

DETAIL

Excel 2016 – 1.13.1 – Apply Cell Styles

This shows you how to apply the pre-made default Styles contained in Excel.

DETAIL

Excel 2016 – 1.2.12 – Create Comments or Notes

You can add comments to cells to help users to work with your data. Depending on the version of Excel that you are using, you might see the choice as Comments – or Notes – or both.

DETAIL

Excel 2016 – 1.8.13 – Custom Date Formatting

Dates are formatted separately to figures and text and this video will show you how to apply custom formatting to dates so that you can display them in the way that you want to see them.

DETAIL

Excel 2016 – 1.8.12 – Custom Format Example

This video shows a practical use of a custom format to display both text and a figure in the same cell. Why is this different to just typing our text directly into the cell? Well, done this way, the cell will remain calculable.

DETAIL

Excel 2016 – 1.8.11 – Create Custom Formats

Now we’re going to create and apply the custom formats that we saw in the last video.

DETAIL

Excel 2016 – 1.8.10 – About Custom Formatting

This video explains what custom formatting is, and that it can be applied to positive figures, negative figures, zero values and text – all at the same time.

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 – 3.5.9 – Pivot Sorting Issue

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!

DETAIL

Excel 2016 – 3.5.8 – Using Calculated Item

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

DETAIL

Excel 2016 – 3.5.7 – Using Calculated 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.

DETAIL

Excel 2016 – 3.5.6 – Using % of Row Total

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

DETAIL

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.

DETAIL

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.

DETAIL

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.

DETAIL

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.

DETAIL

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.

DETAIL

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.

DETAIL

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.

DETAIL

Excel 2016 – 3.4.1 – Group by Numbers

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

DETAIL

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.

DETAIL

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.

DETAIL

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 Open on Refresh

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.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.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.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.5.9 – Pivot Sorting Issue

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!

DETAIL

Excel 2016 – 3.5.8 – Using Calculated Item

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

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