IS 101 Final Exam

Presentation (.pptx) vs. Show (.ppsx) (PowerPoint)

- (.pptx): Editable PowerPoint file containing slides, formatting, and contend, open for modifications and editing. - (.ppsx): Non-editable presentation file designed solely for delivering the slideshow, launching directly into presentation mode to prevent accidental changes.

Difference Between a Formula vs. Function

- A formula is something you create by typing in calculations starting with "=" (like adding numbers together). - A function is a built-in tool in Excel that you use by typing its name and providing details in parentheses (like finding an average or summing a range of numbers).

Conditional Format Rule Manager

- Allows you to see all conditional formats applied to specific cell ranges. - Allows you to place the precedence of conditional formats if there are multiple.

Formatted table options you can turn on and off

- Header Row (Top row is shaded) - First Column (First column is shaded) - Last Column (Last column is shaded) - Banded Rows (Every other row is shaded) - Banded Columns (Every other column is shaded) - Total Row (Adds a new row where you can create aggregations like sum, average, min, max, etc.) - Excel doesn't include the total row in sorts and will update to show values based on filtered results.

Commonly used conditional formats

- Highlight Cells - Top/Bottom Rules - Data Bars - Color Scales - Icon Sets

Page Layout View

- Modify header and footer. - Shows how a spreadsheet looks when printed, helps adjust layout for printing.

Elements that can be added to a header and footer

- Page Number - Number of Pages - Current Date - Current Time - File Path (C:\Users\John\Desktop\MySpreadsheet.xlsx), - File Name (MySpreadsheet.xlsx), - Sheet Name - Picture

Chart Title

A descriptive text placed above a chart, summarizing its content or purpose, aiding in comprehension and context for the represented data.

Data Table

A grid-like structure accompanying a chart, displaying the source data values represented in the chart, facilitating detailed examination and comparison of plotted information.

Legend

A key within a chart describing the meaning of various data series or categories, aiding in the identification and interpretation of plotted elements.

Absolute References

Absolute references in Excel are like fixed references that don't change when you copy a formula to other cells. They're indicated by using dollar signs ($) before both the column letter and the row number, like "$A$1." Examples: If you have a tax rate in cell A1 and you want to use that rate for multiple calculations, you can use an absolute reference like this: =B1 * $A$1. When you copy this formula, the reference to A1 (the tax rate) stays the same in all the calculations. If you have a constant value like the number of hours in a day, say, in cell A1 (24), and you want to use it in various calculations, you can use an absolute reference like this: =B1 * $A$1. Again, when you copy this formula, the reference to A1 remains constant, ensuring that all calculations use 24 as the number of hours in a day.

Page Break View

Adjust amount of spreadsheets printed on each page.

Presenter View (PowerPoint)

An exclusive screen mode for presenters, showing slides, notes, and tools privately.

Notes Pane (PowerPoint)

Area for a presenter's private notes, aiding during the presentation.

What function allows you to select non-adjacent cells?

CTRL

Conditional Formats

Can be applied to a range and only cells that meet the condition will be formatted (ex. bold, italic, font face, font size, etc.)

