xxix
Contents
Removing extra spaces............................................................................... 596
Removing strange characters ..................................................................... 597
Converting values ...................................................................................... 597
Classifying values...................................................................................... 598
Joining columns ........................................................................................ 599
Rearranging columns ................................................................................. 600
Randomizing the rows ............................................................................... 601
Extracting a filename from a URL ............................................................... 601
Matching text in a list ............................................................................... 601
Changing vertical data to horizontal data ................................................... 603
Filling gaps in an imported report .............................................................. 606
Checking spelling ...................................................................................... 607
Replacing or removing text in cells ............................................................. 607
Adding text to cells ................................................................................... 608
Fixing trailing minus signs ........................................................................ 609
Following a data cleaning checklist ............................................................ 609
Exporting Data .................................................................................................. 610
Exporting to a text file .............................................................................. 610
CSV files ........................................................................................... 610
TXT files ........................................................................................... 610
PRN files .......................................................................................... 611
Exporting to other file formats ................................................................... 611Chapter 26: Using Data Validation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 613
About Data Validation ........................................................................................ 613
Specifying Validation Criteria ............................................................................. 614
Types of Validation Criteria You Can Apply ........................................................... 615
Creating a Drop-Down List .................................................................................. 617
Using Formulas for Data Validation Rules ............................................................. 619
Understanding Cell References ............................................................................ 619
Data Validation Formula Examples ....................................................................... 621
Accepting text only ................................................................................... 621
Accepting a larger value than the previous cell ........................................... 621
Accepting nonduplicate entries only ........................................................... 622
Accepting text that begins with a specific character .................................... 623
Accepting dates by the day of the week ...................................................... 623
Accepting only values that don’t exceed a total ........................................... 623
Creating a dependent list ........................................................................... 623
Using Data Validation without Restricting Entry .................................................. 625
Showing an input message ......................................................................... 625
Making suggested entries ........................................................................... 626Chapter 27: Creating and Using Worksheet Outlines. . . . . . . . . . . . . . . . . . . . . . . . . . . 627
Introducing Worksheet Outlines .......................................................................... 627
Creating an Outline ............................................................................................ 631