book

Excel® 2010 Workbook for Dummies®

Name: Excel® 2010 Workbook for Dummies®
Author: Greg Harvey
ISBN: 9780470489604

by Greg Harvey

September 2010

Beginner

408 pages

10h 23m

English

For Dummies

Read now

Unlock full access

Copyright
About the Author
Author's Acknowledgments
Publisher's Acknowledgments
Introduction
About This BookConventions Used in This BookFoolish AssumptionsHow This Book Is OrganizedPart I: Creating SpreadsheetsPart II: Using Formulas and FunctionsPart III: Working with GraphicsPart IV: Managing and Securing DataPart V: Doing Data AnalysisPart VI: Macros and Visual Basic for ApplicationsPart VII: The Part of TensUsing the Practice Material on the CD-ROMIcons Used in This BookWhere to Go from Here
I. Creating Spreadsheets
1. Getting Familiar with the Excel 2010 Interface
1.1. Identifying the Parts of the Excel Display Screen1.2. Selecting Commands on the File Menu in the Backstage View1.3. Selecting Commands from the Ribbon1.3.1. Adding a custom tab to the Excel Ribbon1.3.2. Adding commands to groups on your custom tab1.4. Selecting Commands on the Quick Access Toolbar1.4.1. Customizing the Quick Access toolbar1.4.2. Adding more commands to the Quick Access toolbar
2. Entering the Spreadsheet Data
2.1. Launching Excel2.2. Opening a New Workbook2.3. Moving Around the Workbook2.3.1. Moving within the displayed area2.3.2. Moving to a new area of the worksheet2.3.3. Moving to a different sheet in the workbook2.4. Selecting Cell Ranges2.5. Making Cell Entries2.5.1. Entering data in a single cell2.5.2. Entering data in a cell range2.5.3. Filling in a data series with the Fill handle2.5.4. Copying a formula with the Fill handle2.6. Saving the Spreadsheet Data in a Workbook File
3. Formatting the Spreadsheet
3.1. Resizing Columns and Rows3.1.1. Making column widths suit the data3.1.2. Manipulating the height of certain rows3.2. Cell Formatting Techniques3.2.1. Formatting cells with the Ribbon's Home tab3.2.2. Formatting cells with the Format Cells dialog box3.2.3. Using cell styles3.2.4. Using conditional formatting3.3. Hiding Columns and Rows
4. Printing Spreadsheet Reports
4.1. Previewing Pages in the Worksheet and Backstage View4.2. Adjusting Page Breaks4.3. Adding Headers and Footers4.4. Adding Print Titles to a Report4.5. Modifying the Print Setting for a Report4.6. Printing All or Part of the Workbook4.6.1. Printing a range of cells4.6.2. Printing the entire workbook4.6.3. Printing charts in the spreadsheet4.6.4. Printing the spreadsheet formulas

