Practical Excel for Real-World Use: Excel Formulas, Report Writing, and Data Analysis Know-How from Oppadu, the Top Excel YouTube Channel!

Author Oppadu · Publisher JPub · Published 2022-02-15 · ISBN 9791191600704

Book Introduction

Target Audience

- Office workers who handle most of their tasks, from data management to report writing, with Excel
- New employees who know basic Excel but are clumsy at handling tasks
- Marketers who want to use Excel for data analysis and visualization
- Future office workers who want to be perfectly prepared for employment

Table of Contents

CHAPTER 01 Practical Excel Usage for Real-World Professionals
__LESSON 01 Creating a Customized Environment by Changing Excel Default Settings 22
[Excel Basics] Setting default fonts to influence document mood 22
[Excel Basics] Automatic save settings to minimize unexpected losses 23
[Excel Basics] Automatically generated hyperlink settings 25
[Excel Basics] Quickly input special characters with auto-correct language settings 26
[Excel Basics] Quick Access Toolbar to create shortcuts for frequently used functions 27

__LESSON 02 Essential Excel Fundamentals for Advanced Users 30
[Excel Basics] Undoing mistakes during work 30
[Excel Basics] Adjusting row height for readability 31
[Excel Basics] 200% utilization of the status bar for faster verification 32
[Excel Basics] Quickly moving to a specific sheet or a specific cell on a specific sheet 33
[Excel Basics] Find and Replace to view all sheets and change specific values 34

__LESSON 03 Understanding the Basic Operating Principles of Excel 36
[Excel Basics] Understanding AutoFill, essential for handling large amounts of data 36
[Excel Basics] Creating your own AutoFill patterns with custom lists 38
[Excel Basics] Flash Fill (Excel 2013 onwards) that renders text functions obsolete 39
[Excel Basics] Understanding date/time data, simple once you know how 42
[Excel Basics] Understanding cell reference methods for function utilization 43
[Excel Basics] Understanding the 4 types of operators used in Excel 45

__LESSON 04 Comprehensive Guide to All Excel Errors and Their Solutions 46
[Excel Basics] Reasons for a green triangle appearing in the upper left corner of a cell 46
[Excel Basics] List of all error values occurring in Excel 49
[Practical Application] Displaying error values by replacing them with other values 50
[Practical Application] Quickly identifying and resolving text that looks like numbers 51

__LESSON 05 Collection of Essential Shortcuts for Professionals 53
[Practical Knowledge] The magic Alt key that turns all operations into shortcuts 53
[Practical Knowledge] Shortcuts for managing Excel files 54
[Practical Knowledge] Shortcuts for more convenient Excel work 56
[Practical Knowledge] Shortcuts for quick data editing 60
[Practical Knowledge] Shortcuts for quickly changing cell formats 62
[Practical Knowledge] Shortcuts for faster cell movement and selection 64

CHAPTER 02 Essential Excel Usage for Professionals
__LESSON 01 Essential Knowledge for Presentable Tables and Usable Data 68
[Practical Knowledge] Why tables (formats) and data should be separated 68
[Practical Knowledge] Data management is similar to stacking blocks vertically 71
[Practical Knowledge] Reasons to refrain from using the Merge Cells feature 72
[Practical Knowledge] When simply hiding, don't hide, manage as a group 73

__LESSON 02 Building Skills for Convenient Excel Document Work 75
[Practical Application] Centering without merging cells 75
[Practical Application] Easily filling blank cells after unmerging 78
[Practical Application] Finding and entering content into blank cells all at once 80
[Practical Application] Convenient input with automatic Korean/English conversion 81
[Practical Application] Finding and highlighting cells containing specific words 82
[Practical Application] How to change the basic unit of numeric data all at once 84

__LESSON 03 Basic Data Analysis Starting with Excel 88
[Practical Application] Examining data from a new perspective by transposing rows/columns 88
[Practical Application] Restricting duplicate data entry 90
[Practical Application] Defining and using names for big data aggregation 93
[Practical Knowledge] Compare by displaying different sheets/tables simultaneously 96

__LESSON 04 Splitting and Merging Text for Excel Data Processing 99
[Excel Basics] 4 methods of text splitting provided by Excel 99
[Practical Application] Splitting text with justified fill 100
[Practical Application] Utilizing the Text to Columns Wizard 101
[Practical Application] Combining multiple lines into one or separating one line into multiple 104
[Practical Application] Easily combining content from multiple columns into one 108

