1. Configure Excel to disable all macros in the workbook without notification.

File tab, options, trust center, trust center settings, macro settings then
disable all macros without notification

2. Configure the workbook so that users have to enter the password,

"Wendys" before they can make structural changes to the workbook.

File, info, protect workbook

-DO NOT encrypt with a password because then they cannot even open it,
we want to protect workbook structure

3. Configure Excel to automatically evaluate formulas only when a workbook

is saved, and not each time the data changes.

i. Prevent formulas from being automatically evaluated when data changes.

Formulas should not be evaluated when your workbook is saved.

File, options, formulas then you will see under calculation options it says
automatic but that means every time there's changes to a calculation the
data will change, change from automatic to manual
i)You would do the same as above but uncheck the check box under manual

4. Configure Excel so that you can edit content using Spanish (Spain) as the
editing language.

File, options, language then you wil see the second ribbon that says "office
authoring languages and proofing, press add a language, press on Spanish
from spain, then press ok, press set as preferred "

Protect workbook: file tab then click info and press protect workbook

1. On the "Student Enrollments" worksheet, in cells D4:F7, use the Fill

Series feature to complete the enrollment growth rate of 100 students
per year (Student enrollment and needs fill-series formatting)
Highlight cell B4 to C7, then bottom right corner there's autofill so drag
bottom right corner over and pattern copies, if it asks to fill without
formatting after you drag over theres an icon in the bottom right that are the
auto fill options and you can press that and select fill without formatting

2. On the "Subtotals" worksheet, use the Data Subtotal feature to calculate

the total number of lessons and total due by instructor. Display the total
lessons and total due for all instructors below the data. (Instructor; their
number of teaching and the due payment)

Highlight table, go to data, outline, subtotals then AT EACHCHANGE IN

instructor USE FUNCTION sum(because you want to find the total number of
lessons) ADD SUBTOTALS TO number of lessons and check summary below
data, then press ok, outline, subtotals, AT EACHCHANGE IN instructor USE
FUNCTION sum(because you want to find the total number of lessons) ADD
SUBTOTALS TO total due and uncheck replace current subtotals

3. On the "Weekend Classes" worksheet, change the data validation error

message for cells E4:E24 to "Please enter a number between 5-10" (data
validation between E4:E24)

To quickly highlight the range to the left of the formula bar type in E4:E24,
enter to highlight that range, data tab, data validation drop down, data
validation, make sure in settings the minimum is 5 and maximum is 10, go to
error message tab and type in "Please enter a number between 5-10"

4. On the "Availability" worksheet, remove duplicates from column A.

Select column A by pressing A on top, data tab, remove duplicates, press

continue with current selection

5. On the "Availability" worksheet, modify the conditional formatting rule

applied to the cells in column B so that it applies Bold Italic font and no fill
color to the cells that contain "Every day". (conditional formatting using
highlighted text)

Highlight column B, home tab, press conditional formatting drop arrow, clear
rules, clear rules from selected cells, conditional formatting, highlight cell
rules, text that contains, type in "Every Day", drop down menu for format
and press custom format, change font style to bold italic, press fil tab to
ensure there is no fill, then press ok

6. On the "Profit Analysis" worksheet in cells D4:D10, create a conditional

formatting rule that uses a 3-Color Scale format style to display the minimum
in Yellow, the midpoint in Light Green, and the maximum in Green.
(conditional formatting three conditions in colors)

Highlight cell range, home tab, conditional formatting, new rule, change
format style to 3-color scale, change lowest value to yellow, change midpoint
to light green, change maximum to green, press ok

When trying to return an exact match what are the formulas you could use

-when talking about exact match its either vlookup(vertical lookup in the
columns) or hlookup(horizontal lookup)

1. On the proctors worksheet, in cell C4, enter a formula that returns the
sign-up phone number from the "sign-up information" range, using an exact
match for the "Exam" in column B. (hlookup)

Lookup value is the value you are trying to locate, table array is the table you
want to search for that value in, row_index_num is the row the answer you
want is in, range_lookup type in false to get an exact match

what is a macro

-takes a recording and then you could press in a key or code and it would do
it for you

2. From the "Proctors" worksheet, create a macro named, "Header". Store the
macro in the current workbook. Configure the macro to display,
"Massachusetts Testing Center" in the center headerMass of the active
worksheet. (creating macros)

