00 Introduction |
1 |
0001 Welcome |
|
4.74 MB |
|
|
2 |
0002 Using the exercise files |
|
397.77 KB |
|
|
01 Formula and Function Tips and Shortcuts |
3 |
0101 Using the entire row column references |
|
6.22 MB |
|
|
4 |
0102 Copying column formulas instantly |
|
4.64 MB |
|
|
5 |
0103 Converting formulas to values with a simple drag |
|
4.93 MB |
|
|
6 |
0104 Creating 3D formulas to gather data from multiple sheets |
|
4.98 MB |
|
|
7 |
0105 Updating values without formulas |
|
4.61 MB |
|
|
8 |
0106 Displaying and highlighting formulas |
|
8.48 MB |
|
|
9 |
0107 Simplifying debugging formulas |
|
6.77 MB |
|
|
10 |
0108 Enhancing readability with range names |
|
10.34 MB |
|
|
02 Formula and Function Tools |
11 |
0201 Reviewing function basics |
|
10.12 MB |
|
|
12 |
0202 Using and extending AutoSum |
|
4.67 MB |
|
|
13 |
0203 Using absolute and relative references |
|
4.39 MB |
|
|
14 |
0204 Using mixed references |
|
4.99 MB |
|
|
03 IF and Related Functions |
15 |
0301 Exploring IF logical tests and using relational operators |
|
4.00 MB |
|
|
16 |
0302 Creating and expanding the use of nested IF statements |
|
3.80 MB |
|
|
17 |
0303 Using the AND, OR, and NOT functions with IF to create compound logical tests |
|
6.28 MB |
|
|
04 Lookup and Reference Function |
18 |
0401 Looking up information with VLOOKUP and HLOOKUP |
|
4.96 MB |
|
|
19 |
0402 Finding approximate matches with VLOOKUP |
|
6.46 MB |
|
|
20 |
0403 Finding exact matches with VLOOKUP |
|
3.84 MB |
|
|
21 |
0404 Nesting LOOKUP functions |
|
6.22 MB |
|
|
22 |
0405 Finding table-like information within a function with CHOOSE |
|
3.14 MB |
|
|
23 |
0406 Locating data with MATCH |
|
5.13 MB |
|
|
24 |
0407 Retrieving information by location with INDEX |
|
4.71 MB |
|
|
25 |
0408 Using MATCH and INDEX together |
|
6.61 MB |
|
|
05 Power Functions |
26 |
0501 Tabulating information on a single criterion with COUNTIF, SUMIF, and AVERAGEIF |
|
4.37 MB |
|
|
27 |
0502 Tabulating information on multiple criteria with COUNTIFS, SUMIFS, and AVERAGEIFS |
|
7.38 MB |
|
|
06 Statistical Functions |
28 |
0601 Finding the middle value with MEDIAN |
|
1.66 MB |
|
|
29 |
0602 Ranking data without sorting with RANK |
|
5.03 MB |
|
|
30 |
0603 Finding the magnitude data with LARGE and SMALL |
|
1.61 MB |
|
|
31 |
0604 Tabulating blank cells with COUNTBLANK |
|
4.00 MB |
|
|
07 Data Functions |
32 |
0701 Understanding Excel date time capabilities in formulas |
|
6.49 MB |
|
|
33 |
0702 Identifying the day of the week with WEEKDAY |
|
5.60 MB |
|
|
34 |
0703 Counting working days with NETWORKDAYS |
|
2.67 MB |
|
|
35 |
0704 Determining a completion date with WORKDAY |
|
2.06 MB |
|
|
36 |
0705 Tabulating date time differences with DATEDIF |
|
4.09 MB |
|
|
08 Math Functions |
37 |
0801 Working with rounding functions |
|
7.70 MB |
|
|
38 |
0802 Finding the remainder with MOD and using MOD with conditional formatting |
|
4.69 MB |
|
|
39 |
0803 Building random number generators with RAND and RANDBETWEEN |
|
5.33 MB |
|
|
40 |
0804 Converting a value between measurement systems with CONVERT |
|
3.61 MB |
|
|
41 |
0805 Using the powerful new AGGREGATE function to bypass errors and hidden data |
|
5.23 MB |
|
|
09 Array Formulas and Functions |
42 |
0901 Extending formula capabilities with arrays |
|
8.13 MB |
|
|
43 |
0902 Counting unique entries in a range with an array formula |
|
5.36 MB |
|
|
44 |
0903 Determining frequency distributions with FREQUENCY |
|
3.01 MB |
|
|
45 |
0904 Flipping row column orientation with TRANSPOSE |
|
4.37 MB |
|
|
46 |
0905 Building analysis via regression techniques with TREND and GROWTH |
|
3.76 MB |
|
|
47 |
0906 Combining multiple functions in arrays |
|
4.33 MB |
|
|
10 Text Functions |
48 |
1001 Locating and extracting data with FIND and MID |
|
5.56 MB |
|
|
49 |
1002 Extracting specific data with LEFT and RIGHT |
|
2.79 MB |
|
|
50 |
1003 Removing excess spaces with TRIM |
|
5.01 MB |
|
|
51 |
1004 Using CONCATENATE with functions |
|
3.64 MB |
|
|
52 |
1005 Adjusting case within cells with PROPER, UPPER, and LOWER |
|
4.41 MB |
|
|
53 |
1006 Adjusting character content with REPLACE and SUBSTITUTE |
|
5.47 MB |
|
|
54 |
1007 Reviewing additional text functions |
|
5.23 MB |
|
|
11 Financial Functions |
55 |
1101 Calculating payments with PMT |
|
3.86 MB |
|
|
56 |
1102 Finding future values with FV |
|
3.33 MB |
|
|
57 |
1103 Determining total amount of future payments with PV |
|
3.90 MB |
|
|
12 Information Functions |
58 |
1201 Working with the IS information functions |
|
4.00 MB |
|
|
59 |
1202 Using error-checking functions ISERR, ISERROR, IFERROR |
|
6.15 MB |
|
|
13 Reference Functions |
60 |
1301 Getting data from remote cells with OFFSET |
|
3.92 MB |
|
|
61 |
1302 Returning references with INDIRECT |
|
9.21 MB |
|
|
14 Conclusion |
62 |
1401 Final thoughts |
|
738.14 KB |
|
|
15 ZZZ - فایلهای تمرینی |
63 |
Ex_Files_Excel_10_Ad_Form |
|
820.28 KB |
|
|