Chapter 1: Data Analysis Tools for Successful Workers: Excel Pivot Tables

01-1 "Should I, an ordinary person like me, learn data analysis?"
01-2 Introducing Excel Pivot Tables

Chapter 2: Preparing for Data Analysis - Data Preprocessing

02-1 Using data formats correctly
You can do it! } Create a number in text format using single quotes (')
You can do it! } Create numbers in text format with [Split Text]
You can do it! } Use the conversion option of the error tracking button
Just do it! } Convert it to a number by multiplying it by '1'
You can do it! } Using the VALUE function
Just do it! } Using the [Split Text] feature
Just do it! } Convert 44203, 44373 to date data format
Just do it! } Convert 20220107 to date data format
Just do it! } [Replace] to remove only the unit text from the cell.
Just do it! } Separate text units in a cell with [Split Text]
Just do it! } Display numbers with units without changing the data
You can do it! } Find specific data using [Move Options]
02-2 Creating a table structure that can be analyzed
Just do it! } Unpivot the crosstab table and make it into a list table.
Just do it! } Select all merged cells, split them, and fill in the values.
Just do it! } Select and delete the summary row
Just do it! } Find and fill in empty cells (missing values)
You can do it! } Displaying duplicate values ​​using conditional formatting
You can do it! } Check the number of duplicates using the COUNTIF function
02-3 Using Excel Tables
Just do it! } Let's take a look at the features of creating Excel tables.

Chapter 3: Starting Data Analysis

03-1 Creating a Pivot Table
Just do it! } Create a new pivot table
Just do it! } Create a pivot table in one go with [Recommended Pivot Table]
03-2 Freely manipulate the field list
Just do it! } Calculate total sales for the year - Add a [Value] field
Calculating sales by region and order type - Adding [Row] and [Column] fields
Just do it! } Analyze VIP customer sales by region and order type - Add [Filter] area
You can do it! } Transforming a Pivot Table Using Fields
03-3 Delete, Copy, or Reset a Pivot Table
Just select the pivot table and delete it!
You can do it! } Select and delete pivot tables that are separated
You can do it! } Reset the pivot table frame and leave it blank.
You can do it! } View two pivot tables side by side - [Ctrl] + [C], [Ctrl] + [V]
03-4 Sorting data values
You can do it! } Sort the [Value] area by highest payment amount
Just do it! } Sort in ascending order, but change the order of special cities and metropolitan cities to the first.
You can do it! } Sort by membership level
03-5 Group by region and quarterly analysis
You can do it! } Group by 'Special City/Metropolitan City' and 'General City/Province'
Just do it! } Display quarterly sales trends by date group
03-6 Focusing on the data you need using filters
Just do it! } Filter only rows that start with 'Incheon' - [Label Filter]
Just do it! } Filter the 10 branches with the highest actual sales - [Value Filter]
03-7 Intuitive buttons that replace complex filter manipulation - Slicer
Just do it! } Inserting a slicer into branch-specific sales data
Just do it! } Linking multiple pivot tables to a single slicer

Solve this problem and you'll be a powerhouse! Create a Pivot Table Report

Chapter 4: Data Visualization with Layout, Formatting, and Pivot Charts

04-1 Layout and Design to Enrich Your Pivot Table
You can do it! }Display partial totals
Just do it!}Change the Pivot Table Style
04-2 Two Ways to Make a Pivot Table Fit Your Report
Just do it!}Change Pivot Table Field Names
Just do it! }Change empty cells to the desired value
04-3 Highlighting Data with Conditional Formatting
Highlight rows with more than 600 rows - 'Highlight Cells Rules' Conditional Formatting
Highlighting the bottom 5 sales regions - 'Top/Bottom Rules' conditional formatting
Just do it! }Displaying a bar graph after a number - 'Data Bar' conditional formatting
Just do it! } Emphasize meaning with green-yellow-red - 'Color' conditional formatting
Just do it! } Highlight with arrows - 'Icon Set' conditional formatting
Format Option 1 - All cells displaying the selected value
Format Option 2 - All cells that display values ​​for a specific item
04-4 The Flower of Data Visualization, Pivot Charts
Just do it! Compare regional sales with a grouped bar chart.
Just do it! }Check monthly sales volume trends with a broken line chart.
Just do it! Compare sales by product group with a pie chart.
Create a mixed chart that displays sales quantity and transaction amount by region.

Solve this problem and you'll be a powerhouse! Change the Pivot Table Layout

Chapter 5: Using PivotTable Calculations for 3D Analysis

