Search icon CANCEL
Subscription
0
Cart icon
Your Cart (0 item)
Close icon
You have no products in your basket yet
Arrow left icon
Explore Products
Best Sellers
New Releases
Books
Videos
Audiobooks
Learning Hub
Newsletter Hub
Free Learning
Arrow right icon
timer SALE ENDS IN
0 Days
:
00 Hours
:
00 Minutes
:
00 Seconds
Building Interactive Dashboards in Microsoft 365 Excel
Building Interactive Dashboards in Microsoft 365 Excel

Building Interactive Dashboards in Microsoft 365 Excel: Harness the new features and formulae in M365 Excel to create dynamic, automated dashboards

eBook
₹799.99 ₹1787.99
Paperback
₹2233.99
Subscription
Free Trial
Renews at ₹800p/m

What do you get with a Packt Subscription?

Free for first 7 days. ₹800 p/m after that. Cancel any time!
Product feature icon Unlimited ad-free access to the largest independent learning library in tech. Access this title and thousands more!
Product feature icon 50+ new titles added per month, including many first-to-market concepts and exclusive early access to books as they are being written.
Product feature icon Innovative learning tools, including AI book assistants, code context explainers, and text-to-speech.
Product feature icon Thousands of reference materials covering every tech concept you need to stay up to date.
Subscribe now
View plans & pricing
Table of content icon View table of contents Preview book icon Preview Book

Building Interactive Dashboards in Microsoft 365 Excel

Dashboards, Reports, and M365 Excel

Many Excel users use the words dashboard and report interchangeably and are not able to clearly delineate them. This is often because most people learn to use Excel on the job and not before they are mandated by superiors to churn out reports and dashboards for them. It is not uncommon to have a manager who calls everything with a beautiful chart in it a dashboard and another who believes only specialist software (other than Excel) can create dashboards. Sadly, no analyst who is bounced endlessly between these two types of managers will understand the real difference between a dashboard and a report.

Microsoft sadly adds to the woes analysts face at work by creating different versions of Microsoft Excel. Almost every week, I come across an analyst spending hours and using complex formulas they copied from an online forum without understanding how the 100-plus-characters cocktail of functions works to achieve what is now an easy single function...

Introducing dashboards and reports

Dashboards are visual representations of key metrics and actionable insights to be primarily consumed by decision makers. Many self-acclaimed dashboard experts say it should always be set to fit within a screen view, and the reason for this is a very valid one. Imagine needing to scroll down to see the fuel gauge on your car’s dashboard; seeing everything in one ever-present view is a much better user experience than having to scroll. The trouble, however, lies with whose screen. Screen resolution, monitor size, and Excel zoom settings are never the same for all parties concerned – the dashboard builder and the dashboard users. So it can be a very tricky clause to include in the dashboard definition, but knowing this, it is always good to aim to avoid the need for your dashboard users to scroll right or down when viewing your dashboard.

Reports are well-formatted presentations of data and are often the outcome of all data analysis...

Meeting modern business needs

Modern businesses are drowning in data as technology has made it both easy and cheap to gather data around every aspect of business operations. Even a company’s website can generate thousands of data points monthly, and this is data the business often does not do much to set up the collection of. Now imagine how much more data is being collected from the more intentionally set up accounting tool, customer relationship management tool, distribution management tool, sales management tool, vendor management, procurement tool, and HR management tool.

Increasingly, business managers are getting frustrated with fragmented reports that show activities but have no meaningful or actionable insights. What is now on every modern business manager’s lips to their subordinates is give me dashboards. It is no longer acceptable to just create hundreds of reports covering every single aspect of business operations. An analyst is now expected to build a...

The characteristics of a dashboard

Search for the word dashboard online, and you will be presented with a list of articles about dashboards that seem to have little in common. The reason is that most online articles that are at the top of search results are by professional content writers and not data analysts, and many were hired to promote a specific tool. For data analysts, a dashboard has some clear characteristics that set it apart from other types of reports.

