CHAPTER 01 Excel Utilization by a Specialist from the Beginning

__LESSON 01 Creating a Custom Environment by Changing Excel's Default Settings 22
[Excel Basics] Setting the Default Font to Set the Atmosphere of Your Document 22
[Excel Basics] Auto-Save Settings to Reduce Unexpected Damage 23
[Excel Basics] Setting up automatically generated hyperlinks 25
[Excel Basics] Quickly Enter Special Characters with Language AutoCorrect Settings 26
[Excel Basics] Quick Access Toolbar 27: Creating Shortcuts for Frequently Used Functions

__LESSON 02 30 Excel Basics You Must Know to Use Excel
[Excel Basics] Undo 30: Reverting Mistakes While Working
[Excel Basics] Adjusting Row Height to Determine Readability 31
[Excel Basics] Faster Verification: Utilizing the Status Bar 200% 32
[Excel Basics] Quickly Jump to a Specific Sheet or Cell in a Specific Sheet 33
[Excel Basics] Find and Replace 34 Sheets to View All Sheets and Change Specific Values

__LESSON 03 Understanding the Basic Operations of Excel 36
[Excel Basics] Understanding Autofill: Essential for Handling Large Data Volumes 36
[Excel Basics] Creating Your Own Autofill Pattern with a Custom List 38
[Excel Basics] Quick Fill (Excel 2013 and later) 39
[Excel Basics] Understanding Simple Date/Time Data 42
[Excel Basics] Understanding Cell References for Function Utilization 43
[Excel Basics] Understanding the Four Operators Used in Excel 45

__LESSON 04: A Complete Guide to All Excel Errors and Solutions 46
[Excel Basics] Why does a green triangle appear in the upper left corner of a cell? 46
[Excel Basics] List of all error values ​​that occur in Excel 49
[Practical Application] Displaying Error Values ​​by Replacing Them with Other Values ​​50
[Practical Application] Quickly Identify and Solve Number-Looking Characters 51

__LESSON 05: 53 Essential Shortcuts Every Professional Needs to Know
[Practical Knowledge] The Magic Alt Key: 53 Shortcuts for Every Task
[Practical Knowledge] 54 Shortcuts for Excel File Management
[Practical Knowledge] 56 Shortcuts for More Convenient Excel Work
[Practical Knowledge] 60 Shortcuts to Speed ​​Up Data Editing
[Practical Knowledge] 62 Shortcuts to Quickly Change Cell Formats
[Practical Knowledge] 64 Shortcut Keys for Faster Cell Movement and Selection

CHAPTER 02 Excel Usage Essentials for Professionals

__LESSON 01: Essential Knowledge for Beautiful Tables and Useful Data 68
[Practical Knowledge] 68 Reasons Why You Should Distinguish Between Tables (Formats) and Data
[Practical Knowledge] Data Management is Similar to Stacking Vertical Blocks 71
[Practical Knowledge] 72 Reasons Why You Should Avoid Using the Cell Merge Feature
[Practical Common Sense] When simply covering something up, don't hide it; manage it as a group. 73

__LESSON 02 Build your skills for convenient Excel document work 75
[Practical Application] Centering Cells Without Merging 75
[Practical Application] Easily Fill in Blank Spaces After Unmerging Cells 78
[Practical Application] Finding and Entering Content in Blank Cells 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 in One Go 84

__LESSON 03: Basic Data Analysis with Excel 88
[Practical Application] Switching Rows and Columns to See Data from a New Perspective 88
[Practical Application] Limiting Duplicate Data Entry 90
[Practical Application] Defining and Using Names for Big Data Aggregation 93
[Practical Knowledge] Compare different sheets/tables simultaneously 96

__LESSON 04 Splitting and Merging Text for Excel Data Processing 99
[Excel Basics] 4 Ways to Split Text in Excel 99
[Practical Application] Splitting Text with Justified Fill 100
[Practical Application] Using the Text Split Wizard 101
[Practical Application] Combining Multiple Lines into One or Splitting a Line into Multiple Lines 104
[Practical Application] Easily merge data entered in multiple columns into one column 108

