Data Cleaning Spreadsheets

Question 1

Conditional formatting

Answer 1

A spreadsheet tool that changes how cells appear when values mee specific conditions.

Question 2

Text String

Answer 2

A group pf characters within a cell, commonly composed of letters, numbers or both.

Question 3

Sub-string

Answer 3

A sub-string is a smaller subset of a text string.

Question 4

Syntax

Answer 4

Predetermined. Structure that includes all required information and its proper placement.

Question 5

CONCATENATE

Answer 5

A function that joins multiple text strings into a single string. The syntax is =CONCATENATE(item 1, item 2)

Question 6

LEN

Answer 6

A function that tells you the length of a text string by counting the number of characters it contains.
Syntax: =LEN(range)

Question 7

COUNTIF

Answer 7

A function that returns the number of cells that match a specified value.
Syntax: =COUNTIF(range,”value”)

Question 8

LEFT

Answer 8

A function that gives you a set number of characters from the left side of a text string.
Syntax: =LEFT(range,number of characters)

Question 9

RIGHT

Answer 9

A function that gives you a set number of characters from the right side of a text string.
Syntax: =RIGHT(range,number of characters)

Question 10

MID

Answer 10

Function that gives you a segment from the middle of a text string.
Syntax: =MID(range,reference starting point, number of middle characters)

Question 11

TRIM

Answer 11

A function that removes bleeding, trailing, and repeated spaces in data.
Syntax: =TRIM(range)

Question 12

VLOOKUP (Vertical lookup)

Answer 12

A function that searches for a certain value in a column to return aa corresponding pieces of information.
Syntax: =VLOOKUP(data to look up, ‘where to look’!Range, column,false)

Question 13

Pivot Table

Answer 13

Data summarisation tool that is used in data processing.

Question 14

Data Mapping

Answer 14

The process of matching fields from one data source to another.

Question 15

Schema

Answer 15

A way of describing how something is organised.

Question 16

Primary

Answer 16

References a column in which each value is unique.

Question 17

Foreign Key

Answer 17

A field within a table that is a primary key in another table.