A dashboard has the following definitive characteristics:

  • It is designed to be very visually engaging
  • It must be easy to read and understand
  • It must present actionable insights, using the key business metrics decision makers need to be on top of
  • It must be interactive, allowing users to dynamically view dimensions or aspects of the analysis (this is often achieved with slicers and filters in Excel)
  • It must be in its own dedicated sheet and occupy as close to a one-screen view as possible
  • It...

The different versions of Microsoft Excel

Since the first Excel, Excel 1.0 for Apple Macintosh, in 1985, there have been over 25 versions of Excel by Microsoft, but 99% of Excel users today are spread across the following versions:

  • Excel 365, which is a subscription-based version with constantly updated features and can be referred to as the most recent version.
  • Excel 2021, which came out in October 2021, is the most recent non-subscription-based version of Excel. Unlike the subscription-based Excel 365, it only gets security updates after its initial release, while Excel 365 gets additional monthly features.
  • Excel 2019, which came out in October 2019.
  • Excel 2016, which was released in September 2015.
  • Excel 2010, which was released in June 2010.
  • Excel 2007, which was released in November 2006.

To know what version of Excel you are on, go to File, Account, and in the upper right section, you will see your Excel version. See Figure 1.3 to Figure 1.5...

Summary

In this chapter, we have covered the key foundational things to know as we progress into building modern dashboards. Just to reiterate, the major learning highlights from this chapter are as follows: we started with the difference between dashboards and reports. We then learned about why dashboards are important for modern businesses. We then learned about the definitive characteristics of dashboards. Next, we went through the different Excel versions and why Excel 365 and Excel 2021 are the recommended versions for building dashboards.

And that’s it for this chapter; in the next chapter, we will dive into the common dashboards required by most modern companies.

Further reading

You can read more on the Excel features mentioned in this chapter at this official Microsoft documentation pages:

Left arrow icon Right arrow icon
Download code icon Download Code

Key benefits

  • 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

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.

Who is this book 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.

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

Product Details

Country selected
Publication date, Length, Edition, Language, ISBN-13
Publication date : Feb 29, 2024
Length: 420 pages
Edition : 1st
Language : English
ISBN-13 : 9781803237299
Vendor :
Microsoft
Category :
Languages :
Tools :

What do you get with a Packt Subscription?

Free for first 7 days. ₹800 p/m after that. Cancel any time!
Product feature icon Unlimited ad-free access to the largest independent learning library in tech. Access this title and thousands more!
Product feature icon 50+ new titles added per month, including many first-to-market concepts and exclusive early access to books as they are being written.
Product feature icon Innovative learning tools, including AI book assistants, code context explainers, and text-to-speech.
Product feature icon Thousands of reference materials covering every tech concept you need to stay up to date.
Subscribe now
View plans & pricing

Product Details

Publication date : Feb 29, 2024
Length: 420 pages
Edition : 1st
Language : English
ISBN-13 : 9781803237299
Vendor :
Microsoft
Category :
Languages :
Tools :

Packt Subscriptions

See our plans and pricing
Modal Close icon
₹800 billed monthly
Feature tick icon Unlimited access to Packt's library of 7,000+ practical books and videos
Feature tick icon Constantly refreshed with 50+ new titles a month
Feature tick icon Exclusive Early access to books as they're written
Feature tick icon Solve problems while you work with advanced search and reference features
Feature tick icon Offline reading on the mobile app
Feature tick icon Simple pricing, no contract
₹4500 billed annually
Feature tick icon Unlimited access to Packt's library of 7,000+ practical books and videos
Feature tick icon Constantly refreshed with 50+ new titles a month
Feature tick icon Exclusive Early access to books as they're written
Feature tick icon Solve problems while you work with advanced search and reference features
Feature tick icon Offline reading on the mobile app
Feature tick icon Choose a DRM-free eBook or Video every month to keep
Feature tick icon PLUS own as many other DRM-free eBooks or Videos as you like for just ₹400 each
Feature tick icon Exclusive print discounts
₹5000 billed in 18 months
Feature tick icon Unlimited access to Packt's library of 7,000+ practical books and videos
Feature tick icon Constantly refreshed with 50+ new titles a month
Feature tick icon Exclusive Early access to books as they're written
Feature tick icon Solve problems while you work with advanced search and reference features
Feature tick icon Offline reading on the mobile app
Feature tick icon Choose a DRM-free eBook or Video every month to keep
Feature tick icon PLUS own as many other DRM-free eBooks or Videos as you like for just ₹400 each
Feature tick icon Exclusive print discounts

