Part 4: Excel Data Formatting & Cleaning Quiz (20 MCQ)

Correct answer: B. It helps Excel interpret data correctly and prevents calculation mistakes

★ Key Takeaway: Clean data means Excel reads values as you intend and your results stay trustworthy.

Explanation: Excel behaves differently depending on whether something is stored as text, a number, or a date. If you clean issues like extra spaces, inconsistent formats, and “numbers stored as text,” your sorting, totals, and charts will work properly and you avoid silent errors in analysis.

Why other options are incorrect:

• A. It makes the worksheet look colorful – Visual style can help readability, but cleaning is mainly about correctness and consistency, not decoration.
• C. It automatically reduces the file size – Cleaning improves accuracy; file size depends on things like images, formatting complexity, and saved content, not basic cleaning steps.
• D. It prevents formulas from recalculating – Cleaning does not stop calculation; it usually helps formulas calculate correctly by fixing the underlying data.

Tip: Real-world Excel data is often messy. If you want help cleaning exported reports (CSV, bank statements, sales files), a tutor can guide you step by step. Get guided Excel help from a Fiverr expert →

Correct answer: C. Format as Table

★ Key Takeaway: Tables make lists easier to filter, sort, and reference reliably.

Explanation: “Format as Table” turns your range into an Excel Table, adds filter drop-downs, and keeps formatting consistent as you add new rows. Tables also make formulas easier to read because you can use structured references instead of long cell ranges.

Why other options are incorrect:

• A. Conditional Formatting – This highlights cells based on rules, but it does not convert your range into a structured table.
• B. Format Painter – This copies formatting from one place to another, but it does not create headers or filters.
• D. Text to Columns – This splits one column into multiple columns; it does not create a table with filter headers.

Correct answer: B. To highlight cells automatically based on rules or conditions

★ Key Takeaway: Conditional Formatting changes appearance, not the underlying data.

Explanation: Conditional Formatting applies visual cues (like color fills or icons) when a condition is met—such as highlighting overdue dates or values above a target. It helps you spot patterns and problems quickly without editing the values.

Why other options are incorrect:

• A. To change the actual values inside cells automatically – Conditional Formatting affects how a cell looks, not what value it contains.
• C. To convert text values into numbers – Converting data types requires tools like Text to Columns, VALUE, or correcting “numbers stored as text.”
• D. To remove duplicate rows permanently – It can highlight duplicates, but removing them is done with the Remove Duplicates tool.

Correct answer: C. Remove Duplicates

★ Key Takeaway: “Remove Duplicates” deletes repeated records to keep your dataset accurate.

Explanation: Remove Duplicates checks one or more selected columns and permanently removes repeated rows, keeping only unique records. It’s a common cleanup step for contact lists, exported reports, and merged datasets.

Why other options are incorrect:

• A. Sort – Sorting changes order, but it does not delete duplicate entries.
• B. Filter – Filtering temporarily hides data; it does not remove duplicates from the sheet.
• D. Find and Replace – This swaps text or values, but it does not detect and remove duplicate rows reliably.

Correct answer: B. Formulas may not calculate correctly because Excel treats them like text

★ Key Takeaway: When Excel stores numbers as text, calculations can silently fail.

Explanation: If a number is stored as text (often shown with a green triangle), SUM and other math functions may ignore it or treat it differently. Cleaning this ensures totals, averages, and comparisons behave correctly—especially in imported CSV or copied web data.

Why other options are incorrect:

• A. They automatically change into dates – Date conversion is a different issue; “numbers stored as text” usually stay text until corrected.
• C. They prevent the workbook from being saved – You can save normally; the problem is incorrect calculations, not saving.
• D. They disable filters and sorting – Filters and sorting still work, but numeric sorting might act like text sorting, which is misleading.

Correct answer: D. Text to Columns

★ Key Takeaway: Text to Columns is the fastest way to split combined fields into separate columns.

Explanation: Text to Columns divides data using a delimiter (like comma, tab, or space) or a fixed width. It’s useful when you receive data like “FirstName LastName” or “City, Country” in a single cell and need separate fields for analysis.

Why other options are incorrect:

• A. Flash Fill – Flash Fill can extract patterns, but it doesn’t formally split the original column into multiple columns.
• B. Remove Duplicates – This removes repeated records; it does not split text into separate columns.
• C. Format Painter – Format Painter copies styling; it does not change how text is stored or separated.

Correct answer: C. Automatically filling values by detecting a pattern from examples

★ Key Takeaway: Flash Fill “learns” from your examples and repeats the pattern automatically.

Explanation: Flash Fill is helpful when you need to clean or split text quickly, such as extracting first names, last names, or adding a consistent format. You type a few examples, and Excel predicts the remaining pattern without writing formulas.

Why other options are incorrect:

