Excel Formatting and Functions Quiz

Podcast

Play an AI-generated podcast conversation about this lesson

Questions and Answers

What is the first step to sort a table in ascending order?

Select a cell in the column to be sorted (correct)

Access the custom sort dialog box

Filter the column first

Click the DATA tab on the toolbar

Which button should be clicked to sort a table in descending order?

Sort smallest to largest

Sort A to Z

Sort by filter options

Sort largest to smallest (correct)

How do you initiate a custom sort on a table?

Use the DATA tab options directly

Click the sort & filter button to access the menu (correct)

Click 'sort smallest to largest' on the filter menu

Right-click on the column header

What happens when you click the 'add level' button in the custom sort dialog?

You can add a second sort criterion Signup and view all the answers

Which option is used to sort a table from smallest to largest using autofilter?

Sort smallest to largest Signup and view all the answers

Which action do you take to center a title across a selection in Excel?

Right-click the selection, click 'format cells', and choose 'center across selection' Signup and view all the answers

What is the correct syntax for the VLOOKUP function based on the information given?

=VLOOKUP(F9, $1$3:$M$6, 2) Signup and view all the answers

What is the purpose of clicking the 'accounting number format' button in a cell?

To convert the cell data into a currency format with two decimal places Signup and view all the answers

Which steps are necessary to add a new conditional formatting rule using icon sets?

Select the range, click 'conditional formatting', then 'new rule', and choose 'icon sets' Signup and view all the answers

What should be done to create calculated fields in a table?

Double-click the field to use and enter an IF function Signup and view all the answers

What is the first step to insert a total row in an Excel table?

Click anywhere in the table and display the table tools design tab Signup and view all the answers

Which statement is true regarding using a lookup table?

You must select a range and format cells to create a table array Signup and view all the answers

What is the purpose of using the format painter button in Excel?

To copy formatting from one cell to another Signup and view all the answers

What function can be used to capitalize only the first letter of a text string in Excel?

=PROPER(A11) Signup and view all the answers

Which step should be taken before importing data to ensure unnecessary columns are removed?

Transform the data. Signup and view all the answers

When importing data into Excel, what should be done to prevent a new sheet from being created?

Select 'Existing worksheet' before loading. Signup and view all the answers

What is the purpose of the CCONCATENATE function in Excel?

To combine text from multiple cells into one cell. Signup and view all the answers

What must be done to format a table after importing data into Excel?

Select banded columns and header row. Signup and view all the answers

What is the first step to query a table using autofilter?

Click the filter button to display the filter menu for the desired column Signup and view all the answers

How can filters be removed in a table?

Clicking the clear button in the DATA tab Signup and view all the answers

What is the purpose of the custom filter option in autofilter?

To specify advanced filtering criteria Signup and view all the answers

What is the purpose of the 'copy to another location' option in advanced filtering?

To relocate filtered records to a different area of the worksheet Signup and view all the answers

Which function would you use to calculate the average of records based on a specific criterion?

DAVERAGE Signup and view all the answers

What happens when 'convert to range' is selected?

The table structure will remain but no longer behave as a table Signup and view all the answers

Which action is necessary to open a template-based file in Excel?

Double-click the template file Signup and view all the answers

What is one of the first steps to import data from a text file into Excel?

Choose the cell where data should be placed Signup and view all the answers

Which function is used to trim spaces from data in a specific cell?

TRIM Signup and view all the answers

In which step should the Banded Rows option be unchecked when formatting imported CSV data?

During the data import process Signup and view all the answers

What is the final action required to close the Power Query Editor after deleting a column?

Click 'Close & Load' to keep changes Signup and view all the answers

Which of the following steps is NOT part of the process to copy records to a different location using advanced filtering?

Click 'Clear' in the action area Signup and view all the answers

What is the main purpose of using the COUNTIF function?

To count records that match a particular criterion Signup and view all the answers

What is the first step to import data from a webpage in Excel?

Click the first cell on the worksheet Signup and view all the answers

Which button must you click to transpose data in Excel after copying it?

Paste Signup and view all the answers

In the Convert Text to Columns Wizard, which option must you select for fixed width separation?

Fixed width Signup and view all the answers

What action should you take to replicate formulas in Excel?

Drag the fill handle down Signup and view all the answers

When saving a smart art graphic template for future use, which option should you select?

Excel Template Signup and view all the answers

What key combination is used to copy data from Word to Excel?

CTRL+C Signup and view all the answers

To import data into an existing worksheet, which option must be selected in the Import Data dialog box?

Existing worksheet Signup and view all the answers

Which of the following is NOT a source of data that can be imported into Excel?

Word Document Signup and view all the answers

What should you do after making desired selections in the Power Query Editor?

Click Close & Load To Signup and view all the answers

What is the purpose of displaying the Data tab before converting text to columns?

To access the Text to Columns button Signup and view all the answers

Study Notes

