Building Interactive Dashboards in Microsoft 365 Excel : Harness the New Features and Formulae in M365 Excel to Create Dynamic, Automated Dashboards / Michael Olafusi and Olanrewaju Oyinbooke.

Format:

Book

Author/Creator:

Olafusi, Michael, author.

Oyinbooke, Olanrewaju, author.

Language:

English

Subjects (All):

Microsoft Excel (Computer file).

Dashboards (Management information systems).

Physical Description:

1 online resource (420 pages)

Edition:

First edition.

Place of Publication:

Birmingham, England : Packt Publishing, [2024]

Biography/History:

Olafusi Michael: Michael Olafusi is a 9x Microsoft Most Valuable Professional (MVP) and a business intelligence consultant. He is the lead consultant and founder of MHS Analytics Inc. in Canada and UrBizEdge Limited. He has been consulting for clients across North America, Europe, and Africa on data analysis, business intelligence, and financial modeling for the past 10 years. Outside of his consulting business, he is a member of Rotary and the Southern Cruisers Riding Club (SCRC) Chapter 373. He is a proud member of the Canadian Red Cross friendly calls volunteer team. He is also a faculty member at WorldQuant University, USA.

Summary:

Unleash the full potential of Microsoft Excel's latest version and elevate your data-driven prowess with this comprehensive resource Key Features Create robust and automated dashboards in Excel for M365 Apply data visualization principles and employ dynamic charts and tables to create constantly updated and informative dashboards for your organization Uncover the best practices for effective dashboard creation Purchase of the print or Kindle book includes a free PDF eBook Book Description M365 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 Who this book is for This book is for Microsoft Excel users, especially those tasked with creating dynamic reports and dashboards that require data and help support to decision-makers within an organization with visually engaging and actionable insights. Financial analysts, data analysts, business analysts, and BI professionals will also greatly benefit from this book. Some familiarity with the Microsoft Excel interface is a prerequisite.

Contents:

Cover

Title page

Copyright and credit

Foreword

Contributors

Table of Contents

Preface

Part 1 - Dashboards and Reports in Modern Excel

Chapter 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

Chapter 2: Common Dashboards in Large 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

Part 2 - Keeping Your Eyes on Automation

Chapter 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

Chapter 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 &amp

Load To

Demystifying the underlying M code

Chapter 5: PivotTable and Power Pivot

Mastering Pivot Tables

The role of Slicers

Dynamic reports with PivotTables.

Power Pivot and Data Models

DAX

Chapter 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

Chapter 7: Dynamic Array Functions and Lambda Functions

Dynamic array functions

UNIQUE

FILTER

SEQUENCE

SORT

SORTBY

Lambda functions

LAMBDA

BYCOL

BYROW

MAKEARRAY

MAP

REDUCE

SCAN

Part 3 - Getting the Visualization Right

Chapter 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

Chapter 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

Chapter 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

Chapter 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

Chapter 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

Index

Other Books You May Enjoy.

Notes:

Includes bibliographical references and index.

Description based on print version record.

ISBN:

9781803247472

1803247479

OCLC:

1420920116