Step 1: Get familiar with Excel

Part 1 | Data Entry
001 Examining the Excel Screen
002 Run with ribbon menu shortcut key
003 Adding icons to the Quick Launch Toolbar
004 Pin frequently used documents for easy finding
005 Saving an Excel document as a PDF
006 Save as a compatible document for viewing in Excel 97-2003
007 Entering data
008 Entering numeric data
009 Enter date
010 Change date format in bulk
011 Enter the time
012 Entering symbols and special characters

Part 2 | Fill Handle/Display Format
013 Automatically fill in data values ​​and formats
014 Displaying thousands/decimal places/negative numbers
015 Displaying percentages and pasting selections
016 Displaying currency and accounting formats
017 Display Resident Registration Number
Automatically convert 018 phone number format
019 Easily display symbols for titles and mobile phone numbers
020 Batch specifying multiple display formats

Part 3 | Excel Table/Row/Column/Sheet
021 Selecting cells and specifying a range
022 Inserting and deleting rows and columns
023 Resizing rows and columns
Hide rows and columns 024
025 Pasting data in various ways
026 Paste data as a picture
027 Adding and Deleting Worksheets
028 Hide Worksheet
029 Copying and Moving Worksheets
030 Create a worksheet group
031 Freeze rows and columns simultaneously
032 Database structure and input method
033 Features of Excel Tables
034 Convert to Excel table and easily change the design
035 Add summary row to automatically calculate
036 Unpacking an Excel table

Part 4 | Print
037 Print on one page
038 Repeat printing of header row
039 Displaying page numbers in the footer
040 Inserting a logo image in the footer
041 Setting the print order and printing on both sides
042 Setting the items to display in print

Step 2: Create a Form

Part 5 | Cell Formatting/Conditional Formatting
043 Change font size/color and border
044 Merge multiple cells into one
045 Displaying duplicate values
046 Highlight cells with a year-on-year increase or decrease of 0% or more
047 Display the ranking values ​​of the best items and the bottom 15%
048 Displaying values ​​above/below average
049 Apply fill color according to number size
050 Display the proportion of the total value as a horizontal bar
051 Distinguishing numeric values ​​by traffic light colors
052 Editing and Deleting Conditional Formatting Rules

Part 6 | Charts/Sparklines
053 Understanding Chart Elements
054 Creating charts in various ways
055 Changing the chart type_Bundled/stacked vertical bar chart
056 Changing chart layout and colors_Line chart
057 Adding chart elements (axes/titles/gridlines/legends)
058 Formatting chart elements
059 Adjusting the minimum value and axis units of the Y-axis
060 Creating a Dual-Axis Graph with Different Chart Types_Combo Chart
061 Swapping the positions of X-axis and Y-axis data
062 Changing the data range to display in the chart
063 Inserting a background image into a chart and deleting gridlines
064 Automatically converting percentages to pie charts_Circular charts
065 Overlapping two pie charts_Doughnut chart
066 Comparing two data sets by area_Area chart
067 Product Positioning Diagram_Radial Chart
068 Displaying sales distribution by time zone_Scatter chart
069 Saving chart format
070 Pasting an Excel chart into PowerPoint
Inserting a mini chart into a cell 071_Sparkline
072 Changing the Sparkline Design

Part 7 | Photos/Shapes/Hyperlinks
073 Insert only the image with the white background erased
074 Reduce document size by compressing inserted images
075 Create a Standout Title with Word Art
076 Batch edit a group of shapes with letters entered
077 Creating an Organizational Chart with SmartArt
078 Linking to another worksheet

Step 3: Smart Data Management with Excel

Part 8 | Data Validation
079 Restricting input range and setting error messages with data validation
080 Data Validation Modification and Bulk Deletion
081 Restrict date input
Restricting weekend date entry using formula 082
083 Limit time input
084 Restricting duplicate value input
085 Create a selection list to limit input
086 Define cell addresses in a list range by name
087 Restricting subcategory entry based on major category list

