Part 10: Excel Projects & Practical Skills Quiz (20 MCQ)

Correct answer: C. SUM function

★ Key Takeaway: Most real Excel projects start with simple totals. SUM is the fastest foundation for budgeting and reporting.

Explanation: A budget tracker is mainly about adding numbers correctly. The SUM function lets you total a range (like all Food expenses) and update totals automatically when you add new rows.

Why other options are incorrect:

• A. Text to Columns – This splits text into separate columns; it doesn't add budget amounts.
• B. Conditional Formatting – This highlights values visually, but it does not calculate totals.
• D. Find and Replace – This changes text or values, but it is not a calculation tool for totals.

Tip: Want to build real Excel projects like budget trackers, dashboards, and templates the right way? A 1-on-1 tutor can guide you step by step. Get personalized Excel help from a Fiverr expert →

Correct answer: A. Formulas (for example, multiplication and SUM)

★ Key Takeaway: A professional template should calculate automatically. Formulas prevent manual mistakes and save time.

Explanation: In invoices, line totals are usually Quantity × Unit Price, then you SUM all line totals for the final amount. When a value changes, Excel recalculates instantly, so the invoice stays accurate.

Why other options are incorrect:

• B. Page Break Preview – This helps you see printing layout; it doesn't calculate invoice totals.
• C. Comments – Comments store notes; they do not create automatic calculations.
• D. Spell Check – Spell Check reviews text, not numeric totals and calculations.

Correct answer: B. XLOOKUP

★ Key Takeaway: Lookups connect tables. XLOOKUP is a modern, flexible way to pull matching data.

Explanation: XLOOKUP searches for a value (like Product ID) and returns related information (like Price) from another range. This is extremely common in real projects such as sales sheets and inventory systems.

Why other options are incorrect:

• A. Merge Cells – Merging changes layout; it does not retrieve matching prices from another table.
• C. Wrap Text – This displays long text on multiple lines; it does not pull data from a table.
• D. Freeze Panes – This keeps headers visible; it does not perform matching and returning values.

Correct answer: C. Use an IF formula combined with TODAY and a status check

★ Key Takeaway: Smart trackers use logic. IF + TODAY lets Excel label tasks automatically.

Explanation: A typical formula checks if TODAY() is greater than the Due Date and whether Status is not "Done". This updates automatically each day, which is better than manual tracking.

Why other options are incorrect:

• A. Sort the Due Date column every day – Sorting can help you see dates, but it does not automatically label tasks as "Late".
• B. Manually type "Late" for each overdue task – Manual labeling is slow and easy to forget; formulas update reliably.
• D. Use Spell Check to detect overdue tasks – Spell Check is for text spelling, not date logic or status rules.

Correct answer: A. Data Validation with a rule like "greater than or equal to 0"

★ Key Takeaway: Data Validation is for controlling input. It helps you prevent bad data before it enters your sheet.

Explanation: With Data Validation, you can set a numeric rule so Excel blocks negative entries (or warns the user). This keeps your totals and reports accurate.

Why other options are incorrect:

• B. Find and Replace – This can change existing values, but it does not stop wrong values from being entered.
• C. Page Layout view – This is for print preview style layout, not data entry control.
• D. AutoCorrect – AutoCorrect fixes common typing mistakes in text; it does not validate numeric rules.

Correct answer: C. TRIM

★ Key Takeaway: Clean text first. TRIM removes extra spaces so matching and sorting work correctly.

Explanation: TRIM removes leading and trailing spaces and reduces multiple spaces between words to single spaces. This is crucial when your sheet uses lookups or duplicates checks based on names.

Why other options are incorrect:

• A. RAND – RAND creates random numbers; it does not clean text.
• B. ROUND – ROUND changes number decimals; it does not remove spaces from names.
• D. SUM – SUM adds numbers; it does not fix text formatting issues.

Correct answer: A. Keep raw data on a separate sheet and build summaries on the dashboard

★ Key Takeaway: Separate data from presentation. Dashboards are easier to maintain when raw data is not mixed with visuals.

Explanation: A clean structure usually has one sheet for raw data, another for calculations (optional), and a dashboard for charts and key metrics. This reduces mistakes and makes updates simple.

