Excel data analysis for dummies / by Paul McFedries (2024)

Access Online

• go.oreilly.com

Availability

Finding items...

Series:

• --For dummies

Contents:

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., 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., 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., 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., and 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.

Summary:

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 gauges, maps, charts, sliders, and other graphical elements to present complex data in an easy-to-understand format. Excel Data Analysis For Dummies explains in depth how to use Excel as a tool for analyzing big data sets. In no time, you'll discover how to mine and analyze critical data in order to make more informed business decisions. Work with external databases, PivotTables, and Pivot Charts Use Excel for statistical and financial functions and data sharing Get familiar with Solver Use the Small Business Finance Manager If you're familiar with Excel but lack a background in the technical aspects of data analysis, this user-friendly book makes it easy to start putting it to use for you.

Subject(s):

• Microsoft Excel (Computer file)
• Electronic spreadsheets—Computer programs
• Electronic spreadsheets—Data processing

ISBN:

9781119518228
1119518229
1119518164 (paperback)
9781119518167

Note:

Includes index.