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.4 (16 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.
1,343 students enrolled
$19
$20
5% off
Take This Course
  • Lectures 37
  • Length 3 hours
  • Skill Level All Levels
  • 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 5/2016 English

Course Description

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!

What are the requirements?

  • You will need to have a copy of Excel 2010 / 2013 or 2016. 2016 is recommended, but a lot of this is backwards compatible

What am I going to get from this course?

  • 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

What is the target audience?

  • 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

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: Jumping Straight In
05:57

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.

02:28

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!

09:16

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.

04:20

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.

03:50

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.

04:25

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. 

07:26

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.

Section 2: Increasing Efficiency to save time in Excel
07:52

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.

01:32

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. 

07:45

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.

04:36

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.

02:26

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. 

04:12

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!

03:37

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.

03:01

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.

03:07

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

02:22

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.

05:15

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.

Section 3: Using the data in your Excel 2016 spreadsheet
12:08

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.

06:46

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.

03:38

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.

03:25

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.

06:02

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.

10:06

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.

05:28

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.

06:39

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.

06:19

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.

04:41

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.

Section 4: Pivot Tables - How to use them in Excel 2016 and why you should
08:46

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.

04:04

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.

03:29

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.

03:36

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.

Section 5: More functions - General Excel 2016 Functions
02:39

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

02:12

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. 

01:19

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

02:30

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.

01:37

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

Students Who Viewed This Course Also Viewed

  • Loading
  • Loading
  • Loading

Instructor Biography

Stephen Beak, Home Entrepreneur, and loving it

I have been working from home for myself for nearly ten years. Much of my work gets automated which leaves me with time to learn new things.

I have been a successful Forex Trader and designer/builder of E-Commerce sites, enough to support my family of 6.

My career has lead me to not only working to earn money directly for myself, but also several consulting positions. I don't want a full time job because I enjoy the freedom that my lifestyle offers me too much :D

Ready to start learning?
Take This Course