Microsoft Excel: Beginners+Intermediate+Advanced plus

One of the foundations of Excel is the spreadsheet, so understanding its structure through notions like the grid and the cell, and its referencing is a must and the first matter to learn.

For those who are new to Excel, this lecture starts with an explanation for its launch.

Once the concept of the spreadsheet is introduced, the following lectures approach the heart of Excel: Formulas.

Formulas constitute the heart of Excel, so it's crucial, for mastering this application, to learn about this foundational concept at the beginning of the course and clearly understand it, and become able to create formulas consistently.

The previous lecture introduced formulas in Excel through the addition operation, now it’s time to learn about the other three arithmetical operations: subtraction, multiplication, and division.

It will remain other two operations: power and square root to be done with functions, so they will be approached at the respective section.

When dealing with formulas a reference error may happen, so it’s important to understand and correct it. Also, there are situations you may need to evaluate a formula that is not bringing the expected result, and a formula containing more than one operation arises the issue of the order of execution. All these matters to be approached under this lecture are essential for developing consistent spreadsheets.

Formula’s syntax. Concatenation, grabbing cell reference and interconnecting spreadsheets, AutoSum, and replicating formulas. Relative and absolute references. Formulas Auditing and Defined Names.

Lecture 1.1. Syntax and Concatenation (building long formulas)

Lesson 1.1.1. Formula Syntax

Lesson 1.1.2. Concatenation Operation (joining text)

Lesson 1.1.3. Arithmetical and Concatenation Operations

Lecture 1.2. Getting Cells Reference (building formulas consistently)

Lesson 1.2.1. Introduction

Lesson 1.2.2. Cells Referencing Picking Up

Lesson 1.2.3. Practical Demonstration

Lesson 1.2.4. Connecting Spreadsheets

Lecture 1.3. SUM Function and AutoSum

Lesson 1.3.1. SUM Function

Lesson 1.3.2. AutoSum

Lecture 1.4. Replicating Formulas (building formulas efficiently)

Lesson 1.4.1. Replicating Formulas

Lesson 1.4.2. Replicating Formulas to non-contiguous cells

Lesson 1.4.3. Moving a Formula

Lecture 1.5. Relative and Absolute Cells Reference (building complex formulas)

Lesson 1.5.1. Mixed Cell Reference (partially absolute)

Lesson 1.5.2. Fully Absolute Cell Reference

Lecture 1.6. Formula Auditing

Lesson 1.6.1. Trace Precedents

Lesson 1.6.2. Trace Dependents

Lesson 1.6.3. Complementary Auditing Tools

Lecture 1.7. Defined Names

Lesson 1.7.1. Create a Name

Lesson 1.7.2. Names from Selection

Lesson 1.7.3. Pasting Names in Formula

With syntax in detail, you’ll complete your initiation to formulas and with the introduction of the concatenation operation, it will be an opportunity for developing more complex formulas involving arithmetical and joining text operations.

Then, with the next lectures the Formulas in Depth proceeds, so at the end of this section, you’ll be mastering Excel Formulas.

So far a formula has been created by fully writing it which slows the work and is error-prone. Cell reference grabbing speeds up the building of a spreadsheet reducing significantly the probability of errors.

Being accuracy an important issue this procedure is essential for delivering consistent solutions and dynamically interconnecting spreadsheets.

A structured spreadsheet has many calculations with the same logic which can be replicated from one cell to similar ones, speeding up significantly its development.

There are differences between the horizontal and vertical replication and contiguous and non-contiguous cells and you should master them for using Excel proficiently.

When replicating formulas arises the question of the type of cell reference. Usually and by default cell reference within a formula is relative but it may require to be absolute, to be frozen – fully or partially, which brings up the notion of fully absolute or semi-absolute (or mixed) cell referencing.

This is crucial for building complex spreadsheets and again a very special effort was made for explaining this subject with as much clarity as possible.

Formula Auditing tools such as trace precedents and dependents, watch window, and trace errors help depurate the spreadsheets from miscalculation and errors, enhancing the accuracy of your Excel projects.

You can analyze the flow of information throughout the interconnected spreadsheets and audit if the projected logic of the chain of calculations is correctly applied, assuring their consistency.

A name can be attributed to a cell or a range of cells to be used in formulas instead of cells’ references, adding more flexibility since formulas become not tied to cells’ address but to the value itself.

Suppose that your system of spreadsheets uses a currency conversion rate or any other parameter, instead of placing in the formulas the cell reference, every time you need to use this value simply apply the name defined for the rate or the parameter.

