NEW! EXCEL 2016: Excel from beginner to professional
4.5 (42 ratings)
Course Ratings are calculated from individual students’ ratings and a variety of other signals, like age of rating and reliability, to ensure that they reflect course quality fairly and accurately.
1,403 students enrolled

NEW! EXCEL 2016: Excel from beginner to professional

Learn Excel 2016 and the most important and popular functions. Learn to use Excel like a pro with this Excel Course
4.5 (42 ratings)
Course Ratings are calculated from individual students’ ratings and a variety of other signals, like age of rating and reliability, to ensure that they reflect course quality fairly and accurately.
1,403 students enrolled
Created by Stephen Beak
Last updated 5/2016
English [Auto-generated]
Current price: $13.99 Original price: $19.99 Discount: 30% off
5 hours left at this price!
30-Day Money-Back Guarantee
This course includes
  • 3 hours on-demand video
  • Full lifetime access
  • Access on mobile and TV
  • Certificate of Completion
Training 5 or more people?

Get your team access to 4,000+ top Udemy courses anytime, anywhere.

Try Udemy for Business
What you'll learn
  • Use Excel in a professional environment
  • Create and manage complicated spreadsheets
  • Manage constantly changing data
  • Understand what Excel is and how it really works
  • Create a 3D heat map to plot sales geographically
  • Set up and manage Excel Pivot Tables
  • You will need to have a copy of Excel 2010 / 2013 or 2016. 2016 is recommended, but a lot of this is backwards compatible

By Completing this course, you will receive a verifiable Certificate of Completion.

This course has cut down on the fluff and empty space to keep it shorter, but still cover a wide range of topics. Designed for people starting out with Excel, it also covers some of the more complicated and important areas like Pivot Tables.

This Excel course is recorded completely using Excel 2016, the latest version of Excel offered in the Microsoft Office 2016 suite.

Some of the topics include (but not limited to)

  • Creating your first workbook
  • Referencing from other sheets within a book
  • Quick Charts
  • Quick Sorting
  • Flash Fill
  • Named Ranges
  • Excel Sheet Management (moving, colouring, creating)
  • Creating 3D maps using address data in a list
  • Popular Excel Functions like SumIf, VLookUp, Left, Right, Mid, Len and more
  • Power Excel functionality like nested formulas
  • Using slicers to quickly sort and make spreadsheets look good
  • A complete set of Pivot Table videos

I aim to add 4 new lectures every month to the course, and would love to have your feedback. So once you have signed up and completed the training, let me know what you would like to learn next!

Who this course is for:
  • Anyone who needs to manage large amounts of information with as much efficiency as possible
  • Anyone looking to join an admin related position, or enhance their current employability
  • General PC literacy
Course content
Expand all 37 lectures 02:58:51
+ Jumping Straight In
7 lectures 37:42

Excel 2016 can create and manage huge amounts of information, and filtering things with just a few clicks. But sometimes you want a much smaller and simpler Excel spreadsheet to quickly look over some information and use formulas. In this lesson, we jump right in to Excel 2016 by setting up a small Excel spreadsheet, and introduce you to how it all works.

Preview 05:57

Microsoft Excel has a lot of fantastic templates to help get you started, or perhaps just to use as inspiration. If you are running a small business and need something professional, or perhaps you are in a larger company and want to let your excel spreadsheets stand out from your competitors... sorry... colleagues... then this is something you will want to spend a bit of time with!

Preview 02:28

Besides being able to handle incredible amounts of information in a large table, Excel allows you to build in formula to edit the information live. The best Excel 2016 sheets will require very little work or maintenance once set up properly. This video goes through the creation process of a very simple Excel 2016 spreadsheet and talks you through the thought process.

Preview 09:16

This lecture covers the fastest way to add a chart to your Excel 2016 spreadsheet. We use the quick analysis tool inside Excel to quickly highlight certain sections of our Excel sheet and create a good looking chart. While on a smaller spreadsheet it is often just used to assist in displaying information, these become necessary when looking at large Excel spreadsheets because it creates a graphic way of showing data, and is easier to see important pieces of information.

Quick Charts - How to quickly add charts to your Excel 2016 Spreadsheet

Time to improve your life with quick sorting! If you have a long list of information and you need to quickly sort it by value or in alphabetical order, this can be done in seconds. Even huge Excel documents can benefit from the Quick Sorting feature.

Quick Sorting - How to quickly sort your Excel 2016 Spreadsheet

Excel's "flash fill" feature is far more powerful and useful than many people anticipate. By using a set of algorithms, it predicts how a sequence would continue and displays it as an option, if it is correct you can click and populate massive amounts of data.

This video covers how to use it and what to look for in Excel 2016. Think of it as predictive text on steroids. 

Flash Fill - Excel 2016's incredible time saving fill feature