View tab, macros, record macro, then type in name, ok, it starts recording so
do whatever and make sure to go back to normal view when done, hit stop
recording
3. On the "Wendy's" worksheet, In cell O17, enter a formula that returns the
average "Sales Revenue" for stores located in Montreal and are considered
"Full" restaurants. (averageifs)

-"And" makes it think it could be and function, but because it has average
and theres two conditions it is "average ifs" funcition

4. On the "Wendy's" worksheet, Use the error checking features to locate the
formula that is inconsistent with those around it. Correct the error.
(correcting formulas)

Formulas tab, error checking, error checking, copy formula from above

5. On the hours worksheet, in cell B14, use a function to calculate the

weekday that the swim school begins. (WEEKDAY)

=weekday(enter cell you are trying to convert to weekday)

what is the NPER function

NPER is an Excel financial function that calculates the number of

payment periods for a loan or investment based on equal periodic
payments and a constant interest rate (total # payments needed to
pay off the loan)

what is the PMT function

calculates the payment for a loan based on constant payments and

a constant interest rate (payment due at the end of each month

7. On the "Car Loan" worksheet, in cell B8, use a function to calculate the
number of months needed to pay off the loan. (Using NPER total number of
payments)

Type in =Nper, then enter the rate which is the annual rate B5 divided by 12
because we want the monthly rate, then enter the payment amount due at
the end of each month B6, then type in the principle value which is the loan
amount B4
7. what if you have then per, how do you find the payments due at the end
of each month (using of PMT, the monthly payment)

Type in =pmt(, then do the rate B13 divided by 12 because you are
calculating monthly, then enter nper which Is the total number of payments
due B14, then type in principle value which is B12)

8. Add cells A2:F5 on the "NHLplayers" worksheet to the Watch Window=

(application of watch window)

Highlight cells, Formulas, watch window, add watch

9. In cell B6 on the "About last week" sheet, add a formula that uses a single
logical function to display TRUE if all of the players scored at least one goal
in the last week and FALSE if even one player did not score a goal last week.
(to be viewed)

when is OR function used

the or function is used when it is looking if one or both sets of criteria are
true

when is NOT function used

used when the statement is "true" when it does not equal the criteria

when is AND function used

AND function is used when it is looking if both sets of criteria are true

10. In cell C3 of the "Champions" worksheet, add a formula that uses a single
function to look up the team abbreviation in the "Abbreviation" worksheet for
the team listed in cell B3 of the "Champions" worksheet. (to be viewed)

-it is a vertical table so we know to use a vlookup and we know we need the
lookup value, then the structured range and the column number (since the
range is in column B we call it column 2)

[Link]
when to use an if function

-clue is if values go outside of the true or false which leads us to believe it

may be an IF function, but if there is true two conditions then it is an IFAND

[Link]

11. In cell G3 in the "Recent Wins" worksheet, create a formula that will show
if a recent winning team ranked in the top ten in the league for both goals
scored, and goals allowed during the season. Display "Expected" if the team
did this, and "Not expected" if the team did not rank better than 10 in both.
(to be viewed)

-clue is there are values "expected" and "not expected " that go outside of
the true or false which leads us to believe it may be an IF function, but theres
also another part of this, there is an embedded AND function because if it is
true two conditions have to be true so it is an IFAND function

[Link]

12. To remind Mike how long it has been since his favourite team won a
championship, in cell D43 of the "Champions" worksheet, create a formula
that uses date and time functions to show how many years ago his team
won. (calculating the date)

-wants us to calculate how many years its been since they last won as shown
in B43
-to do this you need the previous date (1967)A43, todays date and also
whether you want to show the elapsed time in days months or years
=dated if(A43, Today(),"y")
Days would be D
Months would be M
Years would be Y

1. In column E, Use a function that will return the text "Yes" if an employee is
part-time (Sessional/Parial-Load) and "No" if the faculty member is
considered "Full-time". (to be viewed)
-we see we have two outcomes so we will have to use an if function with an
embedded or function and we know we have to use the OR function because
the OR function will tell you if it meets either one of the two conditions

[Link]

2. In column F, Use a function to display "Increase Salary" if the employee is

in the Legal Administration department and has a salary less than $50,000.
Leave the cell blank otherwise. (to be viewed) if/not/or/and functions

-both conditions have to be met, the person has to be part of the legal
admission department and make less than 50,000
-IF(AND

[Link]

3. In Column H, Use a function to state , "True" if the employee does not work
in the "Legal administration" or " Office Administration-Medical" and "False" if
they do.

-we want to use a NOT and an OR function embedded within eachother

[Link]

INDEX AND MATCH FUNCTION

what is the index function

-index function: returns the value at a given position in a range or array

-need a table array, which column to use, which row to use

what is the match function

will find an exact position of a value in an array/range

Use the index function to determine what 8 multiplied by 7 is

Use the match function in cell E1 to locate where the name "Easton" ranks
on the 100 most popular boy names in 2021
Type in =match(then type the cell you are looking to match, the range, and 0
means it has to be an exact match)
Press enter and it shows what row it is located in and you can type a
different name and the formula adjusts automatically

1.) In cell F6 create a formula using a Lookup function to return

the Suggested Retail for CODE KE200.

DAYS AND TIME

1. In column B, add a formula that will display the day of the week as a
number of the date indicated in column A of the same row. (Sunday=1,
Saturday=7).

-the hint is at the end when they correlate the days with numbers because
that is how the weekdays work
=weekday(A11)
A11 is the date 4/1/2021

2. Use a function in cell D8 to calculate the date and time right now

=now()

4. Use a function in cell D9 to calculate today's date

=today()

5. In cell F8, use a formula to calculate the date that will be 30 days from
now

=today()+30

6. In cell F9, use a formula to calculate what the date was 45 days before the
current date

=today()-45
7. In cell i8, Use a function and the information in cells i9:i10 to calculate
when the workout program will be completed

=workday(I9,I10)

I9 is the start date

I10 is the amount of days

AVERAGE/AVERAGEIFS
1.) Enter a formula in cell L5 that returns the average "Total Revenue" for the
corresponding "Sales Representative" in column A, and the "Area" in column
B

