Excel data analysis for dummies

Previous edition: 2002.

Includes index.

Machine generated contents note: About This Book -- What You Can Safely Ignore -- What You Shouldn't Ignore (Unless You're a Masochist) -- Foolish Assumptions -- How This Book Is Organized -- pt. I Where's the Beef? -- pt. II PivotTables and PivotCharts -- pt. III Advanced Tools -- pt. IV The Part of Tens -- Icons Used in This Book -- Beyond the Book -- Where to Go from Here -- pt. I Where's the Beef? -- ch. 1 Introducing Excel Tables -- What Is a Table and Why Do I Care? -- Building Tables -- Exporting from a database -- Building a table the hard way -- Building a table the semi-hard way -- Analyzing Table Information -- Simple statistics -- Sorting table records -- Using AutoFilter on a table -- Undoing a filter -- Turning off filter -- Using the custom AutoFilter -- Filtering a filtered table -- Using advanced filtering -- ch. 2 Grabbing Data from External Sources -- Getting Data the Export-Import Way -- Exporting: The first step -- Importing: The second step (if necessary) --

Contents note continued: Querying External Databases and Web Page Tables -- Running a web query -- Importing a database table -- Querying an external database -- It's Sometimes a Raw Deal -- ch. 3 Scrub-a-Dub-Dub: Cleaning Data -- Editing Your Imported Workbook -- Delete unnecessary columns -- Delete unnecessary rows -- Resize columns -- Resize rows -- Erase unneeded cell contents -- Format numeric values -- Copying worksheet data -- Moving worksheet data -- Replacing data in fields -- Cleaning Data with Text Functions -- What's the big deal, Steve? -- The answer to some of your problems -- The CLEAN function -- The CONCATENATE function -- The EXACT function -- The FIND function -- The FIXED function -- The LEFT function -- The LEN function -- The LOWER function -- The MID function -- The PROPER function -- The REPLACE function -- The REPT function -- The RIGHT function -- The SEARCH function -- The SUBSTITUTE function -- The T function -- The TEXT function --

Contents note continued: The TRIM function -- The UPPER function -- The VALUE function -- Converting text function formulas to text -- Using Validation to Keep Data Clean -- pt. II Pivot Tables and PivotCharts -- ch. 4 Working with PivotTables -- Looking at Data from Many Angles -- Getting Ready to Pivot -- Running the PivotTable Wizard -- Fooling Around with Your Pivot Table -- Pivoting and re-pivoting -- Filtering pivot table data -- Refreshing pivot table data -- Sorting pivot table data -- Pseudo-sorting -- Grouping and ungrouping data items -- Selecting this, selecting that -- Where did that cell's number come from? -- Setting value field settings -- Customizing How Pivot Tables Work and Look -- Setting pivot table options -- Formatting pivot table information -- ch. 5 Building PivotTable Formulas -- Adding Another Standard Calculation -- Creating Custom Calculations -- Using Calculated Fields and Items -- Adding a calculated field -- Adding a calculated item --

Contents note continued: Removing calculated fields and items -- Reviewing calculated field and calculated item formulas -- Reviewing and changing solve order -- Retrieving Data from a Pivot Table -- Getting all the values in a pivot table -- Getting a value from a pivot table -- Arguments of the GETPIVOTDATA function -- ch. 6 Working with PivotCharts -- Why Use a Pivot Chart? -- Getting Ready to Pivot -- Running the PivotTable Wizard -- Fooling Around with Your Pivot Chart -- Pivoting and re-pivoting -- Filtering pivot chart data -- Refreshing pivot chart data -- Grouping and ungrouping data items -- Using Chart Commands to Create Pivot Charts -- ch. 7 Customizing PivotCharts -- Selecting a Chart Type -- Working with Chart Styles -- Changing Chart Layout -- Chart and axis titles -- Chart legend -- Chart data labels -- Chart data tables -- Chart axes -- Chart gridlines -- Changing a Chart's Location -- Formatting the Plot Area -- Formatting the Chart Area --

