What this book covers
Chapter 1, Dashboards, Reports, and M365 Excel, explains the difference between dashboards and reports and what is special about Microsoft 365 Excel. It is often common to see people use the words dashboards and reports interchangeably, but they are different. Also, Microsoft Excel has changed a lot in the last four years, in terms of both its capabilities and product versions. This chapter will help you understand the difference between dashboards and reports, and also the concept of modern Excel.
Chapter 2, Common Dashboards in Large Companies, covers the five major types of dashboards the companies require: sales dashboard, financial analysis dashboard, human resources dashboard, supply chain dashboard, and marketing dashboard. You will become aware of the types of needs dashboards address and why businesses require dashboards.
Chapter 3, The Importance of Connecting Directly to the Primary Data Sources, presents different ways of bringing data into Excel, the importance of always connecting to the primary source, and how to overcome common data source challenges. The concepts covered in this chapter can be the difference between a dashboard that is built once and used for years without any reworking and a dashboard that keeps breaking down.
Chapter 4, Power Query: the Ultimate Data Transformation Tool, gives an overview of the amazing data transformation features of Power Query and some demonstrations of its use. Power Query is the most commonly known tool for modern Excel users and is vital for creating repeatable data transformations.
Chapter 5, PivotTable and Power Pivot, presents a clear explanation of how to use pivot tables to achieve interactive analysis and the use of Power Pivot to build robust data models. The chapter covers a deep dive into Pivot Tables and the use of slicers and Data Analytics Expressions (DAX).
Chapter 6, Must-Know Legacy Excel Functions, includes coverage of key math functions, logical functions, text manipulation functions, date manipulation functions, and lookup functions. These form the bedrock of most Excel sheet-based data transformation and aggregation.
Chapter 7, Dynamic Array Functions and Lambda Functions, helps you understand what dynamic array functions are and their special use, with a special focus on Lambda functions. The chapter also walks you through the key dynamic array functions to master and some interesting uses of Lambda functions.
Chapter 8, Getting Comfortable with the 19 Excel Charts, gives a demonstration of each of the 19 chart types in Excel. The chapter explains what each chart is best used for and gives a practical illustration per chart.
Chapter 9, Non-Chart Visuals, shows the use of conditional formatting, shapes, smart art, sparklines, images, and symbols to visually communicate insights. These, when properly used, can greatly enhance the readability, ease of use, and visual appeal of a dashboard.
Chapter 10, Setting Up the Dashboard’s Data Model, presents a hands-on demonstration of how to set up the foundation of a proper dashboard. The chapter takes you through the typical reporting needs in a company and transforming data in Power Query.
Chapter 11, Perfecting the Dashboard, gives a practical walk-through of building a human resources dashboard, a sales performance dashboard, and a supply chain dashboard.
Chapter 12, Best Practices for Real-World Dashboard Building, lists the important guidelines for gathering dashboard requirements and deciding on what dashboards are worth building in Excel.
