MICROSOFT EXCEL 2019 The Ultimate, Comprehensive, And Step-By-Step Training Book To Learn Microsoft Excel And Master Data… (T.Hopper, Mark Page [T.Hopper, Mark Page]) (Z-Library)

📄 File Format: PDF

💾 File Size: 5.6 MB

📖 Read Online ⬇️ Download

Registered users can read the full content for free

Register as a Gaohf Library member to read the complete e-book online for free and enjoy a better reading experience.

📄 Page 1

(This page has no text content)

📄 Page 2

(This page has no text content)

📄 Page 3

Microsoft Excel 2019 A Comprehensive Guide to Familiarizing Oneself with Microsoft Excel 2019 to Understand Business Modeling and Data Analysis Techniques MARK PAGE T. HOPPER

📄 Page 4

© Copyright 2021 by (Mark Page T. Hopper) - All rights reserved. This document is geared towards providing exact and reliable information in regards 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 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 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 such. The presentation of the information is without a contract or any guarantee assurance. The trademarks 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.

📄 Page 5

Table of Contents Introduction CHAPTER 1. Understanding the Microsoft Excel 1.1 What Is Excel 1.2 Versions 1.3 Features Adding the Header and Footer Find & Replace File Password Protection Data Filtering Feature Data Sorting Commands Built-in Formulas for Various Situations 1.4 Tools 1.5 File Types or Extensions .XLS .XLSX .XLSM .XLSB How You Can Find the Extension of Any Excel File CHAPTER 2. Why You Should Learn Microsoft Excel 2.1 Where You Can Get Microsoft Excel 2.2 How You Can Open Microsoft Excel Step 1. Double Click Any XLSX File Step 2. Drag and Drop Files into the Excel Step 3. The Open with Right-Click Menu 2.3 Understand Excel Ribbon and Its Elements Standard Excel Ribbon 2.4 Understanding Your Worksheets and Customize the Work Environment Customizing Excel Work Environment Creating an Excel Template Some of the Excel Workbook Essentials You Must Change 2.5 Customizing the Ribbon

📄 Page 6

Go to the File Tab > Select Options > Select the Customize Ribbon How You Can Create New Tabs for Adding In a Ribbon 2.6 Setting the Colors of the Theme Creating Your Very Own Colors Theme 2.7 Setting Your Formulas 2.8 Proofing Settings 2.9 Best Guidelines to Work with Excel CHAPTER 3. Basic Formulas and Making Calculations in Excel 3.1 How You Should Enter Formulas in Excel Creating a Simple Formula Example in Excel 3.2 Creating the Formulas Which Refer to the Other Cell Inside the Same Worksheet 3.3 Create a Formula That Links to the Other Workbooks 3.4 Use of Apostrophes for Enclosing and to Full the File Name and the Worksheet Name CHAPTER 4. How You Can Select and Activate or Edit Cells in Your Excel Workbook 4.1 Active Cell and How You Can Activate It 4.2 Putting an Excel Cell in Editing Mode 4.3 How You Can Keep a Cell in Active Mode 4.4 Returning to an Active Cell CHAPTER 5. Use of Finding or Replace Functions in Excel 5.1 Use of Find Function 5.2 Use of Replace Function in Excel Use of Wildcards in Find and Replace Functions CHAPTER 6. Creating and Managing Workbooks or Worksheets 6.1 Create a Blank Workbook 6.2 Creating a Template for Opening a Workbook From the Excel Template 6.3 Open an Already Existing Workbook 6.4 A Business Example

📄 Page 7

For Excel 2010 Version You Can Use the Following 6.5 Native or Non-Native Files in Excel To Open a Non-Native File Inside Microsoft Word 2013 From the File Explorer Opening of a Non-Native File in Microsoft Word 2013 by the Program Window 6.6 Connecting Excel With External Sources 6.7 Worksheet Operations Removing Columns or Rows Adding a Column Adding a Row Sorting Show Formulas in the Entire Worksheet Formulas To Change the Column Width Adjusting Height for Rows Adjustment of the Numerical Data Format For the Justification of the Cell Contents Justification of the Columns and Row Copying Cells Columns and Rows Pasting of Cells Columns and Sections Arithmetic Precedence Rules 6.8 Excel Worksheet Operations 6.9 Hiding/Unhiding the Excel Worksheet 6.10 Going to the Named Box Using Go to Function 6.11 What are the Hyperlinks 6.12 Modify Theme for the Workbook Defining a Fresh Custom Theme 6.13 Modify the Page Setup 6.14 Inserting and Removing Columns and Rows For Inserting Rows For Deleting Rows Deleting Column 6.15 Adjusting the Row Height as Well as Column Width Column Width