Contents note continued: Chart fill patterns -- Chart area fonts -- Formatting 3-D-Charts -- Formatting the walls of a 3-D chart -- Using the 3-D View command -- pt. III Advanced Toots -- ch. 8 Using the Database Functions -- Quickly Reviewing Functions -- Understanding function syntax rules -- Entering a function manually -- Entering a function with the Function command -- Using the DAVERAGE Function -- Using the DCOUNT and DCOUNTA Functions -- Using the DGET Function -- Using the DMAX and DMAX Functions -- Using the DPRODUCT Function -- Using the DSTDEV and DSTDEVP Functions -- Using the DSUM Function -- Using the DVAR and DVARP Functions -- ch. 9 Using the Statistics Functions -- Counting Items in a Data Set -- COUNT: Counting cells with values -- COUNT A: Alternative counting cells with values -- COUNTBLANK: Counting empty cells -- COUNTIF: Counting cells that match criteria -- PERMUT: Counting permutations -- COMBIN: Counting combinations -- Means, Modes, and Medians --

Contents note continued: AVEDEV: An average absolute deviation -- AVERAGE: Average -- AVERAGEA: An alternate average -- TRIMMEAN: Trimming to a mean -- MEDIAN: Median value -- MODE: Mode value -- GEOMEAN: Geometric mean -- HARMEAN: Harmonic mean -- Finding Values, Ranks, and Percentiles -- MAX: Maximum value -- MAXA: Alternate maximum value -- MIN: Minimum value -- MINA: Alternate minimum value -- LARGE: Finding the kth largest value -- SMALL: Finding the kth smallest value -- RANK: Ranking an array value -- PERCENTRANK: Finding a percentile ranking -- PERCENTILE: Finding a percentile ranking -- FREQUENCY: Frequency of values in a range -- PROB: Probability of values -- Standard Deviations and Variances -- STDEV: Standard deviation of a sample -- STDEVA: Alternate standard deviation of a sample -- STDEVP: Standard deviation of a population -- STDEVPA: Alternate standard deviation of a population -- VAR: Variance of a sample -- VARA: Alternate variance of a sample --

Contents note continued: VARP: Variance of a population -- VARPA: Alternate variance of a population -- COVARIANCE.P and COVARIANCE.S: Covariances -- DEVSQ: Sum of the squared deviations -- Normal Distributions -- NORM.DIST: Probability X falls at or below a given value -- NORM.INV: X that gives specified probability -- NORM.S.DIST: Probability variable within z-standard deviations -- NORM.S.INV: z-value equivalent to a probability -- STANDARDIZE: z-value for a specified value -- CONFIDENCE: Confidence interval for a population mean -- KURT: Kurtosis -- SKEW and SKEW.P: Skewness of a distribution -- t-distributions -- T.DIST: Left-tail Student t-distribution -- T.DIST.RT: Right-tail Student t-distribution -- T.DIST.2T: Two-tail Student t-distribution -- T.INV: Left-tailed Inverse df Student t-distribution -- T.INV.2T: Two-tailed Inverse of Student t-distribution -- T.TEST: Probability two samples from same population -- f-distributions --

Contents note continued: F.DIST: Left-tailed f-distribution probability -- F.DIST.RT: Right-tailed f-distribution probability -- F.INV:Left-tailed f-value given f-distribution probability -- F.INV.RT:Right-tailed f-value given f-distribution probability -- F.TEST: Probability data set variances not different -- Binomial Distributions -- BINOM.DIST: Binomial probability distribution -- BINOM.INV: Binomial probability distribution -- BINOM.DIST.RANGE: Binomial probability of Trial Result -- NEGBINOM.DIST: Negative binominal distribution -- CRITBINOMT Cumulative binomial distribution -- HYPGEOM.DIST: Hypergeometric distribution -- Chi-Square-Distributions -- CHISQ.DIST.RT: Chi-square distribution -- CHISQ.DIST: Chi square distribution -- CHISQ.INV.RT: Right-tailed chi-square distribution probability -- CHISQ.INV: Left-tailed chi-square distribution probability -- CHISQ.TEST: Chi-square test -- Regression Analysis -- FORECAST: Forecast dependent variables using a best-fit line --