Frequently bought together


Stars icon
Total 8,341.97
Data Cleaning with Power BI
₹2755.99
Building Interactive Dashboards in Microsoft 365 Excel
₹2233.99
Extending Excel with Python and R
₹3351.99
Total 8,341.97 Stars icon

Table of Contents

17 Chapters
Part 1 – Dashboards and Reports in Modern Excel Chevron down icon Chevron up icon
Chapter 1: Dashboards, Reports, and M365 Excel Chevron down icon Chevron up icon
Chapter 2: Common Dashboards in Lsarge Companies Chevron down icon Chevron up icon
Part 2 – Keeping Your Eyes on Automation Chevron down icon Chevron up icon
Chapter 3: The Importance of Connecting Directly to the Primary Data Sources Chevron down icon Chevron up icon
Chapter 4: Power Query: the Ultimate Data Transformation Tool Chevron down icon Chevron up icon
Chapter 5: PivotTable and Power Pivot Chevron down icon Chevron up icon
Chapter 6: Must-Know Legacy Excel Functions Chevron down icon Chevron up icon
Chapter 7: Dynamic Array Functions and Lambda Functions Chevron down icon Chevron up icon
Part 3 – Getting the Visualization Right Chevron down icon Chevron up icon
Chapter 8: Getting Comfortable with the 19 Excel Charts Chevron down icon Chevron up icon
Chapter 9: Non-Chart Visuals Chevron down icon Chevron up icon
Chapter 10: Setting Up the Dashboard's Data Model Chevron down icon Chevron up icon
Chapter 11: Perfecting the Dashboard Chevron down icon Chevron up icon
Chapter 12: Best Practices for Real-World Dashboard Building Chevron down icon Chevron up icon
Index Chevron down icon Chevron up icon
Other Books You May Enjoy Chevron down icon Chevron up icon

Customer reviews

Rating distribution
Full star icon Full star icon Full star icon Full star icon Empty star icon 4
(3 Ratings)
5 star 66.7%
4 star 0%
3 star 0%
2 star 33.3%
1 star 0%
Maxim Jul 12, 2024
Full star icon Full star icon Full star icon Full star icon Full star icon 5
If you like data/business analytics or financial analytics and want to advance your Excel skills, this book is great. Lots of visuals, and pretty easy to read.
Amazon Verified review Amazon
David Hedrick Jul 23, 2024
Full star icon Full star icon Full star icon Full star icon Full star icon 5
This book is a great wide-ranging overview that covers *way* more than I was expecting. I was especially surprised to see a little DAX, that was neat. I've stuck to Power BI mostly but this book has gotten me to appreciate Power Pivot. Would recommend to people who like to see & assemble neat dashboards.
Amazon Verified review Amazon
dogsnightout Jan 01, 2025
Full star icon Full star icon Empty star icon Empty star icon Empty star icon 2
This book has good step-by-step instructions for learning how to get started, however, I wanted and desired more actual information on types of dashboards and interactive dashboards and was left disappointed. Read more
Amazon Verified review Amazon
Get free access to Packt library with over 7500+ books and video courses for 7 days!
Start Free Trial

FAQs

What is included in a Packt subscription? Chevron down icon Chevron up icon

