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

Part 3: Putting it all together with a Dashboard

This part is dedicated to teaching you how to effectively communicate insights from your data models by creating interactive and visually appealing dashboards. It covers the entire process, from conceptualization to deployment, ensuring that you can not only analyze your data but also present it in a meaningful and impactful way. Section 3 will equip you with the knowledge and skills needed to transform your data models into compelling dashboards, thereby completing the data analysis and presentation loop in Excel.

This section has the following chapters:

Chapter 7, Communicating insights from your Data Model using Dashboards
Chapter 8, Visualization Elements for your Dashboard
Chapter 9, Choosing ...

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

Part 3: Putting it all together with a Dashboard

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

Part 3: Putting it all together with a Dashboard

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.