Semester 2 biz info cards #1
In a 3-D cell reference, which symbol is used to separate a worksheet name from a cell number
!
steps for creating a scenario.
1. Data 2. Forecast 3. Scenario Manager 4. Values for the changing cells
What are the steps for inserting an internal link
1. Type ✔ =$! into a cell. 2. Then, select the✔ worksheet that includes the cell you wish to link to. 3. Next, click the cell you wish to reference. 4. Finally, press r✔ Ctrl + Enter.
The watch window
A floating window that allows you to watch the results of formulas change as you change data. You can add certain cells, it will show the location, value, and how many functions it contains. A 3-D formula causes cells in a worksheet to change when edits are made to other worksheets
What are the steps used to track changes in a shared workbook
Click the Review tab Click Track Changes and click Highlight Changes In the dialog box, specify how to track the changes and click OK.
What are the steps for locating the Data Validation dialog box?
Data tab, forecast group, select what if
The share options
Go into backstage view and choose the share tab and choose the people, can be through mail or onedrive. allow others to edit your file via OneDrive
se the drop-down menu to complete the steps for using the Goal Seek feature.
Go to the data tab, in the forecast group select goal seek from the drop-down what-if analysis menu Specify the cell that contains the formula to solve and enter the desired result. Specify the cell that contains the value to adjust
Restrict changes
Highlight cell, format cells, protection tab in format dialog box, go from locked to hidden, review tab, and protect either sheet or workbook, type in password. To hide a worksheet - do right click and hide To protect workbook from opening- encrypt with password
INDEX function
INDEX(Data,rownumber,columnnuber)
How can data consolidation be helpful
It can summarize data from worksheets in different workbooks. It helps to summarize data from worksheets that are not identical. It combines data from multiple sheets to create one concise table
What is the purpose of a forecast worksheet?
It uses historical data to predict one trend line.
What happens to a data table when the formulas or variables used to create it are changed?
It will automatically update immediately because the formulas are linked.
Change tracking
Marks up certain elements within the work book anytime someone makes a change to it. Any entering modifying deleting cell data will be tracked. Does not track formatting, commenting and such is only tracked in the history worksheet.
Microsoft accounts and onedrive
One drive benefits- work anywhere, share with anyone, link accounts
Go to specials dialog box
Pick cell, home tab, editing group, go to specials, check box for go to specials. You can check precedents and dependents at the same time in one work sheet.
Protect workbook command
Protect structure and windows dialog box. Prevent the addition of sheets and opening up new windows.
Excel online
Requires office 365 subscription, Get access to most capabilities, can view a list of other users on that file.
Which tab should you click if you want to access the Show All Comments option in a worksheet
Review
Highlight changes dialog box
Review tab in changes group. When, who, where changes were made or to list all changes.
Document Inspector
Scan workbooks for metadata, file info area, to remove any metadata that should be private
What is the function for displaying differences between two or more scenarios side by side?
Scenario Summary
How to make a comment Enable change tracking Compare and merge workbooks
Select cell, review tab, comment, new comment. Changes group, track, highlight, do track changes while editing, it will share the work book, select when, who, where etc, More commands, view all commands, find compare and merge workbooks option, (allows you to select all documents, click ok, and it will then merge them).
Which section of the Data Validation dialog box allows a user to establish criteria?
Settings
Trace arrows
Shows the relationship between formulas and the cells they refer to (precedent and dependents).
What is the purpose of saving a macro to your Personal Macro Workbook?
The saved macros are available to be added to any workbook.
When working with a shared worksheet, how do you know it's shared
The word "Shared" appears after the workbook title in the title bar
What are benefits of using scenarios? Check all that apply.
They can compare multiple variables. They can predict what is needed to meet a goal. They allow a user to summarize and compare data.
The Accessibility Checker
Used to check the workbook for accessibility issues, file, backstage, info, accessibility. (this is for people with disabilities
What program in Excel allows you to edit, record, and write your own macros?
Visual Basic for Applications
3D map layer pane
Where you plot the data,
Work sheet and workbook element protection
Workbook protection- applies to the particular workbook and its entirety, you can make sure people don't add or delete worksheet, re format etc. Worksheet- lock cells, hide cells formulas, enable, disable formatting, interacting with pivot tables. Protections tab of the format cells dialog box
3D Map
a globe background
What-if analysis
process of changing values in cells to see how those changes affect the outcome of formulas in the worksheet Scenario manager goal seek data tables
To ensure that unauthorized users do not edit your Excel files, what should you use?
protect sheet with password
Data Validation Dialog box
restrict data entries that can go in a specific cell. What are the steps to adding a note in the Data Validation dialog box Go to the data tab on the ribon In the Data Tools group, you will select data validation, , and the dialog box will open. o add a note for anyone viewing or editing the table, select insert message in the box Complete the instructions and click OK.
Dependent cells
result is displayed off of info that is in another location
Using trace arrows
select cell, select formulas tab (auditing), trace precedents,
Insert 1 variable table
select range, data tab, what if analysis, data table, input cell number, ok.
If the VLOOKUP function is used to find an approximate match, what will it return if there is no exact match
the largest value that is less than the lookup value
What does the Goal Seek Status box include? Check all that apply.
the value for a cell a cell to be changed the cell that causes the change
For which task is the Goal Seek feature most helpful?
working backward to identify the input values for a specific result
What are the steps for grouping worksheets in a workbook?
1. Click on the first worksheet's tab at the ✔ bottom of the open workbook. 2. Then, select all the open tabs by holding down the ✔ Shift key and select the last worksheet, OR select individual sheets by holding the Ctrl key and clicking the sheets you wish to group. 3. These actions automatically group the worksheets. Then, apply all formatting changes to the first worksheet, and it will be applied to all in the group.
What are the steps for inserting a 3-D reference?
1. Enter the formula up to the point of needing a reference. 2. Activate the first worksheet. 3. While holding the✔ Shift key, activate the last worksheet. 4. Select the cell or range containing the values the formula should refer to. 5. Complete the formula by pressing these two buttons: Alt✔ Ctrlt and✔ Enter
What are the steps for consolidating data from multiple worksheets
1. Select the range of data on the first worksheet you wish to consolidate. 2. Go to the Data tab on the ribbon and select Data Tools. 3. Then, select ✔ Consolidate Data ValidationSortText to Columns and the dialog box will appear. 4. Choose the command✔ functionrangetab in the drop-down. Then, select the first group of data and press Enter. 5. To add from more worksheets, select Arrange AllFreeze PanesNew Window✔ Switch Windows from the View tab. 6. To include more references, click ✔ Add
Which external reference is formatted correctly
='[My_Sales.xlsx]Quarter1'!$C$12
Match function
=MATCH(lookup_value, lookup_array, [match_type])
Internal reference format
=Quarter1!C12
insert 2 variable table
=b3+b3*b4-b3,
Data validation critera
Allow drop down menu, 7 types of criteria,
The evaluate formula dialog box
Auditing utilities, displays formula, excel runs calc and changes the argument or calc to the result.
What must a 3-D reference include
Cell data from all sheets used must be in the exact same cell.
Sparkines tool contextual tab
Change type, color, group and un group,
Which key should you press and hold to select and open multiple files at one time?
Ctrl
Precedent cells
Feed data, group of cells with raw data with a function that is adding those cells.
Protect work book options
In the file option of the backstage view. Encryption that is protected. Protects sheets, access, and what not. Can add a key.
How to create sparklines
Insert tab, sparklines dialog box.
What steps can be used to trace errors in a worksheet
Select the cell with the error. Go to the formulas tab in the formula auditing group select trace error from the error checking drop-down menu
the steps for using the Scenario command.
Select the cells you want to use in your scenario. Go to the data tab In the forecast group select what-if analysis and click scenario manager Click Add and fill in the name and add comments if you wish. o compare, you will want to create an original version Add a second scenario, changing the values from the first. click show to view the values in each scenario
What does the #NULL error in a cell mean?
The answer does not exist.
The compare and merge workbooks command
To include the work others contributed. Lets you merge two or more copies into one file, they must be from the og copy. File options to enable it.
One-variable data table A data table is a range that displays what?
a data analysis tool that provides various results based on changing one variable, the results of changing certain values in one or more formulas
Which symbol indicates that a cell contains a comment
a red triangle in the upper right corner
What is a scenario in Excel 2016?
a set of input values that produce a result
Which type of data table can predict projected growth using both projected increases in sales and expenses?
a two-variable data table
Data Validation - error alerts
cannot enter valid data, shows a message, restricts, good tool
What is the default security setting for macros?
disable all macros with notification
Sparklines
display relative values of cell data over time. Line type- for trends in data that changes columns- showing diff values in a row that relate win/loss- entries that are positive v negative entries What are miniature charts displayed in a cell or cell range to illustrate patterns or trends?
Go to dialog box
double click black dashes to access.
Which tools exist on the 3-D Map Layer pane? What is the launching of a 3-D map called?
filter options layer options category visualization data value visualization a tour
Where does a forecast worksheet appear once it has been created?
in a separate worksheet within the workbook
What types of forecast worksheets are available in Excel? Check all that apply.
line graph column chart
VLOOKUP Function
looks up a value in a vertical lookup table and returns a related result from the lookup table. =LOOKUP(B1,Data,3) . Column labels in the top row. VLOOKUP(lookup value, table array, col index num, range lookup)
Protect sheet command
select the actions of the users that will have acess to the document and what they will be allowed to preform. You should have a password
What is the purpose of the Circle Invalid Data command?
to circle data that is invalid after it has been entered
Input message tab
to inform a user that data validation has been established
Where is the Share button located for quick access?
top right corner of the ribbon
What are two options available for highlighting changes in the Highlight Changes dialog box?
within a specific time period or by a specific user
