menuPreparing Data in Excel

Chapter 3: Preparing Data in Excel

Learning Objectives

  1. Explain the principles of tidy data and recognize data that breaks them.
  2. Find data problems in Excel with filter drop-downs, COUNTBLANK, UNIQUE, COUNTIF and checks for numbers stored as text, and use R for Excel's column summary and an AI assistant to help find them.
  3. Standardize inconsistent text with TRIM, UPPER, PROPER, IF and XLOOKUP, and convert numbers stored as text with SUBSTITUTE and VALUE.
  4. Handle missing values correctly, and explain why a blank must not be replaced with 0.
  5. Find and remove duplicate records, turn the cleaned data into an Excel Table, and verify it against a trusted source.
  6. Describe how Power Query and data validation make cleaning repeatable and prevent future problems.

Every analysis in this book starts with clean data, and in a real job that's rarely how data arrives. It comes from survey tools, point-of-sale systems and store managers' spreadsheets, each with its own habits. Someone types "yes" instead of "Yes." Someone pastes a price with its dollar sign. A respondent skips a question. A file gets exported twice and the two copies are combined. None of these is a big mistake on its own, but each one can quietly change the answer to a business question.

Here's the situation at Ridgeline. Before you joined, the survey responses were collected in a spreadsheet that several people edited. That spreadsheet is the SurveyRaw sheet in the Ridgeline workbook. Dana Whitfield, the co-owner focused on growth, wants to start using the survey in decisions, and she asks you a fair question:

"Can we trust this survey data? If not, fix it, and tell me how you know it's fixed."

In this chapter you'll answer her in three parts: find the problems, fix them in Excel, and verify the result. The workbook also contains Survey, a version that was cleaned for you, which you used in Chapter 2. Working with AI and R for Excel. It gives you something rare in real life: an answer key. When you finish, you'll check your cleaned Table against it.

Why spend a chapter on cleaning? Analysts often say that preparing data takes most of the time in a real project. It's also where many wrong conclusions start. A test can be computed perfectly and still be wrong because three customers were counted twice or a "no" was spelled four ways. No statistical method can fix data that's wrong.

3.1 What Clean Data Looks Like

Before fixing anything, you need a picture of the goal. Statisticians call well-organized data tidy.

Tidy data — Data arranged so that each variable is one column, each observation is one row, and each cell holds a single value. Each kind of observation (customers, stores, days) gets its own table.

You've already met the first two rules in Chapter 1. Statistics, Data, and AI in Business: observations are rows, and variables are columns. Tidy data adds a few practical rules that matter when a computer, not a person, reads the data:

Here's a common untidy layout that breaks the first rule: total transactions by month, as a store manager might lay them out in a report.

store Jan Feb Mar
Downtown 14,769 12,939 14,096
Riverside 15,893 14,759 15,317

It's easy for people to read. But "month" is a variable, and here it's spread across three column headers. In tidy form the same data has one row per store per month, with columns store, month and transactions. That's exactly how Ridgeline's DailySales Table is organized: one row per store per day. The tidy shape is longer and less pretty, but every tool in this book, including PivotTables and R, works on it directly.

The good news is that SurveyRaw already has a tidy shape: one header row, one row per response, one column per variable. Its problems are in the contents of the cells. That's the most common situation you'll face, and it's what the rest of this chapter fixes.

3.2 Finding the Problems

Go to the SurveyRaw sheet. It has a bold header row in row 1 and data in rows 2 through 604. That's 603 rows of data, three more than the 600 responses the survey collected, which is your first clue. The columns are:

Column A B C D E F G H I J K
Name response_id visit_date store_id age loyalty_member visits_per_week order_type wait_minutes satisfaction spend would_recommend

Don't change anything on this sheet. You'll make a working copy later. For now, just look. You'll use four tools: filter drop-downs, COUNTBLANK, UNIQUE and a check for numbers stored as text.

3.2.1 Filter drop-downs

Click any cell in the data, then choose Data > Filter. A drop-down arrow appears on each header. Click the arrow on loyalty_member. The list shows every different value in the column, with a checkbox next to each.

The SurveyRaw sheet with filters turned on and the filter drop-down for loyalty_member open. The list of values shows the different spellings, including Y, N, Yes, No and versions with an extra space, plus the Select All checkbox. Circle the list of values.

