MCQs on Data Cleaning and Automation | Excel MCQ Questions
Data cleaning and automation are essential skills for efficiently managing large datasets. This chapter covers powerful techniques like Text to Columns, Flash Fill, Power Query, and VBA macros. These methods streamline data transformation, automate tasks, and help in protecting and sharing workbooks. Mastering these functions can improve productivity and accuracy.
Text to Columns and Flash Fill for Data Cleaning
1. Which tool in Excel allows you to split a single column of data into multiple columns based on a delimiter?
a) Flash Fill
b) Power Query
c) Text to Columns
d) CONCATENATE
2. What does the Flash Fill feature automatically detect and apply?
a) It identifies errors in the data
b) It splits columns of data
c) It suggests and applies formatting based on a pattern
d) It aggregates data into a table
3. Which of the following delimiters can you use with the Text to Columns tool?
a) Only comma
b) Only space
c) Any character like comma, space, or tab
d) Only semicolon
4. How can Flash Fill be activated in Excel?
a) By pressing Ctrl+Z
b) By typing the desired format and pressing Enter
c) By pressing Ctrl+E
d) By selecting the “Data” tab
5. Which of the following actions can be performed with the Text to Columns tool?
a) Combine multiple columns into one
b) Split a cell into multiple cells based on a specific delimiter
c) Remove duplicate values
d) Format text into lowercase
Power Query Basics for Data Transformation
6. What is the primary purpose of Power Query in Excel?
a) To perform calculations
b) To automate data cleaning and transformation
c) To create charts
d) To filter data
7. Which button would you click in Excel to open Power Query?
a) Data > Get & Transform Data
b) Data > Sort & Filter
c) Insert > PivotTable
d) View > Workbook Views
8. Which of the following is NOT a feature of Power Query?
a) Extracting data from external sources
b) Merging and appending queries
c) Data visualization
d) Removing duplicates
9. What does Power Query allow you to do with multiple datasets?
a) Merge or append data to combine datasets
b) Compress datasets for storage
c) Split datasets into smaller parts
d) Automatically create a pivot table
10. After transforming data using Power Query, how can you load it back to Excel?
a) Copy and paste the data
b) Click “Close & Load”
c) Use the “Save” option
d) Click “Import”
Automating Tasks with Macros (VBA Introduction)
11. What is the purpose of using macros in Excel?
a) To create visual charts
b) To automate repetitive tasks
c) To format cells
d) To perform calculations
12. What does VBA stand for in the context of Excel?
a) Virtual Business Application
b) Visual Basic for Applications
c) Verified Business Analytics
d) Visual Business Alignment
13. How do you open the Visual Basic for Applications (VBA) editor in Excel?
a) By pressing Alt+F11
b) By pressing Ctrl+V
c) By selecting “Data > Macros”
d) By selecting “Developer > Macros”
14. Which of the following can macros be used to automate in Excel?
a) Inserting new rows
b) Running formulas
c) Formatting cells
d) All of the above
15. What is the first step in recording a macro in Excel?
a) Pressing Alt+F8
b) Clicking the “Record Macro” button
c) Writing VBA code manually
d) Selecting a template
Using Templates for Repeated Tasks
16. What is a benefit of using templates in Excel?
a) Templates automatically calculate data
b) Templates save time by providing pre-designed formats for repeated tasks
c) Templates prevent errors in formulas
d) Templates create pivot tables automatically
17. How do you save a workbook as a template in Excel?
a) File > Save As > Excel Template
b) File > Export > Save as Template
c) File > Save > Template
d) File > Save As > PDF
18. Which of the following is NOT a typical use of an Excel template?
a) Standardizing reports
b) Repeated data entry
c) Automating data analysis
d) Creating unique reports
19. Can Excel templates be used for creating budgets and invoices?
a) Yes, they can be customized for various tasks like budgets and invoices
b) No, they can only be used for charts
c) No, templates are for formulas only
d) Yes, but only for sales reports
20. What happens when you open an Excel template?
a) The template will open as a new, editable workbook
b) The template will automatically create a new file
c) The template will prompt you to enter data
d) The template will generate a chart
Protecting and Sharing Workbooks
21. Which feature in Excel allows you to protect a workbook from unauthorized changes?
a) Sharing and Collaboration
b) Workbook Protection
c) Track Changes
d) Worksheet Formatting
22. How do you password-protect an Excel workbook?
a) File > Info > Protect Workbook
b) Data > Protect > Set Password
c) Review > Protect Sheet
d) View > Lock Workbook
23. What does the “Protect Sheet” option allow you to do in Excel?
a) Prevent data entry into cells
b) Encrypt the entire workbook
c) Change the document’s layout
d) Hide worksheets
24. Which option allows multiple users to edit an Excel workbook simultaneously?
a) Protect Workbook
b) Share Workbook
c) Encrypt Workbook
d) Restrict Editing
25. Which of the following is a feature of the “Track Changes” option in Excel?
a) It automatically saves workbooks
b) It shows modifications made by others in a shared workbook
c) It prevents unauthorized access
d) It creates backups of workbooks
General Functions & Use Cases
26. Which of these features can help automate formatting tasks in Excel?
a) Macros
b) Templates
c) Flash Fill
d) All of the above
27. How would you undo a change made while recording a macro?
a) Press Ctrl+Z
b) Use the Macro Editor
c) Right-click and select “Undo”
d) Disable the macro
28. What is a common use of Power Query in transforming data?
a) Creating complex formulas
b) Cleaning and shaping data before analysis
c) Designing the layout of Excel sheets
d) Creating interactive dashboards
29. How do you stop the macro recorder in Excel?
a) Press Alt+F12
b) Click the “Stop Recording” button
c) Press Ctrl+S
d) Close Excel
30. What is the best method for sharing an Excel workbook with sensitive data?
a) Using cloud sharing services with password protection
b) Sending the workbook through email without a password
c) Using Excel’s default sharing options
d) Posting the file on a public website
Answer Key
| QNo | Answer |
|---|---|
| 1 | c) Text to Columns |
| 2 | c) It suggests and applies formatting based on a pattern |
| 3 | c) Any character like comma, space, or tab |
| 4 | c) By pressing Ctrl+E |
| 5 | b) Split a cell into multiple cells based on a specific delimiter |
| 6 | b) To automate data cleaning and transformation |
| 7 | a) Data > Get & Transform Data |
| 8 | c) Data visualization |
| 9 | a) Merge or append data to combine datasets |
| 10 | b) Click “Close & Load” |
| 11 | b) To automate repetitive tasks |
| 12 | b) Visual Basic for Applications |
| 13 | a) By pressing Alt+F11 |
| 14 | d) All of the above |
| 15 | b) Clicking the “Record Macro” button |
| 16 | b) Templates save time by providing pre-designed formats for repeated tasks |
| 17 | a) File > Save As > Excel Template |
| 18 | c) Automating data analysis |
| 19 | a) Yes, they can be customized for various tasks like budgets and invoices |
| 20 | a) The template will open as a new, editable workbook |
| 21 | b) Workbook Protection |
| 22 | a) File > Info > Protect Workbook |
| 23 | a) Prevent data entry into cells |
| 24 | b) Share Workbook |
| 25 | b) It shows modifications made by others in a shared workbook |
| 26 | d) All of the above |
| 27 | a) Press Ctrl+Z |
| 28 | b) Cleaning and shaping data before analysis |
| 29 | b) Click the “Stop Recording” button |
| 30 | a) Using cloud sharing services with password protection |