Untitled Flashcards Set

Formulas and Functions

• Add

  • =cell+cell

• Subtract

  • =cell-cell

• Multiply

  • =cell*cell

• Divide

  • =cell/cell

• Sum

  • =Sum(cell,cell) → add two cells

  • =Sum(cell:cell) → adds a range of cells 

• Min

  • =min(cell:cell) → finds min value from a range of cells 

• Max

  • =max(cell:cell) → find max value from a range of cells

• Count

  • Count all cells that have numbers in them

  • =count(cell:cell)

• Counta

  • Counts all cells that have value in them whether they’re numbers, words, etc.

  • =counta(cell:cell)

• Average

  • =average(cell:cell)

• Median

  • Middle most value

  • =median(cell:cell)

• Concatenate

  • Taking one and connecting to answer like Abigail Casino

  • =concat(cell, cell)

  • Can also add space by putting in another blank cell

• If

  • Ex: =if(D13=”smile”,”yay”,”boo)

    • We're typing this formula into cell B13

      • If cell D13 says “smile”, then B13 should say “yay”. If it doesn’t say “smile”, then B13 should say “boo”.

• Countif

  • To count the number of cells that meet a criterion 

  • =countif(range, criteria)

• Vlookup

  • =Vlookup(lookup_value, table_array, number of columns, false)

    • False = exact match

    • True = appropriate match

• Sumif

  • Sum the values in a range that meet criteria that you specify 

  • =sumif(range, criteria, sum_range)

• Current Time

  • =now()

  • Exact time

  • Date and military hours 

Copy & Paste

• Basic Copy and Paste options

  • Copy: Control+P

  • Past: Control+V

  • Cut and Paste:

    • Control + X

    • Control + V

• Keyboard shortcuts

• Values without formulas or formatting

  • Paste values (2nd)→ no formatting, just values 

• Copy Paste Formulas

  • Paste formulas (3rd) → only pastes the formulas 

• Copy Formatting

  • Paste formatting (5th) → only pastes the formatting of values 

• Copy Cell Links

  • Paste link (6th) → linking values

• Advanced Paste Functions

  • Paste no borders → copy and paste table but with no borders

• Paste Column Width

  • Paste column width → copy and paste the exact same table with the same width and size 

• Transpose Your Fields

  • Paste transpose (4th) → copy and paste your table either a horizontal or vertical table 

  • A paste special option for pasting a copied block of  cells with rows and columns flipped 

• Paste Special Options

  • Paste Special 

    • Can multiply values by other values and paste them in one go

Excel Order of Operations

• PEMDAS

Absolute and Relative 

•  Relative cell address:

  • One that changes when a formula is copied relative to where it is being copied

• Absolute cell addresses:

  • One that remains the same when the formula is copied 

  • F4 → insert dollar signs

    • Tell’s excel, as this formula is being filled down, don’t change this cell address 

Goal Seek

Key Terms:

• Workbook: an excel file, normally saved in .xlsx format 

• Worksheet: a grid of cells in rows and columns. One workbook may contain several worksheets.

• Ribbon: the band across the top of a worksheet, w/ tabs containing icons for commands. 

• Cell Address: the column and row of an individual cell, such as B2

• Formulas: an expression that begins with “=” and performs a calculation. It often refers to other cells. 

• Autofill: a feature for entering content into several cells at once, with automatic adjustment of cell references. 

• Fill handle: the small square in the lower right corner of a selected cell or block of cells. Click-dragging activates autofill. 

  • If you double click on the fill handle, excel will take a guess where you want copies of the formulas. 

• Functions: a built-in formula. For example, the SUM formula adds many cells together. 

• Cell Range: a block of cells with a first and last cell address, such as C2:C5

• Accounting format: #s appear as quantities of currency, with two decimal places to the right and with a currency symbol such as “$” typically on the left and left-justified 

• Currency format: a format just like accounting format, except that the currency symbol is immediately to the left of the number instead of left-justified

• Number format: #s simply as #s, w/ no comm separators 

• General format: a format just like # format, but with decimal places shown even when they are all zeros 

• Percentage format: a numerical format that shows decimal numbers as percentages 

• Absolute reference: a cell address with a dollar sign before the row and before the column. This keeps the reference from changing when the formula is copied to another cell

• Relative reference: a cell address with no dollar sign in it. The reference changes when the formula is copied or autofilled to another 

• Mixed References: a cell address w/ a dollar sign before the row but now before the column, or vice versa. When the formula is copied or autofilled to another cell, the address part with the dollar sign stays the same but the other part changes. 

• Wrap text: a command for forcing text to stay within its cell by wrapping back

• Home tab: the ribbon tab with the most commonly used commands. 

• Paste Special: A command with options for controlling the format in which contents are pasted into a worksheet

• Match Destination Formatting: a command for forcing pasted content to take on whatever formatting is already present in a worksheet 

  • Same menu as paste special 

• View tab: a ribbon tab with commands related to how a sheet displays 

• Freeze Panes: a command for holding upper rows and columns on the left fixed, so they always remain visible 

• Sort: a command for ordering rows based on the contents of one or more columns 

  • Data tab and click the sort icon 

• Data tab: a ribbon tab with commands related to manipulating data in a worksheet

• Filter: a command for displaying some rows but not others, based on entries in columns

• Table: a block of data with special features that makes the data easier to read and analyze 

• Total Row: in a table, the line at the bottom with various options for summarizing info in columns. 

• Insert tab: a tab w/ command for inserting charts and other special elements 

• Clustered column chart: a chart with categories on the horizontal axis and vertical columns that stand for numeric values

• Chart design tab: a tab with commands for adjusting the way charts display 

• Clustered bar chart: a chart with categories on the vertical axis and horizontal bases that stand for numeric values

• Pie Chart: a round chart that is divided into variously sized wedges to represent each category’s share of a total

• Combo chart: a chart that combines two or more kinds of info, using different formats. 

  • Line chart: a chart that connects data points with a continuous line.  

• Scatter Chart: a chart that shows the relationship between two numeric variables by plotting pairs of values as points

• Trendline: a line showing the general relationship between two numerical variables 

• Criteria: a condition used in an IF-related function to identify certain cells in a range 

• Text String: a variable treated as a string of letters rather than as a # (even when all characters in the string are numerals)

• SUMIF: functions that add up the content of cells which satisfy one or more criteria 

• VLOOKUP: a function for looking up data in a table by reading down a column and then across a row 

• HLOOKUP: a function for looking up data in a table by reading across a row and then down a column

• Conditional formatting: rules for automatically formatting a cell based on its contents 

• Pivot Tables: a tool for analyzing large data tables to identify patterns

• Cross-Tabulation: using rows and columns in a pivot table to explore the relationship between two data fields