A subscription provides you with full access to view all Packt and licnesed content online, this includes exclusive access to Early Access titles. Depending on the tier chosen you can also earn credits and discounts to use for owning content

How can I cancel my subscription? Chevron down icon Chevron up icon

To cancel your subscription with us simply go to the account page - found in the top right of the page or at https://subscription.packtpub.com/my-account/subscription - From here you will see the ‘cancel subscription’ button in the grey box with your subscription information in.

What are credits? Chevron down icon Chevron up icon

Credits can be earned from reading 40 section of any title within the payment cycle - a month starting from the day of subscription payment. You also earn a Credit every month if you subscribe to our annual or 18 month plans. Credits can be used to buy books DRM free, the same way that you would pay for a book. Your credits can be found in the subscription homepage - subscription.packtpub.com - clicking on ‘the my’ library dropdown and selecting ‘credits’.

What happens if an Early Access Course is cancelled? Chevron down icon Chevron up icon

Projects are rarely cancelled, but sometimes it's unavoidable. If an Early Access course is cancelled or excessively delayed, you can exchange your purchase for another course. For further details, please contact us here.

Where can I send feedback about an Early Access title? Chevron down icon Chevron up icon

If you have any feedback about the product you're reading, or Early Access in general, then please fill out a contact form here and we'll make sure the feedback gets to the right team. 

Can I download the code files for Early Access titles? Chevron down icon Chevron up icon

We try to ensure that all books in Early Access have code available to use, download, and fork on GitHub. This helps us be more agile in the development of the book, and helps keep the often changing code base of new versions and new technologies as up to date as possible. Unfortunately, however, there will be rare cases when it is not possible for us to have downloadable code samples available until publication.

When we publish the book, the code files will also be available to download from the Packt website.

How accurate is the publication date? Chevron down icon Chevron up icon

The publication date is as accurate as we can be at any point in the project. Unfortunately, delays can happen. Often those delays are out of our control, such as changes to the technology code base or delays in the tech release. We do our best to give you an accurate estimate of the publication date at any given time, and as more chapters are delivered, the more accurate the delivery date will become.

How will I know when new chapters are ready? Chevron down icon Chevron up icon

We'll let you know every time there has been an update to a course that you've bought in Early Access. You'll get an email to let you know there has been a new chapter, or a change to a previous chapter. The new chapters are automatically added to your account, so you can also check back there any time you're ready and download or read them online.

I am a Packt subscriber, do I get Early Access? Chevron down icon Chevron up icon

Yes, all Early Access content is fully available through your subscription. You will need to have a paid for or active trial subscription in order to access all titles.

How is Early Access delivered? Chevron down icon Chevron up icon

Early Access is currently only available as a PDF or through our online reader. As we make changes or add new chapters, the files in your Packt account will be updated so you can download them again or view them online immediately.

How do I buy Early Access content? Chevron down icon Chevron up icon

Early Access is a way of us getting our content to you quicker, but the method of buying the Early Access course is still the same. Just find the course you want to buy, go through the check-out steps, and you’ll get a confirmation email from us with information and a link to the relevant Early Access courses.

What is Early Access? Chevron down icon Chevron up icon

Keeping up to date with the latest technology is difficult; new versions, new frameworks, new techniques. This feature gives you a head-start to our content, as it's being created. With Early Access you'll receive each chapter as it's written, and get regular updates throughout the product's development, as well as the final course as soon as it's ready.We created Early Access as a means of giving you the information you need, as soon as it's available. As we go through the process of developing a course, 99% of it can be ready but we can't publish until that last 1% falls in to place. Early Access helps to unlock the potential of our content early, to help you start your learning when you need it most. You not only get access to every chapter as it's delivered, edited, and updated, but you'll also get the finalized, DRM-free product to download in any format you want when it's published. As a member of Packt, you'll also be eligible for our exclusive offers, including a free course every day, and discounts on new and popular titles.