Summing up the values of a range of cells is a very frequent calculation in Excel which can be done with the respective function instead of doing by indicating each cell reference.

AutoSum makes the application of this function easier and faster through, as the name suggests, an automated procedure.

The Grid and the Cells and navigation within. Data manipulation, select cells, a single or multiple ranges. Inserting, copying, moving, and deleting cells. Resizing the columns width and rows height.

Lecture 1.1. Navigation in the Grid

Lesson 1.1.1. The Size of the Grid

Lesson 1.1.2. Navigating Between Cells

Lesson 1.1.3. Page Up and Page Down

Lecture 1.2. Data Manipulation

Lesson 1.2.1. Closing and Opening a Workbook

Lesson 1.2.2. Introducing Data

Lesson 1.2.3. Editing Data

Lesson 1.2.4.

Lecture 1.3. Selecting Cells

Lesson 1.3.1. Selecting a Range of Cells

Lesson 1.3.2. Selecting Columns and Rows

Lesson 1.3.3. Selecting a Table

Lesson 1.3.4. Inserting Data in a Range of Cells

Lecture 1.4. Cells Manipulation

Lesson 1.4.1. Inserting and Deleting Rows, Columns and Cells

Lesson 1.4.2. Copying and Moving Cells Data

Lecture 1.5. Resizing Cells Width and Height

Lesson 1.5.1. Resizing Columns and Rows

Lecture 1.6. Practical Application

Lesson 1.6.1. Transforming a Spreadsheet

Lecture 1.7. Introducing Data in a Table

At this point of the course, you realized the importance of the Grid. It’s the working area of Excel where everything takes place, so this lecture shows how to navigate within, the obvious ways, and beyond it.

Of course, for building a spreadsheet data introduction is necessary and its manipulation becomes another basic task to learn, before deepening Excel. You’ll learn how to insert, edit and erase data and the different ways of doing it.

This lecture expands an existing spreadsheet, demonstrating diverse features.

This lecture demonstrates the application of the matters so far learned plus the formatting features, transforming the Laptop Investment spreadsheet into a more complex one. A very elucidative lesson.

Selecting cells, a single or multiple ranges is one of the most frequent tasks, and there are different ways of doing it. Take in mind any feature is applied to cells, they are the target.

Cells manipulation consists in inserting, moving, copying, and deleting cells with their content, which are frequent tasks when building or remaking a spreadsheet. These are another set of basic operations to learn for acquiring a solid base for mastering Excel.

Often you’ll need to resize the columns and rows of the spreadsheet, one at a time, or a range of them, with the mouse or through numerical instructions and fit the width or height to the content.

For introducing data in a table there is a special procedure which makes it more efficient and being a spreadsheet also a data table this is a must for you to learn.

A practical exercise for you to resolve, spanning matters regarding working with the grid and the cells.

Solution of the practical exercise of the previous lecture.

A practical exercise for you to resolve, spanning matters of the main skills taught so far.

Quick formatting Features. Excel interface: working environment with its menus and different elements. Workbooks management, Series of data, auto and custom lists, and manipulation of Comments.

Lecture 1.2. Quick Formatting Features

Lesson 1.2.1. Quick Formatting Features

Lecture 1.3. Excel Interface

Lesson 1.3.1. Excel Interface

Lecture 1.4. Workbooks

Lesson 1.4.1. Introduction

Lesson 1.4.2. AutoSave

Lesson 1.4.3. Opening a Workbook

Lesson 1.4.4. Save As

Lesson 1.4.5. Switching Between Workbooks

Lesson 1.4.6. Closing a Workbook

Lesson 1.4.7. Shortcut Keys

Lecture 1.5. Series

Lesson 1.5.1. Pre-Introduced Series

Lesson 1.5.2. Numeric Auto Series

Lesson 1.5.3. Customized Series

Lecture 1.6. Comments

Lesson 1.6.1. Inserting and Viewing Comments (and formatting comments)

Dealing with the interface of any application is a must for dominating it by understanding its working environment and its components and functioning, and so it is with Excel.

Excel spreadsheets are developed in the worksheets and they are contained in the workbooks so these are the ones to be saved. Also, you can have more than one workbook opened and you’ll need to switch between them.

Series are data to be introduced in the spreadsheet with the AutoFill, saving you the task of writing it and there are two types: lists of labels or textual information that should be inserted or are pre-introduced and auto series, based on numeric values.

Excel comes pre-packed with the months and weekdays full names and abbreviations, so you don’t need to write all of them but just one and then fill the rest with the AutoFill.

