Cover Image
Read Now

Excel data analysis for dummies

Take Excel to the next level Excel is the world's leading spreadsheet application. It's a key module in Microsoft Office--the number-one productivity suite--and it is the number-one business intelligence tool. An Excel dashboard report is a visual presentation of critical data and uses gau...

Full description

Bibliographic Details
Main Author: McFedries, Paul
Format: eBook
Language:English
Published: Hoboken, NJ John Wiley & Sons, Inc. 2019
Edition:4th edition
Series:For dummies
Subjects:
Electronic Spreadsheets / Data Processing
Tableurs / Logiciels
Electronic Spreadsheets / Computer Programs / Fast
Electronic Spreadsheets / Computer Programs / Http://id.loc.gov/authorities/subjects/sh86007613
Microsoft Excel (computer File) / Http://id.loc.gov/authorities/names/n86025775
Tableurs / Informatique
Microsoft Excel (computer File) / Fast
Online Access:
Collection: O'Reilly - Collection details see MPG.ReNa
Table of Contents:
  • 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
  • It's Sometimes a Raw Deal
  • Chapter 5 Scrub-a-Dub-Dub: Cleaning Data
  • Editing Your Imported Workbook
  • Deleting unnecessary columns
  • Deleting unnecessary rows
  • Resizing columns
  • Resizing rows
  • Erasing unneeded contents in a cell or range
  • Formatting numeric values
  • Copying worksheet data
  • Moving worksheet data
  • Replacing data in fields
  • Cleaning Data with Text Functions
  • The CLEAN function
  • The CONCAT function
  • The EXACT function
  • The FIND function
  • The LEFT function
  • The LEN function
  • The LOWER function
  • The MID function
  • The NUMBERVALUE function
  • The PROPER function
  • The REPLACE function
  • The RIGHT function
  • The SEARCH function
  • The SUBSTITUTE function
  • The TEXT function
  • The TEXTJOIN function
  • The TRIM function
  • The UPPER function
  • The VALUE function
  • Converting text function formulas to text
  • Using Validation to Keep Data Clean
  • Chapter 6 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
  • Intro
  • Title Page
  • Copyright Page
  • Table of Contents
  • 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
  • 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 with PivotTables and PivotCharts
  • Chapter 7 Creating and Using PivotTables
  • Understanding PivotTables
  • Exploring PivotTable Features
  • Building a PivotTable from an Excel Table
  • Creating a PivotTable from External Data
  • 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 8 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 9 Building PivotCharts
  • Introducing the PivotChart
  • Understanding PivotChart pros and cons
  • Taking a PivotChart tour
  • Understanding PivotChart limitations
  • Creating a PivotChart
  • Calculating a Moving Average
  • Determining Rank and Percentile
  • Generating Random Numbers
  • Creating a Frequency Distribution
  • Chapter 13 Analyzing Data with 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
  • Creating a PivotChart from a PivotTable
  • Embedding a PivotChart on a PivotTable's worksheet
  • Creating a PivotChart from an Excel 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 Discovering Advanced Data Analysis Tools
  • 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
  • Chapter 11 Analyzing Data with 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 with Descriptive Statistics
  • Loading the Analysis ToolPak
  • Generating Descriptive Statistics

Similar Items