Averageifs is used when you want to find the average for two different
criteria
So you type in =averageifs(highlight total revenue column because that is
the range that you are looking for the average in, then highlight the sales rep
column because that is the first criteria range1, then highlight J5 because
that is the criteria1 for the criteria range1, then highlight the sales
representative column because that is the criteria range2, then highlight K5
because that is the criteria2 that you are searching the criteria range2 for

2) Enter a formula in cell L5 that returns the sum of the "Total Revenue" for
the corresponding "Sales Representative" in column A, and the "Area" in
column B. (TO BE VIEWED)

CHARTS AND TABLES

1. On the "Profit Analysis" worksheet, create a chart that displays the

"Salary" for each instructor as an Area chart and the "Revenue", as a Line
chart on the same axis.

-is saying we will have to use a combo chart which displays two separate
categories on the same axis
Highlight the table, go to insert tab, below the pivotchart option click the
icon in the bottom right to see all charts, then pops up the insert chart
window and click all charts, then click combo tab, then switch the salary to
an area chart under chart type, press ok

2. On the "Revenue per Lesson" worksheet, modify the chart to use Layout 3
and Style 6. Change the colors to Monochromatic 5 and the chart title to,
"Revenue per lesson".

Go to chart design tab, in this tab you see the chart styles shown as images
so press style 6, then to the left on the chart design tab you see a "quick
layout" button press that, choose layout 3, to the right of the "quick layout"
button press "change color" button, then chose monochromatic 5 which is
the 5th row under the monochromatic option, then to change the chart title
press the plus and you can change stuff but because it is a timed exam you
can just press right on the chart title and type it in

3. On the "Advertising" worksheet, create a funnel chart that displays the

"Enrollment" data with the descriptions on the left of the chart. Change the
chart title to "Enrollment from Advertising" (FUNNEL CHART)

Highlight description and enrollments columns since that is what it wants the
table to be of, go to insert tab, then press the insert chart button at the
bottom right under the "pivotcharts" button, press all charts, choose a funnel
chart

4. On the "Family Memberships" worksheet, modify the PivotTable to group

the data by the values in the "Price" column. Group the values in steps of
100, beginning at 1 and ending at 200.

-wants you to group

Press anywhere in the table, then right clock, select the group option, type
in Starting at 1 Ending at 200 By100, press ok

5. On the "Family Memberships Filter" worksheet, insert a slicer for the