Contents note continued: INTERCEPT: y-axis intercept of a line -- LINEST -- SLOPE: Slope of a regression line -- STEYX: Standard error -- TREND -- LOGEST: Exponential regression -- GROWTH: Exponential growth -- Correlation -- CORREL: Correlation coefficient -- PEARSON: Pearson correlation coefficient -- RSQ: r-squared value for a Pearson correlation coefficient -- FISHER -- FISHERINV -- Some Really Esoteric Probability Distributions -- BETA.DIST: Cumulative beta probability density -- BETA.INV: Inverse cumulative beta probability density -- EXPON.DIST: Exponential probability distribution -- GAMMA.DIST: Gamma distribution probability -- GAMMAINV: X for a given gamma distribution probability -- GAMMALN: Natural logarithm of a gamma distribution -- LOGNORMDIST: Probability of lognormal distribution -- LOGINV: Value associated with lognormal distribution probability -- POISSON: Poisson distribution probabilities -- WEIBULL: Weibull distribution -- ZTEST: Probability of a z-test --

Contents note continued: ch. 10 Descriptive Statistics -- Using the Descriptive Statistics Tool -- Creating a Histogram -- Ranking by Percentile -- Calculating Moving Averages -- Exponential Smoothing -- Generating Random Numbers -- Sampling Data -- ch. 11 Inferential Statistics -- Using the t-test Data Analysis Tool -- Performing z-test Calculations -- Creating a Scatter Plot -- Using the Regression Data Analysis Tool -- Using the Correlation Analysis Tool -- Using the Covariance Analysis Tool -- Using the ANOVA Data Analysis Tools -- Creating an f-test Analysis -- Using Fourier Analysis -- ch. 12 Optimization Modeling with Solver -- Understanding Optimization Modeling -- Optimizing your imaginary profits -- Recognizing constraints -- Setting Up a Solver Worksheet -- Solving an Optimization Modeling Problem -- Reviewing the Solver Reports -- The Answer Report -- The Sensitivity Report -- The Limits Report -- Some other notes about Solver reports --

Contents note continued: Descriptive Statistics Are Straightforward -- Averages Aren't So Simple Sometimes -- Standard Deviations Describe Dispersion -- An Observation Is an Observation -- A Samplers Subset of Values -- Inferential Statistics Are Cool but Complicated -- Probability Distribution Functions 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 -- ch. 14 Almost Ten Tips for Presenting Table Results and Analyzing Data -- Work Hard to Import Data -- Design Information Systems to Produce Rich Data -- Don't Forget about Third-Party Sources -- Just Add It -- Always Explore Descriptive Statistics -- Watch for Trends -- Slicing and Dicing: Cross-Tabulation -- Chart It, Baby -- Be Aware of Inferential Statistics -- ch. 15 Ten Tips for Visually Analyzing and Presenting Data --

Contents note continued: Using the Right Chart Type -- Using Your Chart Message as the Chart Title -- Beware of Pie Charts -- Consider Using Pivot Charts for Small Data Sets -- Avoiding 3-D Charts -- Never Use 3-D Pie Charts -- Be Aware of the Phantom-Data Markers -- Use Logarithmic Scaling -- Don't Forget to Experiment -- Get Tufte.

Excel is the number-one spreadsheet application, with ever-expanding capabilities. If you're only using it to balance the books, you're missing out on a host of functions that can benefit your business or personal finances by uncovering trends and other important information hidden within the numbers. Excel Data Analysis For Dummies, 2nd Edition eliminates the need for advanced statistics or analysis courses by allowing you to harness the full power of Excel to do the heavy lifting for you. This 2nd Edition is fully updated to include information about Excel's latest features, making it a your go-to Excel guide for data analysis.