Why other options are incorrect:

• B. Type final totals manually into the dashboard cells – Manual totals can become outdated and cause reporting errors.
• C. Merge many cells to create large spaces for text – Heavy merging often breaks alignment and makes filtering and selection harder.
• D. Use different fonts on every chart for variety – Too many styles reduce professionalism and readability.

Correct answer: D. Conditional Formatting with a weekday-based rule

★ Key Takeaway: Conditional Formatting can highlight patterns automatically. It's perfect for calendars and trackers.

Explanation: You can create a rule based on the date (for example, using WEEKDAY) so Excel formats weekend rows automatically. This saves time and improves readability in real trackers.

Why other options are incorrect:

• A. Spell Check – Spell Check checks words, not dates or weekdays.
• B. Text to Columns – This splits text into separate columns; it doesn't highlight weekends.
• C. Sort A to Z – Sorting changes order; it doesn't apply automatic highlighting rules.

Correct answer: B. Protect the sheet after locking formula cells

★ Key Takeaway: Protecting formulas is a real-world Excel skill. Lock + Protect keeps your logic safe while users enter data.

Explanation: The usual workflow is: select input cells and unlock them, keep formula cells locked, then protect the sheet. This allows teammates to enter data but prevents breaking calculations.

Why other options are incorrect:

• A. Increase the column width – This changes layout only; it does not stop people from editing formulas.
• C. Use a different font for formula cells – Visual styling doesn't prevent editing; protection controls editing access.
• D. Turn on Freeze Panes – Freeze Panes keeps headers visible; it does not protect formulas from changes.

Tip: If you want to build professional Excel templates that are safe for teams (locked formulas, clean inputs, automated summaries), a tutor can help you set it up properly. Learn Excel faster with a 1-on-1 tutor on Fiverr →

Correct answer: D. IFERROR

★ Key Takeaway: Clean reports handle errors gracefully. IFERROR replaces scary errors with clear messages.

Explanation: IFERROR lets you show a friendly output when a formula fails, such as "N/A" or 0. This is common in dashboards and KPI sheets where errors confuse readers.

Why other options are incorrect:

• A. AVERAGE – AVERAGE calculates a mean; it does not catch and replace errors.
• B. COUNT – COUNT counts numeric cells; it does not manage formula errors.
• C. TRIM – TRIM cleans extra spaces in text; it does not fix calculation errors.

Correct answer: A. Filter

★ Key Takeaway: Filtering helps you focus. It shows only the rows you need without deleting anything.

Explanation: Filters allow you to display only records that match a condition (like Stock < 10). This is a daily Excel task in inventory, sales, and customer lists.

Why other options are incorrect:

• B. Format Painter – This copies formatting, not data conditions like "less than 10".
• C. Merge Cells – Merging affects layout; it does not filter inventory items.
• D. Spell Check – This checks spelling in text; it does not filter numeric stock values.

Correct answer: D. Excel Table (Format as Table)

★ Key Takeaway: Tables make Excel projects scalable. They expand automatically and work well with formulas and analysis tools.

Explanation: An Excel Table expands when you add new data rows and supports structured references like [Sales]. This helps build reliable summaries, dashboards, and PivotTables.

Why other options are incorrect:

• A. Manual cell coloring – Colors can improve appearance, but they don't auto-expand ranges or create structured references.
• B. Page Setup – This controls printing layout; it does not create dynamic data structures.
• C. Freeze Panes – This keeps headers visible; it does not turn data into a structured table.

Correct answer: C. PivotTable

★ Key Takeaway: PivotTables are built for summaries. They turn large tables into quick insights in seconds.

Explanation: PivotTables let you drag fields like Region to Rows and Month to Columns, then sum Sales in Values. This creates a clean summary without complex formulas.

Why other options are incorrect:

• A. Goal Seek – Goal Seek solves for a target result; it does not summarize sales by group.
• B. Remove Duplicates – This cleans data, but it doesn't build grouped summaries by month and region.
• D. Text to Columns – This splits text into columns; it is not a summarizing tool for dashboards.

Correct answer: B. Column chart

★ Key Takeaway: Column charts are great for comparisons. They make differences between months easy to see.

