Microsoft Excel 2016 - The Comprehensive Excel 2016 Guide

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

We are going to create our first Excel workbook.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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

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.

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.

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.

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.

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.

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.

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

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.

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.

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.

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.

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

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!

In this lecture you will learn

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

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.

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.

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.

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.

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.

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.

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

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.

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.

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.

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.

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.

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

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.

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

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.

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.

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.