Data Modeling with Microsoft Excel

Data Modeling with Microsoft Excel

by Bernard Obeng Boateng
Released November 2023
Publisher(s): Packt Publishing
ISBN: 9781803240282

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

Book description

Save time analyzing volumes of data using a structured method to extract, model, and create insights from your data

Key Features

  • Acquire expertise in using Excel’s Data Model and Power Pivot to connect and analyze multiple sources of data
  • Create key performance indicators for decision making using DAX and Cube functions
  • Apply your knowledge of Data Model to build an interactive dashboard that delivers key insights to your users
  • Purchase of the print or Kindle book includes a free PDF eBook

Book Description

Microsoft Excel's BI solutions have evolved, offering users more flexibility and control over analyzing data directly in Excel. Features like PivotTables, Data Model, Power Query, and Power Pivot empower Excel users to efficiently get, transform, model, aggregate, and visualize data.

Data Modeling with Microsoft Excel offers a practical way to demystify the use and application of these tools using real-world examples and simple illustrations.

This book will introduce you to the world of data modeling in Excel, as well as definitions and best practices in data structuring for both normalized and denormalized data. The next set of chapters will take you through the useful features of Data Model and Power Pivot, helping you get to grips with the types of schemas (snowflake and star) and create relationships within multiple tables. You’ll also understand how to create powerful and flexible measures using DAX and Cube functions.

By the end of this book, you’ll be able to apply the acquired knowledge in real-world scenarios and build an interactive dashboard that will help you make important decisions.

Note: To access the supplemental material, subscribers should purchase a print copy of the book. The ebook can be accessed through the QR code or link provided inside the Print book. Proof of purchase is mandatory to access the ebook.

What you will learn

  • Implement the concept of data modeling within and beyond Excel
  • Get, transform, model, aggregate, and visualize data with Power Query
  • Understand best practices for data structuring in MS Excel
  • Build powerful measures using DAX from the Data Model
  • Generate flexible calculations using Cube functions
  • Design engaging dashboards for your users

Who this book is for

This book is for Excel users looking for hands-on and effective methods to manage and analyze large volumes of data within Microsoft Excel using Power Pivot. Whether you’re new or already familiar with Excel’s data analytics tools, this book will give you further insights on how you can apply Power Pivot, Data Model, DAX measures, and Cube functions to save time on routine data management tasks. An understanding of Excel’s features like tables, PivotTable, and some basic aggregating functions will be helpful but not necessary to make the most of this book.

