Watch this lecture to get an overview of the top ten tips in this section. While there are a lot of functions and tools available in Excel, there are a few that are extremely helpful when you use Excel on a frequent basis. In this tips section I’ve included topics that are probably not in a basic excel training and also if you’ve taught yourself Excel and worked yourself up to advanced status, you might have missed some of these features.  

If you are already quite advanced in Excel, take this tips section as a "filling in the gaps" type of exercise. 

Take whichever tip you find useful and try to immediately use it to your own Excel spreadsheets.

Why are Excel keyboard shortcuts useful? Because they let you finish your tasks faster. Watch this lecture to find out which Excel shortcut keys are real time-savers. I share my most used ones. You can also print out the short-cut list from my Demo workbook and also use the link I provide to find the list of all the Excel shortcut keys.

This lecture and the next is called “Master the simpler tasks”, because there are a handful of very useful tools that can make working with Excel much easier. You might be familiar with some of these from a previous basic training, or you happened to stumble on the feature by yourself. I just had to be sure you knew these, because it’s important for any Excel user to be familiar with them. 

When you work with large Excel workbooks or worksheets it’s important to know some tips and tricks on how to efficiently cruise between various sheets and work simultaneously with different workbooks. Watch this lecture to find out how.

In most cases when you start to work at a new company you take over the existing Excel workbooks of your predecessor and continue to update and expand on the workbook. Normally these files are quite large and contain many tabs and it’s not always clear if there is hidden information such as  comments, personal information, hidden rows  etc in the file. Some hidden information can be deliberate whereas some might be unintentional. To find all this out manually can be time consuming. That’s where you can use the Inspect document feature. A great tool to use before distributing workbooks.

Protecting an entire workbook, sheets or specific ranges within a workbook, is a good way to make sure templates and common files are not unintentionally or intentionally changed. In this lecture, you will find out the best methods to do this.

In this lecture learn how to best import your data into Excel. Specially those extracts you get from your ERP systems. You will also learn how to import data from the web and how to automatically update your query based on your own specifications.

Once you have mastered Excel functions and you start to use more complex formulas and more robust spreadsheet, you are likely to run into two issues:

1. You have mistakes in your formulas but because they are too long and complex you can’t find where the problem is

2. The result of your Excel formula throws an error for some cells and you’re not sure why

In this lecture I’m going to give you a tour of the formula audit tools that are available and how they can help you track and debug your formulas

Custom formatting is a great way to manipulate the presentation of your data without actually changing the data. Many Excel users try to use intermediary steps to change the look of the data. These steps can generally be avoided with the use of custom formatting.  

In the Demo workbook, I have included a table with the list of characters and what they mean and how they can be used. You can print it out and refer to it when you need to design your custom formatting.

The purpose of goal seek is when you know the end result but don’t know what the input should be to get to your result. Goal seek is one of the simplest tools to use. You just need to know how. Find that out, in this lecture.

In this lecture find out how to create and work with pivot tables. They are a great tool for summarizing and analyzing data.

In this lecture, I will show you some more advanced tips and tricks with regard to the PivotTable. This includes how to create calculated fields, to use slicers, PivotCharts and a little taste for creating advanced charts & dashboards.

PivotTables are a great tool for quick analysis and also as a data feeding tool for your dashboards. People however, rarely use these as an integrated table in the actual reports that they print out. Even though the formatting of the Pivot tables has majorly improved in the past years, they’re still not seen as visually pleasant for the final report, so they’re generally integrated in the back-end and a nicer table that references the pivot is used for the front end. In this lecture I will show you great design tips on how to optimize your pivot tables for the final report.

In this lecture, I will take you through the steps you need to follow to improve any slow-calculating Excel workbooks.

In this lecture, I share my view of Excel's potential and limitations. If you work in a bigger company that has BI systems in place, the common question that arises is usually: Which system do we use to create this dashboard? Or how do we track and report these KPIs? Do we invest in a new tool or use what we have? Sometimes the answer is sitting write on your desktop. It can be Excel.

