Miscellaneous Excel

1. Miscellaneous Excel • Agenda: 4/24/13 • External Data • Discuss data manipulation tools and functions • Discuss data import and linking in Excel • Sorting Data • Date and Time Functions • Grouping Worksheets

2. Sources of External Data • Historical data, long-term trends (text files) • Current trend information (databases) • Up-to-the-minute data (the Internet)

3. Introductions to Data Sources • Data must often be imported from other data sources • ASCII delimited files • Other file types • Access databases • Excel can import data from all of these data sources • Data must often be “massaged” to get it into a usable form • Sometimes, the solutions are not obvious

4. Importing Data from Text Files • Text files • Simple, widely used format for storing raw, unformatted data (text and numbers) • Useful for sharing data across software programs and computer systems • Ways to structure file contents • Use a delimiter • Columns of data are separated by a symbol • Use fixed-width text file • Each column starts at the same location

5. Understanding Text File Formats Delimited text Fixed-width text

6. Converting Text to Columns • Comma separated data appearing in one column can be divided into multiple columns • In 2010, click the Data tab, Text To Columns • The Wizard guides you through the process

7. Converting Text to Columns (1)

8. Converting Text to Columns (2)

9. Converting Text to Columns (3)

10. Converting Text to Columns (Advanced)

11. Sorting Data • Excel can sort columnar data using the SORT dialog box • Columnar data may or may not contain a header row • It’s possible to sort by multiple columns • It’s possible to select the column(s) on which to sort

12. Sorting Data • Sort data in ascending or descending order • Use the Sort A to Z button or the Sort Z to A button to sort data quickly with one sort field

13. Sorting Data • Use sort dialog box to sort multiple columns • Primary and secondary sort fields • Up to 64 sort fields possible

14. Sorting Multiple Columns Using the Sort Dialog Box

15. Sorting Using a Custom List • A custom list indicates sequence to order data • Four predefined custom sort lists • Two days-of-the-week custom lists • Two months-of-the-year custom lists • Can also create a custom list to sort records in a sequence you define

16. String Manipulation Functions (Introduction) • CONCATENATE – string together multiple cells into a single cell • RIGHT and LEFT get a sub string from a string • TRIM removes trailing spaces • SEARCH and FIND search for a sub string within a string

17. CONCATENATE • Concatenate takes a list of single cell arguments • Maximum number of arguments is 30 • =CONCATENATE(“A”,”B”,”C”) IS “ABC”

18. LEFT And RIGHT • LEFT starts at the first character position and returns n characters • =LEFT("This is a line of text.",4) returns “This” • RIGHT starts at the last character position and returns the rightmost n characters • =RIGHT("This is a line of text.",4) returns “ext.”

19. TRIM • TRIM removes leading and trailing spaces • The single spaces between words are preserved • Extra spaces between words are removed • TRIM takes one argument – the value to trim

20. SEARCH And FIND • Both functions work the same way and accept the same arguments • FIND function is case sensitive • SEARCH function is not case sensitive • Arguments • First argument contains the search text • Second argument contains the text to search • The third argument contains the starting character position where searching begins • Starting character position is 1-based • Use the If function to find whether the pattern was found

21. SEARCH And FIND (Example)

22. REPLACE • Replaces one string with another string based on character positions in the original string • First argument contains the original text string • Second argument contains the 1-based position of the first character to replace • Third argument contains the number of characters to replace • Final argument contains the replacement text

23. REPLACE (Example)

24. SUBSTITUTE • Replaces old text with new text • First argument contains the text string • Second argument contains the text to replace • Third argument contains the replacement text • Final optional argument contains the instance counter • How many times to replace one string with another string • The default value is 1

25. SUBSTITUTE (Example)

26. Other String Functions • EXACT takes two arguments • Returns TRUE if the two arguments contain identical values • EXACT is case sensitive • LEN takes one argument • Returns the length of a string • Spaces are counted along with special characters • CLEAN removes non-printable characters from a text string • Tabs for example

27. LOWER, UPPER, PROPER • All functions accept one argument • LOWER converts characters to lower case • UPPER converts characters to upper case • PROPER converts characters to proper case • The first character of each work is capitalized

28. Working With Date and Time Values • Date values are stored in a special internal format • Numbers to the left of the decimal point are days • Day 1 is January 1, 1900 • Days prior to that date are negative • Numbers to the right of the decimal point are fractional parts of days

29. Date Functions (Introduction) • TODAY() returns the current date • It’s possible to perform arithmetic operations on dates • Sometimes the results make no sense and have no real use though

30. Other Date Functions • DATE(year,month,day)returns a Date • HOUR, MINUTE, SECOND return the hour, minute, second in the date • YEAR, MONTH, DAYreturn the year, month, and day parts

31. Grouping Worksheets • Using multiple worksheets makes it easier to group and summarize data. • Worksheet groups save time and improve consistency among worksheets • An action performed once affects multiple worksheets • Can create formulas that function across multiple worksheets • A worksheet group can contain adjacent or nonadjacent worksheets • Any formatting changes made to the active group are applied to all sheets in the group. • When worksheets are ungrouped, each one functions independently again.

32. Grouping Worksheets

33. Example of Multiple Worksheets • Tutorial #6: Ticket Park Sales example • Four worksheets representing four quarters of ticket sales • Each worksheet with a unique name • Selecting and unselecting multiple worksheets • A “worksheet group” is the currently selected group of worksheets • Any actions done to one worksheet will happen to all worksheets in a selected group

34. Working with Multiple Worksheets • Copying worksheets • Use an existing worksheet as a starting point for creating another one • Duplicates all values, formulas, and formats into new worksheet, leaving original worksheet intact • Edit, reformat, and enter new content as needed

35. Cell References • Absolute, Relative, Mixed • Named • Multi-sheet: conceptualize as “3-D” • Rows • Columns • Sheet • A 3-D reference includes the name of the sheet. Example: • Quarter1!B6 • 'Quarter 1'!B6