Table of contents

  1. Data Modeling with Microsoft Excel
  2. Foreword
  3. Contributors
  4. About the author
  5. About the reviewers
  6. Preface
    1. Who this book is for
    2. What this book covers
    3. To get the most out of this book
    4. Conventions used
    5. Get in touch
    6. Packt is searching for authors like you
    7. Share Your Thoughts
    8. Download a free PDF copy of this book
  7. Part 1: Overview and Introduction to Data Modeling in Microsoft Excel
  8. Chapter 1: Getting Started with Data Modeling – Overview and Importance
    1. Understanding the concept of data modeling
      1. Comparing a one-table analysis to multiple-table analysis in Microsoft Excel
      2. Practical use cases for a data model
      3. The accountant
      4. The salesperson
    2. Introduction to Power Pivot, Excel versions, and installation
    3. How do I install Power Pivot?
      1. Exploring the features of Power Pivot
      2. What is DAX?
    4. Best practices with Power Pivot
    5. Summary
    6. Questions for discussion
  9. Chapter 2: Data Structuring for Data Models – What’s the best way to layout your data?
    1. Data structuring – understanding the three golden rules
      1. Understanding data redundancy
      2. Problems caused by data redundancy
      3. What is data normalization?
    2. Denormalized and normalized data
    3. Understanding table relationships
      1. One-to-many relationship
      2. Many-to-many relationship
      3. One-to-one relationship
    4. Understanding dimension and fact tables
      1. Fact table – sales
      2. Dimension table – product
      3. Dimension table – customer
      4. Dimension table – date
    5. Understanding the role of primary keys and foreign keys
    6. Summary
    7. Key terms in this chapter
    8. Questions for discussion
  10. Chapter 3: Preparing Your Data for the Data Model – Cleaning and Transforming Your Data Using Power Query
    1. Understanding connections and queries
      1. Connections
      2. Queries
    2. An overview of the Power Query editor
    3. Getting your data type right
    4. Add Column or Transform?
    5. Merging and appending data using Power Query
    6. Summary
  11. Chapter 4: Data Modeling with Power Pivot – Understanding How to Combine and Analyze Multiple Tables Using the Data Model
    1. Adding queries/tables to your data model
    2. Adding columns to your data model
    3. Creating calendar tables
    4. Creating relationships using primary and foreign keys
    5. Understanding the different types of schemas (snowflake and star)
      1. Creating hierarchies
      2. Hiding fields and tables from client tools
    6. Summary
  12. Part 2: Creating Insightful Calculations from your Data Model using DAX and Cube Functions
  13. Chapter 5: Creating DAX Calculations from Your Data Model – Introduction to Measures and Calculated Columns
    1. DAX as a calculated column or measure
    2. Creating your first measure – where to go
    3. Common DAX functions (time intelligence, FILTER, CALCULATE, and so on)
    4. Understanding row and filter contexts
    5. Editing your DAX formulas
    6. Summary
  14. Chapter 6: Creating Cube Functions from Your Data Model – a Flexible Alternative to Calculations in Your Data Model
    1. What are cube functions?
    2. When do we use cube formulas?
    3. Exploring cube functions in Microsoft Excel
      1. CUBEVALUE
      2. Adding slicers to your cube formulas
      3. CUBEMEMBER
      4. CUBESET
      5. CUBESETCOUNT
      6. CUBERANKEDMEMBER
    4. Spilling cube functions with dynamic array formulas
    5. Summary
  15. Part 3: Putting it all together with a Dashboard
  16. Chapter 7: Communicating Insights from Your Data Model Using Dashboards – Overview and Uses
    1. What are dashboards?
    2. Factors to consider when laying out your dashboard
      1. Purpose and audience
      2. Clarity and simplicity
      3. Hierarchy and organization
      4. Consistency and visual harmony
      5. Interactivity and user controls
      6. Data visualization techniques
      7. Accessibility and documentation
    3. Common dashboard elements
      1. PivotTables
      2. Charts
      3. Text boxes and labels
      4. Images and shapes
      5. Sparklines
      6. Conditional formatting
    4. Making your dashboard interactive
      1. Slicers
      2. Hyperlinks
    5. Summary
  17. Chapter 8: Visualization Elements for Your Dashboard – Slicers, PivotCharts, Conditional Formatting, and Shapes
    1. Laying out your dashboard using mockups or wireframes
      1. Enter mockups and wireframes
      2. Practice your mockup in Excel
    2. Using shapes as cards in your dashboards
    3. Inserting conditional formatting and sparklines
      1. Sparklines
      2. Conditional formatting
    4. Adding and formatting charts
    5. Inserting slicers for interaction
    6. Summary
  18. Chapter 9: Choosing the Right Design Themes – Less Is More with Colors
    1. Understanding your users
      1. Know your audience’s preferences
      2. Accessibility is key
      3. Consistency is crucial
    2. How to reduce noise in your presentation
    3. Using colors to tell a story
      1. Associative colors
      2. Using gradient colors for scales
      3. Highlighting key data
    4. Summary
  19. Chapter 10: Publication and Deployment – Sharing with Report Users
    1. Protecting your workbook
      1. Skill one – restricting access to sensitive parts of your workbook
    2. Collaboration
      1. Skill two – inviting people to collaborate
    3. Publishing a view-only online version via OneDrive/SharePoint
      1. Skill three – using OneDrive/SharePoint as a means to share your final work
    4. Exporting your data model to Power BI
    5. Summary
  20. Index
    1. Why subscribe?
  21. Other Books You May Enjoy
    1. Packt is searching for authors like you
    2. Share Your Thoughts
    3. Download a free PDF copy of this book

Product information

  • Title: Data Modeling with Microsoft Excel
  • Author(s): Bernard Obeng Boateng
  • Release date: November 2023
  • Publisher(s): Packt Publishing
  • ISBN: 9781803240282