Excel 2013 Intermediate

An accessible course for people who know Excel basics and wish to extend their skills and become more confident users.
4.2 (18 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,001 students enrolled
$19
$45
58% off
Take This Course
  • Lectures 67
  • Length 5.5 hours
  • Skill Level Intermediate Level
  • Languages English
  • Includes Lifetime access
    30 day money back guarantee!
    Available on iOS and Android
    Certificate of Completion
Wishlisted Wishlist

How taking a course works

Discover

Find online courses made by experts from around the world.

Learn

Take your courses with you and learn anywhere, anytime.

Master

Learn and practice real-world skills and achieve your goals.

About This Course

Published 2/2016 English

Course Description

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.

Course update July 2016: Using the CHAR function

What are the 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.)

What am I going to get from this course?

  • Use Microsoft Excel 2013 more confidently and effectively, gain a deeper insight into functions, formulas, charts and pivot tables.

What 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.

What you get with this course?

Not for you? No problem.
30 day money back guarantee.

Forever yours.
Lifetime access.

Learn on the go.
Desktop, iOS and Android.

Get rewarded.
Certificate of completion.

Curriculum

Section 1: Getting started
Welcome
Preview
04:34
2. Downloading the course files
Preview
01:59
Section 2: GCS/01: Text functions
02:46

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

03:06

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

04:24

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

03:16

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

02:41

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

06:38

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

Section 3: GCS/02: Conditional functions
05:19

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

03:05

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

06:43

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.

06:51

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.

05:25

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.

Section 4: GCS/03: Conditional number crunching functions
06:01

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

03:19

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

Section 5: GCS/04: LOOKUP functions
07:14

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

02:49

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

05:46

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

Section 6: GCS/05: Date and time functions
04:03

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

03:45

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

05:52

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

Section 7: GCS/06: Advanced formatting
02:48

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

03:29

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

04:04

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

09:43

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

08:22

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.

Section 8: GCS/07: Managing multiple workbooks and worksheets
02:42

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.

05:20

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

04:54

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.

06:56

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.

Section 9: GCS/08: Working with charts
05:47

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.

06:46

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

06:13

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

08:52

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

05:19

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.

08:33

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.

05:36

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.

Section 10: GCS/09: Sparklines and graphics
04:05

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

10:07

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.

Section 11: GCS/10: Working with Excel tables
06:57

Tabular data refers to data which consists of column headings with an entry under each column; when you are working with this type of data, formatting it as an Excel table makes data entry much easier.

05:17

You can create an Excel table either from scratch, or by converting an existing range to a table.

03:36

You can enter data into an Excel table either manually, or by copying and pasting it from somewhere else.

03:54

Excel's total row facility will generate an extra row, below the table, which enables you to analyze any of the columns.

02:50

When you enter a formula into an Excel table, you will notice that Excel will normally refer to columns, rather than to individual cells.

08:51

One of the benefits of creating a chart based on an Excel table is that, as you add more data to the table, your chart will expand to accommodate the new data.

Section 12: GCS/11: Sorting data
03:13

When sorting data in Microsoft Excel, you can either perform a single column sort; or you can perform a multi-level sort, using several columns.

04:00

In order to perform a multi-level sort, using several columns as the key, you need to use Excel's Custom Sort feature.

04:29

There are times, when sorting, when you want to override Excel's normal ascending or descending order, and simply use your own preferred order.

03:24

There may be occasions, when sorting data, where you want to sort your data by moving columns left or right.

03:18

Excel does not contain a feature for automatically sorting your data into a random order; it is therefore necessary to use a workaround when you want to totally randomize the order of rows of data,

Section 13: GCS/12: Filtering data
05:17

The simplest form of filtering is to specify precisely the item or items which you want to remain visible.

04:01

As well as using the checkboxes to filter individual items, it is also possible, and often faster, to use the search box.

03:42

Whenever you click on the filter drop-down menu on a text column, Excel automatically displays the Text Filters sub-menu. Some Text filter options are equivalent to using the search box; but there are some options which are unique.

04:10

Whenever you click on the filter drop-down menu on a column containing numbers, Excel automatically displays the Number Filters sub-menu which contains options such as Equal, Does not Equal, Greater than, etc.

03:53

Whenever you click on the filter drop-down menu on a column containing dates, Excel automatically displays the Date Filters sub-menu. As well as options such as Greater than and Between, many of Excel's date filters use calculations which are based on the current date.

Section 14: GCS/13: Outlining and subtotalling
06:27

Rather than manually hiding and showing columns, by outlining your data, you can show and hide columns with one click.

04:38

As well as manually outlining data, it is sometimes possible to have Excel automatically generate the outline for you.

01:43

Ouline symbols can sometimes take up quite a lot of room; the good news is that there is a very easy shortcut for showing and hiding the outline symbols.

07:12

A single level subtotal creates a three level outline: level one shows only grand totals; level two shows subtotals; and level three shows all rows.

04:51

A two-level subtotal creates a four level outline: level one shows only grand totals; level two shows level one subtotals; level threee shows level two subtotals; and level four shows all rows.

Section 15: GCS/14: Introduction to pivot tables
05:59

This lesson discusses the benefits offered by pivot tables, in comparison to Excel's subtotal feature.

06:07

This lesson shows how to create a pivot table based on an ordinary Excel range.

06:23

This lesson shows how to create a pivot table based on an Excel table.

10:52

This lesson shows how to make your pivot tables more sophisticated and useful by creating hierarchical structures which enable each user to drill down to the level of detail which is most useful for them.

Section 16: GCS15: 15th July 2016 Update - The CHAR function
04:00

Essentially, the CHAR function is used to insert an ASCII character. ASCII characters were used extensively in th early days of computing to insert character codes; and the ones which are most useful in Excel are the ones which can't simply be entered via the keyboard.

03:59

In this video, we'll look at using Excel's CHAR function to produce a useful result. What we want to do is to insert an arrow pointing up or down, depending on whether revenue went up or down, relative to the previous day.

04:10

We end this update by adding some conditional formatting to the worksheet that we created in the last video.

Students Who Viewed This Course Also Viewed

  • Loading
  • Loading
  • Loading

Instructor Biography

Grant Gamble, 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 VBA, web development and Adobe Creative Suite automation.

Ready to start learning?
Take This Course