• A. Protecting worksheets from editing – Sheet protection controls editing permissions; it does not clean or transform data.
• B. Removing all duplicates from a table – Duplicate removal is done using Remove Duplicates, not Flash Fill.
• D. Converting a range into a PivotTable – PivotTables summarize data; they do not auto-fill patterns in text cleaning.

Correct answer: C. Percentage format

★ Key Takeaway: Percentage format displays a decimal as a percentage for easier reading.

Explanation: In Excel, 0.25 represents one quarter, and Percentage format displays it as 25%. This is important in reports because people understand percentages faster than decimals, and it avoids misreading growth rates or conversion rates.

Why other options are incorrect:

• A. Currency format – Currency adds currency symbols and decimal places; it does not convert 0.25 into 25%.
• B. Date format – Date format displays numbers as dates, which would be incorrect for a rate like 0.25.
• D. Text format – Text treats values as plain text, which prevents numeric calculations and does not display percentage properly.

Correct answer: C. Use Text to Columns and set the column data format to Date

★ Key Takeaway: Text to Columns can convert text dates into real Excel dates in a controlled way.

Explanation: When dates are stored as text, Excel may not sort them chronologically or use them in date calculations. Text to Columns can “re-parse” the data and, when you set the data type to Date, it converts the text into true date values that Excel understands for sorting, filtering, and formulas.

Why other options are incorrect:

• A. Manually type each date again in every cell – This is slow and error-prone; it’s not a reliable cleanup method for real datasets.
• B. Use Text to Columns and finish without changing the delimiters – If you do not set the correct data type, Excel may keep the values as text and the problem remains.
• D. Change the font color and apply bold formatting – Formatting changes the appearance, not the underlying data type.

Tip: CSV imports often break dates, numbers, and leading zeros. If you want a repeatable cleanup workflow, an Excel tutor can help you build one. Get personalized Excel help from a Fiverr expert →

Correct answer: D. TRIM

★ Key Takeaway: TRIM fixes messy spacing so text becomes consistent and easier to match.

Explanation: TRIM removes leading and trailing spaces and reduces multiple spaces inside text to a single space. This is crucial when you’re matching names, product codes, or categories—because extra spaces can break lookups and cause duplicates that look identical.

Why other options are incorrect:

• A. CLEAN – CLEAN removes non-printing characters (like some line breaks), but it does not specifically fix extra spaces like TRIM does.
• B. LEFT – LEFT extracts a set number of characters from the start of a text string; it does not remove extra spaces.
• C. PROPER – PROPER changes capitalization (for example, “john smith” to “John Smith”); it does not remove extra spaces.

Correct answer: A. CLEAN

★ Key Takeaway: CLEAN removes hidden characters that can break matching and formatting.

Explanation: CLEAN strips out non-printable characters (such as some line breaks and control codes) that come from copying text from the web or exported systems. These characters can make two cells look the same on screen but behave differently in formulas or comparisons.

Why other options are incorrect:

• B. TRIM – TRIM mainly fixes extra spaces; it doesn’t remove many non-printing characters the way CLEAN does.
• C. UPPER – UPPER changes text to uppercase; it does not remove hidden characters.
• D. CONCAT – CONCAT combines text strings; it does not clean unwanted characters.

Correct answer: A. Find and Replace

★ Key Takeaway: Find and Replace is a fast way to standardize messy text values.

Explanation: Find and Replace can replace a specific value (like “N/A”, “none”, or extra spaces) across a selected range or the whole sheet. This is useful in cleanup because consistent values make filtering, counting, and reporting more reliable.

Why other options are incorrect:

• B. Freeze Panes – Freeze Panes keeps headers visible while scrolling; it does not change data values.
• C. Data Validation – Data Validation restricts what users can enter in the future; it does not bulk-fix existing text across a range.
• D. Goal Seek – Goal Seek solves for an input value to reach a target output; it is not used for cleaning text entries.

Correct answer: A. PROPER

★ Key Takeaway: PROPER standardizes name capitalization to improve readability and consistency.

Explanation: PROPER capitalizes the first letter of each word and makes the rest lowercase, which is useful for cleaning names, locations, and titles. Consistent casing also helps when you compare or deduplicate text values that were typed differently.

Why other options are incorrect:

• B. LOWER – LOWER makes everything lowercase, so “John Smith” would become “john smith,” not proper name format.
• C. TRIM – TRIM removes extra spaces; it does not fix capitalization differences like “SMITH” vs “Smith.”
• D. VALUE – VALUE converts text numbers into real numbers; it does not change text capitalization.

Correct answer: B. The numbers are stored as text, so convert them to real numbers before sorting

★ Key Takeaway: If numeric sorting looks wrong, the values are often stored as text.

Explanation: When numbers are stored as text, Excel sorts them like words, so "100" can come before "20" (just like "apple" comes before "banana"). Convert the column to real numbers first (for example, correct “numbers stored as text”), then sort again for a proper numeric order.

