Preface / Book Structure / Download Example Files

CHAPTER 01 Data Editing and Processing to Reduce Working Time
01 Moving cells and specifying ranges using shortcut keys
02 Enter data at once by specifying only blank cells
03 Fill in data below after unmerging cells
04 Creating an Applicant Form with Validation ★with ChatGPT★
05 Removing spaces in bulk and then customizing the text
06 Find specific values ​​in a table list and change and delete them simultaneously ★with ChatGPT★
07 Splitting and deleting columns using text splitting
08 Extract and merge data with Quick Fill
09 Applying formulas to move values ​​and clean up blank rows
10. Organize your tables by automatically merging duplicate data cells ★with ChatGPT★
11 Change numeric data at once by selecting and pasting
12 Converting incorrectly set number and date formats
[Practical Project] Batch Editing and Processing a List of Accounts Receivable Downloaded from an In-House Management System

CHAPTER 02 Data Transformation and Visualization Using Power Query
01 Splitting multiple lines of data entered in one cell into rows
02 Fill below merged cells and separate comma-separated data
03 Quickly convert a one-dimensional table structure into a two-dimensional structure
04 Extract only words containing specific characters ★with ChatGPT★
05 Converting Data That Cannot Be Converted to Power Query
06 Create a list of photo file names stored in a folder
07 Combine CSV files into one list
08 Merge all Excel files stored in one folder ★with ChatGPT★
09 Quickly format cells with shortcut keys
10 Visualizing Tables with Custom Display Formats
11 Display the total amount of the quotation in Korean
12 Create tables of different sizes on one sheet with linked pictures
13 Clean up unnecessary styles with the XLStyle Tool
[Practical Project] Merging Multiple Separate Tables into a Single Data List

CHAPTER 03 Formulas and practical functions essential for work
01 Calculating profit by applying absolute reference formula
02 Define cell range names and use them in functions
03 Finding ratios of multiple ranges simultaneously with mixed references
04 Calculate subtotals and totals at once and copy the results
05 Visualizing the list of documents submitted with conditional formatting as icons
06 Highlight with fill color when the due date has passed or is approaching
07 Organizing deposit and withdrawal history using the IF function
08 Handling errors with the IFERROR function
09 Counting by Group with COUNTIF and COUNTIFS Functions
10. Finding the number of data without duplicates using the UNIQUE function ★with ChatGPT★
11. Calculating group sums using the SUMIF and SUMIFS functions
12 Creating a Product Shipment List with the SORT and UNIQUE Functions
13 Finding matching information with the VLOOKUP function
14 Retrieving data from a two-dimensional list using the INDEX and MATCH functions
Finding toll rates by region and vehicle type using the 15 XLOOKUP function ★with ChatGPT★
16 Find all data that meets conditions using the FILTER function ★with ChatGPT★
17. Joining Data by Group Using the TEXTJOIN Function ★with ChatGPT★
[Practical Project] Editing the Vendor Database and Using Functions to Complete the Transaction History Table
[Practical Project] Compare the list of candidates for promotion and those who have completed training, manage training history, and create a summary table ★with ChatGPT★

CHAPTER 04 Chart Creation and Editing Features Tailored to Data Characteristics
01 Create monthly and weekly sales charts with ChatGPT recommendations.
02 Creating a composite chart that displays two charts
03 Expressing data with large deviations in a chart that omits the middle
04 Creating a line chart to analyze trends over time
05 Create an overlapping bar chart and save it as a template file.
06 Analyzing Growth and Decrease Rates with a Waterfall Chart
07 Create a chart that automatically displays an average line based on the data.
08 Create a highlight chart to confirm the selected items
09 Create a semicircle chart to check market share
10 Create a waffle chart to track your goals and achievements.
11. Create a sunburst chart to check the ratio by class, such as purpose, item, and size.
[Practical Project] Create a report analyzing sales by day of the week.
[Practical Project] Creating an Automatic Chart that Visualizes Each Sheet's Data on a Single Sheet ★with ChatGPT★

CHAPTER 05 Fast and Efficient Data Management
01 Apply Excel table and check the aggregate
02 After calculating the aggregate by partial sum, edit the results and create a report.
03 Filtering various data using advanced filters
04 Bringing Distributed Data Together with Integration Tools
05 Analyzing data summaries with pivot tables
06 Creating Effective Pivot Reports
07 Adding a New Field with a Pivot Table Formula
08 Creating calculated items using data in fields
09 Importing data from a pivot table with GETPIVOTDATA and creating a report
10 Setting Up Pivot Table Data Refresh with VBA ★with ChatGPT★
11 Create a Pivot Table that Displays the Unique Count of Duplicate Data
12 Creating a Pivot Table by Relating Two Tables
13 Editing Data with Power Query to Create Pivot Reports
14 Connecting Multiple PivotTables with Slicers
[Practical Project] Creating a Dashboard with PivotTables, Slicers, and PivotCharts

Search

What kind of readers is this book for?

- All office workers who need to manage, summarize, analyze, and visualize data using Excel in their work.
- Office workers who want to learn how to use Excel efficiently using ChatGPT
- Office workers who want to learn the core functions of Excel, such as tables, filters, pivot tables, and charts, and then properly utilize them in their work.
- Entrepreneurs, freelancers, and sole proprietors who need to manage and analyze data using Excel for business operations.
- Instructors and trainers preparing Excel lectures at educational institutions

1.
Learn only the core functions needed to utilize and analyze Excel data.

As the work environment rapidly changes, the amount of data that must be handled in practice is increasing exponentially.
Naturally, data management and analysis capabilities using Excel are becoming increasingly important, but many people are not properly utilizing Excel's functions, which reduces work efficiency.
This book systematically presents the core functions you need to know when handling data in Excel, greatly enhancing your data utilization skills.

2.
Maximize work efficiency with ChatGPT.

Learn how to use ChatGPT in Excel.
We provide 13 'with ChatGPT' exercises to help you learn how to maximize work efficiency using ChatGPT.
It provides prompts to get accurate answers from ChatGPT, and also provides 'Prompt Tips' that are useful to know when writing prompts in an actual work environment.

3.
Develop your practical skills with 'practical projects'

At the end of each chapter, you can create a 'practical project' to practice in a real-world work environment.
Practical project examples are provided step by step.
Even complex practical projects can be easily improved by practicing them according to the STEP method.