EXCEL 2020
EXPLAINED
A Well-Explained Guide to Master Microsoft Excel Functions and Formulas from Beginner to Expert Level
AARON BADDEY
CONTENTS
INTRODUCTION
We all deal with numbers in a way or the other. We all have expenditures ranging from the monthly income that we earn to our day to day expenses. For one to spend wisely, one will need to know their income with respect to their expenditure. Microsoft Excel comes in handy when we want to analyze, record, and store such numeric data. Since the inception of Microsoft Excel in 1985, it has won the hearts of many users in the private, education, and especially in the business sector. Excel is a useful and user-friendly software designed to work with spreadsheets, in particular, for simple and complex calculations as well as for analysis and predictive modeling.
Microsoft Excel is an application software (spreadsheet package) that is used to record and analyze numerical data. It is a collection of rows and columns that form a table. Alphabetical letters are generally allotted to columns and numbers to rows. The point where a row and a column meet is called a cell . Excel's spreadsheets are operational in any direction and not just for calculations, but also to create and edit tables, produce forms as well as to develop custom programs and functionalities through Visual Basic for Applications (VBA).
This book focuses on practices appropriate for users desiring to learn the basics of Excel. The steps stated in this book apply not only to Excel 2020 but also to Excel 2007, 2010, 2013, 2016, and 365. This book will also teach you how to use the newly added functions to Excel 2020, which include CONCAT, IFS, MAXIFS, MINIFS, SWITCH, and TEXTJOIN. I believe that you will find this book useful to aid your practical understanding of Excel spreadsheets.
CHAPTER ONE
UNDERSTANDING EXCEL 2020
Whats Excel?
Excel is a program developed by Microsoft which uses spreadsheets to organize data and numbers with formula and functions. It is developed with features like calculation, graphing tools, charts, pivot tables, etc.
What's New in Excel 2020?
The 2020 version of Excel comes with a vast collection of new and improved features and capabilities. These features make its usage easier, more reliable, and well-organized. Below is a list of some newly added features:
Data Import Tool Improvements
The Power Query data import tool in Excel 2013 was renamed Get & Transform in Excel 2016, but the name has reverted to Power Query in Excel 2020, bringing it in line with the name agreements adopted by the associated Microsoft products Power Map, Power Pivot, and Power BI.
The improved Power Query tool provides the capability to import data from external data sources and then clean the data placing them in Excel. Some of these
transmuting skills include the ability to group data, transpose data, detect data types, parse data, reverse rows, merge columns, split columns, replace values, unpivot columns, create conditional columns, and rename column headings. This feature makes it easier, faster, and more precise to import data from external sources for manipulation and analysis in Excel.
Automated Workflow
Excel 2020 now connects better to Microsoft Flow, which allows you to create automated workflows in which data are recovered from other applications and then saved or manipulated in Excel. The dataflow can be programmed to automatically send to or share those data with other people or third-party apps. Flow can recover your data from practically anywhere your accounting system, stock price databases, CRM system, ERP system, weather forecast databases, reminder notices, email messages, etc.
3D Images
Excel 2020 now embraces new 3D imaging capability in which you can rotate a 3D model of an object. The 2020 version of Excel now contains hundreds of 3D images, which are accessible from the Insert tab's 3D Models option. Image categories include animals, letters, gadgets, dinosaurs, dioramas, space, geology,
electronics, biology, and more.
Custom Functions
Excel 2020 now permits users to create their custom functions using JavaScript. Earlier versions of Excel already enabled users to create user-defined functions using Excel's built-in Visual Basic for most Applications programming language; since JavaScript has emerged as a frequently used platform for such purposes, Microsoft has added this new capability to permit more excellent compatibility with third-party solutions.
New Functions
Excel 2020 now includes six new or improved functions, as follows: IFS, SWITCH, CONCAT, TEXTJOIN, MINIFS, and MAXIFS. IFS and SWITCH are simplified versions of the nested IF function, which eliminates the need for more complex nested functions.
The TEXTJOIN function is a simplified version of the CONCATENATE function in which text inserted need not be captured by quotation marks. The CONCAT function permits you to merge text from multiple cells without stipulating a delimiter. The MAXIFS and MINIFS features work like the SUMIF or COUNTIF functions. They calculate only minimum or maximum
for those data that meet the criteria you stipulate.
Custom Visuals
To deliver more visual ways to display and present your data, Microsoft has unified its Power BI suite of apps into Excel 2020. Available visual add-in apps include Word Cloud charts, Speedometer charts, Bullet charts, Lucidchart Diagrams for Excel, the Prevedere Forecasting Tools, and Supermetrics, to name but a few. Most of these add-in apps are available for free, while some are priced from $1.49 to $13.99, and others have payment prices ranging from $1.49 to $5.99 per month.
Full SVG Graphics Support
Excel 2020 now supports scalable vector graphics (SVG), which means your graphic images won't pixelate when you increase the image size.
Over 500 New Built-In Icons
Excel offers over 500 new built-in SVG-compliant icons for creating dashboards and infographics.
New Forms Capabilities
Excel 2020 now works in unison with Microsoft Forms to produce improved documents to request responses to questions such as those included in surveys, quizzes
and polls. To get started making a new Form, visit forms.office.com, sign in, and click the New Form button. As you collect automated responses, you can open the survey results in Excel for additional analysis or charting of the collected data.
Map Chart Improvements
New options allow you to project your maps in Albers, Miller, or Mercator layouts (meaning you can display your map charts as curved or flat). There are also new controls for choosing the map area to be shown.
Chart Improvements
New choices in Excel 2020 allow you to control better axis information on your flue charts, such as tick marks and number formats, and you can now also produce color-coded heat maps.
Co-Authoring
New co-authoring capabilities enable you to share your workbooks with others (third party), so several users can edit the same workbook at the same time when your File is stored on either the OneDrive or SharePoint platform. The sharing tool also permits you to set permissions for collaborators.
New Themes
Excel now offers you three Office themes that you can apply; Colorful, Dark Gray, and White. To access any of these themes, go to File > Options > General, and then click the drop-down menu next to Office Theme.
Microsoft Excel Terminologies
Workbook
The workbook refers to an Excel spreadsheet file. The workbook stores all of the data that you have entered and lets you to sort or calculate the results. A workbook that is accessible to be viewed and modified by multiple users on a network is called a Shared Workbook.
