Excel Exam Cheat Sheet PDF

Preview

Summary

We get a cheat sheet for each exam - this was mine for the Excel test...

Description

●

Top ten: select specific column’s drop down menu, select number filters, click top 10 ○ Output might have more than 10 cuz there might be ties

●

Pivot table ○ Click the PivotTable button in the Tables group on the Insert tab. ○ If necessary, adjust the range in the Table/Range text box under the Select a Table or Range option button. ○ Select the location for the pivot table - By default, Excel builds the pivot table on a new worksheet it adds to the workbook. If you want the pivot table to appear on the same worksheet, click the Existing Worksheet option button ○ Click OK. ○ To complete the pivot table, assign the fields in the PivotTable Field Lis ○ Report Filter: This area contains the fields that enable you to page through the data summaries shown in the actual pivot table by filtering out sets of data — they act as the filters for the report. So, for example, if you designate the Year Field from a table as a Report Filter, you can display data summaries in the pivot table for individual years or for ○ ○ ○

● ●

all years represented in the table. Column Labels: This area contains the fields that determine the arrangement of data shown in the columns of the pivot table. Row Labels: This area contains the fields that determine the arrangement of data shown in the rows of the pivot table. Values: This area contains the fields that determine which data are presented in the cells of the pivot table — they are the values that are summarized in its last column (totaled by default).

=$A$1 - put the $ when you want to keep this specific number for a function VLOOKUP ○ Click the cell where you want the VLOOKUP formula to be calculated. ○ Click "Formula" at the top of the screen. ○ Click "Lookup & Reference" on the Ribbon. ○ Click "VLOOKUP" at the bottom of the drop-down menu. ○ Specify the cell in which you will enter the value whose data you're looking for. In this case, our lookup value is H2, since this is where we will input the name of a tournament such as "PGA Championship," so we input "H2" in the lookup_value box of the popup window. Once we've set up VLOOKUP properly, Excel will return the tournament's Total Rating Value in cell H3 when we type the tournament name in cell H2. ○ Specify the data which you want VLOOKUP to use for its search in the table_array box. In this case, we've selected the entire table (excluding the headers ○ Specify the column number which VLOOKUP will use to find the relevant data in the col_index_num box. Somewhat confusingly, VLOOKUP requires you to use the numerical value of the of the column rather than its letter value. In this case, we want VLOOKUP to use the Total Rating Value column -- column D -- so we enter the number 4. ○ Specify whether you need an exact match by entering either FALSE (exact match) or TRUE (approximate match) in the range_lookup box. In this case, we want an exact match so we enter FALSE. ○ Click "OK" at the bottom of the popup window. ○ Enter the value whose data you're searching for. In our example, we want to find the Total Value Rating of the PGA Championship, so we type "PGA Championship" into cell H2 and VLOOKUP automatically produces the Total Value Rating (in this case, 914) in cell H3.

●

Using VLOOKUP, you can not only search for individual values, but also combine two worksheets into one. For example, if you have one worksheet with names and phone numbers and another sheet with names and email addresses, you can put the email addresses next to the names and phone numbers by using VLOOKUP. Example VLOOKUP Value

●

=VLOOKUP(B5,LookupTable!$A$4:$E$11,2,FALSE) =IF(VLOOKUP(=IF(E5="OR",VLOOKUP(B5,LookupTable! $A$4:$E$11,4,FALSE),VLOOKUP(B5,LookupTable!$A$4:E$11,5, FALSE)) Functions

○

○ ○ ○ ○

○ ○

○

○ ○ ○ ○

○ ○ ○

=MIN(G2:G14) - MIN( set of data ) =AVERAGE(G2:G14) - AVERAGE( set of data ) =ROUND(C17, 2) - ROUND(number, number of digits) =COUNTIF(E2:E14, “false”) - COUNTIF(range of data, condition) ■ Also instead of false can be “=1” “>6” or “=F” ● Need quotes even w/ numbers =COUNTIFS(E2:E14, “F”, H2:H14, “8, countif(ColumnE, Yes)>8), Good, Bad) ■ If there are more than 8 “Yes” for Column C and more than 8 “Yes” for Column E, we show “Good”. Otherwise, we show “Bad”. If(or(salary=2,E5=”SP”),”Yes”,”No”) EXAMPLE FROM HW...