PivotTable that allows you to filter the table by "Description". Use the slicer to
display all of the records except for the "Individual" and "Couple" ones. ''
(slicer using the description)
-a slicer is a way to filter data
Highlight any cell within the pivotable, go to the pivotable analyze, press
"insert slicer", choose description, press ok, click one and then hold down
control key on keyboard to highlight all the records except for "individual"
and "couple" ones

6. On the "Enrollment Change" worksheet, create a pie PivotChart that shows

the PivotTable data. Filter the chart to display only the enrollment change for
Aqua tots and Aqua fit. (PIE chart select the aqua tot and aqua fit)

Highlight any cell in the pivotable, go to pivotable analyze tab, choose the
pivot chart icon, select pie chart, press ok, then in top left of pie chart there
is a drop down where you can filter by the program , it wants the "aqua tot"
and "aqua fit" but not the individual or swim kids so uncheck the individual
and swim kids options

7. On the "Enrollment Trend" worksheet, create a calculated field named,

"Trend" that displays the enrollment increase from 2019 to 2020.

-it just means the difference but how you add that as a calculated field can
be tricky
Highlight anywhere in the pivotable, go to pivotable analyze, click on "fields,
items and sets" button, then click calculated field, the you have to type in a
formula in the formula bar in the calculated field pop up so press 2020 under
fields and press insert field then type in - then press 2019 and press insert
field, then press ok, right click on the sum of field header, press on value
field settings, and change name to Trend, press ok

Create a formula that will return TRUE if a value in cell B5 is between 90 and
100

AND(B5>=90,B5<=100)
- AND Function requires all arguments to be true in order for it to return a
TRUE value
What formula would you use in order to get a TRUE value if cell C3 is greater
than 14 or equal to 3

OR(C3>14,C3=3)
The OR function requires one of its arguments to be true in order for it to
return a TRUE value

Where are macros stored ?

Modules

What sign do references to cells in other workbooks start with?

Equal Sign

Where are the macro settings located?

Trust Center Settings

What is the first step in protecting a worksheet?

Unlock the cells that should be editable

Through the _____ drop-down button on the formulas tab, one can set
workbook formulas to calculate manually or automatically

Calculation Options

Which tab is the Protect Workbook button under?

Review

Where is the option to set or remove a password to protect an entire

workbook located ?

File tab and through protect workbook drop-down button

Which tab contains conditional formatting?

Home

The ____ window allows one to edit and/ or delete conditional formatting
rules

Managing Rules

Which tab contains the Custom Colors formats?

Page Layout Tab

A cell style can be modified through right clicking the style and then clicking
the ____ button

Format

What Tab are form controls located on?

Developer

In which folder are macros stored?

Module

Which ribbon tab is hidden by default?

Developer

By default, what is the status of worksheet cells?

Locked

What is true about implementing the Protect Workbook feature?

Sheets cannot be added

What is the proper order for the formats of a custom number order?

Positive, Negative, Zero, Text

Which are aspects that comprise a theme?

Styles, Fonts, Colors

What are the options for formatting a cell's date to an international format
found?

Number dialog-box launcher

Using the NOT function write a formula to return a false value if the number
in cell A7 is 19

=NOT(A7=19)

Which two functions are used to find a cell value within a grid, based on
criteria?

Match, Index

Which function serializes a date so that it can be used in calculations ?

DATE
Which allows summarizing data from multiple locations and can show the
detail of the data being summarized?

Data Consolidation

Which specific function calculates a payment based on starting amount,

interest rate, and number of payments?

PMT

What is not allowed in names of ranges?

Spaces, Cell Addresses

Which best offers visual representations of filters in PivotTable?

Slicers

Which function allows data from within a PivotTable to be displayed outside a

PivotTable?

GETPIVOTDATA

The ___ Returns the opposite of a calculated value?

NOT

The ____ function is used to add a range of cells based on multiple conditions

SUMIFS

The ____ function is used to get a numeric count, based on multiple

conditions, for a range of cells

COUNTIFS

The ____ function uses a known value to get unknown information from a
vertical table of information

VLOOKUP

The ____ function is used to find a matching value for a criterion at a row and
intersection within a range of cells

INDEX

The ____ displays the current date but not the current time

TODAY

The ____ function displays the current date and time

NOW

The ____ function serializes a date so it can be used in calculations

DATE

The consolidate data feature, located on the data tab, is only used to
summarize data

False

What is a nested function?

The nested function involves having one function inside another function