CHAPTER 03 How to Use Formats to Change Your Report

__LESSON 01 Cell Format Basics You Must Know 112
[Excel Basics] A Look at the Cell Format Dialog Box 112
[Practical Knowledge] The display format only changes the appearance 113
[Practical Knowledge] Separating positive and negative numbers, zeros, and text formats with semicolons 115
[Excel Basics] A Look at the Various Formats Used in Cell Formatting 117

__LESSON 02 Cell Display Format Representative Examples for Practitioners 119
[Practical Application] 0 Erase or mark with a hyphen (-) 119
[Practical Application] Displaying the Date as Year/Month/Day (Day of the Week) 121
[Practical Application] Creating a Number with a Leading Zero 123
[Practical Application] Displaying numerical increases and decreases in blue and red 124
[Practical Application] Displaying Numbers in Korean 125

__LESSON 03 Basic Rules for Writing a Neat Report 127
[Practical Common Sense] Numbers should always be aligned to the right. 127
[Practical Knowledge] Always display the thousands separator 128
[Practical Knowledge] Be sure to specify different units 128
[Practical Knowledge] Clearly Express Item Status with Indentation 129
[Practical Knowledge] Use vertical lines in tables only where absolutely necessary. 130
[Practical Application] Completing a Neat Report 131

__LESSON 04 Quickly Analyze Data with Conditional Formatting 135
[Excel Basics] Conditional Formatting 135: Finding and Formatting Values ​​That Meet Conditions
[Practical Application] Emphasizing Values ​​Greater or Less Than a Specific Value 137
[Practical Application] Highlighting the Bottom 10% of Items 140
[Practical Application] Highlighting Entire Rows When a Condition is Satisfied 141
[Practical Application] Highlighting Cells That Satisfy Multiple Conditions 143
[Excel Basics] Managing Applied Conditional Formatting 144

__LESSON 05 Adding Visualizations to Help Understand Data 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] Sparkline Charts 153
[Practical Application] Adding and Customizing Sparkline Charts 154

CHAPTER 04 Sharing and Printing Completed Excel Reports

__LESSON 01: Complete a 100-Point Report in 1 Minute 158
[Practical Knowledge] Organize the summary sheet into the first page 158
[Practical Knowledge] Removing Gridlines and Headers from Sheets 159
[Practical Knowledge] When there is a lot of data on a single sheet, use the freeze frame function. 160
[Practical Knowledge] If your report is to be printed on paper, change the viewing method. 161
[Practical Application] Distinguishing Between Input and Calculated Values ​​162

__LESSON 02 Handling Errors That Occur When Referencing External Workbooks 164
[Practical Knowledge] Checking External Connections Before Distributing Files 164
[Practical Application] Resolving Connection Errors to External Data Sources 166

__LESSON 03 Managing Information in Preparation for External Sharing 168
[Practical Knowledge] If you reference external sources, be sure to cite the source. 168
[Practical Common Sense] If the document will be made public to an unspecified number of people, remove personal information from the document. 170
[Practical Knowledge] How to Efficiently Manage File Versions 172

__LESSON 04 3 Ways to Cut Excel File Size in Half 174
[Practical Application] Checking File Size and Saving as a Binary File 174
[Practical Application] Converting All Functions to Values ​​175
[Practical Use] Clearing Pivot Cache 176

__LESSON 05 Excel is not a perfect program in terms of security 179
[Practical Application] Restricting Data Entry with Data Validation 179
[Practical Application] Adding Supplemental Explanation with Notes and Explanatory Messages 183
[Practical Application] Protecting Sheet Contents from Editing 186
[Practical Application] Hide Sheets to Keep Them Tight 190

__LESSON 06 Basics of Printing Settings Every Practitioner Must Know 194
[Excel Basics] Setting the Print Area 194
[Excel Basics] Setting Paper Orientation 196
[Excel Basics] Setting Paper Margins 197
[Excel Basics] Centering the Print Area on the Page 198

