داده‌هایتان را به زبان بیاورید: ایجاد داده‌های کاربردی از طریق اکسل برای متخصصان غیرفنی

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 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 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. You will: 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