5. Modifying the Spreadsheet
5.1. Finding and Identifying the Region That Needs Editing5.2. Selecting the Ranges to Edit5.3. Editing Data Entries5.4. Catching Errors with Text to Speech5.5. Deleting and Inserting Data and Cells5.6. Moving and Copying Data and Cells5.7. Using Notes in the Spreadsheet5.8. Using Find and Replace and Spell Checking5.9. Group Editing
II. Using Formulas and Functions
6. Building Formulas
6.1. Building Formulas6.1.1. Building formulas by hand6.1.2. Building formulas with built-in functions6.1.3. Editing formulas6.1.4. Altering the natural order of operations6.2. Using External Reference Links6.3. Controlling When Formulas Are Recalculated
7. Copying and Correcting Formulas
7.1. Copying Formulas with Relative References7.2. Copying Formulas with Absolute References7.3. Copying Formulas with Mixed References7.4. Using Range Names in Formulas7.5. Building Array Formulas7.6. Tracing and Eliminating Formula Errors7.7. Dealing with Circular References
8. Creating Date and Time Formulas
8.1. Constructing Date and Time Formulas8.2. Working with Simple Date Functions8.3. Working with Excel's Fancier Date Functions8.4. Working with the Time Functions
9. Financial Formulas and Functions
9.1. Working with Financial Functions9.2. Using the Basic Investment Functions9.3. Figuring the Depreciation of an Asset
10. Using Math Functions
10.1. Rounding Off Values10.2. Finding Products, Powers, and Square Roots10.3. Doing Fancier Sums10.3.1. Summing products, squares, and their differences10.3.2. Conditional totals
11. Using Common Statistical Functions
11.1. Computing Averages11.2. Finding the Highest and Lowest Values11.3. Counting Cells11.4. Using the Statistical Functions in Analysis ToolPak Add-in
12. Using Lookup Functions
12.1. Returning Single Values from a Lookup Table12.1.1. Performing a horizontal lookup12.1.2. Performing a vertical lookup
13. Using Logical Functions
13.1. Working with the Logical Functions13.2. Constructing Decision-Making Formulas13.2.1. Choosing between alternate values13.2.2. Selecting between alternate calculations13.2.3. Nesting IF functions13.3. Constructing Error-Trapping Formulas
14. Text Formulas and Functions
14.1. Constructing Text Formulas14.2. Using Text Functions
III. Working with Graphics
15. Charting Spreadsheet Data
15.1. Understanding Excel Charts15.2. Creating Charts15.3. Formatting Charts15.4. Editing Charts
16. Adding Graphics to Spreadsheets
16.1. Understanding Graphic Objects16.2. Adding Various Types of Graphic Objects16.2.1. Inserting clip art16.2.2. Importing graphics files16.2.3. Adding graphic shapes and text boxes16.2.4. Constructing WordArt16.2.5. Constructing SmartArt
IV. Managing and Securing Data
17. Building and Maintaining Data Lists
17.1. Creating a Data List17.1.1. Adding records to a new data list17.1.2. Editing records in the data form17.2. Sorting Lists17.2.1. Using sorting keys17.2.2. Sorting a list on multiple keys17.2.3. Sorting the fields (columns) in a data list17.3. Subtotaling a List17.4. Filtering a List17.5. Querying External Database Tables
18. Protecting the Spreadsheet
18.1. Password-Protecting the Workbook18.2. Protecting the Worksheet18.3. Doing Data Entry in a Protected Worksheet18.4. Protecting the Entire Workbook
V. Doing Data Analysis
19. Performing What-If Analysis
19.1. Using Data Tables19.1.1. Creating single-variable data tables19.1.2. Creating two-variable data tables19.2. Exploring Various Scenarios19.3. Performing Goal Seeking19.4. Creating Complex Models with Solver
20. Generating Pivot Tables
20.1. Working with Pivot Tables20.2. Creating Pivot Tables20.3. Modifying the Pivot Table20.3.1. Modifying the table formatting20.3.2. Pivoting the table's fields20.3.3. Changing the table summary function and adding calculated fields20.4. Creating Pivot Charts
VI. Macros and Visual Basic for Applications
21. Using Macros
21.1. Creating Macros21.1.1. Using the macro recorder21.1.2. Recording macros with relative cell references21.2. Assigning Macros to the Quick Access Toolbar21.3. Assigning Macros to the Ribbon
22. Using the Visual Basic Editor
22.1. Using the Visual Basic Editor22.1.1. Editing a recorded macro22.1.2. Adding a dialog box that processes user input22.2. Creating User-Defined Functions22.2.1. Using a custom function in your spreadsheet22.2.2. Saving custom functions in add-in files
VII. The Part of Tens
23. Top Ten Features in Excel 2010
23.1. The Excel Ribbon and Backstage View23.2. Conditional Formatting23.3. Pivot Table Filtering with Slicers23.4. Formatting and Editing from the Home Tab23.5. Charts Directly from the Insert Tab23.6. Format As Table23.7. The Zoom Slider on the Status Bar23.8. Page Layout View23.9. Style Galleries23.10. Live Preview
24. Top Ten Tips for Using Excel Like a Pro
24.1. Generating New Workbooks from Templates24.2. Organizing Spreadsheet Data on Different Worksheets24.3. Creating Data Series with AutoFill24.4. Using Range Names24.5. Freezing Column and Row Headings24.6. Preventing Data Entry Errors with Data Validation24.7. Trapping Error Values in Their Original Formulas24.8. Saving Memory by Using Array Formulas24.9. Controlling the Display of Data in Tables through Outlines24.10. Using View Side by Side to Work with Two Workbooks
A. Comparing Worksheets Side by Side
B. About the CD
B.1. System RequirementsB.2. Using the CDB.3. What You'll Find on the CDB.3.1. Workbook Exercise FilesB.3.2. Excel Feature DemosB.4. TroubleshootingB.5. Customer Care
C. Table of Exercises

Content preview from Excel® 2010 Workbook for Dummies®

Chapter 10. Using Math Functions

In This Chapter

Rounding off numbers in a spreadsheet
Using functions for calculating products, powers, and square roots
Summing the products in cell ranges or arrays
Doing conditional summing

The Math & Trig group of functions includes a wide variety of mathematical functions for computing new values as well as for controlling the results of calculations. This group also includes a large assortment of trigonometric functions most often needed in engineering and scientific endeavors. In this chapter, you get a chance to practice using some of the math functions more commonly needed in spreadsheets for business applications.

Rounding Off Values

Rounding off numbers that have fractions is one of the more frequent tasks you may undertake in Excel. Excel includes three functions in the Math & Trig category that you can use for rounding off the numbers in a worksheet:

ROUND(number,num_digits) to round the value specified by the number argument to the number of digits specified by the num_digits argument
ROUNDUP(number,num_digits) to round up the value away from 0 (zero) that is specified by the number argument to the number of digits specified by the num_digits argument
ROUNDDOWN(number,num_digits) to round down the value toward 0 (zero) that is specified by the number argument to the number of digits specified by the num_digits argument

Note

Q:	What's the difference between using one of Excel's rounding functions to round off values to a particular decimal place ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.

Read now

Unlock full access

More than 5,000 organizations count on O’Reilly

O’Reilly covers everything we've got, with content to help us build a world-class technology community, upgrade the capabilities and competencies of our teams, and improve overall team performance as well as their engagement.

Julian F.

Head of Cybersecurity

I wanted to learn C and C++, but it didn't click for me until I picked up an O'Reilly book. When I went on the O’Reilly platform, I was astonished to find all the books there, plus live events and sandboxes so you could play around with the technology.

Addison B.

Field Engineer

I’ve been on the O’Reilly platform for more than eight years. I use a couple of learning platforms, but I'm on O'Reilly more than anybody else. When you're there, you start learning. I'm never disappointed.

Amir M.

Data Platform Tech Lead

I'm always learning. So when I got on to O'Reilly, I was like a kid in a candy store. There are playlists. There are answers. There's on-demand training. It's worth its weight in gold, in terms of what it allows me to do.

Mark W.

Embedded Software Engineer

Publisher Resources

ISBN: 9780470489604Purchase book

Cloud Computing

Data Engineering

Data Science

AI & ML

Programming Languages

Software Architecture

IT/Ops

Security

Design