CHAPTER 03 Formatting Techniques That Transform Reports
__LESSON 01 Essential Cell Formatting Basics to Master 112
[Excel Basics] Exploring the Format Cells dialog box 112
[Practical Knowledge] Display format only changes the appearance 113
[Practical Knowledge] Semicolons distinguish between positive, negative, zero, and text formats 115
[Excel Basics] Exploring various display formats used in cell formatting 117

__LESSON 02 Representative Examples of Cell Display Formats for Professionals 119
[Practical Application] Deleting zeros or displaying with hyphens (-) 119
[Practical Application] Displaying dates as Year/Month/Day (Weekday) 121
[Practical Application] Creating numbers that include a leading zero 123
[Practical Application] Distinguishing numerical increases/decreases with blue and red 124
[Practical Application] Displaying numbers in Korean characters 125

__LESSON 03 Basic Rules for Writing Clean Reports 127
[Practical Knowledge] Numbers must always be right-aligned 127
[Practical Knowledge] Thousands separators must always be displayed 128
[Practical Knowledge] If units are different, they must be explicitly stated 128
[Practical Knowledge] Clearly express item hierarchy with indentation 129
[Practical Knowledge] In tables, use vertical lines only where absolutely necessary 130
[Practical Application] Completing a clean report 131

__LESSON 04 Quickly Analyzing Data with Conditional Formatting 135
[Excel Basics] Conditional Formatting to find values that meet conditions and apply formats 135
[Practical Application] Highlighting when greater than or less than a specific value 137
[Practical Application] Highlighting bottom 10% items 140
[Practical Application] Highlighting entire rows when conditions are met 141
[Practical Application] Highlighting cells that meet all multiple conditions 143
[Excel Basics] Managing applied conditional formatting 144

__LESSON 05 Adding Visualization Elements to Aid Data Understanding 146
[Excel Basics] Simple data visualization using conditional formatting 146
[Practical Application] Visualizing reports with data bars 147
[Practical Application] Visualizing using icon sets 151
[Excel Basics] Understanding Sparkline charts 153
[Practical Application] Adding Sparkline charts and freely modifying them 154

CHAPTER 04 Sharing and Printing Completed Excel Reports
__LESSON 01 Completing a 100-point report with 1 minute of effort 158
[Practical Knowledge] Organize the summary sheet as the first page 158
[Practical Knowledge] Remove gridlines and headers from the sheet 159
[Practical Knowledge] When a sheet has a lot of data, use the Freeze Panes function 160
[Practical Knowledge] If the report is for printing on paper, change the view mode 161
[Practical Application] Differentiating input values & calculated values 162

__LESSON 02 Handling Errors When Referencing External Workbooks 164
[Practical Knowledge] Check external connections before distributing files 164
[Practical Application] Resolving connection errors to external data sources 166

__LESSON 03 Manage Information for External Sharing 168
[Practical Knowledge] If external sources are referenced, clearly state the data source 168
[Practical Knowledge] If the data will be publicly accessible to an unspecified number of people, remove personal information from the document 170
[Practical Knowledge] Efficient methods for managing file versions 172

__LESSON 04 3 Ways to Reduce Excel File Size by Half 174
[Practical Application] Checking file size and saving as a binary file 174
[Practical Application] Changing all formulas to values 175
[Practical Application] Clearing Pivot Cache 176

__LESSON 05 Excel is not a Perfect Program from a Security Perspective 179
[Practical Application] Restricting data input with data validation 179
[Practical Application] Adding supplementary explanations with notes and comment messages 183
[Practical Application] Protecting sheet content from modification 186
[Practical Application] Hiding sheets carefully 190

__LESSON 06 Essential Print Settings for Professionals 194
[Excel Basics] Setting the print area 194
[Excel Basics] Setting paper orientation 196
[Excel Basics] Setting page margins 197
[Excel Basics] Centering the print area on the page 198

__LESSON 07 Considerations When Printing Multi-Page Reports 200
[Practical Knowledge] If a table spans multiple pages, print the header row repeatedly 200
[Practical Knowledge] Display page numbers in headers/footers 202
[Practical Application] Adding watermarks to headers/footers 204

__LESSON 08 Easiest Way to Convert Excel Documents to PDF 208
[Excel Basics] Converting to PDF in Windows 10 or later 208
[Excel Basics] Converting to PDF in Windows versions older than 10 210

