Excel 2019 A Quick and Complete Guide to Master Spreadsheets, and Boosting Productivity Jonathan Grey © Copyright 2020 by Jonathan Grey All rights reserved This document is geared towards providing exact and reliable information with regard to the topic and issue covered. The publication is sold with the idea that the publisher is not required to render accounting, officially permitted, or otherwise, qualified services. If advice is necessary, legal or professional, a practiced individual in the profession should be ordered. - From a Declaration of Principles which was accepted and approved equally by a Committee of the American Bar Association and a Committee of Publishers and Associations. In no way is it legal to reproduce, duplicate, or transmit any part of this document in either electronic means or in printed format. Recording of this publication is strictly prohibited and any storage of this document is not allowed unless with written permission from the publisher. All rights reserved. The information provided herein is stated to be truthful and consistent, in that any liability, in terms of inattention or otherwise, by any usage or abuse of any policies, processes, or directions contained within is the solitary and utter responsibility of the recipient reader. Under no circumstances will any legal responsibility or blame be held against the publisher for any reparation, damages, or monetary loss due to the information herein, either directly or indirectly. Respective authors own all copyrights not held by the publisher. The information herein is offered for informational purposes solely and is universal as so. The presentation of the information is without a contract or any type of guarantee assurance. The trademarks that are used are without any consent, and the publication of the trademark is without permission or backing by the trademark owner. All trademarks and brands within this book are for clarifying purposes only and are owned by the owners themselves, not affiliated with this document. TABLE OF CONTENTS CHAPTER ONE WHAT IS EXCEL Where are the Pieces on Your Worksheet? What are the Characteristics of the Workbook? Why does Excel Store Worksheets for You? How Many Worksheets Can be Created in a Single Workbook? CHAPTER TWO OPENING EXCEL WORKSHEET HOME TAB Home Tab Themes Group: PAGE LAYOUT Formula Tab Data Tab IMPORTING EXTERNAL DATA Where Do You Decide to Put the Data? SORTING DATA LISTS CHAPTER THREE EXCEL FUNCTIONS: LOGICAL AND TEXT EXCEL FUNCTIONS EXAMPLES OF EXCEL FUNCTIONS COMPATIBILITY FUNCTIONS DATABASE FUNCTIONS INFORMATION FUNCTIONS LOGICAL FUNCTIONS LOOKUP AND REFERENCE FUNCTIONS CHAPTER FOUR ENTERING DATA Create a New Ribbon Tab. FORMATTING CELLS Apply Excel Styles ENTERING FORMULAS CALIBRATING COLUMNS COPYING FORMULAS WITH AUTOFILL CHAPTER FIVE TIPS AND TRICKS SHORTCUTS FOR WINDOWS AND MAC SIMPLE WAYS TO MANAGE YOUR DATA Data Storage Data analysis Multi-user collaboration Security SIMPLE WAYS TO ANALYZE YOUR DATA PRACTICAL APPLICATIONS HOW TO CREATE AN INVOICE HOW TO CREATE A BUDGET PLAN HOW TO CREATE A CREDIT CALCULATING HOW TO CALCULATE CREDIT PAYMENTS IN EXCEL INTEREST CALCULATION FORMULA FOR LOANS IN EXCEL THE CALCULATION OF THE EFFECTIVE INTEREST RATE IN EXCEL CONCLUSION GLOSSARY CHAPTER ONE WHAT IS EXCEL The Excel worksheet is an electronic ledger created by the Microsoft Excel spreadsheet program. Excel is nothing more than a series of rows and columns separated into cells. In a way, the table is just another form of a workbook. A table is only one of many object users that can be inserted into an Excel worksheet. A table serves so many other functions than a worksheet can or is meant to do. Worksheets are used to allow users to store and manipulate data in rows and columns separated into cells. Worksheets often enable users to quickly construct equations and logical functions to execute different features for each particular worksheet. Worksheets allow users to sort and process infinite quantities of data in many specific and familiar ways. Such data can be imported and exported to and from a variety of sources. Excel is usually used to arrange data and carry out a financial review. Excel is used for a lot of organization functions, and from small to large businesses and companies. The essential uses of Excel include Data Entry, Data Processing, Accounting, Financial Analysis, Charting and Graphing Programming, Time Processing, Task Management, and Customer Relationship Management (CRM). Almost everything that needs to be organized! Data Features, Formulas, and Shortcuts Excel software provides several functions, formulas, and shortcuts that can be used to improve its functionality. Business and Accounting Use Excel is commonly used in finance and accounting functions. Also, several companies are running their entire budgeting, forecasting, and accounting functions entirely out of Excel spreadsheets. Although Excel is described as a "data" management tool, the most commonly managed data is financial. At CFI, Excel would be described as the ultimate financial software. While there are other pieces of financial software designed to perform specific tasks, the strongest point about Excel is its robustness and transparency. Excel models are as strong as the analyst needs them to be. Accountants, investment bankers, analysts, and people on all kinds of financial career paths rely on excel to conduct their daily job functions. Excel is a spreadsheet application software that enables the users to store, modify, and manipulate data in a set of table-like structures known as a workbook. The workbook uses a series of worksheets to arrange data within a structure or row and columns separated into cells. Users can insert data or numbers in these cells to make it easy to calculate simple or complex formulas. Users may insert items such as charts and graphs into their workbooks to graphically represent their entered data in a variety of unique ways. Difference Between Tables and Worksheets A worksheet uses thousands and thousands of rows and columns, sometimes even a million. Nonetheless, tables are designed to use smaller numbers of rows and columns to organize related information for sorting and filtering purposes. Tables also allow the use of links to external networks, although worksheets do not enable the use of tables. Formatting Tables Tables can use most of the same formatting options available to worksheets; however, tables provide Excel users with a toolbar not only for formatting tables, but to import and export data from external networks, display and use more tools available in Excel tables, and even sort and filter the values inside the table in terms of current demand. Edit Values in Tables One of the most valuable aspects of the tables is their ability to allow you to start editing their values or formulas the same way you would have if you hadn't even built a table. Still, the same tables also allow for additional flexibility. You can also easily edit, add or remove any value inside any table. Importing a Table from External Resources Importing a table from external resources can be achieved in many ways. The perfect way to do this is to use the copy and paste process, where you only copy the data from the external resource and paste it into your worksheet. There are several different problems with this form. One of the core issues connected with the copy and paste approach is that Excel doesn't understand why you want to create a table automatically. This means that after you copy and paste the details on your worksheet, you have to place the data into a table in the worksheet. Exporting Tables to External Resources Exporting tables within Excel can be as easy as using the same copy and paste process that can be used to import data or as complex as exporting to different applications using various interfaces and menus. The copy and paste approach is the same as if you were importing data, except that you can use the copy portion to transfer the data from your Excel table and paste portion to export the data to the external network. Using different exporting menus and interfaces allows a lot more flexibility, but needs a lot more knowledge of standard formats and external resource structures. Who Created Excel from Microsoft Excel was developed by Microsoft Corporation in 1984. This was designed to provide a more effective, user-friendly way for spreadsheet users to measure data without having to deal with the DOS command line. It was initially planned for use with Apple computers. Since Microsoft Windows was first released in 1987, Microsoft Excel was one of the first applications to be developed for Microsoft Windows. It was the only window dependent spreadsheet program until 1992. Where are the Pieces on Your Worksheet? Worksheets consist of four primary sections. The cell is the most widely used component of the Excel workbook. Cells are where users can enter the data to be used in calculations and charts later. Each cell is made up of a column and a row; A column is all cells in a single vertical line in the worksheet. Panel titles can be found around the top of the workbook. A row is a series of cells in a horizontal line around the workbook. You will see the names of the row or the values scrolling down to the left of the worksheet. What are the Characteristics of the Workbook? Worksheets provide Excel users with a range of features. The primary function offered by the worksheets is the ability to store and manage data in a single central location. Nevertheless, with the development of worksheets, users can now measure both basic and complex math and financial problems as well as display their stored data with any specific custom charts and graphs. Why does Excel Store Worksheets for You? Worksheets are contained in an Excel file called a workbook. Such workbooks are what Excel uses to systematically arrange all the various related sections of several similar worksheets, as well as the same maps, graphs, and other objects within Excel. How Many Worksheets Can be Created in a Single Workbook? Before the release of Excel 2007, users could only build 255 in a single workbook. Nevertheless, with the introduction of Excel 2007, users can now create as many worksheets inside a workbook as the memory of the machine can accommodate. However, if the user does not have access to one of the newest versions of Excel, such as Excel 2007 or Excel 2010, they can still create as many worksheets as they want, but older versions of Excel would need more workbooks. Microsoft Excel Tools has also developed useful tools and menus for Excel users to be more personalized inside their own unique Excel programs and workbooks. The Easy Access Toolbar has been designed to allow users to build a simple way to access frequently used commands. This toolbar can be personalized with an Excel program or a different workbook. The ribbon was created by Excel to replace the older versions of the Excel menu. The ribbon included the ability of Excel users to configure the ribbon to suit their unique specifications. Microsoft Excel File Types has also developed different kinds of ways to save workbooks and other Excel files. Through the introduction of new file types, Excel developed an import and export function that allowed Excel users to easily import already developed workbooks or files and export workbooks or files that could be used later.