Numeric series do the AutoFill based on an incremental ratio which is obtained from the two initial numbers. Similarly, there are also auto series for date and time and text joined to numbers.

Notes can be inserted in the cells for explaining its content, providing instructions to the user, or for any other information you consider convenient to place in.

You may have an older version of Excel where this feature is named as Comments, so this lecture approaches Notes insertion, editing, and formatting under the new version and the next lecture tackles this feature as it was in the previous version as Comments.

Comments can be inserted in the cells for explaining its content, providing instructions to the user, or for any other purpose you consider convenient.

Its insertion, editing, and formatting are approached by this lecture.

Find & Select:

- Find
- Replace
- Go To

A practical exercise for you to resolve, spanning matters of the main skills taught so far.

Solution of the Practical Exercise of the previous section.

Predefined options, customization with 4 sections of codification. Symbols for thousands separator, decimal places, currency sign, and others. Conditional codification with relational comparisons.

Lecture 1.1. Predefined Numbers Formatting

Lesson 1.1.1. Predefined Numbers Formatting

Lesson 1.1.2. Currency Format

Lesson 1.1.3. Accounting Format

Lesson 1.1.4. Percentage Format

Lesson 1.1.5. Text Format

Lesson 1.1.6. Special Format

Lecture 1.2. Most Common Number Format Customization

Lesson 1.2.1. Number Format Codification

Lesson 1.2.2. Currency Formatting

Lecture 1.3. Additional Number Format Customization

Lesson 1.3.1. Adding Text and Literals

Lesson 1.3.2. Color

Lesson 1.3.3. Pre-Packed Codes

Lesson 1.3.4. Hiding Content

Lecture 1.4. Structured Number Format Customization

Lesson 1.4.1. Structured Number Format Customization

Lecture 1.5. Structured Number Format Customization 3rd Section

Lesson 1.5.1. 3rd Section – Zero Values

Lecture 1.6. Structured Number Format Customization 4th Section

Lesson 1.6.1. 4th Section – Text Values

Lecture 1.7. Conditional Number Format Customization

Lesson 1.7.1. Conditional Number Formatting

Lesson 1.7.2. Conditional Number Formatting – Adding Text

Excel comes with predefined formatting for numbers you apply with a click and also with some level of customization, such as currency, percentage, accounting, scientific, fraction, to be approached under this lecture, and further ahead you’ll learn how to fully customize them through codification based on symbols.

This lecture starts by elucidating what is number format codification, then approaches the symbols regarding thousands separator, decimal places, currency sign, leading and ending zeros, and other aspects, through relevant examples.

Through the numbers format codification text and literals can be added just for displaying purpose meaning in their root they stay as numbers, so are eligible for calculations. Also, a color can be applied to the content of the cell. Pre-packed codes are also approached.

After introducing the number format codification and the respective symbols, now it’s time to learn the structured formatting, starting with the first two sections: for the positive and negative numbers. Usually, negative numbers are presented in red color and eventually within parenthesis and you are going to learn how to codify it.

The third section of the numbers format codification is destined to zero values and this lecture will present examples for elucidating on it.

Under the numbers format codification, there is a special section applicable to text for those cases a cell destined for numeric values, gets for some reason a text. Interesting examples will illustrate this subject.

Conditional numbers format codification can have up to three sections. The first two with a condition and a third one for the values not complying with the defined conditions which are the numeric relational ones, such as greater than, less than, equal to and others.

The values not meeting the first condition are evaluated by the second condition or in the absence of this one, the formatting present in the second section is used. And the same rule is applied to the second condition but passes the value to the third section formatting.

Predefined formats and full customization with codification. Extracting members of the date and time and their combination. Date and time and numbers and operations with. Conditional codification.

Lecture 1.1. Date and Time Predefined Formats

Lesson 1.1.1. Date Predefined Format

Lesson 1.1.2. Date Predefined Formats Customization

Lecture 1.2. Date Full Customization

Lesson 1.2.1. Day, Month and Year Codes

Lesson 1.2.2. Full Date Format Customization

Lecture 1.3. Time Full Customization

Lesson 1.3.1. Time Full Customization

Lecture 1.4. Date and Time as Numbers

Lesson 1.4.1. Date as Number

Lesson 1.4.2. Time as Number

Lecture 1.5. Operations with Date and Time

Lesson 1.5.1. Operations with Date

Lesson 1.5.2. Operations with Time

Lecture 1.6. Date and Time Conditional Customization

Lesson 1.6.1. Date Conditional Customization

