Excel Data Analysis Your visual blueprint for creating and analyzing data, charts and PivotTables

by Etheridge, Denise

• ISBN13: 9780470591604
• ISBN10: 0470591609
• Edition: 3rd
• Format: Paperback
• Copyright: 2010-07-06
• Publisher: Visual

Summary

Advanced techniques for Excel power usersCrunch and analyze Excel data the way the professionals do with this clean, uncluttered, visual guide to advanced Excel techniques. Using numerous screenshots and easy-to-follow numbered steps, this book clearly shows you how to perform professional-level modeling, charting, data access, data slicing, and other functions. You'll find super techniques for getting the most out of Excel's statistical and financial functions, Excel PivotTables and PivotCharts, Excel Solver, and more. Provides a clear look at power-using Excel, the world's leading spreadsheet application from Microsoft, and part of the new Microsoft Office 2010 suite Expands your Excel knowledge and helps you use Excel data more efficiently Demonstrates how to retrieve data from databases;; cut, slice, and pivot data using PivotTables; model data and chart data; and use advanced formulas Explores all features and functions in friendly, two-color pages packed with screenshots, numbered steps, and other visual graphics that clearly show you how to accomplish tasks Includes practical examples, tips, and advice to help you get the most out of Excel's features and functionsLearn Excel at the highest levels with this practical guide.

Author Biography

Denise Etheridge (Burlington, NJ) is a certified public accountant as well as the president and founder of BayCon Group, Inc. She publishes Web sites, provides consulting services on accounting-related software, teaches college-level computer and accounting courses, and has authored or coauthored two computer related books: Flash ActionScript: Your visual blueprint for creating Flash-enhanced Web sites. In addition, she authored Excel 2007 Visual Quick Tips and Excel 2007 Top 100 Simplified Tips and Tricks. Denise has trained and assisted clients with the implementation of accounting-related software since 1985. Stanford University and the United States House of Representatives are among her former clients. Prior to founding BayCon Group, she spent two years working for Oracle Corporation as an Oracle Financials software consultant.

Table of Contents

How To Use This Book

Getting Started

Introducing Data Analysis with Excel

Tour the Excel Window

Explore the Ribbon

Using the Mini Toolbar and Context Menu

Select Options on the Status Bar

Take a Look at Backstage View

Upload a File to Excel's Web-based Application

Create a File Using Excel's Web-based Application

Enter Data

Select Cells

Formatting A Worksheet

Using the Ribbon to Format Numbers

Using the Format Cells Dialog Box

Understanding Dates and Times

Format Percentages

Format Fractions

Format in Scientific Notation

Format as Text

Add a Border

Change the Font or Font Size

Add a Background Color

Change the Font Color

Bold, Underline, or Italicize

Align Data

Rotate Data

Wrap Text

Merge and Center

Apply a Style

Using Format Painter

Clear Formats

Structuring Your Workbook

Cut, Copy, and Paste Cells

Using Live Preview with Paste

Paste from the Office Clipboard

Insert or Delete

Find and Replace Information

Change the Name of a Worksheet

Change Column Widths or Row Heights

Hide Columns or Rows

Hide a Worksheet

Move or Copy a Worksheet

Freeze Worksheet Titles

Hide Gridlines, Headings, or the Formula Bar

Creating Formulas

Understanding Formulas

Calculate with an Operator

Calculate Using a Function and Cell Addresses

Create an Array Formula

Using the Sum, Average, Count, Min, and Max Functions

Create a Formula that Refers to Another Worksheet

Understanding Relative and Absolute Cell Addresses

Edit Formulas

Name Cells and Ranges

Define and Display Constants

Create Formulas That Include Names

Check Formulas for Errors

Trace Precedents and Dependents

Using Functions

Understanding the Function Wizard

Round a Number

Create a Conditional Formula

Calculate a Conditional Sum

Calculate a Conditional Count

Find the Square Root

Retrieve Column or Row Numbers

Using VLOOKUP

Determine the Location of a Value

Using INDEX

Perform Date and Time Calculations

Using Financial Functions

Calculate Future Value

Calculate Present Value

Calculate Loan Payments

Calculate Principal or Interest

Calculate the Interest Rate

