Gather and analyze data successfully, identify trends, and then create overarching strategies and actionable next steps - all through Excel. This book will show even those who lack a technical background how to make advanced interactive reports with only Excel at hand. Advanced visualization is available to everyone, and this step-by-step guide will show you how.
The information in this book is presented in an accessible and understandable way for everyone, regardless of the level of technical skills and proficiency in MS Excel. The dashboard development process is given in the format of step-by-step instructions, taking you through each step in detail. Universal checklists and recommendations of a practicing business analyst and trainer will help in solving various tasks when working with data visualization. Illustrations will help you perceive information easily and quickly.
Make Your Data Speak will show you how to master the main rules, techniques and tricks of professional data visualization in just a few days.
What You'll Learn
See how interactive dashboards can be useful for a business
Review basic rules for building dashboards
Understand why it's important to pay attention to colors and fonts when developing a dashboard
Create interactive management reports in Excel
Who This Book is For
Company executives and divisional managers, Middle managers, business analysts
Author(s): Kolokolov, Alex
Year: 2022
Language: English
Commentary: Data Processing, Creating Actionable Data through Excel For Non-Technical Professionals
Pages: 269
Tags: Data Processing, Creating Actionable Data through Excel For Non-Technical Professionals
Contents
About the Author
About the Technical Reviewer
From the Author
Three Stories That Made Me Write This Book
Chapter 1: Data Preparation
1.1 What’s Wrong with the Initial Data
How to Convert Data to a Flat Table
How to Simplify the Process of Copying Cells
Summary
1.2 Preparing the Basis for the Dashboard
Creating a Pivot Table
How to Update Data Correctly
Summary
1.3 Preparing Data Samples for Visualization
How a Pivot Table Report Works
Preparing the First Sample
Replicating Data from a Pivot Table
Summary
1.4 Setting Up the Interactive
Good Old Filters
Magic Slicers
Connect Slicers to Reports
Samples and Slicers in Five Steps
Summary
Quick Tricks for Excel
Chapter 2: Dashboard Assembling
2.1 Assembling Dashboard According to the Layout
Summary
2.2 Creating KPI Cards
Sampling Data for Cards
The “Target” Card
Summary
2.3 Align the Dashboard and Add a Header
Summary
Chapter 3: Anatomy of Diagrams
3.1 Analyzing Ready-made Design Styles
How to Choose a Quick Layout
What Is Wrong with Template Styles
What Should Be the Business Graphics: Recommended Samples
Summary
3.2 Setting Up Data Labels
How to Add Data Labels
Labels Format: Getting Rid of Long Numbers
Size and Color for Data Labels
The Axis and Grid Are No Longer Needed
Summary
3.3 Working with the Text: Remove the Superfluous, Add the Necessary
Where and How to Place the Legend
What to Do with Automatic Labels
How to Delete Field Buttons
Two Ways to Add a Chart Name
Summary
3.4 Designing Bar Charts
Distance Between Columns
Sorting Columns in Descending Order
Sorting Columns on a Bar Chart
Chart Design Checklist
3.5 Setting Up the Chart Template
How to Save a Template
A New Chart Using a Template
Adapting the Template to the Diagram
Summary
Chapter 4: Final Design
4.1 Aligning the Headers to the Grid of Cells
Adding a Headers to the Cells
Applying Cell Style
Copying Formatting to Adjacent Cells
Summary
4.2 Creating New Cards on Top of the Cells
How to Add a Card Using a Shape and Text Box
Totals for Cards in Separate Cells
Card Replication
How to Fix the Background Style
Alignment of New Cards
Summary
4.3 Designing Interactive Slicers
How to Disable Slice Borders
Solving the Problem
Summary
4.4 Working with Colors and Fonts in Excel
How to Work with Palettes
Unsuccessful Similar Palettes
Suitable Palettes
Working with Fonts: Important Points
Working with Themes
Summary
4.5 Improving Standard Excel Themes
Example 1: Primary Colors Should Be Contrasting
Why the Theme Is Suitable
Analyzing Faults
Fixing the Problem
Example 2: Less Brightness for Service Elements
Why the Theme Is Suitable
Analyzing Faults
Fixing the Problem
Example 3: The Background of the Dashboard Does Not Have to Be White
Why the Theme Is Suitable
Analyzing Faults
Fixing the Problem
Summary
Color Theme Adaptation Checklist
Chapter 5: Corporate Identity
5.1 Creating a Theme in Accordance with the Brand Book
How to Create a Theme from Scratch
Life Hack: Importing a Ready-made Theme from PowerPoint
Verification on the Checklist and Points for Improvement
Summary
5.2 Adapting the Theme According to the Checklist
White Spots on the Dashboard Background
Brightness for KPI Cards
New Font for All Elements
More Contrast on Charts
Adapting Slicers
Finishing Touches and Presentation Mode
Summary
5.3 Creating a Dashboard in a Dark Theme
Dashboard Title and Logo
Chart Titles
New Color for Cards
Setting Up a Graph
Charts by Template
Setting Up Slicers in a Dark Theme
Summary
Chapter 6: Data Visualization Rules
6.1 Types of Data Analysis
Basic Types of Analysis
Typical Errors
Pseudo Dynamics
Inappropriate Funnel
Not All Pies Are Equally Useful
Summary
6.2 How to Choose Charts
Charts for Rating
Choose by the Number of Categories
Consider the Length of the Labels
Estimate the Available Space on the Screen
Charts for Dynamics
Less Than Six Periods
A Common Mistake with the Direction of the Scale
Diagrams for the Structure
Six sectors – Maximum for a Pie Chart
Long Labels? Give Up the Pie Chart
Tips for Designing Pie Charts
Summary
6.3 Life Hacks for Multiple Data Series
Combined Graph for Dynamics
Bullet Chart for the Target-Actual Rating
Horizontal Bullet Chart with a combination of Series
Vertical Bullet Chart with a Dash
Diagrams for a Complex Structure
Error: Doughnut Chart for Two Data Series
Complex Trimap
Summary
6.4 How to Show Everything at Once
When a Table Is the Only Option
How to Add a Calculated Field
Rules for Formatting Cells
Conditional Formatting Using Histograms
Conditional Formatting with Icons
Typical Errors
Option 1. Positive
Option 2. Negative
Option 3. Interactive
Summary
6.5 Funnel, Waterfall, and Magic Pillow
Funnel = Structure + Dynamics
How to Build a Funnel in Excel 2019 and Above
Life Hack for Older Versions
Waterfall = Dynamics + Rating
How to Build a Waterfall Chart in Excel 2016 and Above
How to Build a Waterfall Chart in Old Versions of Excel
Summary
How to Improve the Data-Driven Culture in Your Organization
The Analytics Integration Is Not a Revolution, but an Evolution
Personal Level As the Basis
Group Level As a Step Forward
Corporate Level As a Strategy
Why Do You Need a Data Warehouse
How I Work As an Interpreter Between IT and Business
Your Data Must Speak the Language of Business
Index
