365 Excel is a modern Excel version that is constantly updated with features that make creating and automating analyses, reports, and dashboards very easy compared with older Excel versions. This book will help you leverage its full capabilities, beginning with a quick overview of what dashboards are and how they are different from other types of reports. Then, you'll familiarize yourself with the different standard dashboards currently available and what they are meant to accomplish for organizations. As you progress, you'll get to grips with the use of new powerful tools such as Power Query and dynamic array formulae in the automation of analysis, gaining insights into the right approach to take in building effective dashboards. You'll equip yourself with not only all the essential formulae, charts, and non-chart visuals but also learn how to set up your dashboard perfectly. Along the way, you'll build a couple of awesome dashboards from scratch to utilize your newfound knowledge.
By the end of this book, you will be able to carry out an impressive and robust level of analysis on business data that may come from multiple sources or files, using better processes, formulae, and best practices in M365 to create insightful dashboards faster.
What You Will Learn:
Understand the importance of dashboards in today's business analytics environment
Delve into the various essential formulae in Excel
Utilize Power Query to shape and transform data to extract insights easily
Explore the power of the new dynamic array functions in M365
Employ PivotTable and Power Pivot to automate your dashboards
Master the setup and optimization of your dashboard canvas
Discover best practices for visualization, charts, and effective dashboard creation
Consolidate your knowledge through a hands-on concluding project
Author(s): Michael Olafusi
Edition: 1
Publisher: Packt Publishing Pvt. Ltd.
Year: 2024
Language: English
Pages: 401
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
