Font size
WorksheetsDATA ANALYTICS USING EXCEL
Total questions: 15
Worksheet time: 18mins
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
You would use a scatter plot when you want to...
explain a relation between variables
compare similar values
show a trend
want to compute total quantities
What is the function of PROPER?
Removing extra spaces in a cell whether at the beginning, trailing or anywhere between the words.
To replace specific text in a text string Removing extra spaces in a cell whether at the beginning, trailing or anywhere between the words.
Capitalizes the first letter in a text string and any other letters in text that follow any character other than a letter. Converts all other letters to lowercase letters
SUM ( ) is a function that allow to make additions?
TRUE
FALSE
Which symbol must all formula begin with?
=
+
(
@
What would be a correct formula for SUM in excel?
answer choices
=SUMB3+B9
=ADD(B3:B9)
SUM(B3:B9)
=SUM(B3:B9)
