Excel 2016 Hands-On Lab

By Handz Valentin Huiza

The new way to learn Excel 2016 immediately, and now the ebook is constantly updated with new topics.

Learn at your own pace with real "step by step" exercises to guarantee your learning. Do not forget to download the practice files for free.

With Excel 2016 Handson-Lab you will learn to:

- Learn the basics of Microsoft Excel 2016
- Manage different types of data
- Work with basic formulas and functions
- Understand the use of text, logic, search and date and time functions
- Use and convert ranges into tables
- Insert new types of charts

• Language: English
• Publisher: BadPress
• Release date: Sep 6, 2018
• ISBN: 9781547547456

Book preview

Table of Contents

Table of Contents

Introduction

Download Practice Files

How to register your book in ValentinBook

Chapter 1: Excel Basics

What is Excel?

Starting Excel

Open from instant search box

Open from pin to start menu icon

Open from pin to taskbar icon

The Excel Start Screen

Disable the Start screen

Create a workbook

Create a blank workbook

Create a workbook from a predesigned template

Examining the Excel 2016 window

The ribbon

How do the commands work on the ribbon?

Hide and show the ribbon

The dialog box launcher

Search commands

Page views

Zoom tools

Excel terminology

Saving your workbooks

Checking compatibility options

Opening workbooks

Organize your worksheets

Rename worksheets

Rearrange worksheets

Apply colors to tabs

Remove worksheets

Group and ungroup worksheets

Print your work

Chapter 2: Data Management in Excel

Enter data

The numerical values

The texts

Modify the contents of a cell

Apply formats to cells

Insert formulas

Use the fill handle

Enter dates and times

Enter dates

Enter times

Apply formats to numbers

Work with rows and columns

Insert rows or columns

Delete rows or columns

Change the width of the columns and the height of the rows

Apply borders

Align the contents of your cells

Proposed Exercise 1

Chapter 3: Working with Formulas and Functions

The formulas in Excel

Formula operators

Arithmetic operators

Text operators and comparison

Use Cell References

Use Relative, Absolute and Mixed References

Use functions in formulas

Examples of formulas that use functions

Functions’ arguments

Basic functions

SUM function

AVERAGE function

MAX function

MIN function

COUNT function

Formula errors

Proposed Exercise 2

Proposed Exercise 3

Chapter 4: Using Functions that Count and Sum

ABS function

MODE.SNGL function

PI function

SQRT function

ROMAN function

COUNTA Function

COUNTBLANK Function

COUNTIF function

COUNTIFS Function

SUMIF Function

SUMIFS function

Proposed Exercise 4

Proposed Exercise 5

Proposed Exercise 6

Proposed Exercise 7

Chapter 5: Logical Functions

IF function

Examples with IF function

Nest the IF function

AND function

OR function

Add the total if all data is complete

Proposed Exercise 8

Proposed Exercise 9

Proposed Exercise 10

Chapter 6: Text Functions

LOWER function

UPPER function

PROPER function

LEFT function

RIGHT function

SEARCH function

LEN function

MID function

REPT function

Create a sparkline charts with REPT

Proposed Exercise 11

Chapter 7: Working with Dates & Times

How Excel works with Dates and Hours

Enter Dates and Hours

The two-digit years

TODAY function

DATE function

DATEVALUE function

MONTH function

DAY function

YEAR function

NETWORKDAYS function

NOW Function

Proposed Exercise 12

Chapter 8: LookUp and Reference Functions

VLOOKUP function

HLOOKUP function

Proposed Exercise 13

Proposed Exercise 14

Chapter 9: Working with Tables

What is a Table?

Create a table

Change the look of a table

Use Total Row

Sort and Filter Data

Sort

Filter

Work with Slicer

Convert a table into a range

Proposed Exercise 15

Chapter 10: Insert Charts

Features of working with charts

What you should keep in mind before creating a Chart

Selecting data to create a chart

Create a chart

Move a chart

Sizing and moving the elements within the chart

Exchange between rows and columns

Why is it important to leave the upper left cell empty?

Change data source of a chart

Add or remove data series

Change Data Categories

Working with hidden and empty cells

Customize the Design and Style of a Chart

New types of charts

Waterfall chart

Treemap Chart

Proposed Exercise 16

Chapter 11: Manage Data

Sort and Filter Ranges

Sort

Filter

Summarize data that has hidden or filtered rows

Organize Data in Levels

Apply Data Validation

Add Input and Error Messages

Create drop-down lists in cells

Apply the Conditional Formatting

Create Your Own Formatting Rules

Apply shading to cells and ranges

Proposed Exercise 17

Download Practice Files

Introduction

Excel 2016 is a new version that is in the already inevitable Office package and that comes with everything we already know from the previous version. This book is created to share with you much of what I know of this powerful application and, of course, help you to make your work much more efficient.

Excel 2016 is a worksheet application used by almost everyone. Its powerful tools and the easy use of its interface have made this program the most popular among users, information professionals and anyone who needs to perform any data analysis.

