What do you think?
Rate this book
Data Visualization with Excel Dashboards and Reports
Large corporations like IBM and Oracle are using Excel dashboards and reports as a Business Intelligence tool, and many other smaller businesses are looking to these tools in order to cut costs for budgetary reasons. An effective analyst not only has to have the technical skills to use Excel in a productive manner but must be able to synthesize data into a story, and then present that story in the most impactful way. Microsoft shows its recognition of this with Excel. In Excel, there is a major focus on business intelligence and visualization. Data Visualization with Excel Dashboards and Reports fills the gap between handling data and synthesizing data into meaningful reports. This title will show readers how to think about their data in ways other than columns and rows.
Most Excel books do a nice job discussing the individual functions and tools that can be used to create an "Excel Report". Titles on Excel charts, Excel pivot tables, and other books that focus on "Tips and Tricks" are useful in their own right; however they don't hit the mark for most data analysts. The primary reason these titles miss the mark is they are too focused on the mechanical aspects of building a chart, creating a pivot table, or other functionality. They don't offer these topics in the broader picture by showing how to present and report data in the most effective way.
What are the most meaningful ways to show trending? How do you show relationships in data? When is showing variances more valuable than showing actual data values? How do you deal with outliers? How do you bucket data in the most meaningful way? How do you show impossible amounts of data without inundating your audience? In Data Visualization with Excel Reports and Dashboards, readers will get answers to all of these questions. Part technical manual, part analytical guidebook; this title will help Excel users go from reporting data with simple tables full of dull numbers, to creating hi-impact reports and dashboards that will wow management both visually and substantively. This book offers a comprehensive review of a wide array of technical and analytical concepts that will help users create meaningful reports and dashboards.
After reading this book, the reader will be able
Analyze large amounts of data and report their data in a meaningful way Get better visibility into data from different perspectives Quickly slice data into various views on the fly Automate redundant reporting and analyses Create impressive dashboards and What-If analyses Understand the fundamentals of effective visualization Visualize performance comparisons Visualize changes and trends over time
Most Excel books do a nice job discussing the individual functions and tools that can be used to create an "Excel Report". Titles on Excel charts, Excel pivot tables, and other books that focus on "Tips and Tricks" are useful in their own right; however they don't hit the mark for most data analysts. The primary reason these titles miss the mark is they are too focused on the mechanical aspects of building a chart, creating a pivot table, or other functionality. They don't offer these topics in the broader picture by showing how to present and report data in the most effective way.
What are the most meaningful ways to show trending? How do you show relationships in data? When is showing variances more valuable than showing actual data values? How do you deal with outliers? How do you bucket data in the most meaningful way? How do you show impossible amounts of data without inundating your audience? In Data Visualization with Excel Reports and Dashboards, readers will get answers to all of these questions. Part technical manual, part analytical guidebook; this title will help Excel users go from reporting data with simple tables full of dull numbers, to creating hi-impact reports and dashboards that will wow management both visually and substantively. This book offers a comprehensive review of a wide array of technical and analytical concepts that will help users create meaningful reports and dashboards.
After reading this book, the reader will be able
Analyze large amounts of data and report their data in a meaningful way Get better visibility into data from different perspectives Quickly slice data into various views on the fly Automate redundant reporting and analyses Create impressive dashboards and What-If analyses Understand the fundamentals of effective visualization Visualize performance comparisons Visualize changes and trends over time
328 pages, Kindle Edition
Published February 5, 2021
25 people are currently reading
31 people want to read
Ratings & Reviews
Friends & Following
Create a free account to discover what your friends think of this book!
Community Reviews
Displaying 1 of 1 review
August 18, 2024
"Data Visualization with Excel Dashboards and Reports" focuses on three main areas: the dashboard development lifecycle, Excel's capabilities in data querying, transformation, and visualization, and the use of purpose-driven visualization elements.
The first topic, though concise, offers practical advice that could be expanded into a comprehensive checklist. Before creating a dashboard, it's crucial to clarify the requirements with all relevant stakeholders: the dashboard requester, data provider, and end users. It's important to prevent any preconceived conclusions by framing them as a series of questions. Additionally, understanding the details of your data from the outset is essential—this includes knowing the source, whether it's internal or external, its aggregation level, who maintains it, and its refresh frequency. End users can play various roles, such as monitoring, making decisions based on feedback, or planning. The developer should establish iterative feedback checkpoints with users to save time and stay aware of any changes in business objectives.
The second topic delves into design principles and Excel functionalities for creating dashboards. It's recommended to break down data into layers to facilitate quick updates (sources, staging & analysis, presentation). Use Excel to create a wireframe mockup that adheres to key principles: keeping everything on a single screen, delivering messages within five seconds, ensuring data consistency, and using charts appropriately. Common Excel tools for data transformation include PivotTables, Worksheet functions (XLOOKUP, SUMPRODUCT, Array Formulas, PivotCharts), and organizing tables with Structured Table Referencing and Data Models. These design principles are illustrated through three case studies: monitoring a software project, tracking HR KPIs, and analyzing financial information and ratios.
The third area categorizes charts into three types: Comparisons (mostly changes over time), Compositions (parts of a whole), and Relationships. Comparison charts include line charts, panel charts for when there are too many lines, bar charts with differences indicated by arrows, dot/box plots for counts, bullet charts for progress tracking, and funnel charts for tracking progress through interdependent stages. Composition charts include pie and doughnut charts (with the latter being more recommended), sunburst charts for breaking down pie charts, waffle charts (like those used on GitHub for commits), stacked bar charts, histograms, waterfall charts, and treemap charts (like those used on stock boards). Lastly, relationship charts include scatter plots and bubble charts, which can capture an extra dimension with the size of the circles. The book also covers non-chart elements, such as custom number formatting, icons, color scales, data bars, and sparklines, which can be used to render visualizations within a single cell.
The first topic, though concise, offers practical advice that could be expanded into a comprehensive checklist. Before creating a dashboard, it's crucial to clarify the requirements with all relevant stakeholders: the dashboard requester, data provider, and end users. It's important to prevent any preconceived conclusions by framing them as a series of questions. Additionally, understanding the details of your data from the outset is essential—this includes knowing the source, whether it's internal or external, its aggregation level, who maintains it, and its refresh frequency. End users can play various roles, such as monitoring, making decisions based on feedback, or planning. The developer should establish iterative feedback checkpoints with users to save time and stay aware of any changes in business objectives.
The second topic delves into design principles and Excel functionalities for creating dashboards. It's recommended to break down data into layers to facilitate quick updates (sources, staging & analysis, presentation). Use Excel to create a wireframe mockup that adheres to key principles: keeping everything on a single screen, delivering messages within five seconds, ensuring data consistency, and using charts appropriately. Common Excel tools for data transformation include PivotTables, Worksheet functions (XLOOKUP, SUMPRODUCT, Array Formulas, PivotCharts), and organizing tables with Structured Table Referencing and Data Models. These design principles are illustrated through three case studies: monitoring a software project, tracking HR KPIs, and analyzing financial information and ratios.
The third area categorizes charts into three types: Comparisons (mostly changes over time), Compositions (parts of a whole), and Relationships. Comparison charts include line charts, panel charts for when there are too many lines, bar charts with differences indicated by arrows, dot/box plots for counts, bullet charts for progress tracking, and funnel charts for tracking progress through interdependent stages. Composition charts include pie and doughnut charts (with the latter being more recommended), sunburst charts for breaking down pie charts, waffle charts (like those used on GitHub for commits), stacked bar charts, histograms, waterfall charts, and treemap charts (like those used on stock boards). Lastly, relationship charts include scatter plots and bubble charts, which can capture an extra dimension with the size of the circles. The book also covers non-chart elements, such as custom number formatting, icons, color scales, data bars, and sparklines, which can be used to render visualizations within a single cell.
Displaying 1 of 1 review