You'd expect two entries, Yes and No. Instead you'll see several, including Y and N and what look like two different No entries. The difference between those two is invisible: one of them has a space after the word. Now check the drop-downs on order_type, age and wait_minutes. The age and wait_minutes lists end with (Blanks), which means some cells are empty.

The filter list is a quick first look, but it hides some things. Excel ignores capital letters when it compares text, so "yes," "Yes" and "YES" appear as a single entry. When you're done, choose Data > Filter again to turn the filters off.

3.2.2 Counting blanks

A blank cell usually means a respondent skipped a question or someone forgot to enter a value.

Missing value (NA) — A value that should exist but wasn't recorded, such as an age a customer didn't give. In Excel a missing value is an empty cell. In R it's written NA, for "not available." R for Excel turns empty Excel cells into NA.

To count them, click an empty cell to the right of the data, say M2, and type:

=COUNTBLANK(D2:D604)

That's the age column, and the result is 21. For wait_minutes, =COUNTBLANK(H2:H604) gives 17. Try the other columns too (A2:A604, B2:B604 and so on). The rest have no blanks.

3.2.3 Listing distinct values

UNIQUE lists each different value in a range once. In cell M4, type:

=UNIQUE(E2:E604)

The result spills down the column: Excel fills as many cells as it needs, and outlines the result with a thin blue border. You'll see six values: Yes, No, "No " (with a trailing space), Y, " Yes" (with a leading space) and N. Do the same for order_type with =UNIQUE(G2:G604).

To count each spelling, put COUNTIF next to the list. If UNIQUE spilled into M4, then in N4 type:

=COUNTIF($E$2:$E$604,M4)

and fill it down to match the list. The dollar signs keep the range fixed as you fill. For "Yes" you get 241. That's not 232, the number of cells that read exactly "Yes," because COUNTIF also ignores case. It counts "Yes," "yes" and "YES" together, but it doesn't count " Yes" with a space or "Y."

Excel ignores case; R doesn't. UNIQUE, COUNTIF, the filter list and Remove Duplicates all treat "yes" and "Yes" as the same value. R treats them as two different categories. Data that looks fine in Excel can fall apart in R, with "Yes" and "yes" counted as separate groups. Clean the spelling completely, not just enough to look right in Excel.

In fact loyalty_member contains nine different strings once you count case and spaces exactly:

Value Rows
No 334
Yes 232
"No " (trailing space) 10
yes 6
no 6
" Yes" (leading space) 6
Y 3
N 3
YES 3

order_type has six: Counter (334), Mobile (130), Drive-thru (119), counter (13), mobile (6) and drive-thru (1).

3.2.4 Numbers stored as text

Look at the spend column. Most values sit on the right side of their cells, which is how Excel shows numbers. A few are on the left and have a dollar sign typed in, such as $6.31. Those cells contain text that looks like a number.

Number stored as text — A value that looks like a number but that Excel stores as text, for example a price typed with its dollar sign ("$8.27") or a number pasted from another system. Excel's math functions skip it, so sums and averages come out wrong without any error message. Text is usually left-aligned in a cell, and numbers are right-aligned.

Two formulas show how many there are:

(ISTEXT gives TRUE or FALSE for each cell. The -- turns those into 1s and 0s, and SUMPRODUCT adds them up.) Together they make 603. If you computed =AVERAGE(J2:J604) now, Excel would quietly average only the 565 numbers and leave the other 38 customers out, without any warning.

3.2.5 Letting R for Excel show you

R for Excel's column summary is another way to find problems. In Chapter 2. Working with AI and R for Excel you added Tables, but the app can also read a plain range:

  1. On SurveyRaw, click cell A1 and press Ctrl+A to select the whole block of data.
  2. In R for Excel, under 1. Data, click Add selected data. The app names the data frame SurveyRaw, after the sheet, and checks headers because row 1 holds column names.
  3. Under 2. Ask your AI, click Show the prompt and read the "The data" section.

The R for Excel prompt preview (Show the prompt) for the SurveyRaw data frame, showing the heading "SurveyRaw: 603 rows × 11 columns" and the column table. Highlight three rows: response_id with "600 distinct values", age with "21 NA", and loyalty_member listing 7 values with counts: "No" (344), "Yes" (238), "yes" (6), "no" (6), "Y" (3), "N" (3), "YES" (3).

