Intermediate

This page introduces some more advanced Excel topics that will help you push your Excel skills even further. All the lessons are about five minutes long (or less) so it won't take long to learn more, and do more.

How to update or add new data to an existing Pivot Table in Excel

When you create a new Pivot Table, Excel either uses the source data you selected or automatically selects the data for you. But data changes often, which means you also need to be able to update your pivot tables to reflect the new or changed data. This lesson shows you how to update existing data, and add new data to an Excel pivot table.

How to lookup the name of the highest selling sales person in the month

In this lesson, we look at a specific example where you have a table of sales data, and you need to find out the name of the person who had the highest sales for the month. It's one of those things that seems like it should be easy until you actually try to do it. The solutions we present here are not the only way of achieving this, but the do have the advantage of solving the problem with a single formula. The methods here could also be used for a variety of other applications as well.

Use Conditional Formatting to highlight due dates in Excel

This lesson shows you how to use Conditional Formatting in Excel to format cells containing dates that are in the past, using a conditional formatting rule that compares the date in a cell with today's date, and formats it a different colour if it is in the past. We'll also extend this conditional formatting example to check the value of another cell as part of our criteria for applying the formatting.

How to use INDEX MATCH instead of VLOOKUP

This lesson shows you how to write formulas using INDEX and MATCH to let you perform lookups that VLOOKUP can't, and which run much faster on large lookup tables than VLOOKUP. This lesson explains how INDEX and MATCH work on their own, and then shows you how to write an INDEX MATCH formula that can look left as well as right, and performs much faster than VLOOKUP on large tables.

Grouping by Date in an Excel Pivot Table

This lesson shows you how to group data in your pivot table by date. You can group by day, week, month, quarter or year. If your date fields include a time value, you can also group by seconds, minutes or hours. You'll also learn how to collapse and expand data groups in your pivot table so you can quickly see a summary of your data.

How to use Autofilter in Excel

Autofilter is one of the most powerful features of Excel if you need to work with data in tabulated (table) format. It lets you treat a range of cells as a table and then filter out certain rows based on different criteria. It is very powerful if you need to "mine" data in a list and find out specific information about the data in that list. This tutorial covers how to set up a data table in Excel to use with Autofilter, and also shows you how to enable Autofilter and use it for basic filtering. This lesson is applicable for all versions of Excel (including Excel for Mac) although the visual presentation of the options may change from version to version.

Pages
Subscribe to RSS - Intermediate