oit_content_130167 - Office of Information Technology Excel...

Info iconThis preview shows pages 1–7. Sign up to view the full content.

View Full Document Right Arrow Icon

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
This is the end of the preview. Sign up to access the rest of the document.

Unformatted text preview: Office of Information Technology Excel 2010 Analyzing Data 12.12.2011 UNIVERSITY T ECHNOL OG Y TRAINING CE NTE R Excel 2010 Analyzing Data © 2011 Regents of the University of Minnesota University Technology Training Center All Rights Reserved http://uttc.umn.edu The University of Minnesota is committed to the policy that all persons shall have equal access to its programs, facilities, and employment without regard to race, color, creed, religion, national origin, sex, age, marital status, disability, public assistance status, veteran status, or sexual orientation. This publication/material can be made available in alternative formats for people with disabilities. Direct requests to: University Technology Training Center 190 Shepherd Labs 100 Union Street Southeast Minneapolis, MN 55455 612.625.1300 [email protected] UNIVERSITY TECHNOLOGY TRAINING CENTER i Table of Contents LESSON 1 1 FORMULAS 1 Naming Cells 2 Using Names in Formulas 3 Sumproduct 3 VLOOKUP 6 SUMIF and COUNTIF 10 LESSON 2 13 TEXT TO COLUMNS AND CONCATENATION 13 Using the Text to Columns Wizard 13 Text to Columns 14 Combine Text Using CONCATENATE 17 Combine Text Using the & Operator 17 LESSON 3 19 TABLES 19 Converting a Range of Cells to a Table 19 Table Elements 21 Automatically Extend a Table 21 Insert Table Rows and Columns 22 Delete Table Rows and Table Columns 23 Display and Calculate Table Totals 24 Removing Table Formatting 25 LESSON 4 27 SORTING DATA 27 Simple Sorting 27 Perform a simple sort 28 Advanced Sorting 28 Custom Lists and Sorting 31 Add a Custom List 33 Use the Custom List in a Custom Sort 34 LESSON 5 37 FILTERING DATA 37 Simple Filters 37 How to Filter 37 Clear the Filters 40 LESSON 6 43 SUBTOTALS 43 Using Subtotals 43 Removing Subtotals 48 LESSON 7 49 PIVOT TABLES 49 PIVOT TABLES EXCEL 2007 49 Create a Pivot Table 49 Edit a Pivot Table 53 ii UNIVERSITY TECHNOLOGY TRAINING CENTER Create a Report Filter 55 Create a PivotTable with multiple levels of detail 57 Create a PivotTable that calculates Average values 58 Change the Number Format of a PivotTable Field 61 Remove a PivotTable 63 PIVOT TABLES 2010 65 Create a Pivot Table 65 Edit a Pivot Table 68 Create a Report Filter 69 Create a PivotTable with multiple levels of detail 71 Create a PivotTable that calculates Average values 72 Change the Number Format of a PivotTable Field 75 Slicers 77 Format Slicers 79 Remove slicers 79 Remove a PivotTable 80 LESSON 8 81 ADVANCED FILTERS 81 Create Criteria for Filtering 81 Change Criteria for Filtering and Reapply Filter 84 Clear a Filter 85 Use Logical Comparisons to Filter the Data 85 Use Multiple Criteria 87 Use Wild Cards to Find Data 89 LESSON 9 91 DATABASE FUNCTIONS 91 Use a DSUM Function 92 Use Wild Cards for Database Criteria 94 LESSON 10 95 GROUPING PIVOTTABLE ITEMS 95 Group items 96 Group elements by date 98 Ungroup Elements 100 Use Multiple Value Fields 101 Add multiple calculations in the Values field 101 LESSON 11 105 CALCULATED FIELDS AND ITEMS 105 Create a Calculated Field...
View Full Document

This note was uploaded on 02/04/2012 for the course INFORMATIO Download C taught by Professor Office during the Spring '11 term at Minnesota.

Page1 / 137

oit_content_130167 - Office of Information Technology Excel...

This preview shows document pages 1 - 7. Sign up to view the full document.

View Full Document Right Arrow Icon
Ask a homework question - tutors are online