The summary points straight at several problems:

Seven isn't nine, though. The app trims spaces from the ends of text when it reads the data, so "No " and " Yes" are folded into "No" and "Yes" before counting. It also reads "$6.31" as the number 6.31, so spend shows up as a clean numeric column. That helps R, but it means the summary shows fewer problems than the sheet really has. Excel formulas, other software and your coworkers won't be so forgiving. The app's summary is a useful detector, not a substitute for cleaning the data in Excel.

You can also send this prompt to an AI and ask for a second opinion. Type a short data description in the question box, click Copy prompt and paste it into a new chat.

Needs: a reply that uses the column summary to spot duplicated response_ids (603 rows but 600 distinct), NA counts in age and wait_minutes, the inconsistent spellings of loyalty_member, and the lowercase order_type values, and suggests Excel checks such as COUNTIF for duplicate IDs, COUNTBLANK, UNIQUE or a PivotTable, TRIM/UPPER or a lookup to standardize, and Remove Duplicates. It probably won't mention the $ signs in spend or the stray spaces, because the app's summary hides them. The text after this block points out what the AI could and couldn't see.

Compare the AI's list with what you found yourself. It should catch the duplicates, the blanks and the case problems, because all three show up in the summary. It almost certainly won't mention the dollar signs or the extra spaces. That isn't the AI being careless. It can see only what's in the prompt, and the prompt doesn't show those problems. It's a good reminder of the limits of any AI that works from a description of your data: it can only reason about what you show it.

3.2.6 The problem list

Here's everything wrong with SurveyRaw, which is your to-do list for the rest of the chapter:

Problem Where How many
Duplicate rows whole rows; IDs R0058, R0212, R0487 3 extra rows
Inconsistent Yes/No text loyalty_member 9 spellings
Lowercase categories order_type 20 cells
Numbers stored as text ("$8.27") spend 38 cells
Blank cells age, wait_minutes 21 and 17

The other columns are fine: store_id has the 12 expected IDs, would_recommend has only "Yes" and "No," and the dates and numbers are all numbers.

3.3 Make a Working Copy

Never clean data in place. Keep the raw sheet exactly as you received it, so you can always go back, show someone what changed, or start over.

  1. Right-click the SurveyRaw sheet tab and choose Move or Copy….
  2. Under Before sheet, choose (move to end), check Create a copy and click OK. Excel adds a sheet called SurveyRaw (2).
  3. Double-click the new tab, type SurveyClean and press Enter.

Do all of the remaining work on SurveyClean. If you typed any formulas in column M or N of SurveyRaw while exploring, they came along with the copy. Select those columns on SurveyClean and delete them (Home > Delete > Delete Sheet Columns), so the sheet holds only the 11 data columns.

3.4 Removing Duplicate Records

Start with duplicates, because they affect every count you'll check afterward.

Duplicate record — An observation that appears in the data more than once, usually because a file was exported or pasted twice. Duplicates inflate counts and give the repeated observations extra weight in averages.

3.4.1 Flag them first

Before deleting anything, look at what you're about to delete. On SurveyClean, type id_count in L1. In L2, type:

=COUNTIF($A$2:$A$604,A2)

and double-click the fill handle to copy it down to row 604. Each cell now says how many times that row's response_id appears. Turn on Data > Filter, open the id_count drop-down, and uncheck everything except 2. Six rows remain, in three pairs: R0058, R0212 and R0487. Compare each pair across all 11 columns. They're identical, so these are true duplicates: the same response entered twice.

The SurveyClean sheet with a filter on the id_count column showing only rows where id_count is 2. Six rows are visible: two each for R0058, R0212 and R0487, with identical values across every column. The COUNTIF formula is visible in the formula bar.

This check matters. Two rows with the same ID but different answers aren't duplicates. They're a conflict, and someone needs to find out which one is right. Deleting one at random would hide the problem.

3.4.2 Remove them

  1. Turn the filter off (Data > Filter) and delete column L, the id_count helper.
  2. Click any cell in the data and choose Data > Remove Duplicates.
  3. Make sure My data has headers is checked and all 11 columns are checked, so that only rows matching in every column count as duplicates. Click OK.

