Excel Flashcards
What are Named Ranges?
These allow you to define terms or values in a single place, that will be used throughout the whole workbook. You can create them yourself. Click on Formulas –> Defined Names/Name Manager.
Or, you can just highlight the cell(s), type the name you want into the menu in the upper left, and hit Enter.
What are Sparklines?
Sparklines are mini charts placed inside single cells. Highlight a group of data, click the icon that pops up, then click the Sparklines button.
Explain the SUMIF Function
Sum a particular list of values, if a certain condition is met. Example, “>100”, each cell that is over 100 will be summed.
Explain how to Sort and Filter
Select any ONE cell in a “table”, then click Home –> Sort & Filter.
To filter (to create little menu icons), highlight the whole “table”, then click Home –> Sort & Filter –> Filter.
Explain the Text to Columns feature.
This is useful for if you have multiple data points within a single column, but you want to split it. For example, maybe you have First Name, Last Name, Company Name in one column. Go to Data –> Text to Columns and follow the wizard.
Pivot Tables.
Pivot Tables let you sort and view data easily. To create a Pivot Table, create a regular Table first. Then click a cell inside the table, and go to Insert –> Pivot Table.
The Pivot Table Pane should appear on the right. Click the checkboxes for which types of data you want to appear.
COUNTIF
Similar to SUMIF, but for counting the number of values.
Also, COUNTIFS, AVERAGEIF, MAXIFS and MINIFS.
NOW Function
Like =TODAY(), but also includes Time of Day. May or may not show date, depending on number formatting setting for that cell.
CTRL D and CTRL R
Highlight some cells, with 1 full cell and some empty cells following it.
CTRL D will “Fill Down” (like if you grab the little cross in the corner of the cell and drag down).
CTRL R will “Fill Right”
Or, double-click the little cross in the corner of the cell.
Transpose.
When you need to rotate columns and rows, you transpose them in Excel.
Copy, then paste special (it is an option in the box that pops up, or you can go to the Paste button on the Home screen).
You can also do it using the function TRANSPOSE. (Select multiple cells first, then start typing) Then push CTRL + SHIFT + ENTER
VLOOKUP
This function lets you look up a value in a column on the left, then return the value of a column to the right if it finds a match.
Syntax:
=VLOOKUP(what you want to look for, where you want to look, how many columns to the right, TRUE or FALSE (exact or approximate match).
Data Validation.
This can create “limits” or “restrictions” to what may be typed into a cell. It can be values, dates, specific text, etc. Click Data –> Data Validation to set it up.
You can also link it to a table, so that it automatically updates if you need to add a department, for example.
Charts and combo charts.
Click the data, then go to Insert –> Recommended Charts.
A combo chart is one that has a secondary axis to display additional data. It is sort of like having 2 charts in one. To make a combo chart, go to Recommended Charts, then All Charts tab, then Combo Chart (Near the bottom).
SUMIF
SUMIF lets you take the sum of certain values in a range, based on the values in another range.
Syntax:
=SUMIF(Range to look at, Value to look for, Range to sum for each match found)
SUMIFS is similar, but lets you use multiple criteria.
Tables.
You can create a real Table, which helps make the data easier to work with. Go to Insert –> Table.