Calculate the Internal Rate of Return

Calculate Straight-Line Depreciation

Calculate Declining Balance Depreciation

Calculate Double-Declining Balance Depreciation

Calculate Sum-of-the-Years-Digits Depreciation

Using Statistical Functions And Tools

Calculate an Average

Calculate a Conditional Average

Calculate the Median or the Mode

Calculate Rank

Determine the Nth Largest Value

Calculate Frequency

Calculate Variance and Standard Deviation

Find the Correlation

Install Excel Add-Ins

Calculate a Moving Average

Compare Variances

Using the Data Analysis Toolpak to Determine Rank and Percentile

Calculate Descriptive Statistics

Organizing Worksheet Data

Enter Data with a Form

Perform Simple Sorts and Filters

Perform Multilevel Sorts

Perform a Custom Sort

Sort by Cell Color, Font Color, or Cell Icon

Perform Complex Filters

Enter Criteria to Find Records

Using Advanced Filtering Techniques

Filter Duplicate Records

Count Filtered Records

Subtotal Records

Using Auto Outline

Define Data as a Table

Modify a Table Style

Using Database Functions with a Table

Working With Pivottables

Create a PivotTable

Modify a PivotTable Layout

Summarize PivotTable Values

Create a PivotTable Calculated Field

Group the Rows or Columns in a PivotTable

Apply a Style to a PivotTable

Filter a PivotTable

Sort a PivotTable

Retrieve Values from a PivotTable

Using Slicer

Charting Data

Create a Chart

Add Chart Details

Create a Combination Chart

Change the Chart Type

Add or Remove Chart Data

Add Sparklines

Create a Trendline

Add Error Bars

Create a Histogram

Chart Filtered Data

Create a PivotChart

Filter a PivotChart

Working With External Data

Paste Link into Word

Embed a Worksheet

Hyperlink a Worksheet

Query a Web Site

Import a Text File

Import an Access Database

Query an Access Database

Using Data Analysis Tools And Techniques

Perform What-If Analysis

Optimize a Result with Goal Seek

Using Solver

Solve a Formula with a Data Table

Extend a Series with Auto Fill

Join Text

Add a Calculator

Consolidate Worksheets

Highlight Cells that Meet Your Criteria

Find the Highest or Lowest Ranked Values

Add Data Bars to Your Worksheet

Add Icon Sets to Your Worksheet

Remove Conditional Formatting

Change Conditional Formatting Rules

Paste with Paste Special

Sharing Your Workbook With Others

Validate with a Validation List

Validate with Data Entry Rules

Add Comments to Your Worksheet

Track Changes

Protect Your Worksheet

Save Your Workbook as a Template

Choose a Format When Saving a Workbook

Print Your Workbook

Print Multiple Areas of Your Worksheet

Automating With Macros

Introducing Macros

Set Macro Security

Create a Digital Signature

Record a Macro

Assign a Digital Signature to a Macro

Run a Macro

Create and Launch a Keyboard Shortcut

Assign a Macro to the Quick Access Toolbar

Delete a Macro

Illustrating Your Worksheets

Place a Screenshot in Your Worksheet

Insert Clip Art into Your Worksheet

Crop a Clip Art Illustration or a Photograph

Insert a Picture into Your Worksheet

Recolor a Picture

Adjust the Sharpness, Brightness, and Contrast

Compress a Picture

Add a Border

Add a Picture Effect

Apply a Picture Style

Add an Artistic Effect

Remove a Background

Illustrating Your Ideas

Insert a Text Box

Format a Text Box

Insert a Shape

Add Text to a Shape

Change the Size of an Object

Rotate an Object

Change the Stacking Order

Group Objects

Align Objects

Insert a Symbol

Using SmartArt Graphics

Modify a SmartArt Graphic

Apply a SmartArt Style

Apply a SmartArt Layout

Customizing Excel

Add a Form Control to a Worksheet

Assign Values to a Form Control

Add a Macro to a Form Control

Customize the Quick Access Toolbar

Customize the Ribbon

Using Excel Keyboard Shortcuts

Using Excel Keyboard Shortcuts

Index

Table of Contents provided by Publisher. All Rights Reserved.