Excel reports that it found and removed 3 duplicate values and that 600 unique values remain. That matches the 600 responses the survey collected.

Remove Duplicates keeps the first copy and deletes the rest, permanently. There's no list of what it removed. That's why you flag duplicates with COUNTIF first and look at them, and why you work on a copy.

3.5 Make It a Table

Now turn the data into an Excel Table, so that the fixes in the next sections can use structured references and R for Excel can read it by name.

  1. Click any cell in the data on SurveyClean and press Ctrl+T (or Insert > Table).
  2. Check that the range is $A$1:$K$601 and that My table has headers is checked. Click OK.
  3. On the Table Design tab, click in the Table Name box, type SurveyClean, and press Enter.

The sheet and the Table now share the name SurveyClean, just as the Survey Table lives on the Survey sheet. The blank cells in age and wait_minutes stay blank, which is what you want. You'll see why later in this chapter.

3.6 Standardizing Text

3.6.1 Excel's text tools

Four Excel functions do most text cleaning:

Function What it does Example Result
TRIM removes spaces at the start and end, and extra spaces between words =TRIM(" Yes") Yes
UPPER capital letters =UPPER("yes") YES
LOWER lowercase letters =LOWER("Counter") counter
PROPER a capital at the start of each word =PROPER("counter") Counter

These functions don't change the original cell. They return a cleaned copy, which you put in a new helper column. When you're sure the helper column is right, you copy its values over the original and delete the helper. You'll do that twice in this section.

3.6.2 Fixing loyalty_member

The nine spellings fall into two groups. After TRIM removes the stray spaces, every value starts with Y or y (Yes, yes, YES, Y) or with N or n (No, no, N). There are two good ways to turn them into exactly "Yes" or "No."

With IF. Test the first letter:

=IF(LEFT(UPPER(TRIM([@loyalty_member])),1)="Y","Yes","No")

Read it from the inside out. TRIM removes spaces, UPPER makes everything capitals, and LEFT(…,1) takes the first character. If that character is "Y," the result is "Yes"; otherwise it's "No." It's short and it works for this data, but notice what it assumes. Anything that doesn't start with Y becomes "No," including a blank, a typo like "Ues" or an answer like "Not sure." A formula that turns unexpected input into a confident answer hides problems instead of revealing them.

With XLOOKUP. List the spellings you expect and what each should become, and send anything else to a value that stands out:

=XLOOKUP(TRIM([@loyalty_member]),{"Yes","Y","No","N"},{"Yes","Yes","No","No"},"CHECK")

XLOOKUP looks for the trimmed value in the first list and returns the matching item from the second list. The curly braces type each list directly into the formula. Because XLOOKUP ignores case when it matches text, "yes," "YES" and "Yes" all match "Yes" and come back spelled the standard way. Anything not in the list comes back as "CHECK," the fourth argument, so you can find it and decide what to do. This is the safer formula, and it's the one to use.

Put it in a helper column:

  1. Click cell L1 (the first empty cell to the right of the header row), type loyalty_fix and press Enter. The Table grows to include it.
  2. In L2, type the XLOOKUP formula above and press Enter. Because it's a Table, Excel fills the formula down all 600 rows. That's a calculated column.
  3. Check it. =COUNTIFS(SurveyClean[loyalty_fix],"CHECK") should be 0, and =UNIQUE(SurveyClean[loyalty_fix]) should list only Yes and No.

For a longer list, use a lookup table. If a column had dozens of spellings, you'd put the mapping in a small two-column Table on its own sheet (say, raw and standard) and point XLOOKUP at its columns. Then you can see and edit the whole mapping in one place.

3.6.3 Getting help from an AI

You don't have to think up formulas like these yourself. Describing the problem to an AI and asking for Excel formulas works well, as long as you test what it gives you. Since you want Excel formulas, not R code, a short chat works better than the R for Excel prompt. Give the AI the exact spellings and counts you found, and say what Excel version you have.

