Excel Skills Exam 2 Flashcards
Freeze Panes
Freeze one cell below or to the right of the data you want to freeze
Click “View” and “Freeze Panes”
Simple Sort
Click a cell in the column you want to sort
Click Data
Click Sort & Filter (A to Z or Z to A)
Multilevel Sort
Click a cell in the column you want to sort
Click Sort & Filter
Select Custom Sort
Select Add Level
Select the column to sort by from the drop-down menu
Select the second column to sort by
One day
24 hours
One hour
1/24 = 0.041666
Noon
Halfway through the day
0.5
Week later
Add 7
Year later
Add 365
Net30
Add 30
Period employed
Termination Date - Hiring Date
Date Functions
=DATE(yr,mo,day)
=YEAR(datevalue)
=MONTH(datevalue)
=DAY(datevalue)
1/1/1900
Assigned as 1
AND
Checks if all conditions are TRUE
=AND(condition1, condition2)
=AND(A1>10,A1<20)
OR
Checks if at least ONE of the conditions is TRUE
If all of them are false, it returns as FALSE
=OR(condition1, condition2)
=OR(A1>10,A1<5)
Combining AND with IF
If both conditions have to be true for it to be valid
=IF(AND(A1>10,A1<20),”Valid”,”Invalid”)
Combining OR with IF
If at least one condition has to be true for it to be valid
=IF(OR(A1>10,A1<5),”Out of Range”,”In Range”)
SEARCH
NOT case-sensitive
=SEARCH(find_text, within_text, [start_num])
find_text -> The word or letter you’re looking for
within_text -> The cell it’s in
The result is the character number that the word or letter starts at
LEFT
When you want to extract a specific number of characters starting from the left side (beginning) of a text string
=LEFT(text/cell, num_chars)
MID
Extracts a specific number of characters from the middle of a text string
=MID(text/cell, start_num, num_chars)
Sort order of Pivot Table
Right-click on the cell
Click Sort
A to Z or Z to A
or Custom Sorting