Excel is fairly good at predicting what sort of information you have put inside a cell in your spreadsheet. For example, if you enter a number it sees it as a value and can be used in mathematical formula. Sometimes Excel cannot predict what you wanting to enter. For example, if you were to enter a phone number like "012 345 6789" it would see it as a value and not as a text string.

This can cause formatting issues for you down the line. Or perhaps the number you entered is a percentage and not a value, or perhaps it has a monetary value. This video discusses how you can edit the data type inside your Excel spreadsheet to make sure that everything works exactly the way you want it to.

Formatting Data Types in Excel 2016 - Numbers, Text, Percentages and more
+ Increasing Efficiency to save time in Excel
11 lectures 45:45

Copy and paste, one of the most wonderful things in Windows since before we even had Windows. But it gets even more useful in Excel. We are able to copy information of one data type, and paste it as another. We can repeat, we can fill and we can even copy formulas that will automatically update depending on where you paste them. Excel 2016's copy and paste feature is very well designed and will save you a lot of time.

This video takes you through the basics on how it is different from the copy and paste in other products, and how to best use it in Excel to save as much time as possible.

Copy Paste - How to increase your efficiency in Excel 2016

Transpose is a great feature in Excel that allows you to take information from a vertical to a horizontal position. If you have an Excel sheet with a table where your column headings are important, but you need the table to be rotated so columns become rows you can use the transpose feature very easily. 

Transposing with Copy Paste

As your Excel Spreadsheet starts to grow, you will find several columns that are not necessary for people to see, but you need the information there so formulas throughout your Excel Workbook can refer to them. This is where hiding columns becomes very useful. You can clean up your Excel sheet without losing any of the data.

Inserting, Deleting and Hiding Rows in Excel 2016

When you copy and paste cells that have formulas in them, Excel will automatically adjust the cells reference in those formulas. As an example, if you have a formula in C5 that refers to C1, then you copy C5 and paste it to C6, Excel will automatically update the reference from C1 to C2. 

But sometimes you don't want that, sometimes you want Excel to leave the referenced formula alone completely. This is where learning Absolute Reference comes in handy.

Absolute Reference - Copy and Pasting with Absolute Reference in 2016

Up until now, we have been using the Copy and Paste feature in Excel by only moving one section at a time. We can open up the clipboard, and copy separate sets of data in to it, and then pick and choose what we want to place down. 

Exploring the Clipboard using Multiple Copy and Paste in Excel 2016

Named Ranges allows you to select an area of data in your Excel Spreadsheet and give it a name. Instead of referring to a range as A1::C5 (every cell from those two points as a square) we can call it anything we want. We can even reference our named ranges in formulas. This is especially useful if you think that at a later time you will need to increase or decrease the area, and don't want to have to go and edit every single formula that refers to that range.

Excel is supposed to make your life easier, so everything we do is trying to get you to work less!

Named Range Referencing - Simplifying Excel Cross Worksheet Referencing

This lecture covers how to manage your Excel 2016 Workbook. It is best to learn these practices before your Excel workbook becomes too large to manage properly. We go through some of the basic functions like adding, moving, copying and deleting worksheets.

Copying Moving and Deleting Excel 2016 Sheets

This lecture covers how to manage your Excel 2016 Workbook. It is best to learn these practices before your Excel workbook becomes too large to manage properly. This lecture covers how to set colours to your Excel worksheet tabs. The colours you choose is completely up to you. It is a good idea to get a good system before your sheet becomes unwieldy.

Changing Excel Sheet Tab Colors

This is a rather sneaky way to use the Autocorrect feature in Excel to speed up your work and become more efficient. 

Using Excel's Autocorrect to Speed Up Typing

You can add notes and comments to cells in your Excel 2016 spreadsheet. This is especially useful if you are sharing a document with someone and you want to explain something with in it and not use up valuable work space to do so.

Adding, Editing and Deleting Comments in your Excel 2016 Sheet

This lecture is not introducing any new features, but rather taking some of what we have learnt in order to clean up a messy Excel Spreadsheet.

Managing Excel Sheets - Best Practices for Cleaning up Raw Data
+ Using the data in your Excel 2016 spreadsheet
10 lectures 01:05:12

One of the more fun features in Excel 2016! 

You can take a list of clients with addresses, and plot that information on a world map, all inside Excel! You will start finding excuses to use it, so here are some for you to test out.

  1. By plotting customer addresses on a map, and adding in the value of purchases, we can see how profitable our marketing campaign has been by areas. Using heat maps, bar charts or anything else to graphically represent the most profitable areas.
  2. It's cool

Either of those reasons should be sufficient enough excuse to use this Excel feature.

3D Maps - Creating 3D maps and plotting addresses from your Excel spreadsheet

Excel has hundreds of functions that you can use in your formulas. One of the most popular and useful is the SumIf function. This video covers how it works, and how you can use it in your own Excel 2016 Spreadsheets.