Needs: a reply that offers at least one working formula with TRIM plus either IF/LEFT/UPPER or XLOOKUP (or SWITCH), using [@loyalty_member]; explains each in plain words; addresses what happens to unexpected values (ideally recommending a flag such as CHECK rather than defaulting to No); suggests a check with COUNTIFS or UNIQUE; and describes copying the helper column and using Paste Values. The text after this block compares the AI's formulas with the two in the chapter and reminds the reader to test them with the CHECK count.

Compare the AI's suggestions with the two formulas above. It may offer SWITCH, nested IFs or a variation on XLOOKUP. Any of them can be right. Test each one the same way: count the values that aren't exactly Yes or No, and make sure the answer is 0. If the AI's formula sends unexpected values silently to "No," you now know why that's risky, and you can ask it for a version that flags them instead.

3.6.4 Replacing the original column

The helper column is correct, but the analysis needs the clean values in loyalty_member itself. Copy them over as values:

  1. Select the data cells of loyalty_fix (click the top of the column inside the Table, just below the header, to select the whole column of data) and press Ctrl+C.
  2. Click the first data cell of loyalty_member (E2).
  3. Choose Home > Paste > Paste Values (the clipboard icon with "123").
  4. Right-click the loyalty_fix header and choose Delete > Table Columns.

Paste Values matters. A plain paste would copy the formula, which would then refer to the column it was pasted into. Paste Values copies only the results.

Excel's Home tab with the Paste drop-down open and the Paste Values button (clipboard with 123) highlighted. Behind it, the SurveyClean Table shows the loyalty_member column selected and the loyalty_fix helper column with copied cells outlined by a moving border.

3.6.5 Fixing order_type, and a PROPER surprise

order_type has lowercase versions of all three categories. PROPER looks like the obvious fix, since it capitalizes the first letter of each word. Try =PROPER("drive-thru") in an empty cell. The result is "Drive-Thru," with a capital T, because PROPER capitalizes every letter that follows a non-letter, including a hyphen. Applied to the whole column, it would turn all 119 correct "Drive-thru" values into "Drive-Thru."

In Excel, which ignores case, that wouldn't seem to matter. In R it would. "Drive-Thru" wouldn't match "Drive-thru" in the Survey Table or in any earlier analysis, and a report could show both spellings as separate groups. The same XLOOKUP approach avoids the problem, because it returns exactly the spelling you list:

=XLOOKUP(TRIM([@order_type]),{"Counter","Mobile","Drive-thru"},{"Counter","Mobile","Drive-thru"},"CHECK")

The two lists look identical, and that's the point: the match ignores case, so "counter" finds "Counter," and the result is the standard spelling from the second list. Put it in a helper column named order_fix, check that =COUNTIFS(SurveyClean[order_fix],"CHECK") is 0, copy it over order_type with Paste Values, and delete the helper.

3.7 Fixing Numbers Stored as Text

The 38 spend values like "$6.31" need to become numbers. Two steps do it: remove the dollar sign, then convert the remaining text to a number.

=VALUE(SUBSTITUTE([@spend],"$",""))

SUBSTITUTE([@spend],"$","") replaces every "$" with nothing, giving the text "6.31". VALUE converts text that looks like a number into a real number. For the cells that were already numbers, SUBSTITUTE turns them into text and VALUE turns them back, so every row ends up as a number. If a value can't be converted, for example "n/a," VALUE returns #VALUE!, which is easy to spot.

NUMBERVALUE is an alternative to VALUE: =NUMBERVALUE(SUBSTITUTE([@spend],"$","")). It also lets you say which characters are the decimal and thousands separators, which helps with data from countries that write 8,27 for 8.27.

Put the formula in a helper column named spend_fix. Check it with =COUNT(SurveyClean[spend_fix]), which should be 600: every row is now a number. Then copy it over spend with Paste Values, delete the helper, and format the column as currency (select it and choose Home > Number Format > Currency). Every value should now sit on the right side of its cell.

Why not just retype the 38 cells? You could, with this data. But typing by hand doesn't scale to the next file with 3,800 bad cells, it leaves no record of what you did, and each keystroke is a chance for a new mistake. A formula applies one rule to every row and shows exactly what the rule was.

3.8 Blanks and Missing Values

After removing duplicates, SurveyClean has 21 blank ages and 16 blank wait times. (One of the 17 blank waits you counted on SurveyRaw was in a duplicate row.) Check with =COUNTBLANK(SurveyClean[age]) and =COUNTBLANK(SurveyClean[wait_minutes]).

