Reading...
Play button
Play button
Use LEFT and RIGHT arrow keys to navigate between flashcards;
Use UP and DOWN arrow keys to flip the card;
H to show hint;
A reads text to speech;
7 Cards in this Set
- Front
- Back
|
What does the CONCATENATE function do and what is the formula?
What is a simpler way to use the CONCATENATE function? What are the formulas for returning characters on the beginning / end of a string? What function determines the # of characters in a string? What function removes all spaces in a text string except those in between words? |
It combines the value of a cell / range of cells into 1 text item in a new cell
=CONCATENATE (text1,text2..) Use '&' sign (ie. =F11&G11) *use &TEXT() to format a number a certain way! =RIGHT(text, num_chars) =LEFT(text, num_chars) *returns all specified characters from the beginning (left) or the end (right) =LEN(text) =TRIM(text) |
|
|
What formulas return the starting position of one text value within another text value? What is the difference between them?
How do you convert all text to lowercase? How do you Capitalize Each Word? How do you make EACH LETTER AN UPPERCASE? How do you replace specified old text for new text? How do you replace old text for new text based on number of characters? What does the TEXT function do? How do you find out if 2 text strings are identical (case sensitive)? |
=FIND(find_text, within_text, [start_num])
*SEARCH is the same function except it is not case sensitive. If start num is ommitted it will assume a starting value of 1. =LOWER(text) =PROPER(text) =UPPER(text) =SUBSTITUTE(text, old_text, new_text, instance_num) =REPLACE(old_text, start_num, num_chars, new_text) It formats values into text in a way you want. =TEXT(value, format_text) *to obtain the format you want go to the custom section of format cells to see the coding for each format =EXACT(text1, text2) |
|
|
What formula calculates the current date?
What function calculates the number of years between two dates? What is a hidden function that calculates the number of units b/w two dates? How do you separate the various parts of a date? How do you combine them? What does EDATE do? EOMONTH? What functions returns the current date and time? (The cell is updated when the file is ____, ____, or ____.) |
=TODAY()
=YEARFRAC(start,end,basis) basis = lengths of months and years.... 0 or omitted US (NASD) 30/360 1 Actual/actual more... =DATEDIF(start_date, end_date,unit) units - "y" = years, "m" = months, "d" = days, "yd" = days ignoring yrs, "ym" months ignoring years, "md" days ignoring months/years =YEAR(date) =MONTH(date) =DAY(date) =DATE(year, month, date) Adds months to a date =EDATE(date, # of months to add) Finds last day of month after specified # of months: =EOMONTH(start date, months) =NOW() opened, saved, edited. |
|
|
The ______ tool, located in the ____ tab, allows you to organize data into categories with subtotal calculations.
T/F: You can work with only 1 category and 1 subtotal calculation at a time. What should you always do before subtotaling a range of cells? What is an Excel table? It is located in the ______ tab. The heading rows allow you to ____ and ______ the data. *Be careful AutoFilter only filters the results for the naked eye. A sum function for all the cells would still yield the same result. How do you add data into an Excel Table? How do you remove the Excel Table definition? |
Subtotal; Data
TRUE. Sort the column you would like to group the subtotal data by. a range of cells that you formalize as a single unit of data. You should insert empty rows columns around it to sequester the data. Insert. sort; filter. By selecting the last cell in the last row and pressing 'Tab' By clicking Convert to Range. |
|
|
What is the difference between Excel and Access?
When are databases better to use than spreadsheets? Importing data from Access and other DB's is in the ____ tab. In order to filter the data from the database, it may be appropriate to use the _______. |
Excel is a spreadsheet program. Access is a database program. A database ensures the right data is collected and protected. A spreadsheet provides analytical power and flexibility.
When there is a lot of data, or the data is mostly non-numeric. Excel's flexibility also allows it to be unintentionally corrupted. Data Query Wizard. |
|
|
What does a Pivot Table do?
Each field can be place into 1 of 4 categories. Name them. |
It allows you to analyze data from a table from a variety of perspectives.
1. Report Filter 2. Column Labels 3. Row Labels 4. Values |
|
|
The SUBTOTAL function will comply with ignoring an Excel Table filtered results. (ie. if AVERAGE =.... is used, when the table filters out results, AVERAGE will still include them. SUBTOTAL will not)
Formula? |
=SUBTOTAL(function_num, ref1, ref2, ...)
function_num is the following 1 = AVERAGE 101 = AVERAGE excluding hidden rows 2 = COUNT 102 = COUNT excluding hidden rows many more... (look up) |
