Advance excel 2016 training guide

Ritu Arora

• Publisher: BPB Publications
• Year: 2019
• eBook ISBN: 9789386551825
• Topic: Computer Science
• Subtopic: Programming in JavaScript
• Index: Computer Science

Complete guide to Excel Key Features

• Basic components of excel, the available menu options and tools, some customizations, working with multiple workbooks and worksheets.
• An initial introduction to what are formulas, functions, and their components.
• Usage of multiple examples to explain the application in real world scenario.
• Explaining of different chart types available in Excel.
• Different ways and methods to consolidate data, complex analysis, and prediction or forecast based on trends.

Description
The first version of MS Excel was released in 1985 and was widely adopted across the globe and became the most prolific and important programs for over 750 million users. Many people assume that Excel is only used by accountants, analysts, scientists and other similar professions that points to the fact that there is a real problem with the understanding of what in this book. Through this book, we will explain why Excel is such an amazing program and why it can do so much more than you think.
What are the main uses of Excel and how can this help my business?
Excel enables any format of information to be organized in a spreadsheet and its innate flexibility as a program, you can define and structure the layout according to how you want to manage it. It allows for over 1 million rows by over 16, 000 columns of data to be managed, which shows how Excel is advancing as a business tool. This allows Excel to be used for a large variety of business purposes. What You Will Learn
New to Excel 2016 or upgrading from a previous version? Use this guide to learn the basics and advance features of Excel. This user guide will introduce you to some of Excel's 2016 more complex functionality, such as:

• Use Conditional Logic in a formula
• IF statements
• Use the SumIF and CountIF functions
• Produce a chart
• Sort and filter
• Apply subtotals
• Data Validation
• What-if-Analysis Tools
• Introduction to Pivot Tables
• Introduction to Macros
• Advanced Image Processing Operations
• 
  Who This Book is For
  Anyone who wants to learn Excel, do the mundane task easily and quickly, do complex calculations, analysis huge data from internal or external sources, take decisions based on predictions, do forecasting, create plans and charts, monitor dashboards for progress and status, and so on. Table of Contents 1. Overview of Excel 2016
  2. Cell References and Range
  3. Working with Formulas and Functions
  4. Data Validation
  5. Protection
  6. Sorting a Database
  7. Filtering a Database
  8. Subtotals
  9. Pivot Tables
  10. Conditional formatting
  11. What-if-Analysis Tools
  12. Working with Multiple Worksheets, Workbooks and Applications
  13. Working with Charts
  14. Macros About the Author
  Ritu Arora, is a certified MS Excel 2016 corporate trainer having more than 14 years of experience. She has conducted more than 1, 000 corporate trainings on Advance Excel and trained more than 10000 people. Linkedin profile: linkedin.com/in/ritu-arora-29019a13
  

Chapter 1

Overview of Excel 2016

Chapter Objectives

After studying this chapter, student should be able to:

• Understand the new layout of MS Excel
• Figure out how to change the default settings of MS Excel
• Understand the Excel tool in general
• Identify the different types of references
• Explain the Named Ranges

About Excel

Excel 2016 comes as a part of the package MS Office 2016, which is the latest version of the MS Office productivity suite. MS Excel is a spreadsheet software, which is a tool used to record data, support to make plot and analyse the entered data. This is a powerful tool with numerous features that supports you to keep a budget, or to create invoice of sales, maintain a training log. You can store the details of your products or services enquiries and it has lots of other applications in business.

As in the previous version, this version has a set of menus at the top of window known as the Ribbon and all the Excel commands are present in that. A document of excel is known as a Workbook and each Workbook is divided into a set of rows and columns and a intersection of this tabular structure is known as the Cell. Data is entered into cells, also all operations over the spreadsheet are applied to the cells. MS excel is having a set of tools by which we can do the formatting, analysis and also create charts.

Components of the Excel Window

Figure 1.1: The Welcome Window