__LESSON 07 Things to check when printing a multi-page report 200
[Practical Knowledge] If the table spans multiple pages, print the title row repeatedly. 200
[Practical Knowledge] Displaying Page Numbers in Headers and Footers 202
[Practical Application] Adding a Watermark to the Header/Footer 204

__LESSON 08 The Easiest Way to Convert Excel Documents to PDF 208
[Excel Basics] Converting to PDF in Windows 10 or Later 208
[Excel Basics] Converting PDFs on Windows 10 and Earlier Versions 210

CHAPTER 05 From Data Organization to Data Filtering

__LESSON 01 Basic Rules for Excel Data Management 214
[Practical Knowledge] Avoid Line Breaks and Empty Cells 214
[Practical Knowledge] Manage Aggregated Data and Original Data Separately 216
[Practical Knowledge] Headings must be entered on a single line. 218
[Practical Knowledge] Key Rules for Data Management: Stacking Vertical Blocks 220
[Practical Application] Editing Multiple Sheets Simultaneously 223

__LESSON 02 Create your own list and arrange it in the order you want 225
[Excel Basics] Understanding Ascending/Descending Sorting Methods and Their Limitations 225
[Practical Application] Finding Unique Values ​​in Data and Registering Custom Lists 228

__LESSON 03 Automatic filter that checks only data that meets conditions 232
[Excel Basics] Using Basic Functions with AutoFilter 232
[Practical Application] Filtering by Specifying Conditions in Auto Filter 235
[Practical Knowledge] Differences Between Filtering and Hiding Functions 237
[Practical Knowledge] 238 Wildcard Characters That Will Boost Filtering Performance by 200%
[Practical Application] Filtering with Wildcards When Multiple Information is Contained in a Single Cell 239
[Practical Knowledge] 241 Things to Consider When Using Filtering and Sorting Functions Together
[Practical Knowledge] 242 Key Shortcuts to Use Auto Filters 10x Faster

__LESSON 04 Creating a Sales Report with Auto Filter and Sort Features 243
[Practical Application] Filtering Data with a Sales Profit Margin of 10% or More 243
[Practical Application] Filtering and Visualizing the Top 10 Sales Profits 246

__LESSON 05 SUBTOTAL function to easily aggregate filter results 248
[Practical Knowledge] Understanding the Limitations of General Aggregate Functions and the SUBTOTAL Function 248
[Practical Application] Aggregating Only Filtered Data with the SUBTOTAL Function 249

__LESSON 06 Advanced filter that maintains original data and specifies various conditions 251
[Practical Application] Filtering Multiple Client Lists at Once 251
[Practical Application] Running Advanced Filters with AND and OR Conditions 254
[Practical Application] Extracting Filtering Results to a Different Sheet than the Original 258
[Practical Application] Filtering by Selecting Only the Required Columns 260

__LESSON 07 Leveraging Power Query to Automate Data Management 262
[Excel Basics] Preparing to Use Power Query 262
[Practical Application] Organizing Data for Power Query Applications 264
[Practical Application] Running Power Query and Filling in Blank Cells 266
[Practical Application] Changing from Landscape to Portrait 268
[Practical Application] Outputting Power Query Data to an Excel Sheet and Linking It 270

CHAPTER 06 Tables & Pivot Tables for Data Automation and Analysis

__LESSON 01 Excel Table Function with Automatic Range Expansion 274
[Excel Basics] Converting a Range to a Table and Naming It 274
[Practical Knowledge] 277 Things to Consider When Changing a Scope to a Table
[Practical Knowledge] Understanding Structural Reference Methods Used Only in Tables 279

__LESSON 02 Creating a Pivot Table Rearranged to Your Desired Format 281
[Excel Basics] Understanding the Problems of Creating Pivot Tables and Organizing Data 281
[Excel Basics] Understanding How Pivot Tables Summarize Values ​​285
[Practical Knowledge] + Rule 287 for configuring a pivot table the way you want it
[Practical Application] Changing and Decorating Pivot Table Layouts 289
[Practical Application] Analyzing Sales Status by Changing Field Display Format and Aggregation Method 294

