book

Data Modeling with Microsoft Excel

by Bernard Obeng Boateng

November 2023

Intermediate to advanced

316 pages

6h 42m

English

Packt Publishing

Read now

Unlock full access

ForewordContributorsAbout the authorAbout the reviewers
Who this book is forWhat this book coversTo get the most out of this bookConventions usedGet in touchPackt is searching for authors like youShare Your ThoughtsDownload a free PDF copy of this book
Understanding the concept of data modelingComparing a one-table analysis to multiple-table analysis in Microsoft ExcelPractical use cases for a data modelThe accountantThe salespersonIntroduction to Power Pivot, Excel versions, and installationHow do I install Power Pivot?Exploring the features of Power PivotWhat is DAX?Best practices with Power PivotSummaryQuestions for discussion
Data structuring – understanding the three golden rulesUnderstanding data redundancyProblems caused by data redundancyWhat is data normalization?Denormalized and normalized dataUnderstanding table relationshipsOne-to-many relationshipMany-to-many relationshipOne-to-one relationshipUnderstanding dimension and fact tablesFact table – salesDimension table – productDimension table – customerDimension table – dateUnderstanding the role of primary keys and foreign keysSummaryKey terms in this chapterQuestions for discussion
Understanding connections and queriesConnectionsQueriesAn overview of the Power Query editorGetting your data type rightAdd Column or Transform?Merging and appending data using Power QuerySummary
Adding queries/tables to your data modelAdding columns to your data modelCreating calendar tablesCreating relationships using primary and foreign keysUnderstanding the different types of schemas (snowflake and star)Creating hierarchiesHiding fields and tables from client toolsSummary
DAX as a calculated column or measureCreating your first measure – where to goCommon DAX functions (time intelligence, FILTER, CALCULATE, and so on)Understanding row and filter contextsEditing your DAX formulasSummary
What are cube functions?When do we use cube formulas?Exploring cube functions in Microsoft ExcelCUBEVALUEAdding slicers to your cube formulasCUBEMEMBERCUBESETCUBESETCOUNTCUBERANKEDMEMBERSpilling cube functions with dynamic array formulasSummary

What are dashboards?Factors to consider when laying out your dashboardPurpose and audienceClarity and simplicityHierarchy and organizationConsistency and visual harmonyInteractivity and user controlsData visualization techniquesAccessibility and documentationCommon dashboard elementsPivotTablesChartsText boxes and labelsImages and shapesSparklinesConditional formattingMaking your dashboard interactiveSlicersHyperlinksSummary
Laying out your dashboard using mockups or wireframesEnter mockups and wireframesPractice your mockup in ExcelUsing shapes as cards in your dashboardsInserting conditional formatting and sparklinesSparklinesConditional formattingAdding and formatting chartsInserting slicers for interactionSummary
Understanding your usersKnow your audience’s preferencesAccessibility is keyConsistency is crucialHow to reduce noise in your presentationUsing colors to tell a storyAssociative colorsUsing gradient colors for scalesHighlighting key dataSummary
Protecting your workbookSkill one – restricting access to sensitive parts of your workbookCollaborationSkill two – inviting people to collaboratePublishing a view-only online version via OneDrive/SharePointSkill three – using OneDrive/SharePoint as a means to share your final workExporting your data model to Power BISummary
Other Books You May EnjoyPackt is searching for authors like youShare Your ThoughtsDownload a free PDF copy of this book

Content preview from Data Modeling with Microsoft Excel

Preface

Data Modeling with Microsoft Excel is an essential resource for anyone looking to harness the full potential of Microsoft Excel in data analysis.

This book delves deep into the advanced features of Excel, specifically focusing on Power Pivot, Data Analysis Expressions (DAX), and Cube Functions. These tools are pivotal for anyone working with large datasets and seeking to perform complex data modeling and analysis efficiently.

This book is not just about learning the functionalities; it’s about understanding how to apply these tools in real-world scenarios to derive meaningful insights from your data. Whether you are a business analyst, a data scientist, or someone who regularly works with data in Excel, this book will equip you with ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.

Start your free trial

Microsoft Excel Data Analysis and Business Modeling (Office 2021 and Microsoft 365), 7th Edition

Publisher Resources

ISBN: 9781803240282

Data Modeling with Microsoft Excel

by Bernard Obeng Boateng

Preface

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.

You might also like

Microsoft Excel Data Analysis and Business Modeling (Office 2021 and Microsoft 365), 7th Edition

Microsoft Excel 2019 Inside Out

Data Modeling with Microsoft Power BI

Modern Data Analytics in Excel

Publisher Resources

Preface

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,and much more.

You might also like

Microsoft Excel Data Analysis and Business Modeling (Office 2021 and Microsoft 365), 7th Edition

Microsoft Excel 2019 Inside Out

Data Modeling with Microsoft Power BI

Modern Data Analytics in Excel

Publisher Resources

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.