This is a practical, hands-on course for Excel middleweights looking to gain more confidence in using the program. It provides step-by-step practice in building formulas with useful text functions like LEFT, RIGHT, MID, PROPER and CONCATENATE; conditional functions like IF, IFERROR, SUMIF AND COUNTIF; lookup functions like VLOOKUP, INDEX and MATCH; and date functions like NETWORKDAYS. The course also covers charts, sparklines, conditional formating, and graphics. It then moves on to look at all of Excel's key facilities for working with tabular data: customized sorting and filtering, subtotalling and the use of Excel tables and pivot tables. All of the worksheets used by the trainer are available to students, enabling them to follow along and practice all of the techniques being demonstrated.
Last course update: December 2016
In the most recent update to this course, you will construct a basic invoicing system and at the same time get a chance to practice using advanced formulas, data validation and worksheet protection.
Sometimes when cleaning up data you don't need to use formulas; just Flash Fill.
Excel's TRIM function removes unwanted spaces in cell entries.
Excel has three functions for changing the case of text: UPPER, LOWER AND PROPER.
Excel's LEFT and RIGHT functions are used to extract data from cell entries starting from the left or right.
The MID function is used to extract text from the middle of a cell entry.
The CONCATENTATE function allows you to string together a cell entry by combining data from other cells, as well as literal text.
The IF function allows you make the value in a cell dependent on a logical test.
The IFERROR function allows you to replace Excel error values with something more user-friendly.
In order to cater for more than two eventualities, Excel allows you to combine several IF statements by nesting one IF inside another, as its argument.
Excel's OR function is used to create a composite logical test, in which only one of a series of possible tests needs to be true in order for the overall test to be true.
Excel's AND function is used to create a composite logical test, in which several possibilities all have to be true in order for the overall test to be true.
Excel's COUNTIF function is used to count the cells within a given range in which a certain condition is satisfied.
The SUMIF function combines Excel's SUM and IF functions; it allows you to create a conditional total.
This lesson shows how to use Excel's VLOOKUP function when you need an exact match to the lookup value.
This lesson shows how to use Excel's VLOOKUP function when you need to identify the value which most closely matches the lookup value.
This lesson shows how to use Excel's INDEX and MATCH functions in combination to perform more flexible lookup operations impossible with VLOOKUP.
Excel's TODAY and NOW functions are used to extract the date and time from your computers clock.
This lesson looks at using Excel's AutoFill feature to insert a sequence of dates, but telling Excel to miss out weekends.
Excel's NETWORKDAYS function allows you to make calculations which take into account both weekends and bank holidays.
This lesson looks at example of rotating headings in order to make columns narrower.
When you want to enter several words, a sentence or paragraph in a cell, Excel gives you the option of using text wrap.
This lesson shows how to use the format painter effectively; how to copy formats from one location to as many other ranges as required.
Excel's conditional formatting feature allows you to make the appearance of a cell dependent on its contents.
Excel's cell styles feature allows you format cells quickly and efficiently and to modify the formatting of entire worksheets, simply by modifying style attributes.
When you create a new workbook in Excel, by default it will contain just one sheet. However, Excel also allows you to specify the number of worksheets which each new workbook will contain.
Excel's group mode facility allows you modify multiple worksheets simultaneously; it's particularly useful when creating templates.
Whenever you have more than one workbook open in Excel, there are a number of facilities which enable you to view them simultaneously, as tiled windows, side by side.
Excel offers two techniques for moving and duplicating sheets: you can either use the drag and drop technique; or you can right-click on a tab and use the Move or Copy command.
There are two ways of implementing a chart: you can either have an embedded chart; or, you can have a standalone chart, a special type of worksheet which consists entirely of the chart.
The column chart is Excel's default chart type; it is the most versatile and the most widely used.
Excel line charts are ideal for displaying the progress of data over time.
Excel pie charts are ideal for showing the breakdown of a set of data.
The pie of pie chart is a variation on a pie chart which shows you the breakdown of a given data set which differentiates between major players and minor players.
One of the limitations of a pie chart is that you can only plot one data series. However, Excel does have a variant of the pie chart, called a doughnut chart, which is a workaround for this limitation.
A bar chart is, essentially, a column chart with a horizontal orientation. It's particularly useful when plotting data which includes very long category names.
Sparklines are miniature charts, which Excel displays inside a cell, and which explain trends in adjacent data.
All Microsoft programs allow you to insert graphics and vector shapes; but, in Excel, you have the ability to link the text displayed in worksheet to the value of a cell.
Grant Gamble is an experienced IT trainer, developer, consultant and author able to deliver a wide range of training courses. He has a vast experience of delivering public and on-site IT training content at different skill levels, to groups of varying sizes.
His UK company G Com Solutions Limited provide IT training courses and consultancy to a wide range of UK and international clients. His speciality is running week-long, intensive training workshops on topics like Microsoft Power BI, VBA, web development and Adobe Creative Suite automation.