Chapter 01: Learning the know-how to improve work speed

[Essential Work Tips]
Section 01: Mastering Essential Excel Task Tips
01 Learn various ways to move the cell pointer.
02 Learn various ways to easily select a range of cells.
[Key] 03 Centering data in cells without merging them
04 Entering the same value in multiple cells that are separated
Enter a number starting with 05 0
06 Automatically enter today's date and time
07 Enter the same value as the cell above
08 Copy except hidden areas
09 Select only cells with text
10 Quickly enter frequently used symbols
11 Registering desired symbols in the autocorrect list
[Key] Copy and paste the 12 payment fields as an image.
13 Capture web pages and import them into Excel
14 Displaying the + shape when the auto-fill handle does not appear
[Practical Project] Automatically Displaying Data to Fit Cell Width

[How to edit data]
Section 02: Quick Data Editing Methods
01 Separate and input only specific text from a string
02 Find empty cells and change them to 0 at once
03 Change all cells with sales of 0 to 'None' at once
04 Fill in the upper cell value in the unmerged blank cell
05 Delete only rows with empty cells at once
06 Specifying a margin at the end of a number
07 Displaying numbers in Korean or Chinese characters
08 Omit numbers below the thousandth place to simplify display
09 Displaying specific characters before or after data
10. Specify a custom date format for the start date
11 Display the day of the week the entered date is on
[Key] Calculating the total accumulated working hours exceeding 12 24 hours
13 Protect the entire sheet's contents with a password
14 Protect only a specific range of data
[Key] 15 Avoid printing blank pages when printing
[Practical Project] Copying Column Widths in Various Ways

[Format]
Section 03 Using various formats that automatically change based on values
01 Specifying a table style for data
02 Inserting and styling a slicer in a table
03 Delete the table format and change it to general data.
04 Displaying graphs or grades proportional to sales
05 Formatting a course containing the word 'Excel'
06 Automatically formatting duplicate data
07 Formatting cells containing the top 30% of applicants
08 Formatting cells that exceed the average number of members
09 Conditional formatting linked to cells
10 Formatting rows containing specific names
[Key] 11 Formatting Quantities Within a Specific Range
[Core] Formatting Rows Containing Specific Words
13 Automatically display borders when new data is entered
14 Find and edit cells with conditional formatting applied
[Practical Project] Displaying a Border at the Bottom of Cells by Position

Chapter 02: Mastering Useful Functions That Reduce Work Time

[Formula Principle]
Section 04 Understanding the Principles of Formulas: Easy to Understand
01 Review the rules for entering formulas
02 Examining the types of operators
03 Write a formula and fill in data without formatting
04 Creating nested functions using the function wizard
05 Copy the formula by changing it to the value displayed in the cell.
06 Referencing other cells in formulas
07 Understanding Relative and Absolute References
08 Calculating daily working expenses using absolute reference
09 Calculating incentives by percentage of sales amount using mixed references
10. Create formulas by defining names instead of cell addresses.
11. Define names using row/column titles
[Practical Project] Calculating Sales Achievement Rates by Branch

[Function]
Section 05: Learning basic functions that simplify complex calculations
01 Calculate the average sales performance of each product by branch
- AVERAGE, AVERAGEA functions
02 Find the total number of products and the number of products sold
- COUNT, COUNTA, COUNTBLANK functions
03 Finding the highest and lowest sales amounts
- MAX, MIN, MEDIAN functions
04 Find the third largest and second smallest output values
- LARGE, SMALL functions
05 Mark 'Pass' or 'Fail' by score
- IF function
[Key] 06 Displaying Evaluation Results as 'Excellent', 'Average', and 'Effort' 1
- Multiple IF functions
07 Displaying evaluation results as 'Excellent', 'Average', and 'Effort' 2
- IFS function
08 Mark 'Pass' when all conditions are met
- AND, OR functions
Displaying 0 instead of error message 09
- IFERROR function
[Key] Finding the number of people and orders for each of the 10 positions
- COUNTIF, COUNTIFS functions
11. Calculate the total quantity delivered by work method
-SUMIF, SUMIFS functions
12. Calculating the average delivery quantity by work method
- AVERAGEIF, AVERAGEIFS functions
13 Find the total count and sum using only visible cells on the screen
- SUBTOTAL function
14 Extracting only a few characters from text
- LEFT, RIGHT, MID functions
15 Displaying the position of a specific character in a string
- FIND, SEARCH functions
Display *** from the eighth digit of number 16
- REPLACE function
17 Replace specific characters in a string with other characters
- SUBSTITUTE function
Repeat the ★ symbol for 18 practical scores
- REPT function
19 Remove spaces before and after a string
- TRIM function
[Key] Converting 20 characters to numbers and calculating them
- VALUE function
[Key] 21 Converting numbers, dates, and times to text
- TEXT function
[Key] 22. Refer to the list and display information for each code.
- VLOOKUP function
23 Get similar values ​​when the corresponding code is not present
- VLOOKUP function
24 Compare the list displayed horizontally to get the value
- HLOOKUP function
[Core] 25 Retrieving a value at a desired position in a list
- INDEX function
26 Referencing a cell address specified as a string
- INDIRECT function
27 Displaying the region name by referring to the region code 1
- CHOOSE function
28 Displaying the region name by referring to the region code 2
- SWITCH function
Displaying the date and time of the 29th working day
- TODAY, NOW functions
30 Displaying dates using formulas
- DATE function
Separating year, month, and day from a date
- YEAR, MONTH, DAY functions
32 Indicate what day of the week the receipt date is
- WEEKDAY function
[Key]33 Calculating the period from the release date to the working day
- DATEDIF function
Understanding the Principles of 34-Hour Data
[Practical Project] Calculating Total Working Hours and Calculating Payroll