Formatting Cells

To center a title across a selection, select the range, right-click, choose "Format Cells", then click "Alignment", select "Horizontal", click "Center across selection" and confirm with "OK".
To format a table array area, select the range, right-click, choose "Format Cells", then click "Alignment", select "Horizontal", click "Center across selection" and confirm with "OK".
Using the Format Painter button, copy formatting from a selected cell to other cells.

Using VLOOKUP Function

The function =VLOOKUP(f9, $1$3:$m$6, 2) searches for the value "f9" within the range "1113:mmm6" and returns the corresponding value from the second column of that range.

Adding Calculated Fields to Tables

Use the "accounting number format" button to display numeric data in the selected column as a dollar amount with two decimal places.
Use the formula =IF([Specialty]="Loans",[Account Values]*0.0025,0) within a cell to create a calculated field based on the value of a specific "Specialty" cell.

Conditional Formatting

Select the range to apply conditional formatting.
Click the "Conditional Formatting" button and choose "New Rule".
Select "Icon Sets" and choose a desired icon style.
Enter values for each icon in the "Value" box.
Click "OK" to display icons based on cell values.

Working with Tables in Excel

To insert a total row, select a table cell, go to the "Table Tools Design" tab and check the "Total Row" box.
Use the arrow on the right side of the total row cell to select the desired function (e.g., "Sum").
To sort a table in ascending order, select a cell in the column to be sorted and click the "Sort & Filter" button. Then, select "Sort A to Z."
To sort a table in descending order, select a cell in the column to be sorted, go to the "Data" tab, and then click the button named "Sort Largest to Smallest".
To custom sort a table, select a cell and choose "Custom Sort" from the "Sort & Filter" menu. Click the "Column Sort By" button to choose the first sort level and select desired sort options. Click "Add Level" to add another sort level and repeat the process.
To use AutoFilter, click the "Filter" button in the desired column. Check or uncheck fields to filter. Click "OK" to apply.
To remove filters, go to the "Data" tab and click the "Clear" button.
To search a table with AutoFilter, click the "Filter" button, click the search box and type the desired search string. Click "OK" to perform the search.
To use custom criteria with AutoFilter, click the "Filter" button and choose "Number Filters" followed by "Custom Filter." Select the desired options and click "OK".
To turn off AutoFilter, click the "Filter" button again.

Using Criteria and Extract Ranges

To use a criteria range for querying, enter your criteria data in desired cells and click the table to make it active. Choose "Advanced" from the "Sort & Filter" menu and select "OK" to hide records that don't meet the criteria.
To extract records that meet criteria, click the table to make it active, choose "Advanced", click "Copy to another location", and then select "OK".
The filtered results will be copied to the selected location.

Database Functions

Use =DAVERAGE(a8:i11,"Supervisor Review",o12:o13) to calculate the average based on the "Supervisor Review" criteria for the specified range.
Use =DCOUNT(a8:i22,"Supervisor Review",m2:m3) to count the number of records satisfying criteria in the specified range.

SUMIF and COUNTIF Functions

Use =SUMIF(d9:d22,"Checking/Savings",e9:e22) to sum values in cells e9:e22 based on a condition in cells d9:d22, where the condition is "Checking/Savings".
Use =COUNTIF(d9:d22,"Loans") to count the number of cells in the range d9:d22 that contain the word "Loans".

Summarizing Data

To convert a table to a range, right-click within the table, point to "Table" and choose "Convert to Range". Confirm with "Yes" in the dialog box.

Module 4.1: Advanced Spreadsheet for Accountants

Creating Templates:
- To save a template, click the "Save" button, type the desired file name, choose "Excel Template" for the "Save as Type", navigate to a desired location and click "Save."
- To open a template-based file and save it as a workbook, navigate to the template's location using File Explorer, double-click the template file. Then, click "Save", type the desired file name, choose a location, and click "Save."
Importing Data:
- From a text file:
  - Click the first cell to contain the data, go to the "Data" tab, click "From Text/CSV".
  - Double-click the text file to preview the data, click "Load" and choose "Load To".
  - Select "Existing worksheet" and click "OK".
  - Format the imported data by removing "Banded Rows" and "Header Row" from the "Table Tools Design" tab.
  - Use the Trim() function to remove extra spaces in cells.
  - Copy and paste only the values using CTRL+C and selecting "Values" from the "Paste Options" area.
- From an Access Table:
  - Click the first cell to contain the data, go to the "Data" tab, and choose "Get Data" -> "From Database" -> "From Microsoft Access Database".
  - Double-click the Access file, select the desired table in the Navigator dialog and click "Edit" to open Power Query Editor.
  - Remove unwanted columns using "Remove Columns".
  - Rearrange columns by dragging them to desired positions.
  - Click "Close & Load" and choose "Close & Load To", then select "Existing worksheet" and click "OK."
  - Format the data by removing "Banded Rows", "Header Row" and adjusting the column widths.
