Excel was introduced in 1985. Since then, it has become a standard business tool. In this thread, discuss the different features of Excel that make it a valuable business tool. In addition, discuss why Excel skills are so important in today’s job market.
One of the benefits of Excel is the ability to use formulas and functions. Discuss the differences between formulas and functions. Pick a function in Excel and discuss how that function is used to calculate results in your worksheets. Using the following scenarios, discuss how you would apply the Excel functions or create a formula to solve the scenario.
Please feel free to add other, unique mathematical challenge questions to the discussion, and let us work together to figure out how you might calculate it.
Excel provides many different ways in which to analyze data. Discuss the different methods by which you can analyze data in your spreadsheet. Assume the role of a Regional Manager for a retail organization looking at ways to analyze the large amounts of sales data you have. What are some of the ways Excel can be used to analyze this data? What are some examples of reports you might want to review? Using the concepts from this week, how will you determine where to start and what tools can you use within Excel to help you organize everything and make decisions?
This section lists options that can be used to view responses.
Excel 2010 provides the capability to exchange data with other applications. Discuss the different types of ways to exchange data between Excel and other Microsoft applications. Are there limitations to the exchange process? How can you exchange data with other applications that are not part of the Microsoft Office Suite? Discuss how you would exchange data with external data sources such as websites.
Discuss and compare the data consolidation and What-If analysis Excel tools. Under what circumstances would you use each of these tools? Do they both serve the same purpose, or is each one used under different scenarios? Give examples of how each can be used.
This section lists options that can be used to view responses.
This week will be an opportunity to share ideas and approaches to the Excel course project. What are some spreadsheet features you will use to make your spreadsheet professional, readable, reliable, and repeatable? Which spreadsheet functions and data analysis types do you think will be the most challenging to implement in this project? What questions do you have about those functions and types of data analysis?
This section lists options that can be used to view responses.
Discuss the differences between storing data in Access and Excel. Why would you use a spreadsheet over a database? At what point does it become more efficient to store your data in a database rather than a spreadsheet? If you are deciding whether to use a spreadsheet or database, what questions should you consider?
O 1) An Output Area (as it applies to Excel 2010) is defined as_______.
a range of cells containing results based upon manipulation of the variables in the input area
displays the name of a worksheet within a workbook
a range of cells containing values for variables used in formulas
displays the content of the active cell
Question 2. Question :
(TCO 1) Which of the following best describes a cell address?
Identifies the electronic “neighborhood” of a spreadsheet
Identifies a cell by a column letter and a row number
Searches for and displays similar labels as you type
Is a number that represents a quantity and can be the basis of calculations
Question 3. Question :
(TCO 1) Which of the following best describes the AutoComplete function?
Adds all the numbers in the row automatically
Searches for and displays any other similar label in the current column as you begin to type
Adds all the numbers in the columns automatically
Is a number that represents a quantity and can be the basis of calculations
Question 4. Question :
(TCO 1) Which of the following best describes the order of precedence as it applies to math operations in Excel?
Includes letters, numbers, and spaces
Controls the sequence in which Excel performs arithmetic operations
Is a software application used to create and modify business communications
Includes formulas, functions, and formatting
Question 5. Question :
(TCO 1) Which of the following best describes the result of using the fill handle on a cell containing a formula?
Cannot complete a sequence of dates in a column
Changes the background color of the selected cells to yellow
Copies the formula in the active cell to other cells and adapts it based upon the type of cell references in the original formula
Has two or more sub-commands related to the command
: 2 of 2
Comments:
Question 6. Question :
(TCO 1) Ribbon Commands with arrows indicate_____.
a shortcut to cell A1
there are two or more sub-commands related to the command
the next step in the process
directionality
Question 7. Question :
(TCO 1) If you had worksheets that contained the inventory of each room in your house, with a separate sheet for each room, all the sheets together would be a:
workbook.
ledger.
document.
journal.
:
Question 8. Question :
(TCO 1) When you paste copied data, Excel displays the Paste Options button:
in the status bar at the left of the screen.
in the next set of nonadjacent ranges.
on the toolbar.
in the bottom right corner of the pasted data.
Question 9. Question :
(TCO 3) Groups of related numeric values in an Excel worksheet are:
data point.
data roles.
categories.
data series
Question 10. Question :
(TCO 3) Which of the following best describes a column chart?
Displays data comparisons vertically in columns
A horizontal line that borders the plot area to provide a frame of reference for measurement
Contains graphical representation of values in data series
Contains the entire chart and all of its elements
Question 11. Question :
(TCO 3) Which of the following best describes the chart area?
Contains graphical representation of values in data series
Contains the entire chart and all of its elements
A vertical line that borders the plot area to provide a frame of reference for measurement
A horizontal line that borders the plot area to provide a frame of reference for measurement
Question 12. Question :
(TCO 3) To display similar data in a single column, with each series of data distinguished by a different color, use a:
stacked column chart.
pie chart.
3-D column chart.
box diagram.
Question 13. Question :
(TCO 3) A pie chart with one or more slices separated for emphasis is called a(n) ____________ pie chart.
expanded
exploded
displaced
clustered
Question 14. Question :
(TCO 3) In Excel, an area chart _______.
shows the high, low, and close prices for individual stocks over time
trends using two dimensions on a continuous curve
emphasizes the magnitude of changes over time by filling in the space between lines with a color
shows a relationship between two variables
Question 15. Question :
(TCO 3) After creating a chart, you can change the chart type by using _____.
Page Layout tab / Chart Options button
Design tab / Change Chart Type button
Data tab / Chart Changer tool
You cannot change the chart.
TCO 2) In Excel, a relative cell reference:
indicates a cell’s specific location and the reference does not change when you copy the formula.
contains both an absolute and a relative cell reference.
indicates a cell’s relative location from the cell containing the formula; the reference changes when you copy the formula.
occurs when a formula directly or indirectly refers to the cell containing the formula.
:
Question 2. Question :
(TCO 2) In Excel, a function can be defined as a _____.
predefined formula that performs a calculation
cell reference or a value
list of values and defined names as you enter data into a spreadsheet
set of rules that govern the structure and components for a formula
Question 3. Question :
(TCO 2) The rules for constructing a function can be referred to as the function’s _____.
synthesis
synergy
synchronization
syntax
Question 4. Question :
(TCO 2) Which of the following describes the SUM function?
Identifies the midpoint value in a set of values.
Calculates the total of values contained in two or more cells.
Calculates the arithmetic mean or average of values in a range.
Displays the lowest value in a range.
Question 5. Question :
(TCO 2) The _____ function returns a result based on a condition.
Student Answer: MAX
TODAY
FIND
IF
Question 6. Question :
(TCO 2) Which of the following best describes the COUNT function?
Tallies the number of cells in a range that are not empty.
Tallies the number of blank cells in a range.
Displays the current date.
Tallies the number of cells in a range that contain values.
Question 7. Question :
(TCO 2) In the arguments to an IF function, the condition that is evaluated to determine if it is true or false is also sometimes called a _____.
logical test
lookup value
breakpoint
table array action
Question 8. Question :
(TCO 2) If you enter an = and the first few letters of a function in the formula bar, Excel will display a drop-down list of functions beginning with those letters. This feature is called _____.
Syntax Helper
Formula AutoComplete
Formula Wizard
:
Question 9. Question :
(TCO 2) Which of the following best describes the HLOOKUP function?
The periodic interest rate, such as a monthly interest rate.
Calculates the periodic payment for a loan with a fixed interest rate and fixed term.
Looks up a value in a lookup table where the first column contains the values to compare with the lookup value.
Looks up a value in a lookup table in which the first row contains the values to compare with the lookup value.
Question 10. Question :
(TCO 2) One benefit of using range names in formulas is that _____ .
it directly or indirectly refers to the cell containing the formula
it contains both an absolute and a relative cell reference
it identifies the present value of the loan
if you copy the formula, you do not have to make the cell reference absolute
Question 11. Question :
(TCO 2) The PMT function is best described by which of the following?
It is the periodic interest rate, such as a monthly interest rate.
It is the total number of payment periods.
It is the present value of a loan.
It calculates the periodic payment for a loan with a fixed interest rate and a fixed term.
:
Question 12. Question :
(TCO 7) Which of the following is NOT an Excel tool used to improve productivity in developing consistently formatted workbooks?
Macros
Templates
Themes
INCORRECT Background
Question 13. Question :
(TCO 7) Before protecting a worksheet, you should _____ the cells that you want users to be able to edit.
format
hide
lock
unlock
:
Question 14. Question :
(TCO 7) Which of the following file formats can store macros?
xlsm
csv
xml
xlsx
Question 15. Question :
(TCO 7) To create a macro button, click the _____ tab, click Insert, then click Button in the Form Controls section of the Insert palette.
View
Data
Insert
Developer
week 3 quiz
TCO 4) _____ column and row headings enables you to keep these headings on the screen when scrolling through a large spreadsheet.
Formatting
Freezing
Resizing
Tiling
Question 2. Question :
(TCO 4) Which of the following best describes a page break?
Freezes only the top row
The sequence in which pages print
Indicates where data starts on a new printed page
Stops the printer from printing
Question 3. Question :
(TCO 4) To create a table from an existing range of data, _____.
click within the range, click the Table Tools Design tab, and then select Convert to Table
select any cell in the worksheet and then click Existing Range in the Tools group
click in a cell and on the Home tab, and click the Insert arrow in the Cells group
click the Insert tab and then click Table in the Tables group
Question 4. Question :
(TCO 4) Filtering the Cost column (field) to show only records greater than $10,000 is an application of _____.
a Date Filter
a Text Filter
the use of a table element as a formula
a Number Filter
Question 5. Question :
(TCO 4) To apply a red background color to cells for employees who have sales greater than $1,000, you can use _____.
a PivotTable
a multiple level sort
conditional formatting
a range name
Question 6. Question :
(TCO 4) Using Conditional Formatting to draw attention to cells that are blank _____.
displays a particular color based on the relative value of the cell contents to other selected cells
displays an icon representing a value in the top third, quarter, or fifth based on values in the selected range
helps locate where data may be missing
helps locate errors in cells quickly
Question 7. Question :
(TCO 4) The Subtotal command is located on the _____ tab.
data
formula
page layout
insert
Question 8. Question :
(TCO 4) _____ are created to organize and summarize data in PivotTables.
Categories
Types
Titles
Levels
Question 9. Question :
(TCO 4) To begin a filter based on a particular field, you drag the desired field to which area under Drag fields between areas below?
: Column Labels
Report Filter
Row Labels
Values
Question 10. Question :
(TCO 4) To create a calculated field, select _____ located on the PivotTable Tools Options tab.
Calculations
Insert Calculated Field dialog launcher
Tools
PivotTable Calculation
Question 11. Question :
(TCO 4) A Pivot Table Style controls all the following EXCEPT _____.
bolding
font colors
number format
shading colors
Question 12. Question :
(TCO 4) A quick way to display the Go to dialog box is to press the _____ key(s).
F4
Ctrl+Home
Ctrl+End
F5
Question 13. Question :
(TCO 4) The PivotChart Tools contextual tab includes all the following EXCEPT _____.
design
layout
format
type
Question 14. Question :
(TCO 4) Click the _____ to sort or filter the chart representation based on the values.
Axis Field arrows
Report Filter arrows
Values arrows
Legend Field arrows
Question 15. Question :
(TCO 4) Which of the following will NOT delete a PivotChart?
Go to the Home tab and click Cut
Right-click the chart and click Cut
Select the chart and then press Delete
Click and drag the PivotChart off the worksheet
week 4 quiz
TCO 8) Which of the following is one of the most common file types imported into Excel?
Text
PowerPoint
Web
Word
Question 2. Question :
(TCO 8) Which of the following is NOT a text file?
Tab-delimited
Comma-separated value
XML
Fixed-width
Question 3. Question :
(TCO 8) Which of the following is NOT a method for refreshing data?
Click the Refresh All command.
Save the Excel workbook.
Click the Refresh All arrow, then click Refresh.
Right-click in a range of data and then select Refresh.
Question 4. Question :
(TCO 8) The Convert Text to Columns Wizard allows you to choose the file type, such as Delimited or ________ width.
Fixed
Mixed
Divided
Text
Question 5. Question :
(TCO 8) Which of the following is a frequently used function to manipulate txt?
CONCATENATE
PMT
IF
DATE
Question 6. Question :
(TCO 8) To change the text string JANE doe to jane doe, use the ________ function.
: CONCATENATE
PROPER
UPPER
LOWER
Question 7. Question :
(TCO 8) Which of the following will produce the same result as the CONCATENATE function?
=A4&B4
=A4+B4
=A4B4
=A4,B4
Question 8. Question :
(TCO 8) If you want to send a letter to all your customers using Mail Merge, and you already have the customers’ names and addresses stored in an Excel file, you should choose _________ from the Select Recipients menu while performing the merge.
Type New List
Use Existing List
Import from Excel
Select from Outlook Contacts
Question 9. Question :
(TCO 8) The New Comment command is located on the ________ tab.
View
Review
Data
File
Question 10. Question :
(TCO 8) If first and last names are combined in a single column, you can split them into two separate columns using the ________.
Split Names Wizard
PivotTable feature
CONCATENATE function
Convert Text to Columns Wizard
Question 11. Question :
(TCO 8) The command to track changes is located on the ________ tab.
File
Data
Review
View
Question 12. Question :
(TCO 8) If you have ________ configured as your e-mail client, you can e-mail an Excel file directly from Excel.
Gmail
Hotmail
Yahoo
Outlook
Question 13. Question :
(TCO 8) Windows can search for files by author name or keywords that have been entered into ________.
a file name
the Trust Center
document properties
cell A1
Question 14. Question :
(TCO 8) All EXCEPT ___________ are Excel functions that help you manipulate text.
PROPER
UPPER
SUBSTITUTE
RESPOND
Question 15. Question :
(TCO 8) To encrypt a workbook with a password, click Info on the File tab, click ________, and then select Encrypt with Password.
Check for Issues
Protect Workbook
Manage Versions
Options
week 5 quiz
TCO 5) Excel’s multiple worksheet abilities can do all of the following EXCEPT:
create Pivot Charts.
group worksheets together to enter data.
display worksheets side by side.
create links among the worksheets.
Question 2. Question :
(TCO 5) In Excel, which of the following would you perform to group adjacent worksheets?
Click the first tab, hold Shift, and then click the last tab.
Click the first tab, hold Ctrl, and then click the last tab.
Right-click a worksheet tab, then select Select All Sheets.
Right-click a worksheet tab, then select Group.
Question 3. Question :
(TCO 5) When two windows of the same workbook are opened, Excel adds ________ to the title bar of the second window.
the letter B
the number 2
the letter A
the number 1
Question 4. Question :
(TCO 5) After saving the arrangement of windows, a workbook can be opened by opening the ________ file.
worksheet
workspace
workview
worklink
Question 5. Question :
(TCO 5) Which of the following external references is correct for a workbook and worksheet that have no spaces?
“[New.Jersey.xlsx]Qtr4!”B6
‘[New.Jersey.xlsx]Qtr4!’B6
[New.Jersey.xlsx]Qtr4!,B6
[NewJersey.xlsx]Qtr4!B6
Question 6. Question :
(TCO 5) Before you can create an external reference you must first __________.
select the cell or cells to hold the external reference.
open the destination workbook and all source workbooks.
switch to the source workbooks and click the cells.
click on Insert Function on the Formulas tab.
:
Question 7. Question :
(TCO 5) Which of the following is the proper use of a 3D formula?
=SUM(Yr1:Yr5!C7)
=SUM(Yr1-Yr5!C7)
=SUM(‘Yr1:Yr5’!C7)
=SUM(“Yr1:Yr5”!C7)
Question 8. Question :
(TCO 6) A one-variable data table must have at least ________ blank row(s) and ________ blank column(s) between the dataset and the data table.
two, two
two, one
one, two
one, one
Question 9. Question :
(TCO 6) Which What-If Analysis tool would be best at comparing the combined effects of various interest rates and down payments?
Scenario Manager
Goal Seek
One-variable data table
Two-variable data table
Question 10. Question :
(TCO 6) Which What-If Analysis tool would be best at determining how much you can borrow for a car loan while paying only $350 a month?
Scenario Manager
Solver
Data Table
Goal Seek
Question 11. Question :
(TCO 6) The Scenario Manager dialog box enables you to do all the following EXCEPT:
format scenarios.
add scenarios.
delete scenarios.
edit scenarios.
Question 12. Question :
(TCO 6) Before creating a scenario with Scenario Manager, it is important to know which cells contain the variables you want changed and the:
scenario names.
formatting of the results.
cells containing the formulas that generate the results.
final result.
Question 13. Question :
(TCO 6) What happens when you add a new scenario and generate a new scenario summary report?
The new report will be created on a new worksheet.
The new scenario will be included into the old report.
The new report will be added directly below the original report.
The new report will replace the original report.
Question 14. Question :
(TCO 6) Which of the following commands cannot be used on a constraint in the Solver Parameters dialog box?
Add
Format
Change
Delete
Question 15. Question :
(TCO 6) The initial results of a Goal Seek are:
: automatically entered into the worksheet.
inserted into a new worksheet.
presented visually in a chart.
shown in the Goal Seek Status dialog box.
ilab 1
Saddle River Marching Band (50 Points)
Saddle River Marching Band (50 Points)
ilab 2
First National Bank New Loan Report (50 Points)
First National Bank New Loan Report (50 Points)
ilab 3
Alice Barr Realty Analysis (50 Points)
Alice Barr Realty Analysis (50 Points)
ilab 4
Fundraiser Letter (50 Points)
Fundraiser Letter (50 Points)
ilab 5
Bruno’s Pizza Analysis (50 Points each)
Bruno Pizza Analysis (50 Points each)
ilab 6
Day Care Center (50 Points each)
ilab 7
Home Inventory (50 Points)
Home Inventory (50 Points)
course Project: Excel Project
OBJECTIVES
GuidelinesThe general instructions for this project can be found in the Week 6 Lecture material. The Week 6 Lecture describes the assignment and provides an overview of the project as a whole. There are no step-by-steps for the course project. The following are grading criteria for this project.
Data imported from SalesData.csv text file located in Course Project Materials in DocSharing.Organize the Data
Professional formatting follows the formatting guidelines we studied during the first week of class.
Documentation sheet added to provide author, purpose, and date and provide information about each of the sheets you will create as you analyze the data.
At least one calculated field added and copied to all cells in the worksheet.
Analyze the Data
Analyze the data in at least three different ways. Each form of Data Analysis should be provided on a separate, appropriately labeled worksheet. It is expected that each sheet will be professionally formatted and clearly documented with titles, comments, and explanation. Remove any extra sheets so your workbook is as compact as possible when you turn it in. Go back and review your labs for assistance in completing the different types of analysis.
The following are some examples of analysis you might wish to do:
Sort by discount level. Have discounts increased sales volumes? Is there any discount level that appears to be more effective than others?
Graph sales over time to see trends. Are there any peaks and lows in sales? Is there any time of year in which sales are highest? Lowest?
Pivot the data to see total sales by quarter, country, category, and salesperson. Are there any highs? Are there any lows that need to be addressed?
Subtotal the data. How are quarterly sales totals? Sales totals by salesperson? By Country?
Challenge Option: Perform What-If Analysis. What if prices were raised by a certain percentage with a slight decline in sales? What combination of price increases and decline in sales makes most sense?
Present your Conclusions in a Memo
Use the integration techniques we worked on in the course to develop a Memo (Word document) that includes data and graphs copied and pasted from your spreadsheet. You will turn in both your Recommendation Memo and your spreadsheet. (To help you formulate your report, there is a Word document named “ExcelProjectMemoTemplate” located in DocSharing – Course Project Materials. The yellow-lined information provides an explanation of what should be included in each section of the Memo.)
Your Memo will focus one paragraph on each of the Data Analysis sheets in your workbook. Explain the type of analysis you completed, the conclusions you drew, and a recommendation based on the analysis. (45 points)
You should include a final paragraph that explains three things you learned about using Excel for data analysis. (15 points)
GRADING RUBRICS
Category
Points
%
Description
Organize the Data
40
25%
Include imported data, professional formatting, a document sheet, and one calculated field.
Analyze the Data
60
37.5%
Data is analyzed in at least three different ways.
Present your Conclusions in a Report
60
37.5%
Integration techniques are used, explain the type of analysis, and include a final paragraph.
Total
160 100% A quality paper will meet or exceed all of the above requirements.
