IT midterm 2025 - excel

MGCR 331

ribbon

set of menus at the top of the excel program window, switch between by clicking the ribbon name

context menu

set of options appearing when you right click an object. quickly access common formatting choices like copy, paste, clear contents, delete, etc

right click on a sheet tab

rename sheet, or make a copy

function def.

shortcut that allows you to do a specific task/calculation. excel has many built in functions

must be proceeded by a = in order to work

function structure general

=FUNCTIONNAME(argument1, argument2)

• no spaces in the naming

entering and formatting data into the excel sheet

• left click cell to activate

• press tab key to enter next cell or left click another cell

formatting text and numbers

automatically left justifies text and right justifies numbers

to change: home ribbon> options in number section

very big/small numbers are in scientific notation or “###” across its width

adjust width of a column

1. drag the boundary

2. Hover mouse over the dividing line> double click

sort data

data ribbon

• numerical

• alphabetical

sort wizard

1. indicate if data has headers or not

2. choose from dropdown, which col to sort

3. choose how data is to be ordered:

   1. text A-Z or Z-A

   2. small- large or large- small for numbers

add level: first sort, second sort

cell references def

using a cells address and not explicit value, soft coding

1. organization

2. model builder adds notes and documentation to explain how you got values

3. useful tools created

4. easy to change

5. enables goal seek and data tables

column(letter)row(number)

relative references def

refer to the position relative to the active cell, so this changes with where the mouse is

absolute referencing def

excel locking a reference (won’t change depending on where in the sheet you are)

do this by putting in a $, eg. $A$1 (can leave row or column relative like $A1 or A$1 also)

mathematical functions in excel

place = before the formula

follows BEDMAS

basic functions (requires at least one #):

• =SUM() - addition

• =PRODUCT() - multiplication

requires 1 #:

• =SQRT() - takes the square root

• =EXP() - takes the exponent

• =LN() - takes the natural log

inequalities, comparisons

inequalities:

• <= less than, equal to

• >= greater than, equal to

• <> not equal to

=#a=#b (are they equal)

check comparisons excel, returns output true or false

=and

evaluates true if and only if all included arguments are true

=AND(#a>#b, #d>#c)

=or

evaluates true if any statements are true

=OR(3>=5, 7<1) would return false

=if

user specifies preferred output depending on if logical statement is true or false

=IF(logical_test,[value_if_true],[value_if_false])

=count

identifies the number of numeric items in dataset → arguments can be a list or reference to a range

=count(value1,[value2])

=counta

identifies all items in a dataset, not just numeric values (eg can calculate number of non blank cells) → can be a list or a reference to a range

=counta(value1,[value2])

min and max

=min(number1,[number2]) → smallest number in a given set

=max(number1,[number2]) → biggest number in a given set

small and large - beyond max and min

=small(array,k) → returns the kth smallest number

=large(array,k) → returns the kth biggest number

rank

position of observation relative to the other data

=rank.eq(number, ref,[order])

returns rank in list of numbers, the ref

order: optional argument designating ascending =1 or descending =0

mean

=average(number1,[number2])

median

=median(number1,[number2])

variance

=var.s(number1,[number2])

standard deviation

=stdev.s(number1,[number2])

covariance

=covariance(array1, array2)

correlation

=correl(array1,array2) on a fixed scale from -1 to +1

=countif

counts the number of cells within a specified range meeting a specific criteria → criteria assumed to be an equivalence logic test but an inequality can also be specified

=countif(range, criteria)

=countifs

=countifs(criteria_range1,criteria1,[criterai_range2,criteria2])

this will count the number of cells within a specified range that meet all the specified criteria, each criteria range needs to have the same length

=sumif

will sum the values in the first range provided they meet the criteria

=sumif(sum_range,criteria_range1,criteria1)

=sumifs is for many criterias

=averageif

averages the values in first range provided they meet the criteria

=averageif(average_range,criteria_range1, criteria1)

=averageifs is for a range of numbers provided they meet many criteria

naming in excel

names cannot have spaces and must start with a letter, underscore, or backslash

• name for each cells must be different

• can reference the cell name you gave the range in formulas

=countblank(range)

counts the empty cells in a range

=unique

returns a list of unique values in a list or range

=unique(array,[bycolumn],[exactlyonce])

=round()

round to a specified number of digits

eg. =round(a1,2) will round a1 to 2 decimal places

=not()

want to make sure one value doesnt equal another

=not(logical_value)

freeze panes

view>freeze panes

conditional formatting

make patterns and trends more apparent in data, drop down menu, can apply rules

autofill

fills cells with data that follows a pattern or are based on data in other cells

flash fill

automatically fills your data when it senses a pattern

select array>data>flash fill

data validation

restricts the data type or the values that users enter into a cell like a drop down list

dropdown list:

data>data validation>settings: allow list > source box: type text that will be your options for the drop down

excel errors: #value!

finds spaces, characters, text, in a formula expecting a number

excel errors: #name?

excel cannot understand the name of the formula you’re trying to run

excel errors: ########

column isn’t wide enough to display the value you’ve inputted

excel errors:#div/0

dividing a formula by zero or an empty cell

excel errors:#ref!

formula is referencing an invalid cell

excel errors: #null!

when you specify an intersection of two areas that don’t intersect or when an incorrect range operator is used

excel errors: #n/a

numbers you’re trying referring to in your formula aren’t found

excel errors: #num!

contains invald numeric values

=xlookup

=xlookup(lookup_value,lookup_array,return_array)

with ranges:

=xlookup(lookup_value,lookup_array,return_array,[if_not_found],[match_mode],[search_mode])

match_mode:specify the match

• default 0 - exact match and if none, return #n/a

• -1 - exact match but if not found then return next smaller item

• 1 - exact match but if none found then return the next bigger item

nested xlookups:

=xlookup(lookup_value,lookup_array,(xlookup(lookup_value,lookup_array,return_array))

pivot tables

cut data up to use more readily

goal seek, what if analysis

• set cell to what you want to be the desired number

• to value: to the desired value, type in

• by changing cell: the cell that you need to change to get desired set cell

highlight the one you want to change to goal before even opening menu

data>what if analysis

data tables, what if analysis

See how different input values in single or multiple variables in a formula change the results of the formula. useful for sensitivity analysis

• one variable - different inputs for a single variable → either row or column

• two variables - row and column

highlight entire range of where you want to do the data table

data>what if analysis

financial functions: payment =pmt

=pmt(rate, nper, pv, [fv],[type])

financial functions: present value =pv

=pv(rate, nper, pmt, [fv],[type])

financial functions: future value =fv

=fv(rate, nper, pmt, [pv], [type])

put pmt and pv in as negative values

financial functions: type as an optional argument

type is the payment type

→ options:

0 or nothing (default): at the end of the period

1: at the beginning of the period

date

=DATE(year, month, day)

date serial number

days from 1900

prints todays date

=TODAY()

get the full name for the day

“DDDD” in text function

combine text

=CONCAT(text1,[text2])

find

=FIND(find_text,within_text,[start_num])

left

=LEFT(text, [num_chars])

right

=RIGHT(text,[num_chars])

middle

=MID(text,start_num,num_chars)

remove extra space from the text

=TRIM(text)

length (number of characters in string)

=LEN(text)

excel macros

powerful way to record actions you want repeated

use cases for macros

recording formatting highlighting hiding

can connect to other apps

get macros

developer section

drawback of recording macros

have to practice bc every click is recorded