The right thing to do with these blanks is, usually, nothing. Leave them blank. Excel's statistical functions skip empty cells: =AVERAGE(SurveyClean[age]) averages the 579 ages that were recorded. R for Excel turns each blank into NA, and R functions can be told to skip NA values, as you'll see below.

3.8.1 Why you must not type 0

It's tempting to fill blanks with 0 so the column "looks complete." Here's what that does. With the 21 blanks left out, the average age in SurveyClean is 36.91. With 0 typed into those cells, it drops to 35.62, because the formula now includes 21 customers who are supposedly zero years old. A zero isn't "no answer." It's a specific value, and here an impossible one.

The Survey Table gives you a way to see the effect, because in this book's data it has every customer's age, including the 21 who left the question blank in SurveyRaw. Its average age is 36.83.

Average age Value
All 600 customers (Survey, complete) 36.83
Blanks left out (SurveyClean, 579 ages) 36.91
Blanks replaced with 0 35.62

Leaving the blanks out landed within a tenth of a year of the true answer. Typing zeros missed by more than a year, and it would make any later analysis of age wrong too. The same goes for wait_minutes: a wait of 0 minutes is a real value that would make Ridgeline look faster than it is.

Leaving blanks out is safe only if they're missing for no particular reason. If older customers were more likely to skip the age question, the 579 who answered would be younger than customers in general, and their average would be too low no matter what you did with the blanks. When a lot of values are missing, ask why before you trust any summary of what's left.

Two other habits to avoid: don't type "NA" or "n/a" into an empty cell, and don't use a dash. Each one turns a number column into a mix of numbers and text. Excel will skip them, but R for Excel will see text in the column and treat the whole column as text, so you couldn't average it at all. An empty cell is the clearest way to say "missing."

3.8.2 Missing values in R

R is stricter than Excel about missing values. By default, the average of a column that contains even one NA is NA. R's reasoning is that if one value is unknown, the average is unknown too. To average just the values that are present, a script adds na.rm = TRUE ("remove the NAs"). You'll see this in the verification script below and in many scripts later in the book. When a result comes back as NA, a missing na.rm = TRUE is the first thing to check.

3.9 Verifying the Cleaned Table

Dana asked how you know the data is fixed. "I ran some formulas" isn't an answer. A set of checks against a trusted source is. You have one: the Survey Table. Make a new sheet named CleanChecks and set up three columns: the check, the SurveyClean formula, and the Survey formula. Here are the checks and the results you should get.

Check SurveyClean Survey
Rows: =ROWS(SurveyClean[response_id]) 600 600
Same IDs in the same order: =SUMPRODUCT(--(SurveyClean[response_id]=Survey[response_id])) 600
Loyalty members: =COUNTIFS(SurveyClean[loyalty_member],"Yes") 247 247
Exact loyalty spelling matches: =SUMPRODUCT(--EXACT(SurveyClean[loyalty_member],Survey[loyalty_member])) 600
Counter orders: =COUNTIFS(SurveyClean[order_type],"Counter") 345 345
Mobile orders: =COUNTIFS(SurveyClean[order_type],"Mobile") 136 136
Drive-thru orders: =COUNTIFS(SurveyClean[order_type],"Drive-thru") 119 119
Exact order type matches: =SUMPRODUCT(--EXACT(SurveyClean[order_type],Survey[order_type])) 600
Would recommend: =COUNTIFS(SurveyClean[would_recommend],"Yes") 407 407
Spend values that are numbers: =COUNT(SurveyClean[spend]) 600 600
Average spend: =AVERAGE(SurveyClean[spend]) $8.48 $8.48
Blank ages: =COUNTBLANK(SurveyClean[age]) 21 0
Blank waits: =COUNTBLANK(SurveyClean[wait_minutes]) 16 0

For the Survey column, use the same formulas with Survey in place of SurveyClean.

Two of these checks deserve a closer look. COUNTIFS ignores case, so 247 loyalty members would show up even if some still read "yes." EXACT doesn't ignore case. It compares two values character by character, including capitals and spaces, and returns TRUE only for a perfect match. SUMPRODUCT(--EXACT(…)) counts the rows that match exactly, so 600 means every row's spelling is identical to the answer key. That check works here only because the second check confirmed the two Tables list the same IDs in the same order.