CHAPTER 05 From Data Organization to Data Filtering
__LESSON 01 Basic Rules for Excel Data Management 214
[Practical Knowledge] Do not use line breaks or blank cells 214
[Practical Knowledge] Manage aggregated data and raw data separately 216
[Practical Knowledge] Headers must always be entered on a single line 218
[Practical Knowledge] The core rule for data management: stacking blocks vertically 220
[Practical Application] Editing multiple sheets simultaneously 223

__LESSON 02 Creating Your Own List and Sorting in Desired Order 225
[Excel Basics] Understanding ascending/descending sort methods and their limitations 225
[Practical Application] Finding unique values in data and registering custom lists 228

__LESSON 03 AutoFilter to View Only Data That Meets Conditions 232
[Excel Basics] Using basic functions with AutoFilter 232
[Practical Application] Filtering by specifying conditions in AutoFilter 235
[Practical Knowledge] Differences between filtered and hidden ranges 237
[Practical Knowledge] Wildcard characters that boost filtering functionality by 200% 238
[Practical Application] Filtering with wildcards when multiple pieces of information are in one cell 239
[Practical Knowledge] Cautions when using filtering and sorting together 241
[Practical Knowledge] Essential shortcuts for 10x faster AutoFilter usage 242

__LESSON 04 Creating Sales Status Reports with AutoFilter and Sorting Functions 243
[Practical Application] Filtering data with a profit margin of 10% or more 243
[Practical Application] Filtering Top 10 sales profit and visualizing it 246

__LESSON 05 SUBTOTAL Function for Easily Aggregating Filtered Results 248
[Practical Knowledge] Understanding the limitations of general aggregation functions and the SUBTOTAL function 248
[Practical Application] Aggregating only filtered data with the SUBTOTAL function 249

__LESSON 06 Advanced Filter to Maintain Original Data and Specify Various Conditions 251
[Practical Application] Filtering multiple client lists at once 251
[Practical Application] Executing advanced filters with AND, OR conditions 254
[Practical Application] Extracting filtered results to a different sheet than the original 258
[Practical Application] Filtering by selecting only necessary columns 260

__LESSON 07 Utilizing Power Query for Data Management Automation 262
[Excel Basics] Preparing to use Power Query 262
[Practical Application] Organizing data for Power Query application 264
[Practical Application] Running Power Query and filling in blank cells 266
[Practical Application] Changing from horizontal to vertical orientation 268
[Practical Application] Outputting and linking Power Query data to an Excel sheet 270

CHAPTER 06 Tables & PivotTables for Data Automation and Analysis
__LESSON 01 Excel Table Feature with Automatic Range Expansion 274
[Excel Basics] Converting a range to a table and naming it 274
[Practical Knowledge] Precautions when converting a range to a table 277
[Practical Knowledge] Understanding structural references used only in tables 279

__LESSON 02 Creating a PivotTable Reordered into the Desired Format 281
[Excel Basics] Creating a PivotTable and understanding issues related to data organization 281
[Excel Basics] Understanding PivotTable value summarization methods 285
[Practical Knowledge] The '+' rule for configuring a PivotTable in the desired shape 287
[Practical Application] Changing PivotTable layout and styling 289
[Practical Application] Analyzing sales trends by changing field display formats and aggregation methods 294

__LESSON 03 Understanding PivotTable Value Display Formats 299
[Excel Basics] 15 PivotTable value display formats 299
[Practical Application] Analyzing inventory receipts with conditional formatting and value display formats 300
[Practical Application] Displaying total and percentage of receipt quantity with value display formats 305

__LESSON 04 Grouping and Sorting Functions for Rapid Data Aggregation 307
[Practical Application] Analyzing data by intervals with the group function 307
[Practical Application] Grouping date data and separating by week 310
[Excel Basics] 3 types of filters usable in PivotTables 312
[Practical Application] Quickly identifying top customers with filter and sort functions 313

__LESSON 05 Useful Features to Enhance PivotTable Utility 317
[Practical Application] Dividing PivotTable reports into multiple sheets 317
[Practical Knowledge] Calculated fields in PivotTables for calculating new values 319
[Practical Application] Calculating profit margin with calculated fields and resolving #DIV/0! errors 320
[Practical Application] Adding calculated values between row and column items with calculated items 322

__LESSON 06 Slicers and Timelines for Real-time Data Analysis 326
[Excel Basics] Adding slicers, the best combo for PivotTables 326
[Practical Application] Filtering dates with timelines and slicers 329
[Practical Application] Customizing slicers for dashboard creation 333
[Practical Application] Filtering multiple PivotTables simultaneously 336

