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