The blank counts are the one place the two Tables differ, and you know why: Survey has every age and wait time, and your Table has blanks where SurveyRaw did. That's a correct difference, not an error. In a real job you wouldn't have an answer key, so you'd rely on checks that don't need one: the row count matches what the survey collected, no ID appears twice, every category has exactly the expected spellings, every numeric column is all numbers, and the blanks are where they were in the raw data.

A CleanChecks sheet with three columns (Check, SurveyClean, Survey) listing the verification formulas and their results: 600 rows, 247 loyalty members, 345/136/119 order types, 407 would recommend, 600 numeric spend values, average spend $8.48 in both, and blank age counts 21 and 0. The EXACT match rows show 600. The formula bar shows the SUMPRODUCT EXACT formula.

3.9.1 A check in R

Excel can tell you the counts are right. R for Excel can show you that R sees the data the way you intend, which is what matters for every analysis from here on. Add SurveyClean in 1. Data with Add a table… and look at its column list: spend should now be number, and loyalty_member and order_type should be text. Then run a short check. A script for this usually looks like the one below.

# Count each value, including blanks (NA), to spot leftover spellings
loyalty_counts <- table(SurveyClean$loyalty_member, useNA = "ifany")
order_counts <- table(SurveyClean$order_type, useNA = "ifany")

# Count the missing values (NA) in every column
blanks <- colSums(is.na(SurveyClean))

# Average age, leaving out the missing ages
mean_age <- mean(SurveyClean$age, na.rm = TRUE)

# Send the checks to Excel
excel_out(loyalty_counts, "loyalty_member values")
excel_out(order_counts, "order_type values")
excel_out(blanks, "Missing values (NA) in each column")
excel_out(round(mean_age, 2), "Mean age, missing values left out")

In plain language: the two table() lines count how often each value appears, and useNA = "ifany" adds a count of blanks if there are any. Because R is case-sensitive, a leftover "yes" would show up here as its own row. The blanks line counts the NA values in each column. The mean_age line averages age with na.rm = TRUE, so the missing ages are left out. The four excel_out() calls make four output cards. Use Send everything to a new sheet to put them all in the workbook.

You should see exactly two loyalty values (No 353, Yes 247), exactly three order types (Counter 345, Drive-thru 119, Mobile 136), 21 missing ages and 16 missing waits with every other column at 0, and a mean age of 36.91, the same as =AVERAGE(SurveyClean[age]) in Excel. Two tools, one answer.

Try one experiment: delete , na.rm = TRUE from the mean_age line and click Run again. The mean age card now shows NA. That's R telling you some values are missing, and the reason so many scripts in this book include na.rm = TRUE.

Now you can answer Dana:

The raw survey had 603 rows, including 3 duplicated responses, which I removed after confirming they were exact copies. Loyalty status was spelled nine different ways and 20 order types were in lowercase, so I standardized both. 38 spend amounts were stored as text and are now numbers. 21 ages and 16 wait times were never recorded, and I left those blank rather than guessing. The cleaned table has 600 responses and matches the reference survey exactly on every count, spelling and spend amount I checked. It's ready to use.

3.10 A Repeatable Alternative: Power Query

Everything you've done so far is manual: helper columns, Paste Values, deleting columns. That's fine for one file. If Ridgeline sent you a new raw export every month, you'd want to record the steps once and replay them. Excel has a tool for that.

Power Query — A tool built into Excel (Data > Get & Transform Data) that imports data and cleans it with a recorded series of steps. When new data arrives, Refresh repeats every step automatically.

A Power Query version of this chapter's cleaning goes like this:

  1. Click a cell in SurveyRaw and choose Data > From Table/Range. Excel asks to make the range a Table. Click OK, and the Power Query Editor opens.
  2. Select the loyalty_member and order_type columns (Ctrl+click their headers) and choose Transform > Format > Trim.
  3. Right-click loyalty_member, choose Replace Values, and replace each variant (for example "Y" with "Yes"). Under Advanced options, check Match entire cell contents so that "Y" doesn't match the Y inside "Yes." Power Query matches case exactly, so "yes" and "YES" each need their own replacement. Do the same for the lowercase order types.
  4. Select spend, use Replace Values to replace "$" with nothing, then set the type with Transform > Data Type > Decimal Number.
  5. Select all columns (Ctrl+A) and choose Home > Remove Rows > Remove Duplicates.
  6. Choose Home > Close & Load. The cleaned data appears on a new sheet as a Table, which you can rename on the Table Design tab.

