omprehensive Study Guide Excel
C
Certification Exam
his Excel Certification Exam Study Guide is a comprehensive containing
T
215 questions and answers. It covers a wide range of essential Excel
topics, including data import/export, formatting, formulas (e.g., SUM,
AVERAGE, IF, VLOOKUP, INDEX/MATCH), charting, data analysis tools
(PivotTables, Slicers, What-If Analysis), and advanced features like macros
and workbook protection. It's designed to be a clear and professional
resource for exam preparation.
1 . How do you import files into Excel, specifying delimitation and column
formats?Data > Get External Data, then specify delimitation and column
formats.
. Where can you add comments to cells in Excel?Review tab > New
2
Comment.
3. How do you apply "Wrap Text" to a cell in Excel?Home tab > Wrap Text.
. How do you center text across a selection of cells in Excel?Home tab >
4
Alignment group > click the arrow in the bottom right corner (Alignment
Settings) > Horizontal alignment > Center Across Selection.
, . How do you insert a column in between existing columns in Excel?
5
Select the column to the right of where you want to insert, then right-click and
choose Insert. (Alternatively, select columns and use CTRL when selecting to
insert multiple).
. How do you manually adjust column width and row height in Excel?
6
Home tab > Format > Column Width or Row Height.
7. How do you export a worksheet to PDF in Excel?File tab > Export > PDF.
. How do you inspect a worksheet for personal information in Excel?File
8
tab > Check for Issues > Inspect Document > Inspect > Remove All Document
Properties and Personal Information.
. How do you inspect a worksheet for accessibility and compatibility
9
issues in Excel?File tab > Check for Issues > Check Compatibility or Check
Accessibility.
1 0. How do you adjust page breaks in Excel?View tab > Page Break Preview
> drag page break lines.
1 1. How do you specify print titles to repeat on each page in Excel?Page
Layout tab > Print Titles > select rows and columns to repeat on each page.
1 2. Where can you change page margins and scaling for printing in Excel?
File tab > Print.
1 3. How do you outline data in Excel (assuming subtotals are already
present)?Data tab > Group > Auto Outline.
1 4. How do you format cell borders in Excel?Home tab > Font group >
Borders dropdown (square icon).
1 5. How do you use the Format Painter tool in Excel?Select cells with
desired formatting > Clipboard group > Format Painter (double-click for
multiple selections, Esc to stop).
1 6. What are tips for inserting an image in Excel for easier fitting?After
inserting, use options under Format, Arrange, and Align, such as Snap to Grid.
, 1 7. How do you sum values using destination cells with AutoSum?Click the
cell where you want the sum to appear > AutoSum.
1 8. How do you sum values using source cells with AutoSum?Select the
cells you want to sum > AutoSum.
1 9. How do you apply a table style to a cell range in Excel?Home tab >
Format as Table.
0. How do you convert an Excel table back to a cell range?Table Tools
2
Design tab > Convert to Range.
1. How do you remove duplicate values from data in an Excel table?
2
Select the table > Data tab > Remove Duplicates.
2. How do you sort data in Excel, including using a custom list?Select the
2
column > Data tab > Sort. For a custom list: Sort > Order > Custom List.
3. What are important considerations when inserting subtotals in Excel?
2
Ensure the data is not formatted as a Table and is properly sorted by the
column you want to subtotal. Specify the column, the function, and subtotal
appearance.
4. What does the COUNT function do in Excel, and what is its syntax?
2
Counts how many cells in a range contain numbers. Syntax: COUNT(cell #, cell
#...).
5. What does the COUNTA function do in Excel, and what is its syntax?
2
Counts how many cells in a range are not empty (contain anything). Syntax:
COUNTA(cell #, cell #...).
6. What does the AVERAGE function do in Excel, and what is its syntax?
2
Calculates the average of the numbers in a range. Syntax: AVERAGE(cell #, cell
#...).
7. What do the MIN and MAX functions do in Excel, and what are their
2
syntaxes?MIN returns the smallest number in a range; MAX returns the
largest. Syntaxes: MIN(cell #, cell #...) or MAX(cell #, cell #...).
C
Certification Exam
his Excel Certification Exam Study Guide is a comprehensive containing
T
215 questions and answers. It covers a wide range of essential Excel
topics, including data import/export, formatting, formulas (e.g., SUM,
AVERAGE, IF, VLOOKUP, INDEX/MATCH), charting, data analysis tools
(PivotTables, Slicers, What-If Analysis), and advanced features like macros
and workbook protection. It's designed to be a clear and professional
resource for exam preparation.
1 . How do you import files into Excel, specifying delimitation and column
formats?Data > Get External Data, then specify delimitation and column
formats.
. Where can you add comments to cells in Excel?Review tab > New
2
Comment.
3. How do you apply "Wrap Text" to a cell in Excel?Home tab > Wrap Text.
. How do you center text across a selection of cells in Excel?Home tab >
4
Alignment group > click the arrow in the bottom right corner (Alignment
Settings) > Horizontal alignment > Center Across Selection.
, . How do you insert a column in between existing columns in Excel?
5
Select the column to the right of where you want to insert, then right-click and
choose Insert. (Alternatively, select columns and use CTRL when selecting to
insert multiple).
. How do you manually adjust column width and row height in Excel?
6
Home tab > Format > Column Width or Row Height.
7. How do you export a worksheet to PDF in Excel?File tab > Export > PDF.
. How do you inspect a worksheet for personal information in Excel?File
8
tab > Check for Issues > Inspect Document > Inspect > Remove All Document
Properties and Personal Information.
. How do you inspect a worksheet for accessibility and compatibility
9
issues in Excel?File tab > Check for Issues > Check Compatibility or Check
Accessibility.
1 0. How do you adjust page breaks in Excel?View tab > Page Break Preview
> drag page break lines.
1 1. How do you specify print titles to repeat on each page in Excel?Page
Layout tab > Print Titles > select rows and columns to repeat on each page.
1 2. Where can you change page margins and scaling for printing in Excel?
File tab > Print.
1 3. How do you outline data in Excel (assuming subtotals are already
present)?Data tab > Group > Auto Outline.
1 4. How do you format cell borders in Excel?Home tab > Font group >
Borders dropdown (square icon).
1 5. How do you use the Format Painter tool in Excel?Select cells with
desired formatting > Clipboard group > Format Painter (double-click for
multiple selections, Esc to stop).
1 6. What are tips for inserting an image in Excel for easier fitting?After
inserting, use options under Format, Arrange, and Align, such as Snap to Grid.
, 1 7. How do you sum values using destination cells with AutoSum?Click the
cell where you want the sum to appear > AutoSum.
1 8. How do you sum values using source cells with AutoSum?Select the
cells you want to sum > AutoSum.
1 9. How do you apply a table style to a cell range in Excel?Home tab >
Format as Table.
0. How do you convert an Excel table back to a cell range?Table Tools
2
Design tab > Convert to Range.
1. How do you remove duplicate values from data in an Excel table?
2
Select the table > Data tab > Remove Duplicates.
2. How do you sort data in Excel, including using a custom list?Select the
2
column > Data tab > Sort. For a custom list: Sort > Order > Custom List.
3. What are important considerations when inserting subtotals in Excel?
2
Ensure the data is not formatted as a Table and is properly sorted by the
column you want to subtotal. Specify the column, the function, and subtotal
appearance.
4. What does the COUNT function do in Excel, and what is its syntax?
2
Counts how many cells in a range contain numbers. Syntax: COUNT(cell #, cell
#...).
5. What does the COUNTA function do in Excel, and what is its syntax?
2
Counts how many cells in a range are not empty (contain anything). Syntax:
COUNTA(cell #, cell #...).
6. What does the AVERAGE function do in Excel, and what is its syntax?
2
Calculates the average of the numbers in a range. Syntax: AVERAGE(cell #, cell
#...).
7. What do the MIN and MAX functions do in Excel, and what are their
2
syntaxes?MIN returns the smallest number in a range; MAX returns the
largest. Syntaxes: MIN(cell #, cell #...) or MAX(cell #, cell #...).