[Function]
Section 06 Learning frequently used practical functions
01 Understanding Array Functions
[Key] 02 Finding the number of cells that satisfy multiple conditions
- Array function formula
03 Finding the sum of cells that satisfy multiple conditions
- Array function formula
04 Converting amounts to different currencies
- Array function formula
[Key] 05 Calculating Performance-Based Bonuses for Sales Performance
- Multiple IF, IFS, VLOOKUP functions
[Key] 06 Find the branch name corresponding to the sales ranking
- LARGE, MATCH, INDEX functions
[Key] 07 Calculating Vacation Days Based on Length of Service
- VLOOKUP, DATEDIF functions
[Key] 08 Finding gender and age using resident registration number
- REPLACE, MID, DATE functions
[Core] 09 Displaying text in numbers
- SUMPRODUCT, TEXT functions
[Key] Displaying 10 numbers one digit at a time
- TEXT, MID, COLUMN functions
[Key] 11 If the shipping date is Saturday/Sunday, change it to Monday.
- WEEKDAY function
12 Do not display product information when it is incorrect.
- VLOOKUP, IFERROR functions
[Practical Project] Defining Names and Applying Array Functions

Chapter 03 Creating Accurate and Efficient Analysis Data

[Data Analysis]
Section 07: Systematically Analyzing Data to Predict Future Values
01 Organize data into workable data
02 Organize regional data in your desired order
- Sort
03 Extract only data that meets specific conditions
- Automatic filter
04 Extract only data that starts with a specific character or was received in October.
- Automatic filter
05 Extracting data that satisfies either of two conditions
- Advanced filters
06 Extracting data by defining a name in the range
- Advanced filters
[Key] 07 Extracting data that satisfies conditions from another sheet
- Advanced filters
08 Finding the number of orders by region
- Subtotal
09 Adding a new function to the partial sum results
- Subtotal
10 Copy only the subtotal results to another sheet
- Subtotal
11 Extract only unique items from a specific column
- Remove duplicate items
12 Find and delete duplicate data
- Remove duplicate items
[Key] 13 Display only the employees with the best sales performance
- Remove duplicate items
Select the desired list by displaying it in cell 14
- Data validation
[Key] 15 Displaying data from other sheets as a list
- Data validation
16 Restricting input to numbers within a specified range
- Data validation
[Practical Project] Preventing Duplicate Business Partners from Being Entered

[Pivot Table]
Section 08 Creating a Pivot Table to Improve Work Efficiency
01 Find the number of male employees in the Busan and Daegu areas
02 Changing the layout and design of a pivot table
03 Specifying the function to be applied to the pivot table and the format for displaying values
04 Editing row or column labels
05 Changing the location of the partial sum and the function to use
06 Display items with no values ​​in the list as well
07 Sort the list in the order you want
08 Adding a New Calculated Field to a Pivot Table
[Core] 09 Filtering and Styling Data with Slicers
[Key] Filtering Q2 Data with a 10-Hour Bar
[Key] Grouping Delivery Dates by Quarter in a Pivot Table 11
12 Applying Changed Values ​​from Source Data to a PivotTable
Applying original data added outside the range to the PivotTable 13.
[Key] 14 Automatically Reflecting Added Data Areas in Pivot Tables
15 Displaying differences and ratios against mood values
Displaying quarterly totals and percentages
17 Creating a PivotChart that Links to a PivotTable
[Practical Project] Extracting Only Data Used in Pivot Table Results

Chapter 04: Adding Visual Effects to Revitalize Your Report

[Form Control]
Section 09 Conveniently managing data using form controls
01 Understanding Form Controls
02 Examining the types of form controls
03 Displaying the [Developer] tab in the ribbon menu
[Key] 04 Select a specific region from the list to retrieve order details.
- List box
05 Select the employee name to find employee information
- Combo box
06 Select only one of the [Male] and [Female] options
- Options button
07 Specifying a zone for the option button control
- Group box
[Core] 08 Grouping multiple option buttons into one group
- Group box
09 Check the results by selecting and deselecting items
- Check box
10. Specifying additional descriptions for form controls
- Label
11 Display the delivery quantity adjustment button
- Spin button
12 Displaying regional data with a scroll bar
- Scroll bar
13 Linking Conditional Formatting to a Combo Box
14 Connecting Conditional Formatting to Spin Buttons
[Practical Project] Easily choose your work method with the Options button.

[chart]
Section 10: Creating Charts to Enhance Visual Effects
01 Creating and styling a chart
02 Go to the chart sheet and set the chart elements.
03 Display a picture on the chart background and fill it with a gradient
04 Change the chart type and display the original data together
05 Displaying the chart title as a shape linked to the cell
06 Displaying related images on bars in a bar chart
[Key] 07 Displaying daily application status in a line chart
[Key] 08 Decorating the Markers of a Line Chart with Shapes
09 Displaying Percentages and Leader Lines on Pie Chart Slices
10. Isolate and highlight specific slices in a pie chart
[Key] 11 Displaying Bar and Line Charts Together
Creating a chart with a 12-tier structure
13 Displaying Net Income with a Waterfall Chart
14 Creating Mini Charts Using Sparklines
[Practical Project] Displaying an Upper Limit on a Chart

Search

[Latest Revised Edition] Practical! Master Business Excel
Practical Excel for Office Workers

All versions of Excel (2021/2019/2016/2013/Microsoft 365) are available.
: Reflects the latest version of Excel '2021'

▷Faster than anyone else!
_Work Time-Saving Skills and Excel Expert Tips Revealed
▷Accurate analysis and reporting!
_Comprehensive know-how in data management, analysis, and visual report creation
▷Solve the problem in one breath!
_Providing situation-specific solutions through field practice projects