Lesson 1.6.2. Time Conditional Customization

There are predefined formatting options for dates, long and short, and time, with the possibility to customize the regional setting, being by default the one defined in your operating system.

Firstly, you’ll learn about the day, month, and year codes and then combine them for creating diverse forms of dates, long, short, and others, being able to codify your format.

After demonstrating to get each member of the time. hours, minutes, and seconds, this lecture will show how to join and display them. Also, the combination of date and time will be taught. Special codes are also approached.

Since dates and times aren’t values, but numbers displayed like that, you must understand the correspondence between the numbers and the dates and times. Each integer is one day and its fraction represents time and the counting starts on January, 1st, 1900.

Arithmetical operations can be done with dates and times, particularly the addition and subtraction and this lecture will demonstrate them, first with the dates and then with the times.

Similar to the numbers, conditional format codification can be done with dates and times but taking them as numbers and this lecture demonstrates it, first with the date and then with the time.

Formatting the font, the borders, the cell background, and the content alignment. Cell Styles and Table styles for application just with a click. Format Painter for copying formatting to other cells.

Lecture 1.1. Font Formatting

Lesson 1.1.1. Font Type

Lesson 1.1.2. Font Size

Lesson 1.1.3. Font Styles

Lesson 1.1.4. Font Color

Lesson 1.1.5. Font Window

Lecture 1.2. Fill Formatting

Lesson 1.2.1. Fill Color

Lesson 1.2.2. Fill Window

Lecture 1.3. Format Painter

Lesson 1.3.1. Format Painter

Lecture 1.4. Borders Formatting

Lesson 1.4.1. Borders

Lesson 1.4.2. More Borders

Lecture 1.5. Content Alignment

Lesson 1.5.1. Vertical Alignment

Lesson 1.5.2. Horizontal Alignment

Lesson 1.5.3. Merge & Center

Lesson 1.5.4. Text Orientation

Lesson 1.5.5. Alignment Tab

Lecture 1.6. Cell Styles

Lesson 1.6.1. Introduction

Lesson 1.6.2. Duplicate and Modify a Style

Lesson 1.6.3. Create a New Style

Lecture 1.7. Table Styles (Format as Table)

Lesson 1.7.1. Introduction

Lesson 1.7.2. Duplicate and Modify

Lesson 1.7.3. New Table Style

Lesson 1.7.4. Table Style Options

Lesson 1.7.5. Exporting a Table Style

This lecture approaches the features present in the Font group of the Home tab, namely font type and size, styles, coloring the font, and different options for the uppercase and lower cases. Also, the font tab is tackled.

Present in the Font group, Fill formatting is approached separately by this lecture. It fills the background of the cells with a color that can be solid or gradient with or without a pattern. It has also a specific tab for fill coloring.

Replication of the formatting from one cell to others can be done through the Format Painter present in the Home tab at the Clipboard group, saving you from repetitive tasks.

Borders can be applied to the cells with a style and a color surrounding a range of cells, inside the range or at each of the four diagonals. There are predefined options and the customized application can be done through the specific tab.

Cells' content can be aligned horizontally, at left, centered, or at right, and vertically, top, middle, or bottom. Wrapping text, Merging cells, and indentation can be applied and there is also a specific tab with some other options, including the rotation of text.

Styles are preset of formattings such as font, alignment, borders and shading, number format, amongst others, a facility for applying several formattings at once and even update them since the cells stay bonded to the applied style which means any update in the style, updates the bonded cells format.

There are two types of styles features: for the cells and the tables. This lecture will approach Cell Styles and the next one tables styles.

Similar to the Cell Styles, there is the feature Table Styles. For each element of the table, all types of formatting can be predefined, and then you can apply it to the entire table just with a click, so you can create a standard for your spreadsheets. And it is dynamic since the tables are bonded to their style: any alteration in the style and the tables are updated automatically.

Conditional Formatting formats cells depending on their content based on relational and text conditions. Graphical highlighting can be done through bars, color scales, and icons. Formula rules.

Before learning in-depth Conditional Formatting, it’s important to learn what it consists of and the different ways to highlight information with this feature.

With Conditional Formatting, the spreadsheet gains some level of self-communication - you can make it speak by itself and this lecture will show the potential it offers.

Highlight Cell Rules is made with math relational conditions, as well as, text and date ones, such as

greater than, less than, equal to, applied to numbers, contains, begins with, ends with for text or dates occurring, for mentioning some of them.

