Data Preparation

Question 1

order of operations with data

Answer 1

1. gather raw data
2. sort and filter
3. format data
4. remove duplicates
5. summarize in better ways

Question 2

how to remove duplicates

Answer 2

data- remove duplicates

Question 3

fill a whole new column with just the first info before the column if the content looks like this: mirutzi suzuki, aos

Answer 3

write out content for 1-3 cells then, data - flash fill

Question 4

how to create a column called Car ID?

Answer 4

write one under new column, car id, then highlight all the cells, then home- fill- series - series in: columns, type: linear, etc

Question 5

import data

Answer 5

data - get data - From Text/CSV

Question 6

choose table style that’s all white

Answer 6

click any cell - table design - table styles - etc

Question 7

how do you highlight the duplicate values in the Product Id column in light red with dark red font.

Answer 7

highlight column - home - styles - cond. formatting - highlight cells rules - duplicate values

Question 8

make a note of how many duplicates have been removed

Answer 8

count (bottom-right of excel window, or use =COUNTA(range) in an empty cell

Question 9

tips to data prepare

Answer 9

create a lookup sheet that you can use for data prep

Question 10

You have a column called Order Date that shows the date the order was placed with DataCo from 2015 to 2018. You might eventually want to add more date information to help with your analysis. A lookup sheet would be useful for this. how do you create a data lookup sheet?

Answer 10

create new sheet called Dates

Question 11

Using the advanced Fill Series feature, how do I populate the Date column to show all dates from “1/1/2015” to “12/31/2018”, ensuring that every calendar day is included in the range.

Answer 11

click cell with 1/1/2015, then home - editing group - fill - series - stop value field 12/31/2018