The downloadable PDF is a detailed documentation (over 60 pages) of the top 10 Excel Tips we cover in this section. Together with the Excel Workbooks which can be downloaded from the Resources tab, you should have all the material you need to practice and master the techniques.  

Excel offers an incredible range of formulas that enable dynamic analysis of data. In this section I will introduce you to the top 10 Excel functions I believe every user who deals with Excel on a frequent basis should know. With these functions you are well equipped with the knowledge you need to handle pretty much any situation that requires you to analyse data or grab a piece or pieces of information from a large table.

The inclusion of the "IFS" formulas since Excel 2007 was a major gain for Excel users. Why? Because they allow you to easily add exceptions to your sum, average and count calculations. Don’t make your life more difficult by trying to use excel filters to get your sum. Use the SUMIFS formula instead.

You probably know the IF function from a basic Excel training, but what you might not be familiar with is to use nested formulas, meaning more than one IF in a formula. Because the reality is, there are always exceptions to the exceptions. Find out more in this lecture

The IFERROR function provides an elegant and simple way to hide errors in reports. It's a must have to anyone creating reports in Excel.

Most relatively advanced Excel users are familiar with the VLOOKUP function. I have included it in this section to make sure you know it. It's a great formula because it can search for a value or criteria in a table and return a respective value from an adjacent column.  HLOOKUP works exactly in the same way except that it works horizontally rather than vertically. The “V” is for vertical and “H” for horizontal lookups.

If there is one formula that you take away with you from this training, it should be the INDEX and MATCH function. The VLOOKUP formula has its limitations and that’s where the  INDEX and MATCH function comes to the rescue.

In real life, your Excel files are always more complex than the ones in the training. In this lecture I will show you how to tackle real-case scenarios where you have a complex table and you need to lookup values from this table to provide a comprehensive report.

Excel Text formulas can save a great deal of time cleaning up your data and getting them in the format you need. Sometimes you need to manipulate or clean up the descriptions of your products, your customers or account descriptions.  You might need to cut out, substitute or replace a part of your product codes. In this lecture I will show the most useful Excel text formulas.

In this lecture I will show you how to use the text formulas we learnt in the previous lecture to create more flexible reports.

Excel's Date functions are great when you need to create a timeline for your project, or distribute a reporting timetable. I often see people refer to a calendar when they create these reports. There is a big chance of making mistakes. Use Excel's Date function instead. In this lecture, I will introduce you to the ones that consistently save me time.

OFFSET is an interesting formula and it’s one that can do so much more than meets the eye. Chances are you’ve been needing this formula because it can make so many calculations dynamic.

Did you ever need to organize your data into categories and then report on these categories? That's where the FREQUENCY as well as the SMALL and LARGE functions come in.

SUMPRODUCT is one of the most powerful formulas in Excel. Many people use it for it's classical purpose. In this lecture, I will take you beyond this and show you ways of using SUMPRODUCT that can help you avoid many "workarounds" you've been doing.

The downloadable PDF is a detailed documentation (over 30 pages) of the top 10 Excel FORMULAS we cover in this section. Together with the Excel Workbooks which can be downloaded from the Resources tab, you should have all the material you need to practice and master the techniques.  

A real-life example where smart, dynamic formulas can help. In this lecture I show how you can calculate YTD (year to date) values for volumes and prices using SUM, SUMPRODUCT & OFFSET functions.

In this lecture we have the task to create a dynamic sum based on user selection. The user will select the column header name and we need to provide the sum of the columns and also make exceptions.

In this lecture I will show you an alternate method to sum different columns based on user user selection. The method uses the SUMPRODUCT formula. I also compare the speed to the different formulas to see which one performs better on large data sets.

Don't forget: To be a real master in Excel, you have to apply these methods to practical problems. This forces you to use the knowledge you learnt in the training in creative ways to help improve your own spreadsheets.