With More Rules regarding Highlight Cell Rules, this category of conditional formatting goes to a higher level, due to the customization it offers, besides other conditions like blank cells and errors verification, providing a complete set of options.

With Top/Bottom Rules you can highlight data according to conditions like Top 10 items or bottom, based on numeric or percentage values or above and below the average.

Top/Bottom rules criteria are related with ranked values and also below or above average, so through conditional formatting, you can highlight values corresponding to the cells satisfying these criteria.

Data bars conditional formatting places bars inside each cell which length is proportional to the maximum value with or without an axis and solid or gradient colors. There are predefined bars but, they can be customized.

Color Scales fill the cells with a background color that varies according to the weight of each value within the range of cells spanned by the applied conditional formatting and the applied color can be chosen by the user.

Icon Sets can be placed in the cells according to the belonging rank value, if top, middle, or bottom group of values, as well as, divided into four or five groups. The icon for each cell is chosen from a predefined set of icons.

Since several rules can be applied to the same range of cells the need of arranging them arises which is accomplished through the Manage Rules, where you can reorder, delete and redefine them.

By default, the conditional formatting is applied to the cell to be evaluated but with rules-based in a formula, the evaluated cell can be other than the object cell, the one receiving the formatting. This feature brings up great flexibility to the conditional formatting taking it to a higher level.

Basics about worksheets and its referencing even in other workbook or a range of them. Renaming, deleting, copying, moving, and printing. Data manipulation across worksheets and its consolidation.

Lecture 1.1. Basics about Worksheets

Lesson 1.1.1. Structure of a Spreadsheet

Lesson 1.1.2. Referencing Between Worksheets

Lesson 1.1.3. Referencing Between Worksheets of Different Workbooks

Lecture 1.2. Column Referencing System

Lecture 1.3. Manipulating Worksheets

Lesson 1.3.1. Most Common Operations

Lesson 1.3.2. Manipulating Data Across Worksheets

Lecture 1.4. Copy and Move Worksheets

Lesson 1.4.1. Copy and Move Worksheets to Workbooks

Lecture 1.5. Cell Referencing Across Worksheets

Lesson 1.5.1. Cell Referencing Across Worksheets

Lecture 1.6. Printing Worksheets – Introduction

Lesson 1.6.1. Overview

Lesson 1.6.2. Printing Settings

Lesson 1.6.3. Printing Area

Lecture 1.7. Printing Worksheets - Page Setup

Lesson 1.7.1. Header/Footer Page Setup

Lesson 1.7.2. Sheet Tab

Lecture 1.8. Protecting Worksheets and Workbooks

Lesson 1.8.1. Protecting Worksheets

Lesson 1.8.2. Protecting Workbooks (Worksheets Structure)

Lecture 1.9. Worksheets Consolidation

Lesson 1.9.1. Introduction

Lesson 1.9.2. Static Consolidation

Lesson 1.9.3. Dynamic Consolidation

Lesson 1.9.4. Full Consolidation

Some of the topics of this lecture were approached previously and here will be reviewed from a different perspective. It will help you to consolidate these concepts. Besides this review, cell referencing between worksheets, within the same workbook, and from a different one, will be demonstrated.

Despite it’s not being essential for using Excel, many would like to understand the columns referencing system based on letters, so this lecture explains it with a comparison with the decimal system.

Worksheets manipulation operations consist of renaming, selecting a range of worksheets, deleting, copying, applying color to them. Also, data can be handled across several worksheets at once by selecting the cells and the worksheets, then doing the editing.

Worksheets can be copied or moved to the other workbooks which should be opened, the subject of this lecture.

For a formula involving the same range of cells of different worksheets, it should be used the cells reference across worksheets, instead of mentioning the worksheets one by one.

This lecture introduces the general features of printing, mainly the options available directly on the print preview window and the definition of the printing Area.

For printing spreadsheets, headers and footers can be defined with the possibility of differentiating the first page, as well as, odd and even pages. For a worksheet with several pages to be printed, it’s convenient to repeat the headers of the columns and this can be configured. Also, page breaks can be imposed.

Workbooks and worksheets integrity can be assured through the protection features provided by Excel, making the cooperative work safe, since you can prevent alterations to the structure and calculations of the spreadsheet.

Often you’ll have a series of worksheets with the same structure and each one spanning a segment of an organization, so it arises the need for a consolidating worksheet. With this lecture, you’ll learn to use a tool for the creation, with a few steps, a consolidating worksheet that can be static or dynamic.

Practice activity for applying the skils learned under this section and others

Proposal of a solution for the previous practice exercise.