CHAPTER 07 Learning Basic & Essential Functions to Enhance Excel Usage by 10%
__LESSON 01 [Excel Basics] Using Calculation and Statistical Functions 340
[Excel Basics] Building fundamentals for using functions 340
[Practical Application] Understanding inventory status with SUM, AVERAGE, COUNTA functions 342
[Practical Application] Calculating satisfaction statistics with MAX, MIN, LARGE, SMALL functions 345

__LESSON 02 Logical Functions, Reference Functions, Aggregation Functions 348
[Practical Application] Managing grades with the IF function 348
[Practical Application] Managing inventory status with VLOOKUP, IFERROR functions 351
[Practical Application] Managing sales and delivery status with SUMIF, COUNTIF, AVERAGEIF functions 355

__LESSON 03 Practical Helper Functions to Boost Excel Utility 366
[Practical Application] Processing text with LEFT, RIGHT, MID functions 366
[Practical Application] Preventing errors caused by invisible characters with the TRIM function 370
[Practical Application] Automating tasks with FIND, SEARCH functions 374
[Practical Application] Processing text with the SUBSTITUTE function 376
[Practical Application] Writing clean reports with the TEXT function 378
[Practical Application] Calculating dates with TODAY, DATE, YEAR/MONTH/DATE functions 381
[Practical Application] Calculating date differences with DATEDIF, YEARFRAC functions 383
[Practical Application] Real-time referencing of multiple sheets with the INDIRECT function 385

__LESSON 04 New Functions in Excel 2021, M365 Offering More Powerful Features 389
[Excel Basics] Understanding dynamic array functions and spilled ranges 389
[Practical Application] Using arrays in the latest version of Excel 391
[Practical Application] FILTER, an essential new function more important than VLOOKUP 393
[Practical Application] XLOOKUP, a new M365 function that sounds powerful just by its name 396
[Practical Application] UNIQUE, SORT functions that double in utility when combined with other features 399

__LESSON 05 Essential Function Formulas to Solve Real-World Problems 403
[Practical Application] Outputting results by comparing multiple conditions: Multi-condition IF function 403
[Practical Application] ISNUMBER, SEARCH function formulas for searching word inclusion 407
[Practical Application] INDEX/MATCH function formulas to overcome VLOOKUP limitations 412
[Practical Application] VLOOKUP multi-condition formula for searching results that satisfy multiple conditions 415
[Practical Knowledge] Understanding OFFSET dynamic ranges for automated formatting 418
[Practical Application] Automating list boxes with OFFSET dynamic ranges 420
[Practical Knowledge] How to analyze complex formulas step-by-step 422

CHAPTER 08 Everything About Excel Data Visualization Needed in Practice
__LESSON 01 3 Rules for Creating Good Visualization Charts 426
[Practical Knowledge] For design elements, only remember color schemes 426
[Practical Knowledge] Clearly express what you want to convey 427
[Practical Knowledge] Consider how to convey it 428

__LESSON 02 Basic Formulas for Excel Charts Used in Practice 430
[Practical Knowledge] 5 steps to create an Excel chart 430
[Practical Application] Completing a chart following the 5-step process 435

__LESSON 03 5 Essential Charts for Office Workers 445
[Practical Application] Time trends, future data prediction: Line chart 445
[Practical Application] Understanding differences in values per item: Column chart 450
[Practical Application] When there are many items: Bar chart 452
[Practical Application] Highlighting trends in specific items: Stacked column chart 455
[Practical Application] Emphasizing the proportion of specific items: Pie chart 459

__LESSON 04 Creating Combination Charts and Gantt Charts based on Basic Charts 462
[Practical Application] Displaying total labels on stacked column charts with the combination chart feature 462
[Practical Application] Using a secondary axis in combination charts to represent values with different units 467
[Practical Application] Creating Gantt charts for project/schedule management 472

APPENDIX One Step Further
__APPENDIX 01 Enhancing visualization chart completeness with Excel and Paint 486

__APPENDIX 02 Displaying text values in PivotTables with the Data Model feature 490
[Practical Knowledge] Data Model PivotTable vs. PivotTable 490
[Practical Application] Outputting original text values with a Data Model PivotTable 491

__APPENDIX 03 Predicting future data with Excel: Time series data analysis 498
[Practical Knowledge] What to know before time series data analysis 498
[Practical Application] Predicting future data from historical data 499