The Power Query Editor with the SurveyRaw query open, showing the data preview and, on the right, the Query Settings pane with the Applied Steps list: Source, Changed Type, Trimmed Text, several Replaced Value steps, Removed Duplicates. Circle the Applied Steps list.

Every step appears in the Applied Steps list on the right, where you can see it, reorder it or delete it. That list is also documentation: anyone can see exactly what was done to the data. The cost is a new tool to learn. For one-time cleaning, formulas are often quicker. For a monthly report, Power Query pays for itself after the first refresh.

3.11 Preventing Problems With Data Validation

The best cleaning is the cleaning you never have to do. Most of SurveyRaw's problems came from people typing into cells with no rules. Excel can enforce rules at the moment of entry.

Data validation — An Excel feature (Data > Data Validation) that restricts what can be entered in a cell, for example only "Yes" or "No," or only whole numbers from 1 to 10, and shows an error message when someone types anything else.

To limit loyalty_member to Yes or No in SurveyClean:

  1. Select the data cells of the loyalty_member column.
  2. Choose Data > Data Validation. On the Settings tab, set Allow to List and type Yes,No in Source. Leave In-cell dropdown checked.
  3. On the Error Alert tab, choose Style: Stop and type a message such as "Enter Yes or No." Click OK.

Excel's Data Validation dialog on the Settings tab, with Allow set to List, Source set to Yes,No, and In-cell dropdown checked. Behind it, the loyalty_member column of the SurveyClean Table is selected.

Each cell in the column now shows a drop-down with the two choices, and typing anything else, including "yes" with a lowercase y, is refused. Useful rules for the survey would be:

Column Allow Setting
loyalty_member, would_recommend List Yes,No
order_type List Counter,Mobile,Drive-thru
satisfaction Whole number between 1 and 10
age Whole number between 16 and 100
spend, wait_minutes Decimal greater than or equal to 0

In a Table, new rows pick up the column's validation automatically. Validation has limits, though. It checks typing, not pasting: data pasted from elsewhere can still bring in bad values. It also doesn't check values that were already there when you added the rule. To find those, click the arrow on the Data Validation button (on the Data tab) and choose Circle Invalid Data. Excel draws a red circle around every cell that breaks a rule.

For Ridgeline, the practical step is to give store managers an entry sheet with these rules already set, instead of a blank spreadsheet. A few minutes of setup prevents the next round of cleaning.

3.12 Summary

Tidy data has one variable per column, one observation per row, one value per cell, one spelling per category and no duplicates. SurveyRaw had a tidy shape but messy contents: 3 duplicated rows, nine spellings of Yes and No, lowercase order types, 38 spend amounts stored as text, and blank ages and wait times. You found them with filter drop-downs, COUNTBLANK, UNIQUE with COUNTIF, COUNT and ISTEXT, and R for Excel's column summary, which flagged the repeated IDs and the inconsistent spellings but quietly hid the extra spaces and dollar signs. Excel ignores capital letters when it compares text, and R doesn't, so cleaning has to make spellings exactly right, not just right enough for Excel.

You fixed the problems on a copy of the data: COUNTIF to flag duplicates and Remove Duplicates to remove them, an Excel Table named SurveyClean, TRIM with XLOOKUP to standardize text (avoiding PROPER, which turns "drive-thru" into "Drive-Thru"), and VALUE with SUBSTITUTE to turn text into numbers, each finished with Paste Values. Missing values stay blank, never 0: zeros pulled the average age down by more than a year, while leaving the blanks out came within a tenth of a year of the true value. You then verified the cleaned Table against the reference Survey Table with ROWS, COUNTIFS, EXACT and an R check. Power Query can record the same cleaning steps so they repeat with one click, and data validation can stop most of these problems from happening in the first place. With clean data in hand, you're ready to describe it, which is the subject of Chapter 4. Describing Data.