- From a webpage:
  - Click the first cell to contain the data, go to the "Data" tab, and click "From Web."
  - Paste the webpage address in the URL box and click "OK".
  - Select the table within the Navigator dialog box.
  - Click "Edit" to open Power Query Editor.
  - Make desired selections and click "Close & Load" and then "Close & Load To."
  - Select "Existing worksheet" and click "OK" to import.
- Copy and Paste from Word:
  - Open the Word file, select the desired data, and press CTRL+C to copy.
  - Switch to Excel, click the desired cell, and press CTRL+V to Paste.
- Transpose Columns and Rows:
  - Select the range to be transposed, press CTRL+C to copy.
  - Click the desired cell and click the "Paste" arrow, then select "Transpose" from the Paste gallery.
- Convert Text to Columns:
  - Select the cells to be converted and go to the "Data" tab, click "Text to Columns."
  - Choose "Fixed width" and click "Next."
  - Click "Finish" to separate the data.
- Replicate Formulas:
  - Click the formula to be replicated and drag the "Fill Handle" down through the end of the data to replicate the formula.
Working with SmartArt Graphics:
- Click the "Insert SmartArt Graphic" button, choose the desired type of SmartArt from the left pane, and select the layout.
- Click "OK" to insert the graphic.

Creating and Using Templates

Create a new template by saving an existing workbook as an "Excel Template" using the "Save As" feature.
To use a template go to the "File" Tab -> "New" -> "Personal".
Then select the desired template file from the list.

Importing Data from Various Sources

Excel can import data from text files, CSV files, HTML files, and webpages by using the "Get Data" and "From Web" buttons.
Data imported from a webpage requires using Power Query Editor to make selections and transform the data.
Data can be imported from Access tables, and transformed within Power Query Editor before loading into Excel.
Data can be copied from Word and pasted to Excel.
Data can be transposed from rows to columns and vice versa.
Data can be converted from text to columns using the "Text to Columns" feature.
Formulas can be replicated using the "Fill Handle" feature.

Importing Data from Text Files

Process:
- Click the "Get Data" button.
- Select "From File" -> "From Text / CSV."
- Choose the file and preview it.
- Click "Load" and choose the "Existing Worksheet" option.
- Format the data as needed, including removing "Banded Rows" and "Header Row."
- Remove extra spaces in cells with the Trim() function.
- Paste only the values if necessary.

Importing Data from Access and Webpages

Accessing Data:
- Use the "Get Data" -> "From Database" -> "From Microsoft Access Database" to import from Access.
- Use the "Get Data" -> "From Other Sources" -> "From Web" to import from a webpage.
Transforming Data:
- Use Power Query Editor to format, transform, and filter the data before loading it into Excel.
- Use "Remove Columns", "Add Column" and "Transform" features in Power Query Editor for various transformations.
Loading Data:
- Choose the loading option "Close & Load To" and paste the data into the existing worksheet.

Importing Data from Word and Using Transpose

Copying and Pasting:
- Copy data from Word using CTRL+C and paste it into Excel using CTRL+V.
Transposing:
- Use the "Paste Options" area, click the "Transpose" icon to transpose the data.

Creating Advanced Spreadsheets with Features

Using Formulas:
- Use formulas like SUMIF, COUNTIF, DAVERAGE, & DCOUNT to perform calculations based on specific conditions and criteria.
Using TRIM() function:
- Remove extra spaces in cells by utilizing the TRIM() function.
Replicating Formulas:
- Use the "Fill Handle" feature to replicate formulas across cells.
Organizing Data:
- Use "Tables" to organize data into a structured format which allows for easy sorting, filtering, and summarizing.
Conditional Formatting:
- Use "Conditional Formatting" to visually highlight cells based on certain criteria by applying colors or symbols.
SmartArt Graphics:
- Insert SmartArt graphics to visually represent information.

Working with Tables

Formatting:
- Use "Table Tools Design" to modify the appearance of a table by adding "Banded Rows" and "Header Row."
Adding Total Rows:
- Insert a "Total Row" to calculate summary statistics in the table.
Sorting, Filtering, and Searching:
- Use "Sort & Filter" tools to rearrange and sift through data based on various conditions.
Using AutoFilter:
- Use "AutoFilter" to quickly filter and display only specific data in a table.
Converting to Range:
- Convert a table to a regular range by using the "Convert to Range" option.
Additional Tips:
- Adjust column widths to improve the readability of data.
- Explore different Excel features to enhance spreadsheet functionality, including charts, sparklines, and pivot tables.

Studying That Suits You

Use AI to generate personalized quizzes and flashcards to suit your learning preferences.

Description

Test your knowledge on Excel cell formatting, VLOOKUP function, and calculated fields in tables. This quiz covers alignment options, formatting techniques, and practical usage of formulas in Excel. Perfect for anyone looking to enhance their spreadsheet skills.