data set management

Question 1

How do you highlight missing values or blank cells

Answer 1

Conditional formatting>New Rule>Format only cells that contain> change to blank

Question 2

What does Count Blank do, and How is it helpful?

Answer 2

count the number of empty cells in a range of cells.

it can tell you how many total cells are blank compared to how many cells should be filled

Question 3

How do you Highlight duplicate records

Answer 3

conditional formatting>Highlight cell rules>Duplicate values

Question 4

How do you remove duplicates using the remove duplicates button in data?

Answer 4

Data>remove duplicates>(check: data has headers)(each column you want to delete from)

all duplicates showing up the 2nd time will be removed

Question 5

how do you remove duplicates using filter button in data?

Answer 5

select all the data you want to filter>filter(advanced)>Unique records only

Question 6

How do you delete a comma from a list of names?

ex: Danie ,Revie

Answer 6

To delete comma:

1. find & select or ctrl + h
2. replace all
3. find what: [space] comma
4. replace with: [space]

Question 7

how do you split names in excel using text to columns?

Answer 7

1. text to columns
2. deleminate
3. spaces
4. chose a destination
5. finish

Question 8

how do you split names using find & replace?

the first name only

Answer 8

for first name:
replacing the last name with nothing:
ctrl + h = find and replace
1. (space *) in the find what category
- * means any character
2. don’t put anything in the replace with category
3. replace all will then delete all the last names

Question 9

how do you split names using find & replace?

the last name only

Answer 9

replacing the first name with nothing:
ctrl + h = find and replace
1. (*space) in the find what category
- * means any character
2. don’t put anything in the replace with category
3. replace all will then delete all the last names

Question 10

how do you remove the middle name using find and replace

Answer 10

1. space*space
2. space (still want a space between the names)
3. replace all

Question 11

how do you split names using flash fill

Answer 11

1. using flash fill, manually write in the first, first name
2. start on the second name, but notice it will guess the next names
3. press tab

Question 12

how do you rearrange names using flash fill?

Answer 12

1. Same thing applies here, type in the first cell what format you want. In this particular cell it’s name, name
2. doing the same thing in the second cell will activate flash fill

Question 13

how do you use a formula to split just a first and last name?

just the first name

Answer 13

1. find the character number of the space character
2. we’re looking to extract everything to the left of it for the first name
3. find space character =search(“ “, A2)
4. find the number of characters you need to extract for the first name, add a -1 to the equation =search(“ “, A2)-1
5. pull the characters needed from the left of the space so add the function LEFT(A2) to the equation =LEFT(A2, SEARCH(“ “, A2)-1)

Question 14

how do you use a formula to split just a first and last name?

just the last name

Answer 14

1. Start the same way from the space character, but you need to know the character after it. So it’s the total number of characters - the space numbers character number. Len(A2)-Search(“ “, A2)
2. To get the Last name you are telling excel that what you want is to the right of the space character, so you are adding Right(A2, to the equation.
   =Right(A2, Len(A2)-Search(“ “, A2))

Question 15

how do you use a formula to split just a first middle and last name?

just the first name

Answer 15

1. find the character number of the space character
2. we’re looking to extract everything to the left of it for the first name
3. find space character =search(“ “, A2)
4. find the number of characters you need to extract for the first name, add a -1 to the equation =search(“ “, A2)-1
5. pull the characters needed from the left of the space so add the function LEFT(A2) to the equation =LEFT(A2, SEARCH(“ “, A2)-1)

Question 16

how do you use a formula to split just a first middle and last name?

just the last name

Answer 16

This is a little bit more tricky than the w/o a middle name
1. because there are now two spaces, you want to make the second one easier to find. To do this we will substitute it for another non-reoccurring character. =substitute(A16, “ “, “@”, 2)
2. Once you do that it becomes very much like the previous Len(A16)-Search(“@”, =substitute(A16, “ “, “@”, 2))
3. Finally, you are adding =Right(A16, to the equation and it becomes
=RIGHT(A16, Len(A16)-Search(“@”, =substitute(A16, “ “, “@”, 2)))

Question 17

how do you use a formula to split just a first middle and last name?

just the middle name

Answer 17

1. We are looking to find the character number for the space character before the middle initial and after the middle initial
2. the first one is easy enough using =Search(“ “, A16)-1
3. then you are using the same method to find the 2nd space character search(“@”, substitute(A16, “ “, “@”, 2))
4. then you are putting it all together using the mid function
   - text =mid(A16,
   - starting character number (middle initial) (search(“ “, A16)+1
   - finding the character number for what you need search(“@”, Substitute(A16, “ “, “@”, 2))-SEarch(“ “, A16)-1)
   putting it all together:
   =MID(A16, SEARCH(“ “, A16)+1, SEARCH(“@”, SUBSTITUTE(A16, “ “, “@”, 2))-SEARCH(“ “, A16)-1)