05-1 Various calculations in pivot tables - Based on value summary
You can do it! }Change the [Value] field criteria based on 'Sum' to 'Count' and 'Average'
05-2 Displaying ratios and ranks with a pivot table - Value display format
You can do it! }Change the value display format to a percentage of the total
You can do it! }Display as a percentage of the total of the upper row
You can do it! }Display as a percentage of the total of the upper column
You can do it! }Display as a percentage of the top total
Just do it! }Display the difference from the [reference value]
You can do it! }Display the percentage difference for [reference value]
You can do it! } Display sales as a year-to-month cumulative total
You can do it! }Display values ​​as cumulative percentages
Just do it! }Display sales rankings by branch
You can do it! } Displaying values ​​by index
05-3 Creating a calculated column that does not exist in the original data - Calculated field
You can do it! } Calculate the actual payment amount using the calculation field
You can do it! } Edit the calculated field
You can do it! }Delete calculated fields
05-4 Calculating within a PivotTable Only - Calculated Items
You can do it! }Create a member item in the membership level
Just do it!} Edit/delete calculation items
05-5 When changing the original data - Refresh
You can do it! }Change the original data
Just do it! } Add data within the scope of the original data
Adding data outside the scope of the original data
05-6 Double-click to see data! - Show sublevels
Just do it! }Display lower-level data with one double-click
You can do it! } Prevent sublevel display settings

Solve this problem and you'll be a powerhouse! Change the field summary criteria.

Chapter 6 Reports and Dashboards Using Pivot Tables

06-1 Creating a Printable Report in Excel
Just do it! } Inserting a title and body text with [Text Box]
Just do it! } Set the print layout by setting the scale
You can do it! } Create individual reports by membership level
06-2 Creating Your Own Real-Time Excel Dashboard
Just do it! } Building a dashboard with pivot tables and pivot charts
Just do it! } Visualize a single indicator as a card with a shape
Just do it! } Insert a slicer and link it to a report
06-3 Using Excel data in Word and PowerPoint
Just do it! } Paste Excel data into Word by linking it
Just do it! } Collapse the ribbon menu
You can do it! } Turn off ruler and gridline display
Just do it! } Zoom in/out the screen

Solve this problem and you'll be a powerhouse! Insert charts and slicers into your reports.

In conclusion
Search

This book is perfect for these people!

- Job seekers and new employees who want to develop practical Excel skills
- Office workers who are tired of repetitive Excel work and want to find a breakthrough
- Data analysts who are curious about data analysis using Excel.

A book for those of you who work with Excel or need to work with Excel!
A book you can read whether you have experience with Excel or not!
The first thing we need to learn in Excel is the 'pivot table'!

The company's common language is 'data', and the best tool for handling data is 'Excel'.
In a company, you need to know how to handle data to communicate, and you need to communicate with data to understand goals and direction.
So, if you are a working person, you should be able to use Excel as a basic skill.
Pivot tables are one of the most important features of Excel.
Pivot tables can extract the data I want.
If you know how to use pivot tables, you can solve 90% of the work at your company.


No worries if you are suddenly thrown into practical work~ Learn the steps of the work so you can be confident in any task!

There is an order and steps to everything.
The same goes for Excel practice.
To produce a result, data must be created and processed in a way that is easy to view, and also processed in a way that is easy to view.
This book proceeds in this order.
First, we will cover how to clean up messy data (data preprocessing), and we will also learn how to use Power Query, which is very simple.
You will also learn how to organize data into pivot tables and then convert them into graphs and charts to visualize them.
You will learn how to organize the data in this way and finalize it into reports and dashboards.
If you read this book from beginning to end, you will clearly understand the flow of practice.
Excel skills develop naturally like that.


Learn Pivot Tables with 88 free video lectures! With videos throughout the book, you'll learn 200% more effectively!

You can learn Excel pivot tables sufficiently by just reading the book, but watching the video lectures provided in the book will more than double your learning effect.
There are a whopping 88 video lectures prepared like this.
I can feel the author's desire to provide detailed information.
Additionally, the video was filmed and edited by the author himself.
The lecture experience of 'Choi Banjang', who achieved the number one recommendation and 20,000 cumulative views on Infraon, an online lecture platform, was fully demonstrated.
Video lectures can be accessed through the QR code in the book.


This isn't a one-sided, stuffy book! We've created a channel for communication!

Are you anxious about learning Excel from a book and having nowhere to turn for help? There's a place to relieve frustrations like, "I'm not good at this part..." or, "This function seems useful, but I don't know where to use it."
Go to the open KakaoTalk room [Excel 119] run by the author.
You can hear the author's kind answers and also feel the power of collective intelligence that is demonstrated through participation.

Excel 119: bit.ly/excel119_kakao

And you can meet people studying Excel at the 'Do it! Study Room' Naver Cafe run by Aegis Publishing.
Do it! If you complete the entire book as a study group member, you'll even get a free book!

Doit! Study Room: cafe.naver.com/doitstudyroom