Learn Everything about Excel Charts, PivotTables, Dashboards and Master Analytic Techniques for Decision Making
Description
Excel's charts, graphs, and reports are beneficial, so it's time to use them to your advantage. Learn how to execute the most innovative analysis on your preferred data using PivotTables, PivotCharts, What-if-Analysis, descriptive statistics, correlations, histograms, sparklines, animated charts, dashboards, trendlines, and more than 100 other charts and graphs.
The book includes the following
● Try and practice new Excel 2022 Charts and Reports in your Excel 2019, Excel 2021 and Office 365 editions.
● Learn with easy-to-read, step-by-step instructions and screenshots.
● Learn to illustrate your data in a way that is readily digestible at a glance.
● Figure out how to make beautiful infographics that reflect your company's personality or culture.
● Learn the ins and outs of making and editing expert PivotTables and PivotCharts.
● Master PivotTables and PivotCharts to construct dynamic dashboards.
● Utilize Excel's What-If analysis to check your assumptions and theories.
● Create Sensitivity-Analysis tables to check the quality of your decision-making tools.
● Create summaries, cross-tabs, filtering, and other visualizations.
● Conduct in-depth statistical analysis in Excel with minimal effort.
This book's thorough instructions on Excel charts will improve readers' skills in making the most innovative and visually appealing reports. You will be able to make dynamic, eye-catching dashboards once you complete reading this book.
Who this book is for
No matter what your professional or academic status is, if you often engage in data analysis, summary creation, and report writing, this book is for you. You'll be able to generate relatively strong reports and infographics from your data, thereby allowing you to make more well-informed decisions. There is no need for you to be an Excel expert to use this book.
Author(s): Dr. Isaac Gottlieb
Publisher: BPB Publications
Year: 2023
Language: English
Commentary: Excel 2022, 100 + PivotTables, Charts & Reports, Graphs, Animations, Sparklines, Goal Seek, Histograms, Correlations, Dashboards
Pages: 230
Tags: Excel 2022, 100 + PivotTables, Charts & Reports, Graphs, Animations, Sparklines, Goal Seek, Histograms, Correlations, Dashboards
Table of Contents
Part I
1. Selecting Various Charts and Graphs
Introduction
Structure
Objectives
Column chart
Stacked column charts
Percentage column charts
3-D column chart
Line chart
Pie chart
Pie of pie or bar of pie charts
Pie of pie chart
Bar of pie chart
Doughnut chart
Bar chart
Area chart
XY (Scatter) chart
Bubble chart
Waterfall chart
Map chart
Surface chart
Combo chart
Funnel chart
Conclusion
Points to remember
Multiple choice questions
Answers
Questions
Key terms
2. Chart Elements, Styles, and Analysis
Introduction
Structure
Objectives
Chart terminology
Chart styles, filter, and quick layout
Chart style
Chart filter
Quick layout
Charts choices and data structure
Choosing the right Chart
Preparing data for all charts
Data structure for specific charts
Dates spacing of the X-axis values
Numbers spacing of the X-axis values
One more chart data structure problem to avoid
Instant chart shortcuts and quick analysis
Instant chart shortcuts (Alt+F1 and F11)
Quick analysis
Creating Excel charts and modifying them
Column charts
Line charts
Pie charts
2-D pie chart
3-D pie chart
Pie of pie and bar of pie charts
Doughnut charts
Bar chart
Area chart
XY (scatter) Chart
Bubble chart
Waterfall chart
Map chart
Surface chart
Combo chart
Funnel chart
Conclusion
Points to remember
Multiple choice questions
Answers
Questions
Key terms
3. Custom Charts, Data Filtering, and Formatting
Introduction
Structure
Objectives
Swapping rows and columns of data in the Chart
Diverse ways to add data to an existing chart
Deleting chart data and filtering chart parts
Moving charts to other locations or sheets
Quick layout menu
Styles and formatting styles
Personal touch
Create and use your personal chart template
Conclusion
Points to remember
Multiple choice questions
Answers
Questions
Key terms
4. Animations, Sparklines, and Conditional Formatting
Introduction
Structure
Objectives
Animated charts
Combination charts
Sparklines
Text charts
Conditional formatting charts
Trendlines and fitting functions
Conclusion
Points to remember
Multiple choice questions
Answers
Questions
Key terms
Part II
5. Speed Up with PivotTables
Introduction
Structure
Objectives
PivotTable definition and examples
How PivotTables work
Uses of PivotTables
Exploring data with PivotTable
PivotTable reports
PivotTable charts
Conclusion
Points to remember
Multiple choice questions
Answers
Questions
Key terms
6. Preparing and Labeling PivotTable Source Data
Introduction
Structure
Objectives
Preparing data for PivotTable reporting
Ensuring that data is in a tabular layout
Avoiding repeating groups as columns
Eliminating gaps in the data source
Using an Excel Defined Table for PivotTables
Conclusion
Points to Remember
Multiple Choice Questions
Answers
Questions
Key terms
7. Create, Calculate, and Format PivotTables
Introduction
Structure
Objectives
Inserting the PivotTable
PivotTable Parts, Layout—Fields, and Areas.
Dragging the fields
Changing the structure by moving fields
Summarizing values
Calculations
Format values
Updating a PivotTable
Manual PivotTable refresh
Refreshing a PivotTable when opening the file
Refreshing a PivotTable when the database size changes
Formatting and customizing PivotTables
Row and column headers
Banded rows and column
Replace empty cells with “0”
Change the layout form of a PivotTable
Format using PivotTable styles
Conclusion
Points to remember
Multiple Choice Questions
Answers
Questions
Key terms
8. Sort, Filter, and Group PivotTables
Introduction
Structure
Objectives
Filter data in a PivotTable
Report filters
Row and column filters
Group data
Grouping numbers
Grouping dates
Group selected items and name a group
Ungroup grouped data
Sorting the table
Conclusion
Points to remember
Multiple choice questions
Answers
Questions
Key terms
9. PivotTables Calculations, Formulas, and Functions
Introduction
Structure
Objectives
Create formulas in a PivotTable
Add Calculated Fields
Add Calculated Items
View all formulas in a PivotTable
Edit or delete a PivotTable formula
Conclusion
Points to remember
Multiple Choice Questions
Answers
Questions
Key terms
10. PivotCharts
Introduction
Structure
Objectives
Create a PivotChart
Swapping rows and columns in the PivotChart
Change chart type
Refresh a PivotChart
Show running totals in a PivotChart
Move a PivotChart to a new sheet
Conclusion
Points to remember
Multiple choice questions
Answers
Questions
Key term
11. PivotTable Slicers and Timelines
Introduction
Structure
Objectives
What is an Excel Slicer?
Creating the Slicer toolbar
Excel timeline
How to create timelines
Timeline tools
Timeline styles
Timeline size
Timeline Show
Timelines and Slicers hide/unhide
Timeline time units
Timeline caption
Conclusions
Points to remember
Multiple choice questions
Answers
Questions
Key terms
12. PivotTable Dashboards
Introduction
Structure
Objectives
Create multiple PivotTables and PivotCharts
Charts options
Multiple PivotCharts in a dashboard
Adding controls for diverse views
Slicers
Timelines
Updating the dashboard
Conclusion
Points to remember
Multiple choice questions
Answers
Questions
Key term
13. Charts and PivotTable Case Studies
Introduction
Structure
Objectives
Gantt chart
Multi-tier X-axis charts
A PivotTable and PivotChart case study
Special charts
Thermometer chart
Creative charts
Conclusion
Points to remember
Multiple choice questions
Answers
Questions
Key term
Part III
14. Descriptive Statistics, Correlation, and Histograms
Introduction
Structure
Objectives
Excel Data Analysis ToolPak
Descriptive statistics
Frequency distribution and histograms
Correlation
Conclusion
Points to remember
Multiple choice questions
Answers
Questions
Key terms
15. Goal Seek and Sensitivity Analysis
Introduction
Structure
Objectives
Goal Seek
Data-table
One-way data table
Two-way data table
Using Scroll Bars
Case study
Conclusion
Points to remember
Multiple choice questions
Answers
Questions
Key terms
Index