Part 9 | Protecting/Sharing Notes/Documents
Insert a note in cell 088 to add a comment
089 Display notes and adjust their size and position
090 Set a password to restrict opening/writing to a document
091 Preventing worksheet names from being modified or deleted_Protecting the workbook
092 Restricting editing of worksheet cells_Sheet protection
093 Setting a password to protect only some cells in a worksheet
094 Sending Excel documents via Outlook email

Part 10 | OneDrive/Mobile App
095 Saving documents to OneDrive
096 Sharing documents stored in OneDrive
097 Editing documents shared via OneDrive
098 Editing shared documents simultaneously with other users
099 Checking Excel documents on a smartphone

Part 11 | Sort/Subtotal/Filter
Add multiple sort criteria
Unmerge 101 cells and automatically fill in empty cells
102 Sort only selected column data
103 Sort by horizontal order of titles entered
Summarize totals by 104 items_Subtotals
105 Adding Subtotal Group Items and Calculations
106 Applying Auto Filters
107 Disable automatic filters
View sum/average/maximum/minimum values ​​of 108 filter results
Filter by a number range of 109
Filter only the top N or bottom N% of items
111 Filter by average
112 Filtering date data
Filter by 113 colors
114 Copy search results to another location using advanced filters
115 Managing search worksheets and source data separately
116 Filtering tables with inserted images
117 Calculating filter values ​​using the SUBTOTAL function

Step 4: The Ultimate Data Analysis

Part 12 | Pivot Tables
118 Understanding Pivot Tables
119 Creating a Summary Report with a PivotTable
120 Changing the Report Layout and Design
121 Changing the calculation type and field name of the [Value] field
122 Changing the numeric display format in the [Value] field
123 Updating the modified source data in the pivot table
124 Adding Data to an Excel Table and Updating a PivotTable
125 Adding Data to a Regular Table and Updating a PivotTable
126 Adding various formulas to the calculated items in the [Value] field
127 Error Value and Filling Empty Cells
128 Group by date units
129 Automating weekly reports for 7 days
Aligning Row/Column Labels 130
131 Add a slicer to filter
132 Filtering periods with a timeline
133 Create separate worksheets for each filter item
134 Creating a Pivot Chart

Part 13 | Integration/Quick Analysis/Power Query
135 Update the consolidated table with changes to the reference table
136 Combine multiple tables and paste them into a new format.
137 Calculate with Quick Analysis without entering formulas
138 Splitting a string using text split
139 Remove duplicate items
140 Updating exchange rate information with tables from the web

Part 14 | Virtual Analysis and Forecasting
141 Finding the X value of a formula that satisfies the target value_Finding the target value
142 Add a Find menu
143 Adjusting Sales and Expenses to Achieve Net Income Targets_Solution
144 Saving scenarios by predicting various variables
Creating a 145 Scenario Summary Report
146 Obtaining predicted values ​​based on data trends_Prediction sheet

Part 15 | Basic Formulas/Text Functions
Calculating formulas with operators 147
148 Understanding the Order of Operations_Calculating the Profit Growth Rate
149 Understanding Cell References_Relative/Absolute References
Calculating with 150 mixed references
151 Calculate by referring to the name box
152 Writing a function
153 Calculating the sum and average_SUM/AVERAGE function
154 Extracting strings_LEFT/RIGHT/MID/FIND functions
155 Concatenating Characters_CONCATENATE/CONCAT/TEXTJOIN Functions
156 Changing uppercase/lowercase_PROPER/UPPER/LOWER functions
Repeat input to match 157 digits_REPT function
Displaying formulas instead of results in cell 158

Step 5 Excel over Calculator

Part 16 | Date/Time/Statistical Functions
Extracting year/month/day from date 159_YEAR/MONTH/DAY function
160 Extracting today's date and current time_TODAY/NOW function
161 Finding age based on current date_YEAR/TODAY function
162 Highlighting Receivables by Entering a Formula in Conditional Formatting_TODAY Function/Comparison Operator
Extracting the day of the week from a date_WEEKDAY function
164 Calculating Working Days_NETWORKDAYS/NETWORKDAY.INTL Function
165 Calculating the length of service_DATEDIF function
Counting the number of cells in 166_COUNT/COUNTA/COUNTBLANK functions
167 Finding the maximum and minimum values_MAX/MIN functions
Finding the 168th largest or smallest number_LARGE/SMALL functions
169 Finding the Rank_RANK/RANK.AVG Function
170 Rounding up/rounding down_ROUND/ROUNDUP/ROUNDDOWN
171 Rounding up/up/down to multiples of N_MROUND/CEILING/FLOOR functions
172 Find the remainder_MOD function