Why other options are incorrect:

• A. The column is filtered, so sorting cannot work – Sorting works even with filters; filters only control what rows are visible.
• C. The worksheet is protected, so sorting is random – Protection may block sorting, but it does not make sorting random or cause “100 before 20” behavior.
• D. Excel always sorts large numbers before small numbers – Excel sorts correctly when values are truly numeric; the issue happens when values are treated as text.

Correct answer: D. Data Validation (List)

★ Key Takeaway: Data Validation prevents messy data by controlling what can be entered.

Explanation: Data Validation (List) creates a drop-down so users select only allowed values like "Paid", "Unpaid", or "Overdue". This prevents typos and keeps your dataset consistent for filtering, counting, and reporting.

Why other options are incorrect:

• A. Conditional Formatting – It can highlight certain statuses, but it does not restrict what users can type.
• B. Remove Duplicates – This removes repeated rows; it does not control allowed entries in a column.
• C. Wrap Text – Wrap Text changes how text displays inside a cell; it does not enforce allowed values.

Correct answer: D. Format the column as Text before entering or importing the data

★ Key Takeaway: Leading zeros disappear when Excel treats codes as numbers, so store codes as text.

Explanation: Product codes, IDs, and ZIP codes are identifiers, not quantities. Formatting the column as Text tells Excel to keep the exact characters (including leading zeros) so the codes stay correct for matching, lookups, and exports.

Why other options are incorrect:

• A. Apply Currency format – Currency changes display with symbols and decimals; it does not reliably preserve leading zeros for codes.
• B. Use Remove Duplicates on the column – Removing duplicates does not control how values are stored; it won’t bring back lost leading zeros.
• C. Use Conditional Formatting to highlight the codes – Highlighting changes appearance only; it does not fix the underlying storage of the code.

Correct answer: B. Filter

★ Key Takeaway: Filters help you view the right data without changing or deleting the dataset.

Explanation: Filtering hides rows that don’t match your selected criteria, which is perfect for reviewing subsets like “Paid,” “Overdue,” or a specific region. The data is still there, so you can clear the filter and return to the full list at any time.

Why other options are incorrect:

• A. Remove Duplicates – This permanently deletes repeated entries, which is different from temporarily showing a subset.
• C. Wrap Text – Wrap Text changes how text displays inside a cell; it does not hide or show rows based on criteria.
• D. Merge and Center – This combines cells for layout; it does not filter records.

Correct answer: D. Use SUBSTITUTE to replace CHAR(160) with a normal space, then TRIM

★ Key Takeaway: Some “spaces” are not normal spaces, so TRIM alone may not fix them.

Explanation: Web pages and PDFs often include non-breaking spaces (CHAR(160)) that look like normal spaces but behave differently. A reliable cleanup method is replacing CHAR(160) with a regular space using SUBSTITUTE, then using TRIM so matching and duplicate checks work correctly.

Why other options are incorrect:

• A. Use UPPER to make everything uppercase – Changing letter case does not remove hidden non-breaking spaces that cause mismatches.
• B. Use Format Painter to copy correct spacing – Format Painter copies formatting, not hidden characters inside the text.
• C. Use Merge and Center to combine the text – Merging cells changes layout and can damage tables; it does not clean hidden characters.

Correct answer: B. Apply a Custom number format like 000000

★ Key Takeaway: Custom number formats control how numbers display without changing the stored value.

Explanation: A custom format like 000000 displays the number 123 as 000123, but Excel still stores the value as 123 for numeric sorting and calculations. This is useful for fixed-length IDs in reports, while keeping the underlying data consistent.

Why other options are incorrect:

• A. Use Remove Duplicates so zeros stay visible – Removing duplicates affects records, not how a number is displayed.
• C. Apply Percentage format to add zeros automatically – Percentage format changes how decimals display as percentages; it does not add leading zeros to IDs.
• D. Use Wrap Text to force Excel to display all digits – Wrap Text changes line wrapping inside a cell; it does not add leading zeros.

Correct answer: A. Make a backup copy of the data or workbook before cleaning

★ Key Takeaway: Smart cleaning starts with protecting your original data.

Explanation: Cleanup steps like Remove Duplicates and Text to Columns can permanently change your dataset, so a backup lets you recover quickly if something goes wrong. In real work, this simple habit prevents lost records, broken reports, and time-consuming rework.

Why other options are incorrect:

• B. Turn off AutoSave and skip saving completely – This increases risk; you could lose work and still accidentally damage the data during editing.
• C. Merge all cells first to keep the data together – Merged cells make sorting, filtering, and cleaning harder and can damage the structure of a dataset.
• D. Convert everything to text format so nothing changes – Converting everything to text breaks calculations and analysis and does not prevent mistakes in cleanup steps.