A Definitive Guide to Microsoft Excel 2019

✍ Scribed by Derrick Richard

• Year: 2020
• Tongue: English
• Leaves: 241
• Category: Library

✦ Synopsis

2020 UPDATED GUIDE TO MICROSOFT EXCEL 2019
This book is intended for anyone looking to learn the basics of applying Excel’s robust Data analysis and statistical tools to their businesses or work activities. If understanding formulas and functions is a major challenge or you are not especially mathematically-inclined, or if you want a spreadsheet package to put your business or accounting needs together, then this is guide will help you.

Here you’ll learn how to use statistical tests and key data analysis using Excel 2019 without being confused by the underlying theory. This book clearly and methodically explains how to create and use Excel formulas and functions to solve everyday business problems.

Excel 2019 is a readily available computer program for business owners and people who need to keep proper accounting. It is also an effective tool for quantitative analysis in Accounting, data analysis and business modeling courses. Excel’s powerful numerical computational ability and graphical functions make learning this package a necessity.
This illustrative guide shows Excel’s capabilities and focuses on rendering the subject as simple as possible, appropriately and efficiently.
Some of the unique features of this book include:
•Steps to use Excel and a detailed explanation of each action so that you can understand how to apply them.
•Specific objectives for each Excel function and how to apply them in your business.
•Over 130 screenshots to help you carryout Excel calculations correctly
•Some practical examples and problems taken from real business models.
•Clear and straightforward explanation to Excel Functions and formulas without bogging you down with mathematical details.
•To create each Excel file used in the illustrations yourself. Note that you are expected to develop an Excel file for your business.
•Provision of ample practical illustrations on how to develop your Excel files.
•how to perform complex calculations, and create a database.
•You will also have insights to Excel features like Charts, Collaboration, Data Loss Protection, Smart Lookups, Text lines in a cell, Enhanced PivotTable, Multi-select Slicer, etc.
•A “List of Microsoft Excel Keyboard Shortcut Keys” is given in Appendix A.
This book is appropriate for Data Analysis, Business Modeling and Accounting. It also helps users who wish to understand the basics as well as advanced Excel Functions or improve their Excel Application.

✦ Table of Contents

Contents
Copyright ............................................................................................................i
Why This Guide? ..............................................................................................vii
Chapter One.......................................................................................................1
Introduction.......................................................................................................1
Opening Excel Worksheet.............................................................................. 1
New Workbook.............................................................................................. 3
Home Tab...................................................................................................... 4
Insert tab....................................................................................................... 6
Page Layout................................................................................................... 6
Worksheet..................................................................................................... 7
Creating Additional Worksheet.....................................................................10
Status Bar .....................................................................................................10
Chapter Two ....................................................................................................12
Excel Basics ......................................................................................................12
Customizing Excel Environment ....................................................................12
Customizing the Quick Access Toolbar ..........................................................13
Saving A Workbook.......................................................................................18
Saving A Workbook without Closing the Entire Program ...............................19
Accessing Recent Worksheets.......................................................................19
Paste Options ...............................................................................................20
Inserting a Function ......................................................................................21
Calculation options.......................................................................................22
Chapter Three..................................................................................................23
Functions .........................................................................................................23
SUM & AVERAGE Function............................................................................24
AVERAGE Function .......................................................................................29iii
RANK.AVG Function......................................................................................30
AVERAGEA Function .....................................................................................32
AVERAGEIF Function.....................................................................................33
SUMIF Function ............................................................................................34
SUMIFS Function ..........................................................................................38
COUNT Function...........................................................................................39
COUNTA .......................................................................................................43
COUNTIF Function ........................................................................................44
COUNTIFS Function ......................................................................................45
COUNTBLANK Function.................................................................................46
MIN & MAX Function....................................................................................46
ROUND Function ..........................................................................................49
ROUNDUP Function ......................................................................................51
ROUNDDOWN Function................................................................................52
CONCATENATE Function...............................................................................53
INDEX Function.............................................................................................57
OFFSET Function...........................................................................................60
MATCH Function...........................................................................................67
Trim Function ...............................................................................................69
SQRT Function ..............................................................................................70
TODAY Function ...........................................................................................71
Chapter Four....................................................................................................73
Logical Excel Functions.....................................................................................73
IF Function....................................................................................................73
AND Function ...............................................................................................76
OR Function..................................................................................................81
NOT Function ...............................................................................................84iv
XOR Function................................................................................................88
IFERROR Function .........................................................................................90
IFNA Function...............................................................................................90
Error Checking ..............................................................................................91
NESTED IF Function.......................................................................................92
Chapter Five.....................................................................................................95
Text Functions .................................................................................................95
LOWER Function...........................................................................................95
UPPER Function ............................................................................................96
PROPER Function..........................................................................................97
LEFT Function ...............................................................................................97
RIGHT Function.............................................................................................99
MID Function..............................................................................................100
LEN Function ..............................................................................................101
EXACT Function ..........................................................................................102
RANDBETWEEN Function............................................................................104
Chapter Six.....................................................................................................106
More Excel Tips..............................................................................................106
AutoFIll Options..........................................................................................106
Duplicating Content of a Cell.......................................................................111
Text to Column ...........................................................................................111
Editing Data................................................................................................116
Editing Cells, Columns and Rows.................................................................116
Find & Replace Data ...................................................................................117
Go To Option ..............................................................................................120
Transpose Data...........................................................................................123
AutoFormat Feature ...................................................................................126v
Chapter Seven................................................................................................129
Conditional Formatting..................................................................................129
Basic Conditional Formatting ......................................................................129
Split WorkSheet..........................................................................................137
Freeze Panes ..............................................................................................138
Viewing Multi Worksheets and Workbooks ................................................140
Speak Cells .................................................................................................143
Sound Effects..............................................................................................144
Accessibility Checker ..................................................................................145
Translate ....................................................................................................148
Row Height, Column Width and AutoFit......................................................148
Hiding Column(s), Row(s) and Worksheet(s) ...............................................149
Checking Worksheet for Spelling Errors ......................................................150
Smart Lookup & Thesaurus .........................................................................152
Basic Cell Formatting ..................................................................................153
Custom Number Format .............................................................................153
Borders & Colors ........................................................................................161
Formatting Shortcuts ..................................................................................165
Adding Borders to Worksheet.....................................................................167
Align & Position ..........................................................................................168
Text Wrapping............................................................................................171
Data Validation...........................................................................................172
What-if Tools..............................................................................................177
Goal Seek Function .....................................................................................178
Data Tables.................................................................................................182
Scenario Managers .....................................................................................188
Data Sorting ...............................................................................................198vi
Filter Option ...............................................................................................198
Chapter Eight .................................................................................................201
Statistical Functions.......................................................................................201
Average ......................................................................................................201
Median Function ........................................................................................202
Mode Function ...........................................................................................204
Standard Deviation Function.......................................................................205
Large Function............................................................................................207
Small Function............................................................................................209
CORREL Function ........................................................................................210
Chapter Nine..................................................................................................212
Charts in Excel................................................................................................212
Charts.........................................................................................................212
Column Chart .............................................................................................212
Line Charts .................................................................................................214
Pie Chart.....................................................................................................215
Bar Chart ....................................................................................................217
Surface Chart..............................................................................................218
Recommended chart ..................................................................................219
Pivot Table..................................................................................................220
APPENDIX 1 ...................................................................................................227
APPENDIX 2 ...................................................................................................229
About the Author...........................................................................................231

✦ Subjects

A Step by Step Guide to Master Microsoft Excel Formulas and Functions for Accounting