Data Cleaning

Question 1

autofit

Answer 1

To autofit in Excel, you can use the AutoFit feature to automatically adjust the width of columns or the height of rows based on their contents:
Select the columns or rows you want to resize
Go to the Home tab
Click the Format button
Select AutoFit Column Width or AutoFit Row Height from the drop-down menu

You can also use keyboard shortcuts to autofit:
Autofit columns: Press Alt + H, then O, then I
Autofit rows: Press Alt + H, then O, then A

Question 2

identify duplicates with conditional formatting

Answer 2

To highlight duplicate values in Excel using conditional formatting, you can do the following:
Select the cells you want to check
Go to the Home tab
Click Conditional Formatting
Click Highlight Cells Rules
Select Duplicate Values

Question 3

removing duplicates

Answer 3

select data tab
remove duplicates
choose the columns you want to delete

Question 4

trimming extra spaces using TRIM function

Answer 4

Removes all spaces from text except for single spaces between words. Use TRIM on text that you have received from another application that may have irregular spacing

The TRIM function syntax has the following arguments:

Text Required. The text from which you want spaces removed.

can add within a PROPER function

Question 5

eliminating blank cells

Answer 5

select range
home tab
find and select
select blanks

to fill those blank cells, enter something into the first blank cell and then hit control+enter

Question 6

spell check

Answer 6

select qualitative columns
review tab
spelling

add any words to your dictionary that you don’t want flagged as a spelling issue

Question 7

data validation (dropdown lists)

Answer 7

data tab
data validation
allow a list of items
you can type these manually to add items to your list

Question 8

format as a table

Answer 8

control + T
you can change both format and style option to add specific features to your table

Question 9

iferror

Answer 9

IFERROR(value, value_if_error)

The IFERROR function syntax has the following arguments:

value Required. The argument that is checked for an error.

value_if_error Required. The value to return if the formula evaluates to an error. The following error types are evaluated: #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL!.

Question 10

number formats

Answer 10

home tab
use thousand separator instead of the comma icon on the toolbar

Question 11

find and replace

Answer 11

command + H

home tab
find and replace

Question 12

remove gridlines

Answer 12

select page layout tab
uncheck “gridlines” option

Question 13

LOWER function

Answer 13

LOWER(text)

The LOWER function syntax has the following arguments:

Text Required. The text you want to convert to lowercase. LOWER does not change characters in text that are not letters.

Question 14

PROPER function

Answer 14

PROPER(text)

The PROPER function syntax has the following arguments:

Text Required. Text enclosed in quotation marks, a formula that returns text, or a reference to a cell containing the text you want to partially capitalize.

can add within a TRIM function

Question 15

paste as value

Answer 15

do this when you make a new column using a formula to make sure that when you delete the source column that it doesn’t become an error