Excel data analysis
Book - 2022
"Turn jumbles of numbers into graphics, insights, and answers with Excel. With Microsoft Excel, you can, well, excel at data analysis. And Excel Data Analysis For Dummies can help, with clear and easy explanations of the mountain of features for creating, visualizing, and analyzing data. PivotTables, charts, what-if analysis, statistical functions -- it's all in here, with examples and ideas for Excel users of all skill levels. This latest edition covers the most recent updates to Excel and Microsoft 365. You'll beef up your data skills and learn powerful techniques for turning numbers into knowledge. For students, researchers, and business professionals, Excel is the spreadsheet and data application of choice -- and Dummies ...is the best choice for learning how to make those numbers sing. Learn how to use Excel's built-in data analysis features and write your own functions to explore patterns in your data ; Create striking charts and visualizations, and discover multiple ways to tell the stories hidden in the numbers ; Clean up large datasets and identify statistical operations that will answer your questions ; Perform financial calculations, database operations, and more -- without leaving Excel. Excel Data Analysis For Dummies is the go-to resource for Excel users who are looking for better ways to crunch the numbers."--
Saved in:
- Subjects
- Genres
- Instructional and educational works
- Published
-
Hoboken, NJ :
For Dummies, a Wiley brand
[2022]
- Language
- English
- Main Author
- Edition
- Fifth edition
- Item Description
- Includes index.
- Physical Description
- [368] pages : illustrations ; 24 cm
- ISBN
- 9781119844426
- Introduction
- About This Book
- What You Can Safely Ignore
- Foolish Assumptions
- Icons Used in This Book
- Beyond the Book
- Where to Go from Here
- Part 1. Getting Started with Data Analysis
- Chapter 1. Learning Basic Data-Analysis Techniques
- What Is Data Analysis, Anyway?
- Cooking raw data
- Dealing with data
- Building data models
- Performing what-if analysis
- Analyzing Data with Conditional Formatting
- Highlighting cells that meet some criteria
- Showing pesky duplicate values
- Highlighting the top or bottom values in a range
- Analyzing cell values with data bars
- Analyzing cell values with color scales
- Analyzing cell values with icon sets
- Creating a custom conditional-formatting rule
- Editing a conditional-formatting rule
- Removing conditional-formatting rules
- Summarizing Data with Subtotals
- Grouping Related Data
- Consolidating Data from Multiple Worksheets
- Consolidating by position
- Consolidating by category
- Chapter 2. Working with Data-Analysis Tools
- Working with Data Tables
- Creating a basic data table
- Creating a two-input data table
- Skipping data tables when calculating workbooks
- Analyzing Data with Goal Seek
- Analyzing Data with Scenarios
- Create a scenario
- Apply a scenario
- Edit a scenario
- Delete a scenario
- Optimizing Data with Solver
- Understanding Solver
- The advantages of Solver
- When should you use Solver?
- Loading the Solver add-in
- Optimizing a result with Solver
- Adding constraints to Solver
- Save a Solver solution as a scenario
- Chapter 3. Introducing Excel Tables
- What Is a Table and Why Should I Care?
- Building a Table
- Getting the data from an external source
- Converting a range to a table
- Basic table maintenance
- Analyzing Table Information
- Displaying simple statistics
- Adding a column subtotal
- Sorting table records
- Filtering table records
- Clearing a filter
- Turning off AutoFilter
- Applying a predefined AutoFilter
- Applying multiple filters
- Applying advanced filters
- Chapter 4. Grabbing Data from External Sources
- What's All This about External Data?
- Exporting Data from Other Programs
- Importing External Data into Excel
- Importing data from an Access table
- Importing data from a Word table
- Introducing text file importing
- Importing a delimited text file
- Importing a fixed-width text file
- Importing data from a web page
- Importing an XML file
- Querying External Databases
- Defining a data source
- Querying a data source
- Chapter 5. Analyzing Table Data with Functions
- The Database Functions: Some General Remarks
- Retrieving a Value from a Table
- Summing a Column's Values
- Counting a Column's Values
- Averaging a Column's Values
- Determining a Column's Maximum and Minimum Values
- Multiplying a Column's Values
- Deriving a Column's Standard Deviation
- Calculating a Column's Variance
- Part 2. Analyzing Data Using Pivottables and Pivotcharts
- Chapter 6. Creating and Using PivotTables
- Understanding PivotTables
- Exploring PivotTable Features
- Building a PivotTable from an Excel Range or Table
- Creating a PivotTable from External Data
- Building a PivotTable from Microsoft Query
- Building a PivotTable from a new data connection
- Refreshing PivotTable Data
- Refreshing PivotTable data manually
- Refreshing PivotTable data automatically
- Adding Multiple Fields to a PivotTable Area
- Pivoting a Field to a Different Area
- Grouping PivotTable Values
- Grouping numeric values
- Grouping date and time values
- Grouping text values
- Filtering PivotTable Values
- Applying a report filter
- Filtering row or column items
- Filtering PivotTable values
- Filtering a PivotTable with a slicer
- Chapter 7. Performing PivotTable Calculations
- Messing around with PivotTable Summary Calculations
- Changing the PivotTable summary calculation
- Trying out the difference summary calculation
- Applying a percentage summary calculation
- Adding a running total summary calculation
- Creating an index summary calculation
- Working with PivotTable Subtotals
- Turning off subtotals for a field
- Displaying multiple subtotals for a field
- Introducing Custom Calculations
- Formulas for custom calculations
- Checking out the custom calculation types
- Understanding custom calculation limitations
- Inserting a Custom Calculated Field
- Inserting a Custom Calculated Item
- Editing a Custom Calculation
- Deleting a Custom Calculation
- Chapter 8. Building PivotCharts
- Introducing the PivotChart
- Understanding PivotChart pros and cons
- Taking a PivotChart tour
- Understanding PivotChart limitations
- Creating a PivotChart
- Creating a PivotChart from a PivotTable
- Embedding a PivotChart on a PivotTable's worksheet
- Creating a PivotChart from an Excel range or table
- Working with PivotCharts
- Moving a PivotChart to another sheet
- Filtering a PivotChart
- Changing the PivotChart type
- Adding data labels to your PivotChart
- Sorting the PivotChart
- Adding PivotChart titles
- Moving the PivotChart legend
- Displaying a data table with the PivotChart
- Part 3. Disovering Advanced Dat-Analysis Tools
- Chapter 9. Dealing with Data Models
- Understanding Excel Data Models
- Creating a relationship between tables
- Importing related external data tables
- Basing a PivotTable on multiple, related tables
- Managing a Data Model with Power Pivot
- Enabling the Power Pivot add-in
- Adding a table to the Data Model
- Importing related tables from an external data source
- Viewing table relationships
- Viewing relationship details
- Creating a relationship between tables with Power Pivot
- Transforming Data
- Creating a PivotTable or PivotChart from Your Data Model
- Chapter 10. Tracking Trends and Making Forecasts
- Plotting a Best-Fit Trend Line
- Calculating Best-Fit Values
- Plotting Forecasted Values
- Extending a Linear Trend
- Extending a linear trend using the fill handle
- Extending a linear trend using the Series command
- Calculating Forecasted Linear Values
- Plotting an Exponential Trend Line
- Calculating Exponential Trend Values
- Plotting a Logarithmic Trend Line
- Plotting a Power Trend Line
- Plotting a Polynomial Trend Line
- Creating a Forecast Sheet
- Chapter 11. Analyzing Data Using Statistics
- Counting Things
- Counting numbers
- Counting nonempty cells
- Counting empty cells
- Counting cells that match criteria
- Counting cells that match multiple criteria
- Counting permutations
- Counting combinations
- Averaging Things
- Calculating an average
- Calculating a conditional average
- Calculating an average based on multiple conditions
- Calculating the median
- Calculating the mode
- Finding the Rank
- Determining the Nth Largest or Smallest Value
- Calculating the nth highest value
- Calculating the nth smallest value
- Creating a Grouped Frequency Distribution
- Calculating the Variance
- Calculating the Standard Deviation
- Finding the Correlation
- Chapter 12. Analyzing Data Using Descriptive Statistics
- Loading the Analysis ToolPak
- Generating Descriptive Statistics
- Calculating a Moving Average
- Determining Rank and Percentile
- Generating Random Numbers
- Creating a Frequency Distribution
- Chapter 13. Analyzing Data Using inferential Statistics
- Sampling Data
- Using the t-Test Tools
- Performing a z-Test
- Determining the Regression
- Calculating the Correlation
- Calculating the Covariance
- Using the Anova Tools
- Performing an f-Test
- Part 4. The Part of Tens
- Chapter 14. Ten Things You Ought to Know about Statistics
- Descriptive Statistics Are Straightforward
- Averages Aren't So Simple Sometimes
- Standard Deviations Describe Dispersion
- An Observation Is an Observation
- A Sample Is a Subset of Values
- Inferential Statistics Are Cool But Complicated
- Probability Distributions Aren't Always Confusing
- Uniform distribution
- Normal distribution
- Parameters Aren't So Complicated
- Skewness and Kurtosis Describe a Probability Distribution's Shape
- Confidence Intervals Seem Complicated at First But Are Useful
- Chapter 15. Ten Ways to Analyze Financial Data
- Calculating Future Value
- Calculating Present Value
- Determining Loan Payments
- Calculating a Loan Payment's Principal and Interest
- Calculating Cumulative Loan Principal and Interest
- Finding the Required Interest Rate
- Determining the Internal Rate of Return
- Calculating Straight-Line Depreciation
- Returning the Fixed-Declining Balance Depreciation
- Determining the Double-Declining Balance Depreciation
- Chapter 16. Ten Ways to Raise Your PivotTable Game
- Turn the PivotTable Fields Task Pane On and Off
- Change the PivotTable Fields Task Pane Layout
- Display the Details Behind PivotTable Data
- Apply a PivotTable Style
- Create a Custom PivotTable Style
- Preserve PivotTable Formatting
- Rename the PivotTable
- Turn Off Grand Totals
- Reduce the Size of PivotTable Workbooks
- Use a PivotTable Value in a Formula
- Appendix: Glossary of Data Analysis and Excel Terms
- Index