SumIf Function - How to use it in an Excel Spreadsheet

Drop down cells in Excel allows you to create a list of options, and when someone clicks on the cell that you have set up, they will see the list and be able to select one. This is especially useful if that cell is being referred to by another formula somewhere else, and you need to make sure that people don't type in the incorrect information which could cause issues elsewhere in your Excel Workbook.

Creating a Drop Down Cell - Using SumIf command with it

This lecture covers Drop Down lists again, but this time using independent information that is not reliant on data elsewhere in your Excel Workbook. Each option has its merits and flaws, so understanding both is important so you can decide which application to use to keep your Excel sheets as efficient as possible.

Creating an Independent Drop Down - Using SumIf

VLookup, explained in all its glory. Possibly the most used feature Excel has to offer, and for a very good reason. This lecture covers how it works, how to use it, and how to remember how to use it because everyone forgets the first few times.

VLookUp Part 1 - How it works, and how it makes your life easier in Excel

We take Excel 2016's VlookUp to the next level, but referencing cells in other sheets to an existing one. So that we can create a clean Excel Sheet that pulls relevant information from various other sheets and then applies the VLookUp function to filter and find what you want.

VlookUp Part 2 - More indepth and pulling information from other Excel sheets

the Left, Right and Mid functions allows us to format text cells in to a standard format. This is not only to make the spreadsheet more visually appealing, but it also allows us to start pulling pieces of information out of text to use in other areas of our Excel Workbook.

Left, Right and Mid Functions - Editing Text in Excel

Find and Len functions allow us more control over pulling information from text in our Excel spreadsheets. We also cover nested formula for the first time in this video.

Nested formula, explained as simply as possible, is when we use a formula inside another formula. Watch the video for an example.

Find and Len Functions - And using nested formula

Creating tables inside an Excel Spreadsheet might seem a little counter-intuitive, but once you have seen the additional features it offers and how to use it, you will see that in many cases it can make your life a lot easier.

Creating a Table Within your Excel 2016 Spreadsheet

Slicers offer you a visual way of selecting filters. Not only do they look good, they also offer a lot of functionality. Your Excel sheet can have slicers offering several different filters, to reduce massive lists down to just a handful of options. This lecture covers how they work so that you can prepare for managing large amounts of information, or just make your regular Excel sheets look more impressive.

Using Slicers in your Table for Quick Filtering your Excel 2016 Sheet
+ Pivot Tables - How to use them in Excel 2016 and why you should
4 lectures 19:55

Pivot tables are one of Excel's most powerful features. A pivot table allows you to extract and sort the important information from a large, detailed data set.

This lecture covers how to setup a pivot table in Excel 2016, from selecting the data range to using it.

Pivot Tables Part 1 - Setting up for first Excel Pivot Table, and how it works

Pivot tables are one of Excel's most powerful features. A pivot table allows you to extract and sort the important information from a large, detailed data set.

This lecture covers how we can quickly setup a chart that pulls information from the created pivot table. This allows the chart to dynamically change as you change the data pulled and sorted in the Pivot Table.

Pivot Tables Part 2 - Adding Automatically Updating Charts

Pivot tables are one of Excel's most powerful features. A pivot table allows you to extract and sort the important information from a large, detailed data set.

This lecture covers how we can use slicers to assist in quickly editing and selecting what information we want or need in our pivot tables.

Pivot Tables Part 3 - Using Slicers in your Pivot Table for Quick Filtering

Pivot tables are one of Excel's most powerful features. A pivot table allows you to extract and sort the important information from a large, detailed data set.

Timelines allow us to quickly sort data by time. So if you had a list of all your sales and one of those columns included invoice date, you can set up a time line which will allow you to highlight particular dates or even select a date range. This enhances the already powerful functionality of Pivot Tables in Excel 2016.

Pivot Tables Part 4 - Using Timeline for even easier filtering in Excel sheets
+ More functions - General Excel 2016 Functions
5 lectures 10:17

Concatenate is a useful function that allows you to join two or more different text cells in Excel together. 

Concatenate - How to join text together in Excel 2016

Very often when you import data from different sources, the text fields like names and addresses will be in different cases. By using the Lower, Proper and Upper functions in Excel 2016 we can change the case to make everything uniform and legible. 

Lower, Proper, and Upper functions - Formatting text in Excel 2016

Excel's Repeat function allows you to repeat text as many times as you like.

Repeat Function - Using REPT in your Excel 2016 sheet for quick duplication

The substitute function in Excel allows you to replace a piece of text within text, with another piece of text. Don't worry, I explain this much better in the actual video.

Substitute - Easily replace pieces of text with other pieces of text in Excel

Another useful tool to quickly edit out poor formatting. Excel's Trim Function allows you to remove double spaces from text strings.

Trim - An Excel function to quickly format out extra spaces