Building Interactive Dashboards in Microsoft 365 Excel

Building Interactive Dashboards in Microsoft 365 Excel
Foreword
Contributors
About the author
About the reviewer
Preface
   Who this book is for
   What this book covers
   To get the most out of this book
   Download the example code files
   Conventions used
   Get in touch
   Share Your Thoughts
   Download a free PDF copy of this book
Part 1 – Dashboards and Reports in Modern Excel
1
Dashboards, Reports, and M365 Excel
   Introducing dashboards and reports
   Meeting modern business needs
   The characteristics of a dashboard
   The different versions of Microsoft Excel
      Excel 365
      Excel 2021
      Older versions of Excel
   Summary
   Further reading
2
Common Dashboards in Lsarge Companies
   Major types of dashboards
   Understanding the sales dashboard
   Understanding the financial analysis dashboard
   Understanding the HR dashboard
   Understanding the supply chain and logistics dashboard
   Understanding the marketing dashboard
   Summary
Part 2 – Keeping Your Eyes on Automation
3
The Importance of Connecting Directly to the Primary Data Sources
   The different ways to bring data into Excel
      Copying and pasting data into Excel
      Importing data from flat files
      Importing data from databases
      Importing data from cloud platforms
   Connecting directly to the primary data source
   Common issues and how to overcome them
   Summary
4
Power Query: the Ultimate Data Transformation Tool
   Introduction to Power Query
   Connecting to over 100 different data sources
   Transforming data in Power Query
   Appending data from multiple sources in one data table
   Merging data from two tables into one table
   Common data transformations
      Choose Columns
      Keep Rows and Remove Rows
      Unpivot Columns and Pivot Columns
      Group By
      Fill Series and Remove Empty
      Replace Values
   Important tips
   Understanding Close & Load To
   Demystifying the underlying M code
   Summary
5
PivotTable and Power Pivot
   Mastering Pivot Tables
   The role of Slicers
   Dynamic reports with PivotTables
   Power Pivot and Data Models
   DAX
   Summary
6
Must-Know Legacy Excel Functions
   Math and statistical functions
      SUM
      SUMIFS
      COUNT
      COUNTIFS
      MIN
      MAX
      AVERAGE
   Logical functions
      IF
      IFS
      IFERROR
      SWITCH
      OR
      AND
   Text manipulation functions
      LEFT
      MID
      RIGHT
      SEARCH
      SUBSTITUTE
      TEXT
      LEN
   Date manipulation functions
      TODAY
      DATE
      YEAR
      MONTH
      DAY
      EDATE
      EOMONTH
      WEEKNUM
   Lookup and reference functions
      VLOOKUP
      HLOOKUP
      INDEX
      MATCH
      OFFSET
      INDIRECT
      CHOOSE
   Summary
7
Dynamic Array Functions and Lambda Functions
   Dynamic array functions
      UNIQUE
      FILTER
      SEQUENCE
      SORT
      SORTBY
   Lambda functions
      LAMBDA
      BYCOL
      BYROW
      MAKEARRAY
      MAP
      REDUCE
      SCAN
   Summary
Part 3 – Getting the Visualization Right
8
Getting Comfortable with the 19 Excel Charts
   Column chart
   Bar chart
   Line chart
   Area chart
   Pie chart
   Doughnut chart
   XY (scatter) chart
   Bubble chart
   Stock chart
   Surface chart
   Radar chart
   Treemap chart
   Sunburst chart
   Histogram chart
   Box and whisker chart
   Waterfall chart
   Funnel chart
   Filled map chart
   Combo chart
   Summary
9
Non-Chart Visuals
   Conditional formatting
      Highlight Cells Rules
      Top/Bottom Rules
      Data bars
      Color scales
      Icon sets
      Custom formula conditional formatting
   Shapes
      SmartArt
      Sparkline
      Images
      Symbols
   Summary
10
Setting Up the Dashboard's Data Model
   Adventure Works Cycle Limited
      HR schema
      Sales schema
      Purchasing schema
      Production schema
      Person schema
   Building business-relevant dashboards
   Data transformation in Power Query
   Summary
11
Perfecting the Dashboard
   Building the HR manpower dashboard
      Inserting PivotTables
      Inserting PivotCharts
      Inserting picture, shapes, and icons
   Building the sales performance dashboard
      Creating measures
      Inserting slicers and timelines
      Inserting a PivotTable and a PivotChart
      Inserting shapes and a picture
      Connecting slicers to the PivotTables and PivotCharts
   Building the supply chain inventory dashboard
   Summary
12
Best Practices for Real-World Dashboard Building
   Gathering the dashboard requirements
      Existing established analysis dashboards
      Newly established analysis dashboards
      Ad hoc analysis dashboards
   An overview of different data professionals
      Data analyst
      Business intelligence analyst
      Data engineer
      Data scientist
      Database administrator
   Advantages and limitations of Excel dashboards
   Summary
Index
   Why subscribe?
Other Books You May Enjoy
   Packt is searching for authors like you
   Share Your Thoughts
   Download a free PDF copy of this book