📄 Page 8

Modifying Row Height 6.16 Hide and Unhide Rows and Columns in the Worksheet How You Can Unhide the Columns or Rows from Excel 6.17 Add and Customize Header and Footer To Insert the Footer Customize the Header and Footer 6.18 Warning Messages and Data Validation Customize the Errors 6.19 Enabling the Developer Tab 6.20 Macro Recording and the Security Option Recording the Macro Macro Record Dialog Window 6.21 Backward Compatibility 6.22 Workbook View Normal Mode Page Break Mode Page Layout Mode 6.23 Zoom Function for Workbooks Zoom Slider Set a Custom Zoom Magnification Zoom Using the Shortcut Keys 6.24 Freezing Pane and Splitting Windows For Splitting the Panes 6.25 Show Formulas SUM AVERAGE MAX & MIN COUNT & COUNTIF IF Function 6.26 Select the Print Area and the Microsoft Excel Workbook Using the Page Breaks 6.27 Save your Workbook to Remote Destinations CHAPTER 7. Make a Basic Understanding of the Main Screen 7.1 Ribbon Navigation Home Tab

📄 Page 9

Insert Tab Page-Layout Tab Formulas Tab Data Tab Review Tab The View Tab Help Tab CHAPTER 8. Formula Bar 8.1 Navigating on a Ribbon to Use the Formula Tab in Microsoft Excel Expanding the Formula Bar or Tab Entering or Modifying Your Data Into the Formula Bar Section CHAPTER 9. Creating a Spreadsheet in MS Word 9.1 Understanding the MS Word Interface 9.2 MS Word Importing Spreadsheet To Embed Excel in the Word CHAPTER 10. Creating Charts in Excel and Their Types 10.1 The Charts and Their Types 10.2 The Pie Charts 10.3 Column Graphs 10.4 The Line Charts 10.5 Bar Charts 10.6 The Area Charts 10.7 Scatter Charts 10.8 Other Charts Types Bubble Chart Stock Chart Surface Chart Radar Chart Combo Chart 10.9 Customizing Excel Charts 10.10 The Importance of the Charts Visualization Automation Customization

📄 Page 10

Integration CHAPTER 11. Headers and Footer in a Worksheet 11.1 Adding or Changing the Headers or Footers via Page Layout Setting 11.2 Adding or Changing Header or Footer via the Page Setup Dialog Making Header and Footer via Page Setup Dialog Steps CHAPTER 12. Formatting the Excel Tables 12.1 Selecting Table Style 12.2 Creating or Deleting Custom-Made Table Style 12.3 Choosing a Table Style for Formatting the Elements of a Table There are Three Methods You Can Adopt to Sort a Table Filter Options CHAPTER 13. Putting a Signature in an Excel File 13.1 Adding Signatures Line and Putting the Signatures CHAPTER 14. Inserting Symbols 14.1 Adding Currency Special and International Symbols in MS Excel Inserting Symbol Special Characters Tilde Shortcut 14.2 Inserting Symbols Using Your Keyboard Shortcuts or Asci and Unicode CHAPTER 15. Bring the Elements Forward and Backward in MS Excel 15.1 Move Text Box Word-Art and Shapes Forward or Backward in a Stack 15.2 Grouping & Sorting Sorting Custom Sorting Multilevel Sorting Grouping 15.3 Data Filtering CHAPTER 16. Protect Worksheets 16.1 Enable Worksheet Protection

📄 Page 11

