Microsoft Excel 2016 - The Comprehensive Excel 2016 Guide

Learn Excel 2016 from scratch or improve your skills and learn new tricks to speed up your work.
4.9 (13 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.
195 students enrolled
$19
$55
65% off
Take This Course
  • Lectures 122
  • Length 12 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 10/2015 English

Course Description

Do you want to make your work with Excel faster, more efficient and fun?

Do you want to learn more about hidden tricks that make Excel easier to use?

Are you looking for a structured way to get really good with Excel?

If your answer was YES to some of the questions, this course might be of interest to you.

When I created this course I put full emphasis on a structured and didactically sound way to gently show you how Excel 2016 and 2013 works. This course is especially suited towards a beginners and intermediate user group.

The course was shot with Excel 2016, which basically means that you can apply the knowledge from this course also to Excel 2013 (and 2010) since those versions are quite similar.

Excel is such a huge and powerful software so that learning it can be confusing. It is advisable to learn it in a structured way so that you can improvise and find proper solutions to new problems. Many users tend to only know the parts of Excel that are needed for their daily routine. Once a totally new problem arises, they are stuck. Having structured training combined with practice is key to finding solutions faster and without frustration.

In this course you will learn about the general interface, the capabilities and limitations, popular and efficient functions, useful tools, data visualization, database handling, getting help and much more. Take a look at the course content overview to see how extensive the whole product is.

At the end of this beginners and intermediate course you should be confident in using for your daily tasks. You will know

how to use Excel's functions and other tools

what the capabilities of Excel are

where to find the appropriate features

how to get help

and how to work more efficiently with Excel.

Excel is one of the key software products of today’s professional world. You will use it extensively in your work life, and of course your colleagues will be thankful if you can show them some tricks they did not know before!

What are the requirements?

  • you will need Excel 2016 (or 2013) installed on your machine
  • you will need a general understanding of maths
  • interest in learning and solving the exercises provided

What am I going to get from this course?

  • know how to get help in Excel
  • know how to use the many features of Excel 2016 or earlier
  • know how to use Excel in order to solve new problems that arise
  • help out your colleagues in general questions concerning Excel
  • make your work faster, better and more efficient

What is the target audience?

  • people already using Excel but wanting to get more out of the software
  • beginners to Excel 2016 or earlier versions
  • users of earlier versions looking to update their skills
  • everybody interested in the Excel software

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: Introduction
Introduction
Preview
02:49
Download these files before you start
Article
Section 2: The Basics of Excel
03:44

A short introduction on Excel's file types and on the different attributes.

We are going to create our first Excel workbook.

05:12

The Quick Access Toolbar is a very useful feature of Microsoft Office products. In this lecture we are going to take a look at its default tools, and we are also going to learn how to customize it in order the make our daily work quicker.

04:32

This lecture contains core information on finding your way within Excel. We are going to take a look at the ribbons, as well as at columns and rows.

08:45

However Excel is like a huge calculator it makes a great job handling not only number values, but texts, dates, currencies and other types of data. In this lecture we are going to learn how Excel differentiates between those types of data, and how you, the user, can do it.

07:10

In this lecture we are going to dive deeper into different cell format types. Did you know that Excel can handle the very same data differently depending on the actual cell format type? This lecture could save you some headache, if you are struggling with Excel's auto-formatting feature.

07:51

A single Excel workbook can contain several worksheets which can help you keeping things tidy and organized. In this lecture we are going to learn

  • how to manage those worksheets;
  • how to create new ones;
  • how to delete the unnecessary ones;
  • how to rename them;
  • how to move them within a workbook or between different workbooks;
  • how to organize and even color code them.
07:35

This lecture will teach you how to manage the rows and columns in Excel.

You will learn

  • how to insert new rows and columns - even multiple rows and columns - and what are the rules of creation;
  • how to delete unwanted rows and columns, and what are the rules of removing;
  • how to hide parts temporary, how to discover hidden rows and columns, and how to reveal them if needed;
  • about the rules and dangers of deleting single cells;
  • about the difference between deleting a cell and deleting its content.



04:24

In this lecture we are going to learn how to copy and paste data within an Excel worksheet and between worksheets as well. We are also going to learn on the specific rules of those actions.

08:06

In this tutorial you will get familiar with one of most useful and most frequently used tools of Excel. The Fill Handle tool is perfect for copying data or filling cells with series. This tool is one of the biggest times savers as well, and we are going to use it a lot through this course.

Topics of the lecture:

  • how to find the Fill Handle tool;
  • rules of usage;
  • creating custom lists.
06:16

In the last lecture of the section we are going to take a closer look at Excel's file menu. We are going to discover the old features as well as the new ones which Excel 2016 provides.

Topics:

  • workbook information;
  • new document creation;
  • saving - file types and places;
  • share, export and publish feature.

Please note, that printing has its own section in this Excel curse.

The Basics of Excel
10 questions
Section 3: Content Formatting
06:22

Like other products in the Microsoft Office family, Excel also provides the user content formatting tools. They can be used very easily and intuitively. In this lecture we are going to walk through them together.

Topic:

  • font types, styles, size and color;
  • using the theme color set and finding new colors;
  • setting borders;
  • background colors.
05:01

In this video we are going to take a closer look at the cell alignment features of Excel.

Topics:

- vertical and horizontal alignment;

- custom alignment;

- merging cells.

03:47
  • After completing this lecture you will understand the concept of themes in Excel.
  • You will be able to change the actual theme as well as composing a custom one.
Section 4: Print
06:20

Printing in Excel might not be so obvious as e.g. in Microsoft World. Therefore, this course dedicates a whole section for printing. In this lecture we are focusing on the Print Dialog Box.

Topics:

  • orientation within the dialog box;
  • print preview setting;
  • page orientation;
  • margins;
  • scaling;
  • paper sizes.
07:26

We are going further with the topic of printing.

In this lecture you will learn

  • about the different print areas and their settings;
  • about different views offered by Excel;
  • about some further tools offered by the Page Layout ribbon.
06:54

In the final lecture of the section we are going to learn more on headers and footers.

Topics:

  • how to get the column letters and row numbers printed;
  • how to get particular rows and columns printed on every single page;
  • how to set custom headers and footer for your pages;
  • how to set different headers for the front page, even and odd pages.
Section 5: Basic Mathematics
05:47

In this lecture I'm going to show you how to do mathematical operations in Excel.

Topics:

  • building simple formulas;
  • using mathematical operators;
  • using cell references for formulas.
05:18

The order of operations is a core element in mathematics, therefore it is crucial to learn it properly. In this lecture I'm going to explain those rules and also we are going do a simple exercise in order to understand those rules better.

08:51

SUM is probably the most used function of them all in Excel. In this lecture you will learn how to use it for consecutive cells, and also how to combine it with the Fill Handle tool. We are going to solve some exercises together for better understanding.

03:23

This lecture contains an exercise which will teach you how to use the SUM function with non-consecutive cells.

06:58

Using formulas like SUM and the Fill Handle tool together might cause cell reference shifting. We can take advantage of it, like we did in the previous two lectures, but sometimes this phenomenon is undesirable. In such cases absolute referencing comes handy. In this lecture we are going to learn on this feature of Microsoft Excel.

Basic Math with Excel
5 questions
Basic Mathematics - Exercises
02:25
Basic Mathematics - Solutions
04:29
Section 6: Functions in Excel
04:11

In this lecture we are going to learn about the counting functions of Excel through a couple of examples.

Topics:

  • COUNT, COUNTA and COUNBLANK functions;
  • the syntax of those functions;
  • the usage of those functions.
06:55

In this lecture you will learn about Excel's statistical functions.

Topics:

  • MIN, MAX, LARGE, SMALL, AVERAGE and MEDIAN functions;
  • the syntax and the usage of those functions.
09:00

The following two lectures are on Excel's most used logical function, called IF. In this tutorial we are focusing on the numeric value based logical test. Example exercises is provided for better understanding.

Topics:

  • syntax and usage of the IF function;
  • available logical operators for that function;
  • how to produce Boolean output data.
06:10

In this lecture we are going to try Excel's IF function with text based logical test.

08:31

Not properly built formulas result error messages. In this lecture we are going to take a closer look at common error messages. We are also going to learn what causes them and how to fix them.

Topics:

  • common error messages and their background;
  • fixing error messages;
  • finding error messages;
  • #DIV/0!
  • #NAME?
  • #NULL!
  • #NUM!
  • #REF!
  • #VALUE!
05:51

In this lecture you will learn

  • what a circular reference is;
  • how to find it;
  • how to fix it.
09:41

However the SUM function is very useful on its own, Excel provides some extensions for it as well. This extensions are the SUMIF and SUMIFS functions.

In this lecture you will learn the syntax and usage of those extensions through the provided example.

07:07

Not only SUM, but the functions COUNT and AVERAGE also have extensions. In this lecture we are going to take a closer look at them, we are going to learn their syntax as well as their usage.

05:47

Making calculation with dates can be a confusing business. Fortunately Excel provides useful date functions like DATEDIF, DATEVALUE or TODAY which can make our work better. This tutorials shows you how to use them for various purposes.

07:17

Copying and pasting formulas can be different from copying and pasting other cell content. This lecture summarizes the available methods and highlights the points you should pay attention to. Furthermore you will learn how to display formulas on different ways, which skill might also come useful in some situations.

Excel's functions
10 questions
Section 7: Working with multiple sheets
06:16

Name facilities can really make our work quicker and more precise whether we build multiple sheet formulas or just use them for absolute referencing.

In this lecture we are going to learn how to create named ranges and cells, and I'm also going to show you how to use them in formulas.

07:34

Till then we built formulas using cells from a single Excel worksheet, but it might happen that we need to connect multiple sheets together. In this video you will learn the exact method of creating such formulas, and we are also going to discuss some rules you should keep in mind.

03:22

Excel worksheets can not only be connected within formulas, but we are able to format them simultaneously. This method is a great time-saver.

Multiple-sheet Tasks: Exercises
05:26
Multiple-sheet Tasks: Solutions 1-5
06:00
Multiple-sheet Tasks: Solutions 6
07:55
Section 8: Protection
11:00

This lecture will guide you through the different levels of protection Excel offers.

Topics:

  • protecting single cells;
  • limiting the allowed actions;
  • protecting single worksheets within a workbook;
  • protecting the structure of the workbook;
  • setting a password for opening the document;
  • adding a 'Read Only' warning to your document;
  • setting a different password for editing the workbook.
Section 9: Data Visualization in Excel
07:34

This lecture is an introduction to data visualization. We are talking about the advantages of visualizing data, and you will also going to learn how to use Excel's Recommended Charts features. This one is really useful especially if you are not familiar with the different charts types yet.

07:47

This tutorial will guide you through the process of creating a chart from scratch.

Topics:

  • choosing a suitable chart type;
  • how to define a data set;
  • how to add categories;
  • how to add series;
  • how to customize the chart to fit your purposes.
04:56

In this tutorial I'm going to show you

  • how to format your chart (style, color, size etc.);
  • how to customize it in order to make it more legible and understandable for your audience;
  • how to add (and remove) useful chart elements.



03:30

So far we learned how to work with multiple worksheets concerning functions and formatting. Creating diagrams which draws data from multiple sheets is further powerful feature of Excel. In this lecture I'm going to teach you how to build those kind of charts from scratch.

07:57

Creating graphs is not the only way of data visualization in Excel. In this video we are going to take a closer look at those options.

Topics:

  • creating a customizing sparklines;
  • using conditional formatting rules for highlighting data.

Please note, that this course has another lecture on conditional formatting where you can find more information on that topic (Section: Database).

14:52

The next two lectures are taken from another Excel course of mine. They are discussing the usage of various chart types which you might find helpful.

BONUS: Data visualization - which chart type to use - p2
11:11
Section 10: Illustrations in Excel
06:12

In this lecture I'm going to show you the main types of illustrations Excel provides.

04:33

Illustrations can be formatted similarly to graphs. In this lecture we are going to walk through the different options, and I'm also going to show you specialities.

04:31

In this lecture we are going to get familiar with the way Excel layers the illustration. We are going to learn how to alter that order, and also how to group those items for better control.

05:11

Caption boxes are a really fun feature of Excel. We can communicate data through them, and this data can be controlled by functions in the background. Really cool add-ons for questionnaires and informal forms.

Section 11: Database
07:05

In this lecture you will learn

  • how to search and find particular values, formulas or formats;
  • how to find and replace values;
  • how to change the format of some elements using the replace tool.
Sorting Data
06:28
Removing Duplicate Entries
03:33
Filtering Data
05:51
10:16

We learned about conditional formatting rules in one of the previous lectures. This time I'm going to show you how to create and use your very own conditional formatting rules.

Conditional Formatting - Exercise
02:03
Conditional Formatting - Solution
08:54
Table Formatting
Preview
05:48
Adding Subtotals to Data
04:45
The Rank Function
Preview
06:54
Section 12: Tools to Generate Data
05:02

In this lecture you will learn how to generate new data based on existing data using Excel's Flash Fill tool.

08:37

In this lecture I'm going to teach you how to use Excel's Text to Column tool in order to separate or reformat data.

11:00

After completing this video you will be able to use Excel's concatenating and text manipulation functions. This tools can be very useful if you need to restructure your data, or just quickly and simply generate new data.

Section 13: New Chart Types in Excel 2016 and their Creation in Earlier Versions
01:23

Excel 2016 introduces a handful of new chart types like histogram, Pareto chart, box and whisker chart, treemap, sunburst and waterfall chart.

In this section we are going to learn on the usage and creation of those charts.

If you use an earlier version of Excel, you can also create some of those charts if you want to.

05:05

Among the new Excel chart types there are three for statistical purposes. Histogram is one of them.

In this lecture we are going to learn

- what a histogram is good for;

- how to create it in Excel 2016.

07:20

Earlier versions of Excel, like 2010 and 2013, don't offer a histogram, however this chart type can be easily created with those versions as well. In this lecture I'm going to show you the steps of creation.

Alternative Histogram - Formulas
Article
04:16

The next statistical chart type Excel 2016 offers is the Pareto chart. In this lecture I'm going to teach you what a Pareto chart is good for, as well as the steps of creation in Excel 2016.

06:22

In this tutorial I'm going to show you step by step how to create a Pareto chart with older Excel version.

Statistical Charts 3: Box and Whisker Plot
05:21
How does Excel calculate Quartiles?
Article
Hierarchical Charts 1: Treemap
04:19
Hierarchical Charts 2: Sunburst
02:37
Which one should I choose? Treemap vs. Sunburst
02:03
Finance: Waterfall Chart
06:46
Waterfall Chart - alternative method
10:42
Section 14: Advanced Formulas
Additional Operators of the IF function - AND, OR, NOT
11:10
The IS Functions
09:27
The Functions VLOOKUP and HLOOKUP
07:01
VLOOKUP - Exercise
00:57

Students Who Viewed This Course Also Viewed

  • Loading
  • Loading
  • Loading

Instructor Biography

R-Tutorials Training, Data Science Education

R-Tutorials is your provider of choice when it comes to analytics training courses! Try it out – our 30,000+ students love it.

We focus on Data Science tutorials. Offering several R courses for every skill level, we are Udemy's number one R training provider. On top of that courses on Tableau, Excel and a Data Science career guide are available.

All of our courses contain exercises to give you the opportunity to try out the material on your own. You will also get downloadable script pdfs to recap the lessons.

The courses are taught by our main instructor Martin – trained biostatistician and enthusiastic data scientist / R user.

Should you have any questions, you are invited to check out our website, you can open a discussion in the course or you can simply drop us a pm.

We are there to help you boost your career with analytics training – Just learn and enjoy.

Ready to start learning?
Take This Course