Part 17 | IF Series/Reference Functions
173 Displaying results based on conditions_IF function
174 Displaying values ​​that satisfy multiple conditions_IF/OR/AND functions
175 Displaying multiple result values ​​based on conditions_IF function nesting
176 Counting the number of cells that meet a condition_COUNTIF/COUNTIFS functions
177 Finding the average of values ​​that meet conditions_SUMIF/AVERAGEIF series functions
178 Finding the maximum/minimum value within a range of conditions_MAXIFS/MINIFS functions
179 Replacing error values ​​in formulas_IFERROR/IFNA functions
Extracting row number values ​​from a reference range_HLOOKUP function
181 Extracting approximate/matching values ​​from a reference table_VLOOKUP function
182 Creating a summary report by referencing tables in other sheets_VLOOKUP function
183 Reference only some characters of the value to be found using wildcard characters_VLOOKUP function
Find row/column number and get reference value_MATCH/INDEX function

Part 18 | Database/Array Formulas
185 Calculating the sum/average of values ​​that meet a condition_DSUM/DAVERAGE function
Extracting the maximum/minimum values ​​from values ​​that match the 186 conditions_DMAX/DMIN functions
Counting the number of cells that match 187 conditions_DCOUNT/DCOUNTA function
188 Extracting values ​​that satisfy multiple conditions_DGET function
189 Finding the grand total using an array formula
190 Display or remove duplicate values
191 Calculating frequency by interval using array formula_FREQUENCY function

Step 6: The Secret to Leaving Work on Time

Part 19 | Macro Recorder
192 Adding a Macro Menu to the Ribbon Menu
193 Recording and Running Macros
194 Save Macro
195 Disabling and Resetting Security Warnings in Macro Documents
196 Lock and unlock only cells containing formulas
197 Creating a Macro Select Button_Form Control
198 Applying a macro using relative references

Part 20 | VBA
199 Recording a Macro with the VB Editor
Resolving a Name Conflict Error When Copying 200 Sheets
Combine all Excel sheets in folder 201 into one document
202 Sort by worksheet name in ascending order
203 Merging tables from multiple worksheets into one

Search

Get sample files you can use right away at work!

The 187 example files, containing the author's know-how, boast a level of perfection that makes them suitable for immediate application in practice.
This diverse sample file system is a unique advantage of this book that you won't find anywhere else. By repeating the process of converting the sample files into finished files according to the book's explanations, you will realize that anyone can learn Excel and utilize it widely in their work.
Now, let's walk the path of a professional worker with the God of Excel.

One cue, quick and easy!

Based on his experience working with leading companies such as Adidas, Calvin Klein, Korea Tourism Organization, and Hotel Shilla, the author clearly identifies cases where Excel is necessary in business and provides corresponding Excel functions and explanations.
Another feature of this book is that it is structured so that theory and practice can be conducted together in one part without dividing them, thereby increasing efficiency.
If you find the part names and example files needed for the work in progress and study them repeatedly, you will find yourself becoming an Excel expert before you know it.
Rather than simply listing Excel's functions, this is a practical Excel manual that points out only the functions necessary for work.

Apply to all versions of Excel you have, from 2007 to Office 365!

From 2007, 2010, 2013, 2016, 2019, to Office 365, programs are bound to be upgraded, and Excel is no exception.
However, it is not possible to work overtime all the time just to study the version of Excel installed on the company computer.
No more frustration over different versions of Excel at home and work.
This book primarily explains functions that can be used in all versions of Excel, but focuses on reducing errors and increasing usability by separately highlighting only the differences.