16.2 Unlocking Cells Required to Be Modified CHAPTER 17. Basic Arithmetic Functions in Excel 17.1 Basic Moths in Excel Sum Subtract Multiplication Division 17.2 Complex Equations Calculating the Percentage for a Total Calculating the Difference in Terms of Percentage for Two Numbers CHAPTER 18. Activating Thousands Separator 18.1 Creating a Schedule Using Excel CHAPTER 19. Use of Count If Function 19.1 Use of Count-If for Finding Out the Frequency of Words in a Range 19.2 Count Characters in a Cell CHAPTER 20. Freezing the Panes for Locking Rows and Columns 20.1 Freeze the First Column 20.2 Freezing Two Columns 20.3 Freezing the Column and Rows 20.4 Un-Freeze Rows and Column CHAPTER 21. Convert Your SCV or TXT File and Open It Into an Excel Sheet 21.1 Difference Between TXT CSV and XLS Files XLS CSV TXT CHAPTER 22. Advanced Formatting Options in Excel 22.1 Use of Fill Handle for Copying Formatting 22.2 Use of Paste Option for Copying Formatting 22.3 Copying Style Between the Workbook 22.4 Create Your Custom Format for Data That Is Readable 22.5 Creating Cell Style Indicating a Purpose

📄 Page 12

CHAPTER 23. How to Solve Your Common Printing Issues 23.1 Verifying Page Orientation 23.2 Putting Page Break 23.3 How to View the Preview Option 23.4 Set or Clear the Print Area for Your Spreadsheet To Set the Area To Clear the Area CHAPTER 24. How to Move & Copy Your Worksheet Data 24.1 Moving Your Worksheet Within the Same Workbook 24.2 Copying Your Worksheet Within Your Workbook CHAPTER 25. Creating a Database Using an Excel Spreadsheet 25.1 How Relational Database Functions 25.2 Relational Database and Its Applications 25.3 A Simple Example of the Database 25.4 What Is Meant by a Master Database 25.5 Creating a Database in Excel Along With the Use of Filters Converting Data to the Tables Creating the Relations Adding the Fields Filters Options CHAPTER 26. Data Entry Using the Input Masks 26.1 Input Masks Input Mask Has Three Important Parts Characters That Define the Input Masks 26.2 Avoid the Use of Input Masks 26.3 Add an Input Mask Using Mask Wizard 26.4 Adding Input Mask for a Query or Report For Query For Reporting 26.5 Creating Customized Input Mask 26.6 Customizing the Input Masks Using the Field Property Settings 26.7 Input Masks Examples

📄 Page 13

26.8 Using Input Mask Specifically for an Email Address Conclusion

📄 Page 14

Introduction There's not a lot of middle ground when it's about Excel. You've got customers who enjoy it very much, and you're going to sing the songs on the spreadsheet all day. On the other hand, you've got the ones who dislike it. They think it is the software of row and column, and however, in reality, the advanced Excel theories rely on a variety of essential skills set that can be used and recognized in virtually every role in an organization. Since you've familiarized yourself with these definitions, you must be more informed to: Visualize, analyze, and assess your data. Build equations that will enable you to have more details on critical business functions, including workflow, project performance, financial forecasts, and budget estimates. It helps to maintain inventory and consumption levels. Build a convenient data collection platform that senior managers can utilize to evaluate ongoing tasks or conditions in an organization. Develop spreadsheets that help coordinate details and offer a better view of the new information. Interpret and grasp data from the other agencies, suppliers, and clients. Provides answers and responses to market issues by enabling users to view data more sophisticatedly. Maintain a balance between diverse financial and inventory records. Build monitoring mechanisms for various agencies and activities, with different workflow procedures. Advanced Excel preparation would supply companies with highly trained workers. It will also enable employees to help them function more efficiently in their existing positions and transition to higher-level positions.

📄 Page 15

The new MS Excel features (2016 to 2019) are equipped with many essential features, enabling you to explore the power of excel. Improving the efficiency and output to render yourself being an important part of the business Make you great at data organizing and analyzing Have the work faster Greater performance and improved production To sum up all, it's often said that information is strength, and there's no easier way to inspire yourself, except developing your abilities and making your business more valuable through knowledge and use of technology.

📄 Page 16