When you immerse yourself working with Excel, you will notice that your window is very similar to a grid sheet, and it is possible that as soon as you see it, you already have an idea of how to use this application. Yes, just add the data in each of those small, thin rectangles, and then, use a function to get a result, that's it, that's all.

Of course, not only will you want to follow my previous example, you may have in mind to present a first quarter sales report, or that you need to visually compare the 2015 and 2016 financial income, or that you simply want to store a list of your invoices data day by day; whatever your purpose, Excel can help you.

This book will guide you through the most used tools Excel has. The chapters are designed with complete independence, so you do not need to read each chapter sequentially; if you already know certain topics, you can go to the chapter that interests you most and keep learning. If you are a user who will use Excel for the first time, I recommend that you start from the beginning. In addition, this book can be used as a reference material when necessary, so, whatever the level you have, you can take advantage of this content.

Download Practice Files

Before you can complete the exercises in this book, you need to download the practice files to your computer. These practice files can be downloaded from the link provided at the end of the book.

Excel 2016 program is not available as part of the practice files. You must buy it or purchase a subscription to Office 365.

How to register your book in ValentinBook

To benefit from the Constant Update program, register your book on our website by following these steps:

1. Go to www.valentinbook.com.

2. Click on the Register your book link.

3. Fill in the fields in the form:

•       Name & Last Name: First and last names.

•       Email address: Email address.

•       ISBN: ISBN code found in the bar code of the book or on the Download practice files page if it is an ebook.

4. Click on the Send button.

If necessary, check your spam tray and make sure to add ValentinBook as a trusted address.

Chapter 1: Excel Basics

In this chapter, you will learn to:

•  Know how Excel works

•  Create a blank workbook and through a template

•  Master the Excel ribbon

•  Use page views

•  Save and open workbooks

•  Organize worksheets

•  Print worksheets

PRACTICE FILES To complete the exercises in this chapter, use the practice files in the Chapter 1 folder.

What is Excel?

Excel is the most used program in the world for the creation of powerful worksheets with which you can perform statistical data analysis, generate reports, and even add specialized charts to present your data to any audience.

Once you start working with Excel 2016, you will realize that your tools are very easy to use. If you have already had experience with previous versions you should not worry since everything is still in its place; on the other hand, if you are a new user in Excel, it will not take long to understand how it works. In this book, you will learn the essential concepts to use Excel and get the most out of it.

Starting Excel

Excel can be installed mainly in new operating systems such as Windows 8, Windows 8.1 and Windows 10. The way to access Excel 2016 works slightly different in Windows 8 and Windows 10. For example, in Windows 10 there is a menu called Start that appears when pressing the Windows logo key. In contrast, in Windows 8, pressing the same key, takes us to the Start screen, which shows a series of tiles and a new interface.

This book is using Windows 10 as the client operating system. If you have another operating system, some screenshots may not look identical to yours.

To open Excel using Windows 8

1.     On your Start screen, navigate to the Microsoft Office 2016 tile group.

2.     Click or tap in Excel 2016.

To open Excel Using Windows 10

1.     Click on the Start button.

2.     Scroll through the start menu until you get to the letter E.

3.     If you have a pre-Anniversary Update version of Windows 10, you should first click on All Applications.

4.     Click on Excel 2016.

A screen shot of a video game Description generated with high confidence

Open from instant search box

In Windows 7, when I clicked on the Start button, below the menu options, a search box appeared which helped to speed up the search of a program or file. In Windows 10, we have a similar search bar that is integrated into a new search concept called Cortana. To use this box and find Excel 2016 follow these steps:

1.     Click on the search box.

Imagen que contiene objeto Descripción generada con confianza muy alta

2.     Write Excel.

3.     Wait for the searcher to show results that match the written word.

4.     Click on Excel 2016.

Imagen que contiene captura de pantalla Descripción generada con confianza muy alta

Open from pin to start menu icon

One way to open Excel 2016 more quickly is by pinning the program icon to the Start menu. A pinned icon allows it not to disappear from the start menu and is easier to access.

To pin the Excel 2016 icon

1.     Click on Start | All applications | E | Excel 2016.

2.     Right-click on the Excel 2016 icon.

3.     Select Pin to Start.

Now the icon (mosaic) of Excel 2016 is embedded in the mosaics area to the right of the Start menu.

Open from pin to taskbar icon

Just as you could pin the Excel icon to the Start menu, you can also do it in your taskbar. For example, if you have already pinned the Excel 2016 icon to the start menu, just right-click on that icon and select the Pin to taskbar option.

The Excel Start Screen

When you start Excel 2016, the first thing you will see is the Start screen. On this screen you can easily create a new workbook, or open an existing one. When you create a new workbook, you will have the option of using a blank one or choosing a predesigned template, the latter is ideal for you to edit it as you like.

Imagen que contiene captura de pantalla Descripción generada con confianza muy alta

A workbook is the name given to Excel files. To work with Excel 2016 you must first create a blank workbook or through a predesigned template.

Disable the Start screen

The Start screen is the first thing that is displayed when you open Excel and