Brainscape's Knowledge GenomeTM

Browse over 1 million classes created by top students, professors, publishers, and experts.


See full index

Microsoft Excel Bootcamp > Excel Stuff > Flashcards

Excel Stuff Flashcards

1
Q

What shortcut allows you to select a whole column?

A

Crt + Space Bar

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
2
Q

Which key on the keyboard unlocks all of the functions on the ribbon?

A

Alt

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
3
Q

Why should you a customize your Quick Access Toolbar?

A

When used with the Alt key, you can create your own custom keyboard shortcuts and it gives you quick access to your most commonly used functions

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
4
Q

Which ey combinations allow you to quickly select a set of data?

A

Crtl + Shift + Arrow Keys

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
5
Q

What should the font color of any cell that is an assumption (which can be changed) or hard input be?

A

Blue

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
6
Q

SHORTCUT: Enter the current date

A

Crtl + ;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
7
Q

SHORTCUT: Open Format Cells dialog box

A

Ctrl + 1

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
8
Q

SHORTCUT: Enter a basic chart of the selected data

A

Alt + F1

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
9
Q

SHORTCUT: Toggle Filtering on and off

A

Ctrl + Shift + L

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
10
Q

SHORTCUT: Insert a table

A

Ctrl + T

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
11
Q

SHORTCUT: Automatically fill relative information (patterns)

A

Ctrl + E

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
12
Q

What is “Absolute Cell Reference”?

A

A cell reference in which the column and row coordinates stay constant while copying a formula from one cell to the other( G$$7). Making the cell reference absolute ensures that it remains constant regardless of whether the formula is transferred to a different worksheet or workbook. A cell reference in Excel is always relative (like D2), which means it changes when the formula is duplicated.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
13
Q

How many times to hit F4 to reference a column?

A

2 times

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
14
Q

How many times to hit F4 to reference a row?

A

3 times

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
15
Q

What is the opposite of hardcoding?

A

Absolute Cell Reference

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
16
Q

range in a formula asks for what?

A

What items we are summing (What we are looking for)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
17
Q

criteria range in a formula asks for what?

A

The criteria of a formula to consider (Include these, if…)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
18
Q

criteria in a formula asks for what?

A

The criteria of a criteria range (include there if… they are ___)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
19
Q

Out load, describe a SUMIFS formula

A

Sum everything in this column but only if they have this value in this here column. Sum column, Criteria Column, Criteria.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
20
Q

Give me an example of what to use SUMIFS for

A

Example: If you have a dataset of sales with columns for products, regions, and dates, you can use SUMIFS to calculate the total sales for a specific product in a certain region during a specific time period.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
21
Q

What’s the difference between VLOOKUP and SUMIFS?

A

VLOOKUP is used to retrieve a single value from a table, while SUMIFS is used to perform conditional sums by specifying multiple criteria. These functions are valuable tools for different types of data analysis tasks in Excel.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
22
Q

Give me an example of what to use SUMIFS for

A

Example: If you have a dataset of sales with columns for products, regions, and dates, you can use COUNTIFS to determine how many sales occurred for a specific product in a certain region during a specific time period.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
23
Q

Out loud, describe a COUNTIFS formula

A

criteria range , criteria , criteria range #2 , criteria #2

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
24
Q

What is the COUNTIFS syntax?

A

COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], …)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
25
Q

Give an example of what to use COUNTIFS for

A

Example: If you have a dataset of sales with columns for products, regions, and dates, you can use COUNTIFS to determine how many sales occurred for a specific product in a certain region during a specific time period.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
26
Q

Out loud, describe a VLOOKUP formula

A

Value (what we are looking for), array (the phonebook range[best to select the whole table]), F4 (You need the reference of the phonebook), column index number (what is is we want to extract according to the column we are grabbing {the column number we are pulling from}), True/False

27
Q

Provide the VLOOKUP syntax

A

VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

28
Q

Give me an example of what to use VLOOKUP for

A

Example: If you have a list of products with their prices, you can use VLOOKUP to find the price of a specific product based on its name.

29
Q

If a VLOOKUP results in a bunch of #N/A results, what is the most common mistake?

A

Not locking in the reference. You need to hit the F4 after selecting the array. Look for the $ on each of the array values

30
Q

What happens if you do not put true or false on the end of your VLOOKUP formula?

A

Excel will now have options and might choose a different Look Up Vale. Do not leave false or true off. Results will be untrustworthy

31
Q

Which keys on the keyboard allows you to lock down a cell or range of cells?

A

F4

32
Q

What does “Greater than or Equal to” look like in Excel?

A

> =

33
Q

What does “Less than or Equal to” look like in Excel?

A

<=

34
Q

What does “Greater than” look like in Excel?

A

>

35
Q

What does “Less than” look like in Excel?

A

<

36
Q

What does “Not equal to” look like in Excel?