CHAPTER 1. Understanding the Microsoft Excel MS Excel is a spreadsheet software built for Windows Operating system, Mac operating system, Android operating system, and iOS by Microsoft Corporation. It involves estimation, interactive methods, and pivot tables. It also has a macro programming language named Visual basic. This chapter of the book intends to offer essential knowledge about Microsoft Excel 2019 in detail. 1.1 What Is Excel The Excel spreadsheet program can be considered as a series of columns and rows representing a table. Normally alphabetical characters are typically allocated to columns, whereas the numbers are generally assigned to the rows. The position where one column and a row merge are named a cell. Usually, the address of any cell is indicated by the letters on top of the columns and the numbers representing the rows. 1.2 Versions Since the beginning, almost 29 editions of Excel have been released, starting from 1985 to the present date. Today, most people would have Excel 2016 edition, 2019 edition, or office 365. Each Excel edition was drastically different from the last one, just like the Mac operating system and MS Windows versions are somewhat different today. Here is a compilation of Microsoft Excel versions. OS-2 Versions (2.2, 2.3, 3) Older versions for Windows (2k2, 2k, 97, 95, 3.0 and 2.0) Older versions for Mac (2k1, 2k, 98, 5, and 4) Microsoft Excel 2k3 (Windows) Microsoft Excel 2k4 (Mac) Microsoft Excel 2k7 (Windows)

📄 Page 17

Microsoft Excel 2k8 (Mac) Microsoft Excel 2k10 (Windows) Microsoft Excel 2k11 (Mac) Microsoft Excel 2k13 (Windows) Microsoft Excel 2k16 and MS 365 Microsoft Excel 2k19 1.3 Features Excel offers its customers rich functionalities; the end customer can use the features according to their needs. Below is a collection of certain common functions that excel claims to offer. Adding the Header and Footer Microsoft Excel helps us to maintain the header section and footer section in our spreadsheet file. Find & Replace MS Excel helps its user locate the appropriate data (it might be text or numbers) in your workbook. You can also change the original data with new data. File Password Protection It helps users secure their workbooks from unwanted access and share their details using a code/ password. Data Filtering Feature The filtering option is a fast and simple way to locate and manage data within a collection. Only rows that follow the requirements you define for a column are seen in the filtered list. MS Excel offers two filtering functions: AutoFilter, which contains a filter by the option, with simple parameters

📄 Page 18

Advance filter; used for more specific criteria; Data Sorting Commands In excel, data sorting is referred to as a method of grouping data in a sequential way. MS Excel helps users to sort the data in both ascending and descending order or vice versa. Built-in Formulas for Various Situation s Microsoft Excel has many built-in formulas for addition, average, minimum, maximum, etc. you can use these formulations to fulfill your needs. 1.4 Tools Microsoft Excel has many tools tailored for various users; for the time being, we have mentioned some of them, which will be addressed more in-depth in the next chapters. SUM: This feature adds all the data values of the cells or a range in the document. AVERAGE: In excel, this feature specifies the average values for data. It determines the total of a range and divides the result on the number of datasets of the selection. COUNT: This feature counts the numbers of cells containing numerical data in the statement. This feature is helpful for the fast counting of objects in a set. MAX: This feature defines the greatest value of the cells used in the selection. MIN: This feature defines the lowest value of the cell used in the selection. 1.5 File Types or Extensions An Excel file may have different extensions. For the time being, we are going to cover some of the most popular types:

📄 Page 19

.XLS This file extension is the most popular one; it is the default extension of the spreadsheet that Microsoft Office produces. This extension was commonly used in versions earlier than MS office 2k7. .XLSX This file extension is used for spreadsheet files created with version 2k7 of Excel. The new and latest default extension for an Excel file is usually XLSX. .XLSM An excel 2k7 program creates this extension. This type includes Excel macro. It's a quick way to know that a certain file holds macros with the aid of this extension. .XLSB In case excel files involve a significant volume of data or details, this file extension can support you by compressing, saving, and easy opening functions. How You Can Find the Extension of Any Excel File Pick any file you like to find the extension for Now Right-click this File. A drop-down menu of choices opens. Select the Properties link in the list. Please refer to the screenshot given below. You will have properties window in front of you. Under the section of file types, you will see file type or file extension.

📄 Page 20

Note: Every file type does not accept all the functionalities of excel, such as formulas, etc., and then shows a warning message window. In this situation, you should change the current format of the file to make it compatible.