__LESSON 03 Understanding the Value Display Format in PivotTables 299
[Excel Basics] 15 Value Display Formats in Pivot Tables 299
[Practical Application] Analyzing Incoming Orders Using Conditional Formatting and Value Display Formats 300
[Practical Application] Displaying the Total and Percentage of Incoming Quantities in Value Display Format 305

__LESSON 04 Group and Sort Functions for Quickly Aggregating Data 307
[Practical Application] Analyzing Data by Segment with Group Functions 307
[Practical Application] Grouping Date Data and Dividing It into Weekly Units 310
[Excel Basics] 3 Types of Filters Available in Pivot Tables 312
[Practical Application] Quickly Identify Your Best Customers with Filter and Sort Features 313

__LESSON 05 Useful Features to Enhance Pivot Table Utilization 317
[Practical Application] Splitting a PivotTable Report into Multiple Sheets 317
[Practical Knowledge] Pivot Table Calculated Fields Used to Calculate New Values ​​319
[Practical Application] Calculating Profit Margin with Calculated Fields and Resolving the #DIV/0! Error 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 Ultimate Pivot Table Combo 326
[Practical Application] Filtering Dates with Timelines and Slicers 329
[Practical Application] Customizing Slicers for Dashboard Creation 333
[Practical Application] Filtering Multiple Pivot Tables Simultaneously 336

CHAPTER 07: Mastering Basic & Essential Functions That Will Fill 10% of Your Excel Usage

__LESSON 01 [Excel Basics] Using Calculation and Statistical Functions 340
[Excel Basics] Mastering the Basics of Functions 340
[Practical Application] Using SUM, AVERAGE, and COUNTA Functions to Monitor Inventory Status 342
[Practical Application] Calculating Satisfaction Statistics Using the MAX, MIN, LARGE, and SMALL Functions 345

__LESSON 02 Logical Functions, Reference Functions, and Aggregate Functions 348
[Practical Application] Managing Grades with the IF Function 348
[Practical Application] Managing Inventory Status with VLOOKUP and IFERROR Functions 351
[Practical Application] Managing Sales and Delivery Status with SUMIF, COUNIF, and AVERAGEIF Functions 355

__LESSON 03 Practical Auxiliary Functions to Increase Excel Utilization 366
[Practical Application] Processing Text with LEFT, RIGHT, and MID Functions 366
[Practical Application] Preventing Errors Caused by Invisible Characters Using the TRIM Function 370
[Practical Application] Automating Tasks with FIND and SEARCH Functions 374
[Practical Application] Processing Text with the SUBSTITUTE Function 376
[Practical Application] Creating Neat Reports with the TEXT Function 378
[Practical Application] Finding Dates with TODAY, DATE, and YEAR/MONTH/DATE Functions 381
[Practical Application] Calculating Between Dates with the DATEDIF and YEARFRAC Functions 383
[Practical Application] Real-time Reference to Multiple Sheets with the INDIRECT Function 385

__LESSON 04 New Functions in Excel 2021 and M365: 389 More Powerful Features
[Excel Basics] Understanding Dynamic Array Functions and Variance Ranges 389
[Practical Application] Using Arrays in the Latest Version of Excel 391
[Practical Application] FILTER 393, an Essential New Function More Important Than VLOOKUP
[Practical Application] A New M365 Function That Sounds Powerful Just by Its Name: XLOOKUP 396
[Practical Application] UNIQUE and SORT Functions: Their Utilization Doubles When Combined with Other Functions 399

__LESSON 05 Essential Function Formulas for Solving Difficulties in Practice 403
[Practical Application] Comparing Multiple Conditions and Outputting Results, Multi-Condition IF Function 403
[Practical Application] ISNUMBER and SEARCH Function Formula 407 for Finding Whether a Word Contains a Specific Character
[Practical Application] INDEX/MATCH Function Formula 412: Solving the Shortcomings of the VLOOKUP Function
[Practical Application] Finding Results That Satisfy Multiple Conditions: VLOOKUP Multi-Condition Formula 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 You Need to Know About Excel Data Visualization in Practice