A

<>

37
Q

Out loud describe an IF Formula

A

the statement(cell is great, less, whatever), then do this if true, do this if false

38
Q

IF Function syntax:

A

IF(logical_test, value_if_true, value_if_false)

39
Q

Give an example for using a IF Function

A

You can use the IF function to determine if a student’s score in a test is passing or failing by comparing the score to a passing threshold. For instance, =IF(B2>=70, “Pass”, “Fail”) would return “Pass” if the value in cell B2 is greater than or equal to 70, and “Fail” otherwise.

40
Q

What’s the difference between Standard IF functions and Nested IF functions?

A

A standard IF function is a simple conditional function that checks a single condition and returns one of two possible results based on whether the condition is true or false.

A nested IF function allows you to evaluate multiple conditions and return different results based on the outcomes of these conditions.

41
Q

IF Function syntax vs Nested IF Function syntax

A

Standard: IF(logical_test, value_if_true, value_if_false)

Nested: IF(logical_test1, value_if_true1, IF(logical_test2, value_if_true2, IF(logical_test3, value_if_true3, … , value_if_false))).

42
Q

Describe the EDATE function out loud

A

a function used to calculate a date that is a specified number of months in the future or past relative to a given starting date. It’s particularly useful when you need to project or determine future or past dates based on a certain time frame

43
Q

What is the EDATE syntax?

A

=EDATE(start_date, months)

44
Q

What is an example for the use of a EDATE function?

A

Let’s say you have a project starting on January 1, 2023, and you want to determine the date that is three months into the future. You can use EDATE as follows:

=EDATE(“2023-01-01”, 3)

This formula would return April 1, 2023, which is three months after the starting date.

45
Q

Describe the EOM function out loud

A

The EOMONTH function is used to calculate the last day of the month that falls a specified number of months before or after a given date. This function is particularly useful for tasks like tracking deadlines, billing cycles, and other date-related calculations.

46
Q

What is the EOMONTH syntax?

A

=EOMONTH(start_date, months)

47
Q

What is an example for the use of a EDATE function?

A

Suppose you have an invoice that is due 2 months after a specific date, and you want to determine the exact due date (the last day of the month) for that invoice.

=EOMONTH(“2023-03-15”, 2)

This formula would return May 31, 2023, which is the last day of the month that is two months after March 15, 2023.

48
Q

What does =YEAR , =MONTH, and =DAY functions do?

A

They pull numbered year, monthes, and days out of a table

49
Q

What are the functions =YEAR , =MONTH, and =DAY most useful in?

A

Pivot tables. To make uniformed lists

50
Q

What is the =LEFT syntax?

A

=LEFT(text, num_chars)
ex. =LEFT(Hello, World!, 5) > Hello

51
Q

What is the =RIGHT syntax?

A

=RIGHT(text, num_chars)
ex. =RIGHT(Hello, World!, 6) > World!

52
Q

What is the =MID syntax?

A

=MID(text, start_num, num_chars)
ex. =MID(Good day. Hello, World!, 8, 5) > Hello

53
Q

How do you put an indent in a cell?

A

Home tab > Indent button

54
Q

What is Text-to-Columns good for?

A

Splitting up cells that are separated by a consistent, yet undesired system (cells with 3 sources of data but we want them to be 3 separate columns with the divided data)

55
Q

Is the a way to remove duplicates in a table in one full sweep?

A

Yes. Data > Remove Duplicates > make sure all headers are selected > hit “ok”

56
Q

Give a fact about COnditional Formatting

A

They supersede all other formatting, they can be selected for rows, sheets, columns, or any selected area

57
Q

Should you ever have more than 2 Conditional Formats in one sheet?

A

No. And if you use Conditional Formatting, place a key in the sheet for explanation

58
Q

What are Conditional Formatting Data Bars good for?

A

Data bars are great for showing a visual comparison between values in a table

59
Q

What are Conditional Formatting Color Scales good for?

A

Color Scales are awesome for showing the highest in a color versus another color. It is a way to show relationship as well

60
Q

Can you have multiple Conditional Formatting rules in a set of cells?

A

Yes! The Conditional Formatting rules will lay over all other formatting you currently have

61
Q

What’s the best way to think about PivotTables?

A

Think of this thing as creating graphs. You are selecting your x-axis and then selecting your y-axis

62
Q

Would you ever put something like sales number in a PivotTable rows or columns field?

A

No. That would result in a long ugly list of numbers. You put qualitative values in the rows and columns while putting quantitative values in the values field

63
Q

Are PivotTables easy to manipulate or challenging and rigid?

A

Easy. Totally easy to choose your different desired fields and values

64
Q
A

Microsoft Excel Bootcamp (1 decks)

Brainscape helps you reach your goals faster, through stronger study habits.
© 2024 Bold Learning Solutions. Terms and Conditions