Excel Intermediate Workshop
4.9 (29 ratings)
Instead of using a simple lifetime average, Udemy calculates a course's star rating by considering a number of different factors such as the number of ratings, the age of ratings, and the likelihood of fraudulent ratings.
2,605 students enrolled
Wishlisted Wishlist

Please confirm that you want to add Excel Intermediate Workshop to your Wishlist.

Add to Wishlist

Excel Intermediate Workshop

A hands-on Microsoft Excel course for people who know Excel basics and wish to become more confident Excel users.
4.9 (29 ratings)
Instead of using a simple lifetime average, Udemy calculates a course's star rating by considering a number of different factors such as the number of ratings, the age of ratings, and the likelihood of fraudulent ratings.
2,605 students enrolled
Created by Grant Gamble
Last updated 12/2016
English
English [Auto-generated]
Current price: $10 Original price: $45 Discount: 78% off
5 hours left at this price!
30-Day Money-Back Guarantee
Includes:
  • 6 hours on-demand video
  • 3 Supplemental Resources
  • Full lifetime access
  • Access on mobile and TV
  • Certificate of Completion
What Will I Learn?
  • Use Microsoft Excel 2013 more confidently and effectively, gain a deeper insight into functions, formulas, charts and pivot tables.
View Curriculum
Requirements
  • To complete this course, students will need to have a copy of Microsoft Excel 2013 installed on their PC. If you do not own a copy, why not download the 30 day trial version of Excel 2016 from Microsoft's website. (The differences are not significant.)
Description

This is a practical, hands-on course for Excel middleweights looking to gain more confidence in using Microsoft Excel. It provides step-by-step practice in building Excel 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 Excel 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 Excel 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.

Who is the target audience?
  • This course is suitable for those with a basic knowledge of Excel, who have perhaps had some basic training in using the program, or those who are self-taught.
Students Who Viewed This Course Also Viewed
Curriculum For This Course
74 Lectures
06:13:22
+
Getting started
2 Lectures 06:33
+
GCS/01: Text functions
6 Lectures 22:51

Sometimes when cleaning up data you don't need to use formulas; just Flash Fill.

Preview 02:46

Excel's TRIM function removes unwanted spaces in cell entries.

Preview 03:06

Excel has three functions for changing the case of text: UPPER, LOWER AND PROPER.

Preview 04:24

Excel's LEFT and RIGHT functions are used to extract data from cell entries starting from the left or right.

GCS/01-4: The LEFT and RIGHT functions
03:16

The MID function is used to extract text from the middle of a cell entry.

GCS/01-5: The MID function
02:41

The CONCATENTATE function allows you to string together a cell entry by combining data from other cells, as well as literal text.

GCS/01-6: The CONCATENATE function
06:38
+
GCS/02: Conditional functions
5 Lectures 27:23

The IF function allows you make the value in a cell dependent on a logical test.

GCS/02-1: The IF function
05:19

The IFERROR function allows you to replace Excel error values with something more user-friendly.

GCS/02-2: The IFERROR function
03:05

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.

GCS/02-3: Nested IF statements
06:43

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.

GCS/02-4: The OR function
06:51

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.

GCS/02-5: The AND function
05:25
+
GCS/03: Conditional number crunching functions
2 Lectures 09:20

Excel's COUNTIF function is used to count the cells within a given range in which a certain condition is satisfied.

GCS/03-1: The COUNTIF function
06:01

The SUMIF function combines Excel's SUM and IF functions; it allows you to create a conditional total.

GCS/03-2: The SUMIF function
03:19
+
GCS/04: LOOKUP functions
3 Lectures 15:49

This lesson shows how to use Excel's VLOOKUP function when you need an exact match to the lookup value.

GCS/04-1: VLOOKUP exact match
07:14

This lesson shows how to use Excel's VLOOKUP function when you need to identify the value which most closely matches the lookup value.

GCS/04-2: VLOOKUP approximate match
02:49

This lesson shows how to use Excel's INDEX and MATCH functions in combination to perform more flexible lookup operations impossible with VLOOKUP.

GCS/04-3: Using INDEX and MATCH
05:46
+
GCS/05: Date and time functions
3 Lectures 13:40

Excel's TODAY and NOW functions are used to extract the date and time from your computers clock.

GCS/05-1: The TODAY and NOW functions
04:03

This lesson looks at using Excel's AutoFill feature to insert a sequence of dates, but telling Excel to miss out weekends.

GCS/05-2: Inserting dates using AutoFill
03:45

Excel's NETWORKDAYS function allows you to make calculations which take into account both weekends and bank holidays.

GCS/05-3: The NETWORKDAYS function
05:52
+
GCS/06: Advanced formatting
5 Lectures 28:26

This lesson looks at example of rotating headings in order to make columns narrower.

Preview 02:48

When you want to enter several words, a sentence or paragraph in a cell, Excel gives you the option of using text wrap.

GCS/06-2: Text wrap
03:29

This lesson shows how to use the format painter effectively; how to copy formats from one location to as many other ranges as required.

GCS/06-3: Using the Format Painter
04:04

Excel's conditional formatting feature allows you to make the appearance of a cell dependent on its contents.

GCS/06-4: Conditional formatting
09:43

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.

GCS/06-5: Creating cell styles
08:22
+
GCS/07: Managing multiple workbooks and worksheets
4 Lectures 19:52

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.

GCS/07-1: Setting the number of sheets in new workbooks
02:42

Excel's group mode facility allows you modify multiple worksheets simultaneously; it's particularly useful when creating templates.

GCS/07-2: Working in group mode
05:20

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.

GCS/07-3: Viewing multiple workbooks
04:54

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.

GCS/07-4: Moving and duplicating sheets
06:56
+
GCS/08: Working with charts
7 Lectures 47:06

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.

GCS/08-1: Anatomy of an Excel chart
05:47

The column chart is Excel's default chart type; it is the most versatile and the most widely used.

GCS/08-2: Creating a column chart
06:46

Excel line charts are ideal for displaying the progress of data over time.

GCS/08-3: Creating a line chart.
06:13

Excel pie charts are ideal for showing the breakdown of a set of data.

GCS/08-4: Creating a pie chart
08:52

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.

Preview 05:19

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.

GCS/08-6: Creating a doughnut chart
08:33

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.

GCS/08-7: Creating a bar chart
05:36
+
GCS/09: Sparklines and graphics
2 Lectures 14:12

Sparklines are miniature charts, which Excel displays inside a cell, and which explain trends in adjacent data.

GCS/09-1: Creating sparklines
04:05

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.

GCS/09-2: Using formulas with graphics
10:07
7 More Sections
About the Instructor
Grant Gamble
4.5 Average rating
379 Reviews
12,882 Students
6 Courses
Developer, trainer and author

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.