Download a PDF of this page here.

Download the Spanish version here.

Essential Concepts

Formatting and Layout

With conditional formatting we can apply different formats (highlight, bold, strikethrough, font color, etc) depending on the information contained in the cell. This can be based on the size of the numbers, the date if given a date, or the contents of the text.

While Excel excels with online data organization, it’s sometimes necessary to print your workbook or individual worksheets. In this case we can use the page layout and margin settings to adjust our data and make it print-friendly.

Pivot Tables

The PivotTable tool offers a way to reorganize and summarize data in a spreadsheet. Generally best for multi-column data, a PivotTable can help identify sums, averages, and trends in a dataset. We recommend using “Recommended PivotTables” when just starting out, but there are many custom PivotTables that can be used for data analysis.

Macros

Macros are programs we build in Excel using Visual Basic and help us automate steps that we perform frequently on the data in our daily work. To create a macro we can record, or code, the steps we want Excel to be able to follow. Then, once recorded, we can use the macro to repeat those steps for us.

Common actions for excel macros include adding filters, removing duplicates and blanks, and conditional formatting, but the list is endless.

Functions and Formulas

A function is a pre-built formula in Excel. Remember, all formulas and functions begin with an equal sign (=””). Here are the main functions introduced in this module:

• Lookup functions: will search for a keyword, then return the data in a connected row or column
  • VLOOKUP: =VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup]) – searches the first column for a keyword, then returns the data in a neighboring column of the same row when found
  • HLOOKUP: =HLOOKUP(lookup_value,table_array,row_index_num,[range_lookup]) – searches the first row for a keyword, then returns a value in the same column from a row you define.
  • Match: =MATCH(lookp_value,lookup_array,[match_type]) – returns the position of a value in a given range
  • Index: =INDEX(reference, row_num,[column_num],[area_num] – returns the position of a value that looks at the intersection of a row and a column.
• Logical functions: evaluate whether the statement and data are TRUE or FALSE according to the formula
  • Nested IF rules: if your IF statement has more than one logic test creates a nested if rule. For example: =IF(logical_test,[value_if_true],[value_if_false],IF(logical_test,[value_if_true],[value_if_false]))
  • AND: =AND(logic1,logic2) returns true only if BOTH conditions are met
  • OR: =OR(logic1,logic2) returns true if EITHER condition is met
  • NOT: returns the opposite
  • IFERROR: =IFERROR(value, value_if_error) – allows you to set an error message or a default value if the formula does not work.
• Financial functions
  • Payment (PMT): =PMT(rate,nper,pv,[fv],[type]) – returns the payment amount assuming the payments are made on a consistent basis
  • Number of periods (NPER): =NPER(rate,pmt,pv,[fv],[type]) – returns the number of periods needed to pay a loan to a specified future value
  • Rate (RATE): RATE(nper,pmt,pv,[fv],[type],[guess]) – returns the interest rate given the other key information of the loan
  • Present Value (PV): =PV(rate,nper,pmt,[fv],[type]) – returns the current value of a loan given the other inputs
  • Future Value (FV): =FV(rate,nper,pmt,[pv],[type]) – returns the future value of a loan after a given number of periods

Internet and Excel

Add-ins are available on Windows and MacOS and can be found in the File->Options menu under Add-ins. Excel add-ins have the extension xlam, xla, or xll.

ToolPack adds additional data analysis tool, but you can also find add-ins from the internet. Among other things you can import from the internet are pictures, hyperlinks, and even full data tables!

Glossary

add-ins

tools that add extra features and capabilities to Excel, such as Analysis ToolPak and Solver.

AND

a logical operator that returns TRUE if all its arguments are true.

conditional formatting

a feature that changes the appearance of cells based on specific conditions.

filters

tools used to display only the rows in a table that meet certain criteria.

formula

an expression that calculates the value of a cell.

function

a predefined formula that performs calculations using specific values, called arguments.

FV (future value)

a financial function that calculates the future value of an investment.

greater than

a condition used in logical expressions to compare if one value is larger than another.

highlight cell rules

a type of conditional formatting used to highlight cells that meet specific criteria.

HLOOKUP

a lookup function that searches for a value in the top row of a table or array and returns a value in the same column from a specified row.

IF

a logical function that returns one value if a condition is TRUE and another value if it is FALSE.

IFERROR

a function that returns a specified value if a formula results in an error.

INDEX

a function that returns the value of a cell in a specified array or range.

landscape

a page orientation that prints the document horizontally.

less than

a condition used in logical expressions to compare if one value is smaller than another.

logical operators

operators used to combine two or more conditions (AND, OR, NOT).

lookup functions

functions used to search for and return data from a specific location in a table.

macro

a set of instructions that automate tasks in Excel.

MATCH

a function that searches for a specified value in a range and returns the relative position of that value.

margins

the space around the edges of a printed page.

nested IF

a complex IF function with multiple if conditions embedded within each other.

NOT

a logical operator that returns the opposite of the given logical value.

NPER

a financial function that calculates the number of periods for an investment.

orientation

the direction in which a document is printed (portrait or landscape).

OR

a logical operator that returns TRUE if any of its arguments are true.

page layout

the arrangement and formatting of elements on a printed page.

PMT

a financial function that calculates the payment for a loan based on constant payments and a constant interest rate.

portrait

a page orientation that prints the document vertically.

PivotTable

a tool that summarizes and analyzes data from a large table.

PV (present value)

a financial function that calculates the present value of an investment.

RATE

a financial function that calculates the interest rate for a loan or an investment.

rules

conditions defined in conditional formatting to apply specific formatting to cells.

VLOOKUP

a lookup function that searches for a value in the first column of a table and returns a value in the same row from a specified column.