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.
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.
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.
When creating a chart in Excel, Excel will default to inserting your new chart on the same worksheet that contains the data you created it from. This lesson shows you various options for moving or resizing your chart so it looks how you want it to, where you want it to be.
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.
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.
This lesson shows you a way to calculate the number of times a single character occurs in a cell in Excel, and provides a real-life example where I needed to split a column of cells containing part numbers into individual components for each part number.
The MATCH() function allows you to find the position of a value in a list. For example, in a list of weekdays starting with Monday, MATCH() would return a value of 3 for Wednesday. This lesson explains how to use the MATCH() function in Microsoft Excel, explains where you might use it, and provides a real world example of the MATCH() function in action.
Sometimes you need to count the number of cells in a spreadsheet that contain a value or set of values. The COUNTIF function allows you to do this by counting only those cells in the range that meet the criteria you set. This lesson explains how to use COUNTIF, and provides an example of how you can use it.