Non-Adjacent Cells (cells that aren't connected)

Cells in Excel that are not directly contiguous or connected, allowing for selection or manipulation of dispersed or disconnected data points within a worksheet.

Freeze Panes

Click cell, go to 'View' tab, use 'Freeze Panes' to lock rows or columns.

4 Most Common Chart Types

Column, Bar, Line, and Pie Chart.

Column Chart

Compares values across categories with vertical columns.

Bar Chart

Compares values with horizontal bars.

Axis Titles

Descriptive labels along the X and Y axes in a chart, providing context and clarity by denoting the represented variables or categories.

Pie Chart

Displays proportions of a whole.

Animations (PowerPoint)

Effects that add movement to text or images, making presentations more dynamic.

Be able to identify ethical questions (questions of right or wrong) and why different parties might identify the answer as ethical or unethical depending upon their position

Ethical Question: How should individuals handle sensitive information within information systems? Perspectives: Some might believe it's ethical to protect data confidentiality and integrity, while others might prioritize information sharing for societal benefits.

Hackers

Ethical Question: Is hacking ethical? Perspectives: Some hackers may see themselves as activists exposing vulnerabilities, while others engage in unethical behavior for personal gain or malicious intent.

Ethical dilemma around workplace monitoring

Ethical Question: Is it ethical for employers to monitor employees' activities in the workplace? Perspectives: Employers may see monitoring as necessary for security or productivity. Employees might view it as an invasion of privacy that affects trust and autonomy.

Viruses

Ethical Question: Is spreading computer viruses ethical? Perspectives: Some may view creating viruses as a means to expose security flaws, while others create them maliciously for personal gain, causing harm to systems and users.

Axes

Fundamental lines in a chart that visually represent and scale data, providing reference points along horizontal (X-axis) and vertical (Y-axis) dimensions for precise analysis and comprehension of plotted information.

Formatted Tables

Group a range of cells into something Excel recognizes as a table.

Gridlines

Horizontal and/or vertical lines extending across the plot area of a chart, serving as visual aids to assess and align data points with specific values on the axes.

Order of Operations

In Excel, when you have a formula with multiple operations, it follows a specific sequence to do the calculations: 1. It starts with whatever's inside parentheses. 2. Then, it handles exponentiation (raising to a power). 3. After that, it does multiplication and division from left to right. 4. Finally, it adds and subtracts from left to right. PEMDAS for short.

F4 adds dollar signs ($) before column or row of cell reference or both.

In Excel, when you press F4, it adds or removes dollar signs ($) before the column letter, the row number, or both in a cell reference. It helps lock or unlock parts of the reference.

Button used to change between referencing types (F4)

In Excel, when you press the F4 key, it helps you change the way cell references work in a formula. It can make a reference stay fixed or adjust as needed. Each time you press F4, it cycles through different reference options.

Goal Seek Manager

Is a What-If Analysis used to find what a value needs to get to in order to reach a specific goal or target.

Mixed References

Mixed references in Excel are like a mix of two types of references: Relative Column and Absolute Row (e.g., $A1): The column adjusts when you move horizontally, but the row stays the same when you move vertically. Example: If you copy it from cell B2 to C2, it changes to $A2. But if you copy it to B3, it stays $A1. Absolute Column and Relative Row (e.g., A$1): The column stays the same when you move horizontally, but the row adjusts when you move vertically. Example: If you copy it from cell B2 to C2, it remains A$1. But if you copy it to B3, it changes to A$2. Absolute References Written Form Examples: =B2-C2-$F$2 =C3*A$2

Is it illegal to monitor employees?

Perspectives: Laws differ by region. While some monitoring is legal for security or business purposes, extensive or intrusive monitoring may breach privacy laws.

Freeze First Column

Pick column, go to 'View' tab, click 'Freeze Panes,' select 'Freeze First Column.'

Quick Layouts

Predefined arrangements or formats readily applicable to charts, allowing swift modification of chart elements and styles for improved visualization of data. Create prebuilt layouts of various elements.

Chart Styles

Predefined visual designs or formatting options applied to charts, enabling quick adjustments in colors, effects, and overall appearance for enhanced data representation.

Definition of Information Ethics

Principles governing responsible and moral handling, access, and use of data and information across various contexts, considering societal impact and ethical implications. In other words, this means doing the right thing with information---using it fairly and responsibly.

Proper Syntax of a function vs incorrect syntax

Proper syntax in Excel is the correct way to structure a function, including the function name, opening and closing parentheses, and arguments (values or cell references) separated by commas. Proper Syntax (SUM function) Example: =SUM(A1, B1, C1) Correct! Includes proper opened and closed parentheses, and separates the cell references by commas. Incorrect syntax means not following this structure, which can lead to errors, such as missing parentheses, wrong function names, or incorrect comma usage. Incorrect Syntax (SUM function) Example: =SUM(A1, B1 C1) Incorrect! Missing a comma between B1 and C1. Should have commas that separate these arguments.

Be able to identify each type of reference and figure out changes to a formula or function using either relative, absolute, or mixed references

Relative Reference Practice: Problem: Calculate the cost of buying 4 items at $10 each. In cell A1, use a formula to find the total cost. Answer: In cell A1, the formula should be =4 * 10. The total cost is $40. Absolute Reference Practice: Problem: You have a budget of $100 in cell A1. In cell B1, use a formula to calculate the remaining budget after spending $30. Ensure that the budget cell reference is absolute. Answer: In cell B1, the formula should be =$A$1 - 30. The remaining budget is $70. Mixed Reference Practice: Problem: Calculate the cost of buying 3 items at different prices in column A. In cell B1, use a formula to find the total cost for the first item (A1 * 3) while keeping the quantity constant. Answer: In cell B1, the formula should be =A1 * 3. The total cost for the first item is $30. When you copy this formula down, it adjusts to calculate costs for other items with the same quantity (3).

Relative References

Relative references in Excel are like instructions that tell a formula how to adjust when you copy it to different cells. Here are a few examples to illustrate: 1. If you have a formula in cell B2 that says "=A1+B1," and you copy it to cell B3, the formula becomes "=A2+B2." The references change to match the new location. 2. If you have a formula in cell C4 that says "=A1*$B$2," and you copy it to cell D4, the formula stays the same as "=A1*$B$2." The "$" signs make it an absolute reference, which doesn't change when you copy it. 3. If you have a formula in cell E5 that says "=A1/B$1," and you copy it to cell E6, the formula becomes "=A1/B$1." The column reference to A1 stays the same, but the row reference to B$1 is fixed because of the "$" sign. Relative Reference Written Form Examples: =A1 + B1 = A2 + B2

How to clear sparklines from a cell

Select cell, go to 'Data' tab, choose 'Clear' to remove the sparkline.

Freeze Top Row

Select row, go to 'View' tab, click ' Freeze panes,' choose 'Freeze Top Row.'

Line Chart

Shows trends over time or intervals.

Data Labels

Text or numeric indications directly placed on data points in a chart, providing specific information, such as values or percentages, aiding in data comprehension and analysis.

Active Cell (Cell outlined in green)

The "Active Cell" in Excel is the cell outlined in green, and it's the one you're currently working on. Any data you enter or change will go into this cell.

Formula Bar

The "Formula Bar" in Excel is a place where you can see and change the content of the cell you're working on, like numbers or calculations. It's a helpful spot for editing your data.

Name Box

The "Name Box" in Microsoft Excel is a small box that tells you which cell or range of cells is currently selected. You can also use it to quickly jump to specific cells or named ranges.

Data Source

The origin or reference for the information used to Excel, typically found within a worksheet or an external file, serving as the basis for analysis, calculations or visualization.

Changing number format doesn't change the value of a cell

This is a correct statement. Changing the number format in Excel doesn't change the number itself; it just changes how the number looks when displayed in a cell.

Equal sign necessary to start a formula

This is a correct statement. To create a formula in Excel, you usually begin with an equal sign (=). This tells Excel that you're about to do a calculation.

Sorting data puts it in a specific order

This is a true statement! From this function, it is clear that it reorders data based on chosen criteria (ascending or descending).

Filtering data with narrow results shown based on criteria selected per column.

This is a true statement! Shows specific data by criteria, column-wise, hiding rows that don't match.

A cell range looks like A4:C20 and references every cell from the first cell (A4) through the last cell (C20) and every cell in between.

This statement is true! A cell range like A4:C20 includes all the cells within a rectangular area starting from A4 and ending at C20. It covers the cells in this specific rectangular region, like A4, B4, C4, A5, B5, and so on, up to C20.

An argument is optional if it's shown in square brackets [ ] or if it's unbolded in your insert function dialog box.

This statement is true! An argument is considered optional if it's enclosed in square brackets [ ] or if it's not shown in bold in the Insert Function dialog box. You can choose to include or exclude optional arguments in your function, depending on your specific needs. It gives you flexibility when using functions in Excel.

Arguments in functions are separated by commas (,)

This statement is true! When you're using a function, you list the arguments within parentheses, and you use commas to separate them. These arguments provide the function with the information it needs to perform a specific task or calculation.

Sparklines (Chart within a cell)

Tiny charts within Excel cells, giving a snapshot of data trends in a compact.

Methods of inserting a function in a cell.

You can insert a function in an Excel cell using these methods: Function Wizard: Click the "fx" button, select a function, and set its arguments. Manual Entry: Start with "=" and type the function name and arguments. Insert Function Dialog Box: Go to the "Formulas" tab, click "Insert Function," and choose a function. Autosum Button: Click the "Σ" button for common functions like SUM. Function Prefixes: Type function names followed by an opening parenthesis to start a function. Built-in Functions: Type the function name, and Excel will suggest available functions for you to select.

Nested Function

a function that resides as an argument inside of another function (i.e. =IF(AND(B7<300,B18>700),"It was a good day", "Not so much"))

Transitions (PowerPoint)

a tab that allows the user to use options for the slides such as reveal, slide, random bars, honeycomb - you should apply transitions to all to be consistent.

Number Formats (Number, Accounting, Currency, Percent)

are settings in Microsoft Excel that determine how numbers are displayed in cells. They help you format numbers according to your specific needs. Number Format: This displays numbers as regular numerical values without any special formatting. Accounting Format: This is often used for financial data and aligns the currency symbols and decimal points neatly. It also applies two decimal places. Currency Format: Similar to the Accounting format, it displays numbers as currency values with currency symbols and decimal points, but with more customization options. Percent Format: This multiplies the value in the cell by 100 and adds a percentage sign. It's commonly used for showing percentages.

Range Name

assigning a name like "expenses" to a range.

AVERAGE Function

calculates the "middle" number in a group of numbers. It adds up all the numbers and divides the total by how many there are. It's helpful for tasks like finding the average score in a class, the typical temperature over time, or the average sales for a period. It makes it easy to figure out the average value in your data.

NOW Function

displays both the current date and time in a cell. It keeps updating to show the exact date and time whenever you open or recalculate the Excel file. You can use it for creating timestamps, tracking when data was last updated, or monitoring time intervals. It's a convenient way to have the current date and time in your Excel sheet.

>= (Greater Than or Equal to)

helps you check if one thing is either bigger or equal to another. If it is, it says "TRUE." If it's not, it says "FALSE." It's like asking Excel, "Is this thing greater or the same?" and Excel answers with a "yes" or "no."

<= (Less Than or Equal to)

helps you check if one thing is either smaller or equal to another. If it is, it says "TRUE." If it's neither smaller nor equal, it says "FALSE." It's like asking Excel, "Is this thing smaller or the same?" and Excel answers with a "yes" or "no."

< (Less Than)

helps you check if one thing is smaller than another. If it is, it says "TRUE." If it's not, it says "FALSE." It's like asking Excel, "Is this thing smaller?" and Excel answers with a "yes" or "no."

<> (Not Equal to)

helps you check if two things are different. If they are, it says "TRUE." If they're the same, it says "FALSE." It's like asking Excel, "Are these things not the same?" and Excel answers with a "yes" or "no."

VLOOKUP Function

helps you find a value in a table or range and return another value from the same row. It's like a digital lookup tool that helps you find things in a big list, like getting product details based on a product code or finding a student's grade using their student ID. It's a handy way to fetch specific data from a large table.

PMT Function

helps you find out how much you need to pay or can expect to receive regularly for a loan or investment. It considers things like the interest rate, total number of payments, and the initial amount. This function is useful for tasks like calculating monthly loan payments or planning your savings. It makes financial calculations easy.

IF Function

helps you make decisions in your spreadsheets. It checks if a condition is true or false and returns different values based on the result. This function is handy for tasks like classifying data, calculating bonuses, determining pass/fail outcomes, or creating customized reports. It lets you automate choices in your Excel files.

> (Greater Than)

helps you see if one thing is bigger than another. If it is, it says "TRUE." If it's not, it says "FALSE." It's like asking Excel, "Is this thing greater?" and Excel answers with a "yes" or "no."

VLOOKUP Function

is a function in Microsoft Excel that allows you to search for a specific value in a table, and once found, it returns a corresponding value from a specified column within that table. It's commonly used for looking up data in spreadsheets. It returns: VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]) lookup_value: searches in the first column of your data. table_array: keeps your data. States the value you are looking for in the first column, and data you want in other columns. col_index_num: this is the column number where you can find the data you want to receive. range_lookup: Tells Excel whether you want an approximate (TRUE) or exact (FALSE) match. This is optional.

IF Function

is a tool in Excel that helps you make decisions based on a condition. It returns one value if a condition is true and another value if the condition is false. It returns: IF(logical_test, value_if_true, value_if_false) logical_test: This is a condition or expression that you want to evaluate. If the logical_test is true, the function returns value_if_true. If the logical_test is false, it returns value_if_false. value_if_true: This is the value that the IF function returns if the logical_test is true. value_if_false: This is the value that the IF function returns if the logical_test is false.

SUM Function

is used to add up a bunch of numbers or values. It's like a calculator that quickly gives you the total of the numbers you choose. This function is handy for tasks like finding the total sales, adding up test scores, or calculating expenses. It saves you time and effort when you need to do a lot of addition in Excel.

LARGE Function

is used to find the "kth" largest value in a list of numbers. You specify the rank (like 1 for the largest, 2 for the second largest, and so on), and the function tells you what that value is. It's helpful for tasks like finding the top scores in a class, the biggest sales in a month, or the largest orders in a list.

SMALL Function

is used to find the "kth" smallest value in a list of numbers. You specify the rank (like 1 for the smallest, 2 for the second smallest, and so on), and the function tells you what that value is. It's helpful for tasks like finding the lowest scores in a class, the smallest expenses in a budget, or the least expensive products in a list.

MAX Function

is used to find the biggest number in a list or a group of numbers. It's handy for tasks like identifying the highest score in a class or the hottest temperature in a dataset. It makes it easy to pinpoint the largest value in your data.

MIN Function

is used to find the smallest number in a list or a group of numbers. It's handy for tasks like identifying the lowest score in a class or the coldest temperature in a dataset. It makes it easy to pinpoint the smallest value in your data.

= (Equal to)

is used to see if two things are the same. If they are, it says "TRUE." If they're not, it says "FALSE." It's like asking Excel, "Are these things equal?" and Excel answers with a "yes" or "no."

Structured Reference

is used when creating aggregations in a formatted table [January].

What does the dollar sign ($) do?

locks the column or row from changing when dragging the formula or function to other cells.

Colon ( : )

separates the cell references in a range. Whasssigning a name like "expenses" to a range this is called a r

TODAY Function

shows the current date in a cell. It updates to the current date whenever you open or recalculate the Excel file. You can use it to keep track of deadlines, create date-stamped logs, or calculate someone's age based on their birthdate. It's a handy way to always have the right date in your Excel sheet.