When you open the Excel by clicking its shortcut, a unique landing page known as welcome page appears. This welcome page offers you various sample spreadsheets for you like movie lists, personal budgeting, trend, analysis and more. Most importantly it offers the Blank worksheet option by which we can open a blank spreadsheet and enter the data according to our requirement. The Welcome Window of Excel is shown in Figure 1.1.

This window also has a Search online template search textbox. By this you can synchronize yourself with online office templates library.

When you double click the Blank window option, a blank spreadsheet opens. (Figure 1.2)

Figure 1.2: Various Components of an Excel 2016 Window

Back Stage View

In this you manage your documents and the data related to them. In this you can create, save, send, inspect documents for hidden metadata or personal information. File tab replaces the MS office button and the file menu used in the earlier releases of MS Office. (Figure 1.3)

Figure 1.3: Options Available in the Backstage View

Quick Access Toolbar: It is present at the top left corner of the window. It contain commands for saving the current workbook, undoing and redoing the actions. This toolbar can be customize by adding buttons for frequently used commands. This toolbar is movable and can be moved below the Ribbon.

Ribbon: It is organized into various tabs, and each ribbon tab activates a ribbon. Each tab is divided into a set of commands known as groups with commands and options that relate to the group name.

Gallery: It might display within a Ribbon but more often it's a drop-down group of commands or functions. They use icons or other graphics to show the result of commands rather than the commands themselves. (Figure 1.4)

Figure 1.4: Gallery Options

Saving and Sharing Files Online

You can share a link by attending my fiction writers group, in case if you do not have MS office 2016 or any of its version. It allows you to view your essential Steampunk Titles spreadsheet on their own internet-connected device. (Figure 1.5)

Figure 1.5: Share Option in the Backstage View

Interacting with Excel

There are various ways by which any user can interact with Excel worksheet. These are typing, using the mouse to choose a command, make selections, click buttons and other options.

Using the Ribbon: It's a main container for menus and tools. When you choose a Ribbon tab, it displays Ribbon groups which contains tools (buttons and lists). Some of these tools expand to display simple lists, gallery (Figure 1.3).

Using galleries: It's an interactive list of options which display the option under the click command. For example, font gallery, shows a list of fonts available. Some galleries use live preview, as when you move pointer over the options on a gallery, each option is previewed. For example, if you select text in the worksheet and display the font gallery, moving the pointer over each font in the gallery causes the selected text on the screen to display in that font. (Figure 1.4)

Using Tools: When you keep your mouse pointer over any tool a small description about tool appears which is called a super tooltip. It provides small description about the tool and you can get the idea about what exactly the tool is doing.

Press

key to see the shortcuts related to the option inside the ribbon.

Working with Default Settings

Excel allows you to customize various aspects, behaviour and way by which you can interact with it. You can change the default settings of Excel, for example font, number of iterations, file locations, and the file which open on starting Excel. To select the dialog box of options you need to click on File Tab button and then select Options. (Figure 1.6)

Figure 1.6: Option Window of MS Excel

Personalize Options

You can change the workbook settings by using the Personalize Options to change the type and size of font, number of worksheets in the workbook and can also activate the Developer tab, which is used for Macros.

Save Option

It allows you to change the default file location, file format...

Table of contents

• Cover Page
• Title Page
• Copyright Page
• Table of Contents
• Preface
• Acknowledgement
• Chapter 1: Overview of Excel 2016
• Chapter 2: Cell References and Range
• Chapter 3: Working with Formulas and Functions
• Chapter 4: Data Validation
• Chapter 5: Protection
• Chapter 6: Sorting a Database
• Chapter 7: Filtering a Database
• Chapter 8: Subtotals
• Chapter 9: Pivot Tables
• Chapter 10: Conditional formatting
• Chapter 11: What-if-Analysis Tools
• Chapter 12: Working with Multiple Worksheets, Workbooks and Applications
• Chapter 13: Working with Charts
• Chapter 14: Macros