CIS 150: Excel Test 1

What was the first electronic spreadsheet?

VisiCalc

What is the horizontal line (ABC) called?

Column

What is the vertical line (123) called?

Row

Cell

Coordinates for columns and rows

Ctrl + →

Transfers you to last column

Ctrl + Downward arrow

Transfers you to last row

What is the small square in the corner of a square that has been clicked on called?

Fill Handle

How do you get rid of gridlines:

View → Gridlines

Why would one want to get rid of gridlines?

That is how the document will look when printed so the information will most likely want to be centered

How to replace data without physically doing it to each cell?

Home → Find and Select → Replace

Ctrl + Shift + Downwards Arrow

Highlight a column

=COUNT

Tells the number of cells in range that have numbers

=COUNTA

Tells the number of cells in range that are not empty

=COUNTBLANK

Tells the number of cells in range that are empty

How to make things look like money?

Accounting, currency, or currency (no symbol) button

=AVERAGE

Finding the average of a column, usually relating to money

=MAX

Tells the largest number in a column

=MIN

Tells the lowest number in the column

True or False: You want to make sure to “ignore error” on all tests

True

How to center a title/heading

Highlight row → merge and center button

=COUNTIF

Tells the number of cells in range that have a certain criterion

How to multiply values

[Cell of data]*[Cell of data] → F4 key

When multiplying values, when would you not want to use the F4 key?

If there are values down the column that need to be multiplied by different values

How to use conditional formatting

Conditional formatting → new rule → format only cells that contain → fill in second and third box (unless blank) → format color → okay

How to highlight top values

Conditional formatting → highlight top/bottom → top 10 → adjust number

Which kinds of conditional formatting show physical highest and lowest values?

Icon sets and data bars

=SUMIF or =AVERAGEIF

Finds the sum or average of a column with certain criteria

How to toggle between regular view and formula view

Ctrl + ~ button

What types of numbers are stored as text?

Zip codes and ID numbers

Why are some numbers stored as text?

Usually because you won’t do math with these numbers, especially ones that start with 0’s that must be show in cells

=IF Formula

=IF(Logical Test, True, False)

How to make default font bigger

File → Options → Body font → down or up → close out of excel

How to count how many days

=days(end date, start date)

How to count how many years

=yearfrac(start date, end date, basis [usually 1])

How to count how many years in terms of age

=datedif(start date, end date, “Y”)

How to count how many months in terms of age

=datedif(start date, end date, “M”)

How to count how many months past years in terms of age

=datedif(start date, end date, “YM”)

How to count how many days past months past years in terms of age

=datedif(start date, end date, “MD”)

How to make all cells the same size

Highlight all cells → pull highlighted section to the right

How to put city and state together

=[city cell]&”, “&[state cell]  OR  =concatenate(text 1, “, “, text 3)

How to find GPA

=iferror(quality points/credits attempted, “ “)

True or False: There can be spaces wherever in single word cells

False

How to separate city and state

=textbefore([cell with data], “,”) AND =textafter([cell with data], “, ”)

How to get specific characters from cells with no space into another cell

=left([cell with data], [number of characters wanted])  OR  =right([cell with data], [number of characters wanted])

Input Mask

A set of rules that define the format in which data is entered

How to create full zipcodes

Home → Number → Box with arrow in bottom right corner → special → zip + 4

How to find the middle of a cell

=mid(text, [how many characters until start counting], [number of characters])

How to separate a first and last name

=textbefore([CWD], “_”)

How to put a first and a last name together

=concatenate([last name], “_”, [first name])

What function can also separate a first and last name and why is it different from the other function?

Flash fill; it doesn’t change if you make changes to original cell with data

How to count with more than one condition

=countifs([column with data], criteria, [column with data], criteria 2)

How to align/always see first column

Click in B1 → view tab → freeze panes

How to highlight a whole row in conditional formatting

Highlight all information → conditional formatting → “use formula to determine which cells to format” → = → click on CWD BUT get rid of the second “$” → then input whatever rule you need to be fixed

How to find the sum of multiple criteria

=sumifs(sum range, criteria range, criteria, criteria range, criteria, etc.)

How to sort a list of data

Sort and filter → custom → sort by __

Another way to just show a certain criteria

Highlight all data → data tab → filter → dropdown button → deselect and select certain criteria (but for numbers use the number filters)

What is the downside of using the filter button?

It’s not clear what you’re sorting

How to insert a column

Home → insert sheet column → highlight column → clear button → clear all

How to create a set of answers with specific criteria

Data tab → data validation button → click any value drop button → list → type into source whatever boundaries

If you’re using =datedif, what would you input for end date if the date is today?

today()

With grades, how many decimal points do you round to?

One

How to do data validation with a certain number range

In validation, replace “any value” with “whole numbers” which will give you a min/max range

Pro Bono

For the good of the public

Quid Pro Quo

Something for something

e.g.

Exempli gratia; for example

i.e.

Id est; in other words

Not-Equal-To Formula

=IF ([CWD] <> “", data, data)

“" means what?

Something

Ambiguous

Unclear

How to use the advanced filter

Highlight ALL data → go to data tab → advanced → highlight criteria → press okay (for multiple criteria, just add more cells in the appropriate direction)

For the advanced filter, cells side by side mean what?

And

For the advanced filter, cells above and below each other mean what?

Or

Tacit

Unspoken

True or False: Pivot table data refreshes

False

How to refresh pivot table data

Go to pivot table analysis and press refresh

How to change from sum to average in a pivot table

Right click on grand total in pivot table → summarize values → pick average

Where is the insert slicer button?

In the pivot table analyze button