Testing Saints' Knowledge: DATA ANALYTICS USING EXCEL

Which of the following answers best describes the Trim function in Excel?

• Removes odd characters that don't belong

• Removes the spaces between words

• Removes all spaces in the cell

• Removes extra spaces, leaves one

  space between words

SUBSTITUTE is a function to replaces text in a given string by matching. The syntax is

=SUBSTITUTE (text, old_text, new_text, [instance])

Which of the following statement is false?

• SUBSTITUTE finds and replaces old_text with new_text in a text string. Instance limits SUBSTITUTE replacement to one particular instance of old_text.

• Instance is NOT optional. if not supplied, all instances of old_text are replaced with new_text.

• Use SUBSTITUTE to replace text based on content. Use REPLACE when to replace text based on its location.

Extra headers and Inconsistent input are the problems in the concatenated spreadsheet you have seen normally?

• True

• False

What is the difference between a table and a range in Excel? Select four that apply.

• In tables, you can add many types of totals for each column without writing any formulas.

• Formulas in table columns are automatically applied to new rows.

• Formulas in tables refer to other columns by name and not by regular Excel reference.

• Tables are formatted with alternate colours by default.

• A range can be converted to a table, but a table cannot be converted to a range.

You have a pivot that is based on an Excel table. You need to add new rows to the Excel table and want to be able to see the values for these new rows in the PivotTable. What should you do?

• Create a new pivot table.

• Add rows anywhere in the table, and it will automatically update the pivot to include the new values.

• Add rows anywhere in the table, and refresh the pivot table.

• You must add the new rows only after the last row and refresh the pivot table.

You are asked to display a trend for each product by year. What should you do?

• Change the chart type to pie chart.

• Modify the chart to have the product categories as Legend and years as horizontal axis.

• Add summary far, and insert a new column chart.

• Transpose the table, and create the chart from scratch.or each ye

You have collected the following data and want to use a chart to display it. Which chart should you use to display it?

• Line Chart

• Histogram

• Pie Chart

• Scatter Plot

Instead of using the classic sparkline to represent the trend, we can apply the 'Win and Loss' design on the same data that we are using. A Win Loss sparkline .

• looks only at values over a certain range

• focuses on max and min overall values

• display a chart inside a cell that shows the performance over time

• shows whether each value is positive or negative instead of how high or low the values are

In the screenshot above, the colour formatting is:

• Appropriate, because you can see the highest and lowest numbers over all items (pants, socks...)

• Inappropriate, because it is irrelevant to consider both sales and cost price at the same time

• Appropriate, because having unrelated numbers on the same scale helps you better understand the trends in the separate prices

• Inappropriate, because colours do not help you understand trends

What is the useful tip when we want to display a dashboard?

• Try to avoid too many colors in your dashboard since that is distracting

• Consider on what kind of medium the dashboard will be used in the end correct

• A two row table works best for a dashboard