Explanation: Column charts display each month as a separate bar, so viewers can quickly compare values. For reports and dashboards, this is often the clearest starting chart.

Why other options are incorrect:

• A. Pie chart – Pie charts show part-to-whole, not month-to-month comparisons.
• C. Radar chart – Radar charts are specialized and can confuse readers for simple revenue comparisons.
• D. Surface chart – Surface charts are for complex 3D relationships, not basic monthly revenue reporting.

Correct answer: D. Data Validation (List)

★ Key Takeaway: Dropdowns make spreadsheets cleaner and safer. Data Validation helps standardize data entry.

Explanation: Data Validation List creates a dropdown so users select only approved values. This prevents spelling differences like "In progress" vs "In Progress" which break filters and reports.

Why other options are incorrect:

• A. Conditional Formatting – This highlights cells; it does not create a dropdown selection list.
• B. Sort – Sorting rearranges rows; it does not restrict what users can type.
• C. Wrap Text – Wrap Text changes display, not input rules or dropdown creation.

Correct answer: C. Use a formula that subtracts Start Time from End Time and format as time

★ Key Takeaway: Timesheets should calculate automatically. Time subtraction plus correct formatting gives reliable totals.

Explanation: Excel stores times as numbers, so End minus Start returns the duration. With proper time formatting (and handling overnight shifts if needed), your timesheet stays accurate.

Why other options are incorrect:

• A. Type totals manually each day – Manual totals are slow and often contain mistakes.
• B. Use Merge Cells for the time columns – Merging changes layout and can cause selection issues; it doesn't calculate hours.
• D. Use Find and Replace to calculate hours – Find and Replace changes text; it is not a calculation method for durations.

Correct answer: A. Remove Duplicates

★ Key Takeaway: Clean contact lists prevent real mistakes. Remove Duplicates is a quick way to fix repeated records.

Explanation: Remove Duplicates checks selected columns and deletes extra repeated rows while keeping one copy. In customer, inventory, or staff lists, this improves accuracy immediately.

Why other options are incorrect:

• B. Spell Check – Spell Check helps with spelling, but it does not identify duplicate rows.
• C. Page Setup – Page Setup is for printing layout, not cleaning duplicates.
• D. Zoom – Zoom changes the view size; it does not remove duplicate records.

Correct answer: D. Freeze Panes

★ Key Takeaway: Freeze Panes improves usability in large sheets. It keeps headings visible so you don't lose context.

Explanation: Freeze Panes locks rows and/or columns in place while you scroll. This is very practical for reports, trackers, and any sheet with many rows.

Why other options are incorrect:

• A. Wrap Text – Wrap Text changes how text displays; it doesn't keep headers visible while scrolling.
• B. Merge Cells – Merging combines cells; it does not lock header rows on screen.
• C. Sort – Sorting changes row order; it does not keep headers fixed during scrolling.

Correct answer: C. Create a PivotTable to sum spend by customer, then sort descending and take top 5

★ Key Takeaway: PivotTables are perfect for "top customers" style business questions. They summarize fast and stay reliable as data grows.

Explanation: A PivotTable can group transactions by Customer and calculate total Spend. Then you sort the totals from highest to lowest and quickly identify the top 5 without messy manual work.

Why other options are incorrect:

• A. Manually scan the table and guess the top customers – This is unreliable and easy to get wrong when data is large.
• B. Use Merge Cells to group customer rows – Merging affects layout and can break data tools; it does not calculate totals.
• D. Change the worksheet theme colors – Themes change appearance only; they don't identify top customers.

Correct answer: B. Practice with real datasets and build small templates you can reuse

★ Key Takeaway: Real skill comes from real practice. Reusable templates and real datasets make you faster and more confident.

Explanation: When you practice with realistic data (sales, expenses, inventory), you face real problems like messy inputs and changing requirements. Building templates also teaches structure, clarity, and long-term reliability.

Why other options are incorrect:

• A. Avoid using Excel until you forget the basics – Skills improve with repetition, not avoidance.
• C. Only memorize shortcuts without using them in projects – Shortcuts help, but projects teach how everything works together.
• D. Use random formatting to make sheets look more complex – Complexity is not the goal; clarity and accuracy are what professionals value.