__LESSON 01 Three Rules for Creating Great Visualization Charts 426
[Practical Knowledge] Remember only color when it comes to design elements 426
[Practical Knowledge] Clearly State What You Want to Communicate 427
[Practical Knowledge] Think About How to Deliver Your Message 428

__LESSON 02 Basic Excel Chart Formulas Used in Practice 430
[Practical Knowledge] 5 Steps to Creating an Excel Chart (Part 430)
[Practical Application] Complete the Chart in 5 Steps 435

__LESSON 03 5 Practical Charts Every Office Worker Must Remember 445
[Practical Application] Time Flow, Future Data Prediction: Line Chart 445
[Practical Application] Identifying the Difference in Values ​​by Item Using a Vertical Bar Chart 450
[Practical Application] When there are many items, use a horizontal bar chart 452
[Practical Application] Stacked Bar Chart 455 to Highlight Change Trends in Specific Items
[Practical Application] When emphasizing the market share of a specific item, use a pie chart 459

__LESSON 04 Creating a Mixed Chart and Gantt Chart Using Basic Charts 462
[Practical Application] Displaying Total Labels on Stacked Bar Charts Using the Mixed Chart Feature 462
[Practical Application] Using the Secondary Axis of a Mixed Chart When Representing Values ​​with Different Units 467
[Practical Application] Creating a Gantt Chart for Project/Schedule Management 472

APPENDIX One Step Further

APPENDIX 01 Improving the Quality of Visualization Charts with Excel and Paint 486

__APPENDIX 02 Displaying Text Values ​​in a Pivot Table Using Data Model Features 490
[Practical Knowledge] Data Model Pivot Table vs.
Pivot Table 490
[Practical Application] Outputting Original Text Values ​​with a Data Model Pivot Table 491

APPENDIX 03 Predicting Future Data with Excel, Time Series Data Analysis 498
[Practical Knowledge] 498 Things to Know Before Analyzing Time Series Data
[Practical Application] Predicting Future Data Using Past Data 499

Stop organizing concepts and learn the basics quickly and practice properly using real-world examples with 100% practical application.
100% customized free video lecture service!

1.
You can choose to learn Excel basics, practical knowledge, and practical application according to your needs.

If you have enough time, it is recommended that you familiarize yourself with everything before starting the work.
However, there will be not enough time to balance work and study.
First, let's look at [practical application] that can be applied immediately to work.
After that, whenever time permits, you can build up your basics with [Excel Basics] and [Practical Knowledge].
Also, refer to the '8-Hour Study Roadmap for Busy Office Workers' on page 13 of the book.


2.
Rather than just introducing the obvious features, we've included know-how on Excel reports, data management, and task automation!

You've probably come across plenty of Excel function descriptions, like, "Clicking a certain icon on the ribbon menu will execute a certain function."
This book is not just a simple explanation of functions, but contains the author's 10 years of experience in the workplace, demonstrating how to effectively handle reports created in Excel and data analysis.
Additionally, I've included Excel automation tips to help solve the concerns of office workers that I've heard while running a YouTube channel.
Starting with this book, transform yourself from being just proficient in Excel to becoming a professional who excels at handling work using Excel.

3.
Please actively utilize the YouTube channel and Oppa Doo Excel website.

The representative Excel YouTube channel, OppaduExcel (https://www.youtube.com/오빠두Oppadu), run by the author, and the OppaduExcel homepage (https://www.oppadu.com/), which provides almost all information about Excel with the goal of becoming an Excel Wikipedia, are always open.
If you have any questions or problems that you find difficult to solve while studying Excel or using Excel for work, please visit us anytime.
It's packed with video lectures covering the content covered in the book, as well as advanced automation formulas not covered in the book, and tons of other Excel-related information.

Target audience

- An office worker who handles most of his work, from data management to report writing, using Excel.
- A new employee who knows the basics of Excel but is clumsy at handling tasks.
- Marketers who want to use Excel for data analysis and visualization.
-Aspiring job seekers who want to prepare perfectly for employment