1 2<!DOCTYPE html> 3<html lang="en-US"> 4<head> 5<meta charset="UTF-8" /> 6<meta name='viewport' content='width=device-width, initial-scale=1.0' /> 7<meta http-equiv='X-UA-Compatible' content='IE=edge' /> 8<link rel="profile" href="https://gmpg.org/xfn/11" />
9<script>
vendor: 327 bytes, lines 9-13
9(function(w,d,s,l,i){w[l]=w[l]||[];w[l].push({'gtm.start': 10new Date().getTime(),event:'gtm.js'});var f=d.getElementsByTagName(s)[0], 11j=d.createElement(s),dl=l!='dataLayer'?'&l='+l:'';j.async=true;j.src= 12'https://www.googletagmanager.com/gtm.js?id='+i+dl;f.parentNode.insertBefore(j,f); 13})(window,document,'script','dataLayer','
13GTM-M8T7N66
vendor: 3 bytes, line 13
13');
13</script>
13<script type="text/javascript" src="//store.excel-university.com/template/_default/js/jquery/jquery-3.2.0.min.js"></script>
vendor: 1 bytes, line 13
13
14<script type="text/javascript"> 15window.jquery_cm = jQuery.noConflict(true); 16</script>
vendor: 1 bytes, line 16
16
17<script type="text/javascript" src="//store.excel-university.com/template/_default/js/js-cookie/js.cookie.js"></script>
vendor: 1 bytes, line 17
17
18<script type="text/javascript"> 19window.cookies_cm = Cookies.noConflict(); 20</script>
20 21 22<meta name="keywords" content="Volume 1 - Freshman Year 23Foundations 24Selected Shortcuts 25Named References 26Tables 27Data Validation 28Conditional Formatting Basics 29Better Summing 30Skinny Row 31Hide Worksheets 32Highlight Input Cells 33Workbook Organization and Principles 34Workbook Organization 35Worksheet Organization 36Nested Functions 37Selection Groups 38Workbook Design Principles 39Volume 2 - Sophomore Year 40Conditional Summing 41Conditional Summing Basics with SUMIFS 42Remove Duplicates 43Lookups 44Lookup Basics VLOOKUP 45Improving VLOOKUP with MATCH 46Improving VLOOKUP with VALUE and TEXT 47Moving Beyond VLOOKUP with INDEX 48Trap Errors with IFERROR 49The IF Function 50List Comparisons 51The ISERROR Function 52Multicolumn List Comparisons with COUNTIFS 53Indentation 54Perform Lookups with SUMIFS 55Date and Text Functions 56Determine the Last Day of the Month with EOMONTH 57Date Parts â MONTH and YEAR 58Build Your Own Date with DATE 59Concatenation Basics 60Formula-based Reports 61Improve SUMIFS with CONCATENATE 62Dynamic Headers 63Horizontal Reports 64Mapping Tables 65Data Validation and Reporting 66Improving Error Check with Boolean Values and the Logical AND Function 67Concepts Applied 68Volume 3 - Junior Year 69PivotTable Fundamentals 70PivotTable Basics 71Row Fields 72Value Fields 73Column Fields 74Filter Fields 75Updating Data
76Groups and Subtotals 77Value Formats 78Report Type Comparison 79Working with PivotTables 80Options 81Report Layout and Design 82Multiple Value Fields 83Calculated Fields 84Calculated Items 85Show Values As 86Sorting 87Filtering 88Hybrid Reports with GETPIVOTDATA 89Obtain > Prepare > Summarize 90Web Data 91External Data 92Text Files 93Data Preparation 94Accounting System Data 95Getting Graphic 96Volume 4 - Senior Year 97Features 98Sorting & Filtering 99Outlining 100Formatting 101Printing 102Protecting 103Hyperlink Feature 104Functions 105HYPERLINK Function 106IF Alternatives 107Lookup Functions 108Text Functions 109Date Functions 110Insert Function 111Techniques 112Ribbon Review 113Conditional Formatting Revisited 114Names Revisited 115Wildcards Revisited 116Tables Revisited 117Start Here Revisited 118Applications 119JE Preparation 120Budget Ideas 121Common Workpapers 122 123 124Masters Program 125Masters Topics 126The Masters curriculum teaches technical Excel skills, and is broken down into two courses. 127 128Masters 1 covers Power Query, Power Pivot, Power BI, and table and graph design practices. 129Masters 2 demonstrates how to build macros with VBA (Visual Basic for Applications) to automate repetitive, routine tasks. 130Masters 1 131Power Query Topics 132Get external data 133Various transformations, including unpivot 134Load to data model and table 135Chain queries 136Merge queries 137Power Pivot Topics 138Relationships 139Measures (implicit / explicit) 140Various DAX functions 141Date (calendar) table 142Data vs lookup tables 143Power BI Topics 144Getting external data 145Modeling data and writing measures 146Various visuals (matrix, table, column, bar, line, scatter, map) 147Power BI desktop, service, mobile app 148Slicers (and sync slicers) 149Table and Graph Design Topics 150Display type: selecting a table or graph 151Chart design practices 152How to feature key relationships graphically 153Nominal (order not important) 154Ranking (order matters) 155Part-to-whole (contributions to whole) 156Time series (over time) 157Distribution (distributed over range) 158Deviation (difference) 159Correlation (vary in relation to each other) 160Geospatial (locations) 161Case Studies (practical applications of skills) 162Sales manager (nominal) 163Travel expenses for CFO (ranking, part-to-whole) 164Year-end sales promotion (time series) 165Budget vs actual (deviation) 166We Collect 4U (distribution) 167Employee surveys (correlation) 168Donor state map (geospatial) 169Dashboard 170Masters 2 171VBA Topics 172Visual Basic Editor 173Macro Recorder 174Excel object model, properties, methods 175Procedures, subs, functions 176Collections 177Variables, constants, scope, type, object variables 178VBA functions, MsgBox, InputBox 179Program flow, If Then, Exit, Labels, Select Case 180Loops, collection loops, For Next, Do While 181Error handling, On Error, Debugging, Watch/Locals 182Copy cell, range, table 183Charts, chart sheets 184Performance, ScreenUpdating 185Module variables 186Workbook, worksheet loops 187Names, StatusBar, variant/array, Split 188Case Studies (practical application of skills) 189Insert standard worksheet title 190Insert the SUBTOTAL function 191Prepare a workbook to deliver to staff 192Apply standard PivotTable design 193Apply standard chart design 194Export journal entries 195Update a set of budget workbooks 196Create PDF reports from workbooks"/> 197 198<style>.cm-visuallyhidden { 199 border: 0; 200 clip: rect(0 0 0 0); 201 height: 1px; 202 margin: -1px; 203 overflow: hidden; 204 padding: 0; 205 position: absolute; 206 width: 1px; 207} 208</style> 209<title>Study Pass (Monthly)</title> 210<link href='https://fonts.gstatic.com' crossorigin rel='preconnect' /> 211<link rel='stylesheet' id='wp-block-library-css' href='//store.excel-university.com/template/excel-university/css/style.min.css' media='all' /> 212<link rel='stylesheet' id='font-awesome-5-css' href='//store.excel-university.com/template/excel-university/css/all.min.css' media='all' /> 213<link rel='stylesheet' id='ultimate-icons-css' href='//store.excel-university.com/template/excel-university/css/style.css' media='all' /> 214<link rel='stylesheet' id='fl-builder-layout-bundle-deed8872d74b678f1c2ab2e955778250-css' href='//store.excel-university.com/template/excel-university/css/deed8872d74b678f1c2ab2e955778250-layout-bundle.css' media='all' /> 215<link rel='stylesheet' id='jquery-magnificpopup-css' href='//store.excel-university.com/template/excel-university/css/jquery.magnificpopup.min.css' media='all' /> 216<link rel='stylesheet' id='bootstrap-css' href='//store.excel-university.com/template/excel-university/css/bootstrap.min.css' media='all' /> 217<link rel='stylesheet' id='fl-automator-skin-css' href='//store.excel-university.com/template/excel-university/css/skin-5ef2595f0398e.css' media='all' />
218<link rel='stylesheet' id='fl-builder-google-fonts-3eff494ed45b1c1bc68d866328ea33e1-css' href='https://fonts.googleapis.com/css?family=Open+Sans%3A300%2C400%2C700%2C400%7CMerriweather+Sans%3A700&ver=5.4.2' media='all' /> 219<link rel="stylesheet" type="text/css" href="//store.excel-university.com/template/excel-university/css/coursemerchant.css" /> 220<link rel="stylesheet" type="text/css" href="//store.excel-university.com/template/excel-university/css/coursemerchant-accounts.css" /> 221<link rel="stylesheet" type="text/css" href="//store.excel-university.com/template/excel-university/css/coursemerchant-cartcheckout.css" /> 222<link rel="stylesheet" type="text/css" href="//store.excel-university.com/template/excel-university/css/coursemerchant-manage.css" /> 223<link rel="stylesheet" type="text/css" href="//store.excel-university.com/template/excel-university/css/coursemerchant-products.css" /> 224 <link rel="stylesheet" type="text/css" href="//store.excel-university.com/template/excel-university/css/tabcontent.css" />
225<script src='//store.excel-university.com/template/excel-university/js/jquery.js'></script>
vendor: 1 bytes, line 225
225
226<script src='//store.excel-university.com/template/excel-university/js/jquery-migrate.min.js'></script>
vendor: 1 bytes, line 226
226
227<script src='//store.excel-university.com/template/excel-university/js/imagesloaded.min.js'></script>
227 228
228<script src='//store.excel-university.com/template/excel-university/js/tabcontent.js'></script>
228 229<link href='https://fonts.googleapis.com/css?family=Open+Sans' rel='stylesheet' type='text/css'> 230<link rel="icon" href="//store.excel-university.com/template/excel-university/images/cropped-Excel_u-logo_for_Favicon-32x32.png" sizes="32x32" /> 231<link rel="icon" href="//store.excel-university.com/template/excel-university/images/cropped-Excel_u-logo_for_Favicon-192x192.png" sizes="192x192" /> 232<link rel="apple-touch-icon" href="//store.excel-university.com/template/excel-university/images/cropped-Excel_u-logo_for_Favicon-180x180.png" /> 233<meta name="msapplication-TileImage" content="//store.excel-university.com/template/excel-university/images/cropped-Excel_u-logo_for_Favicon-270x270.png" /> 234 <style id="wp-custom-css"> 235 .fl-page-content { 236 padding-top: 200px; 237} 238 239/* store, login, cart icons */ 240.secondary_nav { 241 color: #2e2e2e; !important; 242 padding: 0px 2.5px 2.5px 2.5px; 243} 244.secondary_nav a:link{ 245 color: #2e2e2e !important; 246} 247.secondary_nav a:hover{ 248 color: #c4c4c4 !important; 249} 250 251 252/* Hide "Posts" heading and separator on /blog page */ 253.blog .fl-node-5ee92d5544cac { 254 display: none; 255} 256.blog .fl-node-5ee92dfd7bb17{ 257 display: none; 258} 259 260/* Hide additional title and Edit link from top of each page */ 261.fl-post-header{ 262 display:none !important; 263} </style> 264 </head> 265<body class="blog fl-theme-builder-footer fl-theme-builder-header fl-theme-builder-archive fl-framework-bootstrap fl-preset-default fl-full-width" itemscope="itemscope" itemtype="https://schema.org/WebPage">
vendor: 68 bytes, lines 265-266
265 266<noscript><iframe src="https://www.googletagmanager.com/ns.html?id=
266GTM-M8T7N66
vendor: 82 bytes, lines 266-267
266" 267height="0" width="0" style="display:none;visibility:hidden"></iframe></noscript>
267<a aria-label="Skip to content" class="fl-screen-reader-text" href="#fl-main-content">Skip to content</a> 268 <div class="fl-page"> 269 <header class="fl-builder-content fl-builder-content-13428 fl-builder-global-templates-locked" data-post-id="13428" data-type="header" data-sticky="1" data-sticky-breakpoint="medium" data-shrink="1" data-overlay="1" data-overlay-bg="transparent" itemscope="itemscope" itemtype="http://schema.org/WPHeader"><div class="fl-row fl-row-full-width fl-row-bg-color fl-node-5ee10b07b5126" data-node="5ee10b07b5126"> 270 <div class="fl-row-content-wrap"> 271 <div class="fl-row-content fl-row-fixed-width fl-node-content"> 272 273<div class="fl-col-group fl-node-5ee10b07bb431 fl-col-group-custom-width" data-node="5ee10b07bb431"> 274 <div class="fl-col fl-node-5ee93991602fc" data-node="5ee93991602fc"> 275 <div class="fl-col-content fl-node-content"> 276 </div> 277</div> 278 <div class="fl-col fl-node-5ee10b07bb560 fl-col-small top-secondary-nav-bar" data-node="5ee10b07bb560"> 279 <div class="fl-col-content fl-node-content"> 280 <div class="fl-module fl-module-html fl-node-5ee939916017d secondary_nav" data-node="5ee939916017d"> 281 <div class="fl-module-content fl-node-content"> 282 <div class="fl-html"> 283 <a class="secondary_nav" style="margin-right: 20px; color: #066e00;" href="https://www.excel-university.com/buy-now">Buy Now</a><a class="secondary_nav" href="/" title="Store"> <i class="fas fa-store fa-lg"></i></a><a class="secondary_nav" href="https://campus.excel-university.com/login/" title="Login"> <i class="fas fa-user fa-lg"></i></a><a class="secondary_nav" href="/cart?action=viewcart" title="Cart"> <i class="fas fa-shopping-cart fa-lg"></i></a></div> 284 </div> 285</div> 286 </div> 287</div> 288 </div> 289 </div> 290 </div> 291</div> 292<div class="fl-row fl-row-full-width fl-row-bg-color fl-node-5eb1a130a47b3" data-node="5eb1a130a47b3"> 293 <div class="fl-row-content-wrap"> 294 <div class="fl-row-content fl-row-fixed-width fl-node-content"> 295 296<div class="fl-col-group fl-node-5ed6917dac4d6" data-node="5ed6917dac4d6"> 297 <div class="fl-col fl-node-5ed6917dac67b fl-col-has-cols" data-node="5ed6917dac67b"> 298 <div class="fl-col-content fl-node-content"> 299 300<div class="fl-col-group fl-node-5ed691b76be37 fl-col-group-nested fl-col-group-equal-height fl-col-group-align-center fl-col-group-custom-width" data-node="5ed691b76be37"> 301 <div class="fl-col fl-node-5ed691b76beed fl-col-small" data-node="5ed691b76beed"> 302 <div class="fl-col-content fl-node-content"> 303 <div class="fl-module fl-module-photo fl-node-5eb1a26f1b48c" data-node="5eb1a26f1b48c"> 304 <div class="fl-module-content fl-node-content"> 305 <div class="fl-photo fl-photo-align-left" itemscope itemtype="https://schema.org/ImageObject"> 306 <div class="fl-photo-content fl-photo-img-png"> 307 <a href="https://excel-university.com/" target="_self" itemprop="url"> 308 <img class="fl-photo-img wp-image-13423 size-medium" src="//store.excel-university.com/template/excel-university/images/excel_u_logo_stacked_grey_font-300x90.png" alt="excel_u_logo_stacked_grey_font" itemprop="image" height="90" width="300" title="excel_u_logo_stacked_grey_font" data-no-lazy="1" srcset="//store.excel-university.com/template/excel-university/images/excel_u_logo_stacked_grey_font-300x90.png 300w, //store.excel-university.com/template/excel-university/images/excel_u_logo_stacked_grey_font-1024x308.png 1024w, //store.excel-university.com/template/excel-university/images/excel_u_logo_stacked_grey_font-768x231.png 768w, //store.excel-university.com/template/excel-university/images/excel_u_logo_stacked_grey_font.png 1128w" sizes="(max-width: 300px) 100vw, 300px" /> 309 </a> 310 </div> 311 </div> 312 </div> 313</div> 314 </div> 315</div> 316 <div class="fl-col fl-node-5ed691b76bef0" data-node="5ed691b76bef0"> 317 <div class="fl-col-content fl-node-content"> 318 <div class="fl-module fl-module-menu fl-node-5eb1a130a47ce" data-node="5eb1a130a47ce"> 319 <div class="fl-module-content fl-node-content"> 320 <div class="fl-menu fl-menu-responsive-toggle-medium-mobile fl-menu-responsive-flyout-overlay fl-flyout-right"> 321 <button class="fl-menu-mobile-toggle hamburger" aria-label="Menu"><span class="svg-container"><svg version="1.1" class="hamburger-menu" xmlns="http://www.w3.org/2000/svg" xmlns:xlink="http://www.w3.org/1999/xlink" viewB
321ox="0 0 512 512"> 322<rect class="fl-hamburger-menu-top" width="512" height="102"/> 323<rect class="fl-hamburger-menu-middle" y="205" width="512" height="102"/> 324<rect class="fl-hamburger-menu-bottom" y="410" width="512" height="102"/> 325</svg> 326</span></button> <div class="fl-clear"></div> 327 <nav aria-label="Menu" itemscope="itemscope" itemtype="https://schema.org/SiteNavigationElement"><ul id="menu-main-menu" class="menu fl-menu-horizontal fl-toggle-arrows"><li id="menu-item-12834" class="menu-item menu-item-type-post_type menu-item-object-page"><a href="https://excel-university.com/start-here/">Start Here</a></li><li id="menu-item-378" class="menu-item menu-item-type-post_type menu-item-object-page"><a href="https://excel-university.com/training/">Training</a></li><li id="menu-item-13689" class="menu-item menu-item-type-post_type_archive menu-item-object-articles"><a href="https://excel-university.com/articles/">Publications</a></li><li id="menu-item-362" class="menu-item menu-item-type-post_type menu-item-object-page page_item page-item-89"><a href="https://excel-university.com/blog/">Blog</a></li></ul></nav></div> 328 </div> 329</div> 330 </div> 331</div> 332 <div class="fl-col fl-node-5ed691b76bef2 fl-col-small" data-node="5ed691b76bef2"> 333 <div class="fl-col-content fl-node-content"> 334 <div class="fl-module fl-module-button fl-node-5ebd8a8bbb13c" data-node="5ebd8a8bbb13c"> 335 <div class="fl-module-content fl-node-content"> 336 <div class="fl-button-wrap fl-button-width-auto fl-button-right"> 337 <a href="https://excel-university.com/free-gift/" target="_self" class="fl-button" role="button"> 338 <span class="fl-button-text">FREE GIFT</span> 339 </a> 340</div> 341 </div> 342</div> 343 </div> 344</div> 345 </div> 346 </div> 347</div> 348 </div> 349 </div> 350 </div> 351</div> 352</header><div class="uabb-js-breakpoint" style="display: none;"></div> <div id="fl-main-content" class="fl-page-content" itemprop="mainContentOfPage" role="main"> 353 354 <div class="fl-builder-content fl-builder-content-13531 fl-builder-global-templates-locked" data-post-id="13531"><div class="fl-row fl-row-full-width fl-row-bg-photo fl-node-5eecff12e1c2a" data-node="5eecff12e1c2a"> 355 <div class="fl-row-content-wrap"> 356 <div class="fl-row-content fl-row-fixed-width fl-node-content"> 357 358<div class="fl-col-group fl-node-5eecff12e1c21" data-node="5eecff12e1c21"> 359 <div class="fl-col fl-node-5eecff12e1c26" data-node="5eecff12e1c26"> 360 <div class="fl-col-content fl-node-content"> 361 <div class="fl-module fl-module-heading fl-node-5eecff12e1c27" data-node="5eecff12e1c27"> 362 <div class="fl-module-content fl-node-content"> 363 <h1 class="fl-heading"> 364 <span class="fl-heading-text">Study Pass (Monthly)</span> 365 </h1> 366 </div> 367</div> 368 </div> 369</div> 370 </div> 371 </div> 372 </div> 373</div> 374<div class="fl-row fl-row-fixed-width fl-row-bg-none fl-node-5ee928872699f" data-node="5ee928872699f"> 375 <div class="fl-row-content-wrap"> 376 <div class="fl-row-content fl-row-fixed-width fl-node-content"> 377 378<div class="fl-col-group fl-node-5ee92887269a9" data-node="5ee92887269a9"> 379 <div class="fl-col fl-node-5ee92887269ac" data-node="5ee92887269ac"> 380 <div class="fl-col-content fl-node-content"> 381 382<div class="fl-module fl-module-post-grid fl-node-5ee92887269ad"> 383 <div class="fl-module-content fl-node-content"> 384 <div class="fl-post-feed" itemscope="itemscope"> 385 <div class="fl-post-feed-post fl-post-feed-image-beside fl-post-align-left post-12020 post type-post status-publish format-standard has-post-thumbnail hentry category-excel category-techniques category-training tag-speed-loop" itemscope itemtype="https://schema.org/BlogPosting"> 386 387 <div class="cm_content"> 388 <!-- 389store managed product: yes 390parent product: no 391selectable bundle product: no 392--> 393 <div class="page-product-details"> 394 <form class='cm-product-form cm-product-details-form' method='post' action='https://store.excel-university.com/cart?action=add' > <div class="product-container"> 395 <div class="product-description"> 396 <a name="148"></a> 397 <h4><span style="font-weight: bold;">Study Pass</span></h4> 398<p>Our Study Pass is a self-service product that includes the training videos and files from our Undergraduate (1-4) and Masters (1-2) lessons. Stop the monthly subscription anytime with the Manage Subscription link.</p> 399<p><em>Note: a Study Pass is a self-service program which does not include support, forums, live Office Hours, quizzes, certificatates of completion, and more. If you'd like these elements, please consider a Campus Pass or CPE Pass instead. Compare our <a href="https://www.excel-university.com/training-passes">Training Passes</a>
399 to see which items are included in each pass.</em></p> 400<p><strong>Have a team?</strong> Check out our <a href="product?catalog=SPGroup">Group Study Pass</a>.</p> 401<p> </p> 402<ul> 403</ul> 404<hr /> 405<ul id="countrytabs" class="shadetabs"> 406<li><a class="selected" rel="country2" href="#">Undergrad Lessons</a></li> 407<li><a rel="country3" href="#">Masters Lessons</a></li> 408</ul> 409<div style="border: 1px solid #ccc; width: 100%; margin-bottom: 1em; padding: 10px;"> 410<div id="country2" class="tabcontent"> 411<h2>Undergrad Lessons</h2> 412<img style="float: right;" src="/images/undergrad.png" alt="" width="200" height="68" /> 413<p>The Graduate Program teaches classic Excel topics and includes the lessons from the four undergraduate courses (v1-4).</p> 414<ul> 415<li><strong>Freshman</strong> covers the foundations needed to build reliable workbooks.</li> 416<li><strong>Sophomore</strong> demonstrates how to build efficient formula-based reports.</li> 417<li><strong>Junior</strong> digs into the details of PivotTables.</li> 418<li><strong>Senior</strong> illustrates how to create efficient internal-use workpapers.</li> 419</ul> 420<p> </p> 421<table style="width: 100%;"> 422<tbody> 423<tr> 424<td valign="top"> 425<h4>Freshman (Undergrad 1)</h4> 426<strong>Foundations</strong> 427<ul> 428<li>Selected Shortcuts</li> 429<li>Named References</li> 430<li>Tables</li> 431<li>Data Validation</li> 432<li>Conditional Formatting Basics</li> 433<li>Better Summing</li> 434<li>Skinny Row</li> 435<li>Hide Worksheets</li> 436<li>Highlight Input Cells</li> 437</ul> 438<strong>Workbook Organization</strong> 439<ul> 440<li>Workbook Organization</li> 441<li>Worksheet Organization</li> 442<li>Nested Functions</li> 443<li>Selection Groups</li> 444<li>Workbook Design Principles</li> 445</ul> 446</td> 447<td valign="top"> 448<h4>Sophomore (Undergrad 2)</h4> 449<strong>Conditional Summing</strong> 450<ul> 451<li>SUMIFS</li> 452<li>Remove Duplicates</li> 453</ul> 454<strong>Lookups</strong> 455<ul> 456<li>VLOOKUP, INDEX, MATCH</li> 457<li>IFERROR, IF, ISERROR</li> 458<li>List Comparisons COUNTIFS</li> 459<li>Indentation</li> 460<li>Lookup with SUMIFS</li> 461</ul> 462<strong>Date and Text Functions</strong> 463<ul> 464<li>EOMONTH, MONTH, YEAR, DATE</li> 465<li>Concatenation Basics</li> 466</ul> 467<strong>Formula-based Reports</strong> 468<ul> 469<li>SUMIFS with CONCATENATE</li> 470<li>Dynamic Headers</li> 471<li>Mapping Tables</li> 472</ul> 473</td> 474</tr> 475<tr> 476<td valign="top"> 477<h4>Junior (Undergrad 3)</h4> 478<strong>PivotTable Fundamentals</strong> 479<ul> 480<li>PivotTable Basics</li> 481<li>Row Fields</li> 482<li>Value Fields</li> 483<li>Column Fields</li> 484<li>Filter Fields</li> 485<li>Updating Data</li> 486<li>
486Groups and Subtotals</li> 487<li>Value Formats</li> 488<li>Report Type Comparison</li> 489</ul> 490<strong>Working with PivotTables</strong> 491<ul> 492<li>Options</li> 493<li>Report Layout and Design</li> 494<li>Multiple Value Fields</li> 495<li>Calculated Fields</li> 496<li>Calculated Items</li> 497<li>Show Values As</li> 498<li>Sorting</li> 499<li>Filtering</li> 500<li>GETPIVOTDATA</li> 501</ul> 502<strong>Obtain > Prepare > Summarize</strong> 503<ul> 504<li>Text Files</li> 505<li>Data Preparation</li> 506<li>Accounting System Data</li> 507<li>Getting Graphic</li> 508</ul> 509</td> 510<td valign="top"> 511<h4>Senior (Undergrad 4)</h4> 512<strong>Features</strong> 513<ul> 514<li>Sorting & Filtering</li> 515<li>Outlining</li> 516<li>Formatting</li> 517<li>Printing</li> 518<li>Protecting</li> 519<li>Hyperlink Feature</li> 520</ul> 521<strong>Functions</strong> 522<ul> 523<li>HYPERLINK Function</li> 524<li>IF Alternatives</li> 525<li>Lookup Functions</li> 526<li>Text Functions</li> 527<li>Date Functions</li> 528<li>Insert Function</li> 529</ul> 530<strong>Techniques</strong> 531<ul> 532<li>Ribbon Review</li> 533<li>Conditional Formatting Revisited</li> 534<li>Names Revisited</li> 535<li>Wildcards Revisited</li> 536<li>Tables Revisited</li> 537<li>Start Here Revisited</li> 538</ul> 539<strong>Applications</strong> 540<ul> 541<li>JE Preparation</li> 542<li>Budget Ideas</li> 543<li>Common Workpapers</li> 544</ul> 545</td> 546</tr> 547</tbody> 548</table> 549<p> </p> 550</div> 551<div id="country3" class="tabcontent"> 552<h2>Masters Lessons</h2> 553<img style="float: right;" src="/images/masters.png" alt="" width="200" height="68" /> 554<p>The Masters Program teaches more technical Excel topics and includes the lessons from the two masters courses (m1-2).</p> 555<ul> 556<li><strong>Masters 1</strong> covers Power Query, Power Pivot, Power BI, and table/graph design.</li> 557<li><strong>Masters 2</strong> demonstrates how to automate repetitive tasks with VBA and Macros.</li> 558</ul> 559<p> </p> 560<table style="width: 100%;"> 561<tbody> 562<tr> 563<td valign="top"> 564<h4>Masters 1</h4> 565<strong>Power Query Topics</strong> 566<ul> 567<li>Get external data</li> 568<li>Various transformations, including unpivot</li> 569<li>Load to data model and table</li> 570<li>Chain queries</li> 571<li>Merge queries</li> 572</ul> 573<strong>Power Pivot Topics</strong> 574<ul> 575<li>Relationships</li> 576<li>Measures (implicit / explicit)</li> 577<li>Various DAX functions</li> 578<li>Date (calendar) table</li> 579<li>Data vs lookup tables</li> 580</ul> 581<strong>Power BI Topics</strong> 582<ul> 583<li>Getting external data</li> 584<li>Modeling data and writing measures</li> 585<li>Various visuals (matrix, table, column, bar, line, scatter, map)</li> 586<li>Power BI desktop, service, mobile app</li> 587<li>Slicers (and sync slicers)</li> 588</ul> 589<strong>Table and Graph Design Topics</strong> 590<ul> 591<li>Display type: selecting a table or graph</li> 592<li>Chart design practices</li> 593<li>How to feature key relationships graphically</li> 594<li>Nominal (order not important)</li> 595<li>Ranking (order matters)</li> 596<li>Part-to-whole (contributions to whole)</li> 597<li>Time series (over time)</li> 598<li>Distribution (distributed over range)</li> 599<li>Deviation (difference)</li> 600<li>Correlation (vary in relation to each other)</li> 601<li>Geospatial (locations)</li> 602</ul> 603<strong>Case Studies (practical applications of skills)</strong> 604<ul> 605<li>Sales manager (nominal)</li> 606<li>Travel expenses for CFO (ranking, part-to-whole)</li> 607<li>Year-end sales promotion (time series)</li> 608<li>Budget vs actual (deviation)</li> 609<li>We Collect 4U (distribution)</li> 610<li>Employee surveys (correlation)</li> 611<li>Donor state map (geospatial)</li> 612<li>Dashboard</li> 613</ul> 614</td> 615<td valign="top"> 616<h4>Masters 2</h4> 617<strong>VBA Topics</strong> 618<ul> 619<li>Visual Basic Editor</li> 620<li>Macro Recorder</li> 621<li>Excel object model</li> 622<li>Properties</li> 623<li>Methods</li> 624<li>Procedures, subs, functions</li> 625<li>Collections</li> 626<li>Variables, scope, type</li> 627<li>Constants</li> 628<li>Object variables</li> 629<li>VBA functions</li> 630<li>MsgBox</li> 631<li>InputBox</li> 632<li>Program flow</li> 633<li>If Then</li> 634<li>Exit, Labels</li> 635<li>Select Case</li> 636<li>Program flow, If Then, Exit, Labels, Select Case</li> 637<li>Loops, For Next, Do While</li> 638<li>Collection loops</li> 639<li>Error handling, On Error, Debugging, Watch/Locals</li> 640<li>Copy cell, range, table</li> 641<li>Charts, chart sheets</li> 642<li>Performance</li> 643<li>ScreenUpdating</li> 644<li>Module variables</li> 645<li>Workbook, worksheet loops</li> 646<li>Names, Variant/Array, Split</li> 647<li>StatusBar</li> 648</ul> 649<strong>Case Studies (practical application of skills)</strong> 650<ul> 651<li>Insert standard worksheet title</li> 652<li>Insert the SUBTOTAL function</li> 653<li>Prepare a workbook to deliver to staff</li> 654<li>Apply standard PivotTable design</li> 655<li>Apply standard chart design</li> 656<li>Export journal entries</li> 657<li>Update a set of budget workbooks</li> 658<li>Create PDF reports from workbooks</li> 659</ul> 660</td> 661</tr> 662</tbody> 663</table> 664<p> </p> 665</div>
666<script type="text/javascript">// <![CDATA[ 667var countries=new ddtabcontent("countrytabs") 668countries.setpersist(true) 669countries.setselectedClassTarget("link") //"link" or "linkparent" 670countries.init() 671// ]]></script>
671 672</div> </div> 673 <table class="product-store-managed"> <tr> 674 <td colspan="4" valign="top" align="right" class="product-details-recurring-price-display-cell"> 675 <b>Subscription:</b> $29.00 per period<br> 676 </td> 677 </tr> 678 <tr> 679 <td colspan="4" valign="top" align="right" class="product-details-recurring-order-button-cell"> 680 <div class="product-details-recurring-order-button-container"> 681 <input type="submit" value="Buy subscription" name="buy_with_installments" class="orderbutton_installments formbutton" /> 682<input type="hidden" name="product_code" value="StudyPass" /> </div> 683 </td> 684 </tr> 685 </table> </div> 686 </form> </div> 687 <p class="product-return-link"><a href="https://store.excel-university.com/catalog?pagename=Subscriptions">Return to Catalog</a></p> 688 </div> 689 690 691 692 <div class="fl-clear"></div> 693</div> 694 695 696 </div> 697 </div> 698</div> 699 </div> 700</div> 701 <div class="fl-col fl-node-5ee9292523bbb fl-col-small" data-node="5ee9292523bbb"> 702 <div class="fl-col-content fl-node-content"> 703 <div class="fl-module fl-module-widget fl-node-5ee9243d48ab5" data-node="5ee9243d48ab5"> 704 <div class="fl-module-content fl-node-content"> 705 <div class="fl-widget"> 706<div class="widget widget_search"> <form id="search_box" action="https://store.excel-university.com/" method="get"> 707 <input type="hidden" value="46" name="category" id="catgegory"> 708 <input type="text" aria-label="Search" placeholder="Search" name="keyword" id="search_box_keyword"> 709 <input type="hidden" name="cond" value="advand"> 710 <input id="search_box_submit" type="submit" name="search" value="" aria-label="Search" > 711 </form> 712 </div></div> 713 </div> 714</div> 715<div class="fl-module fl-module-widget fl-node-5ee9243d48ace" data-node="5ee9243d48ace"> 716 <div class="fl-module-content fl-node-content"> 717 <div class="fl-widget"> 718<div class="widget widget_categories"><h2 class="widgettitle">Categories</h2> 719<ul> 720<li><a href="https://store.excel-university.com/catalog?pagename=featured" class="sidebar_category_link">Featured Training</a> 721</li> 722<li><a href="https://store.excel-university.com/catalog?pagename=Subscriptions" class="sidebar_category_link selected_sidebar_category_link">Training Passes</a> 723</li> 724<li><a href="https://store.excel-university.com/catalog?pagename=Undergraduate" class="sidebar_category_link">Undergraduate</a> 725</li> 726<li><a href="https://store.excel-university.com/catalog?pagename=Graduate" class="sidebar_category_link">Masters</a> 727</li> 728<li><a href="https://store.excel-university.com/catalog?pagename=guest" class="sidebar_category_link">Guest Lectures</a> 729</li> 730<li><a href="https://store.excel-university.com/catalog?pagename=AlumniAssoc" class="sidebar_category_link">Alumni Association</a> 731</li> 732<li><a href="https://store.excel-university.com/catalog?pagename=allcourses" class="sidebar_category_link">All Courses</a> 733</li> 734<li><a href="https://store.excel-university.com/catalog?pagename=gift" class="sidebar_category_link">Gift Cards</a> 735</li> 736<li><a href="https://store.excel-university.com/catalog?pagename=faq" class="sidebar_category_link">Frequently Asked Questions</a> 737</li> 738</ul> 739 740 </div></div> 741 </div> 742</div> 743 </div> 744</div> 745 </div> 746 </div> 747 </div> 748</div> 749</div><div class="uabb-js-breakpoint" style="display: none;"></div> 750 </div><!-- .fl-page-content --> 751 <footer class="fl-builder-content fl-builder-content-12452 fl-builder-global-templates-locked" data-post-id="12452" data-type="footer" itemscope="itemscope" itemtype="http://schema.org/WPFooter"><div class="fl-row fl-row-full-width fl-row-bg-color fl-node-5eb18b4f2a5c6" data-node="5eb18b4f2a5c6"> 752 <div class="fl-row-content-wrap"> 753 <div class="fl-row-content fl-row-fixed-width fl-node-content"> 754 755<div class="fl-col-group fl-node-5eb18b4f2a5c7 fl-col-group-equal-height fl-col-group-align-top fl-col-group-custom-width" data-node="5eb18b4f2a5c7"> 756 <div class="fl-col fl-node-5eb18b4f2a5cc fl-col-small" data-node="5eb18b4f2a5cc"> 757 <div class="fl-col-content fl-node-content"> 758 <div class="fl-module fl-module-heading fl-node-5eb18b4f2a5de" data-node="5eb18b4f2a5de"> 759 <div class="fl-module-content fl-node-content"> 760 <h3 class="fl-heading"> 761 <span class="fl-heading-text cm-footer-heading">About</span> 762 </h3> 763 </div> 764</div> 765<div class="fl-module fl-module-rich-text fl-node-5eb18c70d30ec" data-node="5eb18c70d30ec"> 766 <div class="fl-module-content fl-node-content"> 767 <div class="fl-rich-text"> 768 <p><a href="https://excel-university.com/university-preview/">About Us</a></p> 769 <p><a href="https://help.excel-university.com/">Docs</a></p> 770 <p><a href="https://excel-university.com/instructors/">Instructor List</a></p> 771 <p><a href="https://excel-university.com/testimonials/">Testimonials</a></p> 772 <p><a href="https://excel-university.com/scholarship">Scholarship</a></p> 773 <p><a href="https://excel-university.com/sponsor">Advertising</a></p> 774 <p><a href="https://excel-university.com/affiliate">Affiliate Program</a></p> 775 <p><a href="https://excel-university.com/privacy-policy/">Privacy Policy</a></p> 776 </div> 777 </div> 778</div> 779 </div> 780</div> 781 <div class="fl-col fl-node-5eb18bb343671 fl-col-small" data-node="5eb18bb343671"> 782 <div class="fl-col-content fl-node-content"> 783 <div class="fl-module fl-module-heading fl-node-5eb18b4f2a5d8" data-node="5eb18b4f2a5d8"> 784 <div class="fl-module-content fl-node-content"> 785 <h3 class="fl-heading"> 786 <span class="fl-heading-text cm-footer-heading">Connect</span> 787 </h3> 788 </div> 789</div> 790<div class="fl-module fl-module-rich-text fl-node-5eb18b4f2a5d9" data-node="5eb18b4f2a5d9"> 791 <div class="fl-module-content fl-node-content"> 792 <div class="fl-rich-text"> 793 <p><a href="https://www.linkedin.com/in/jefflenning/">LinkedIn</a></p> 794<p><a href="https://www.youtube.com/c/exceluniversityinc">YouTube</a></p> 795<p><a href="https://www.facebook.com/exceluniversityinc">Facebook</a></p> 796<p><a href="https://www.instagram.com/exceluniversity/">Instagram</a></p> 797<!--<p><a href="https://twitter.com/exceluniversity">Twitter</a></p>--> 798<p><a href="https://excel-university.com/blog/">Blog</a></p> 799</div> 800 </div> 801</div> 802 </div> 803</div> 804 <div class="fl-col fl-node-5eb18b4f2a5ca fl-col-small" data-node="5eb18b4f2a5ca"> 805 <div class="fl-col-content fl-node-content"> 806 <div class="fl-module fl-module-heading fl-node-5eb18bafeebf7" data-node="5eb18bafeebf7"> 807 <div class="fl-module-content fl-node-content"> 808 <h3 class="fl-heading"> 809 <span class="fl-heading-text cm-footer-heading">Training</span> 810 </h3> 811 </div> 812</div> 813<div class="fl-module fl-module-rich-text fl-node-5eb18bbb4995e" data-node="5eb18bbb4995e"> 814 <div class="fl-module-content fl-node-content"> 815 <div class="fl-rich-text"> 816<p><a href="https://store.excel-university.com">Course List</a></p> 817<p><a href="https://campus.excel-university.com/my/">Student Login</a></p> 818<p><a href="https://excel-university.com/courses/">For CPAs</a></p> 819<p><a href="https://excel-university.com/campus-pass/">For Individuals</a></p> 820<p><a href="https://excel-university.com/online-group-excel-training/">For Groups</a></p> 821<p><a href="https://excel-university.com/excel-content-for-accounting-information-systems-or-other-college-course/">For College Professors</a></p> 822<p><a href="https://excel-university.com/speedloop">Speed Loop</a></p> 823<p> </p> 824</div> 825 </div> 826</div> 827 </div> 828</div> 829 <div class="fl-col fl-node-5ed0516765c06 fl-col-small" data-node="5ed0516765c06"> 830 <div class="fl-col-content fl-node-content"> 831 <div class="fl-module fl-module-heading fl-node-5ed0515f31364" data-node="5ed0515f31364"> 832 <div class="fl-module-content fl-node-content"> 833 <h3 class="fl-heading"> 834 <span class="fl-heading-text cm-footer-heading">Related</span> 835 </h3> 836 </div> 837</div> 838<div class="fl-module fl-module-rich-text fl-node-5ed05169e324e" data-node="5ed05169e324e"> 839 <div class="fl-module-content fl-node-content"> 840 <div class="fl-rich-text"> 841 <p><a href="https://www.mrexcel.com/board/">Excel Forum</a></p> 842<p><a href="https://jooble.org/jobs-microsoft-excel">Excel Jobs</a></p> 843<p><a href="https://www.excel-university.com/consultants/">Excel Consulting</a></p> 844<p><a href="https://excel-university.com/book">Excel Books</a></p> 845<p><a href="https://excel-university.com/coaching">Excel Coaching</a></p> 846<p><a href="https://excel-university.com/learn-excel-free">Free Resources</a></p> 847</div> 848 </div> 849</div> 850 </div> 851</div> 852 <div class="fl-col fl-node-5eb18b4f2a5c9 fl-col-small" data-node="5eb18b4f2a5c9"> 853 <div class="fl-col-content fl-node-content"> 854 <div class="fl-module fl-module-heading fl-node-5eb18b4f2a5dd" data-node="5eb18b4f2a5dd"> 855 <div class="fl-module-content fl-node-content"> 856 <h3 class="fl-heading"> 857 <span class="fl-heading-text cm-footer-heading">Contact</span> 858 </h3> 859 </div> 860</div> 861<div class="fl-module fl-module-rich-text fl-node-5eb18b4f2a5ce" data-node="5eb18b4f2a5ce"> 862 <div class="fl-module-content fl-node-content"> 863 <div class="fl-rich-text"> 864 <p><a href="https://excel-university.com/contact">Contact Form</a></p> 865<p><a href="tel:+19492007688">+1 (949) 200-7688</a></p> 866<p>[email protected]</p> 867<p>Excel University<br /> 868518 N Cliff Ave #3, Harrisburg, SD 57032</p> 869</div> 870 </div> 871</div> 872 </div> 873</div> 874 </div> 875 </div> 876 </div> 877</div> 878<div class="fl-row fl-row-full-width fl-row-bg-color fl-node-5eb18b4f2a5b4" data-node="5eb18b4f2a5b4"> 879 <div class="fl-row-content-wrap"> 880 <div class="fl-row-content fl-row-fixed-width fl-node-content"> 881 882<div class="fl-col-group fl-node-5eb18b4f2a5c0" data-node="5eb18b4f2a5c0"> 883 <div class="fl-col fl-node-5eb18b4f2a5c3 fl-col-has-cols" data-node="5eb18b4f2a5c3"> 884 <div class="fl-col-content fl-node-content"> 885 886<div class="fl-col-group fl-node-5ed53fbc68fe2 fl-col-group-nested fl-col-group-equal-height fl-col-group-align-center fl-col-group-custom-width" data-node="5ed53fbc68fe2"> 887 <div class="fl-col fl-node-5ed5401a77525 fl-col-small" data-node="5ed5401a77525"> 888 <div class="fl-col-content fl-node-content"> 889 <div class="fl-module fl-module-photo fl-node-5ed5401a7735e" data-node="5ed5401a7735e"> 890 <div class="fl-module-content fl-node-content"> 891 <div class="fl-photo fl-photo-align-center" itemscope itemtype="https://schema.org/ImageObject"> 892 <div class="fl-photo-content fl-photo-img-png"> 893 <img class="fl-photo-img wp-image-13440 size-full" src="//store.excel-university.com/template/excel-university/images/MVP_Logo_Secondary_dark_grey_RGB_300ppi_footer.png" alt="MVP_Logo_Secondary_dark_grey_RGB_300ppi_footer" itemprop="image" height="475" width="300" title="MVP_Logo_Secondary_dark_grey_RGB_300ppi_footer" srcset="//store.excel-university.com/template/excel-university/images/MVP_Logo_Secondary_dark_grey_RGB_300ppi_footer.png 300w, //store.excel-university.com/template/excel-university/images/MVP_Logo_Secondary_dark_grey_RGB_300ppi_footer-189x300.png 189w" sizes="(max-width: 300px) 100v
893w, 300px" /> 894 </div> 895 </div> 896 </div> 897</div> 898 </div> 899</div> 900 <div class="fl-col fl-node-5ed53fec999a7 fl-col-small" data-node="5ed53fec999a7"> 901 <div class="fl-col-content fl-node-content"> 902 <div class="fl-module fl-module-photo fl-node-5ed53fec997d2" data-node="5ed53fec997d2"> 903 <div class="fl-module-content fl-node-content"> 904 <div class="fl-photo fl-photo-align-center" itemscope itemtype="https://schema.org/ImageObject"> 905 <div class="fl-photo-content fl-photo-img-png"> 906 <img class="fl-photo-img wp-image-13439 size-full" src="//store.excel-university.com/template/excel-university/images/MS_Cert_Trainer_logo_dark_grey_footer.png" alt="MS_Cert_Trainer_logo_dark_grey_footer" itemprop="image" height="733" width="1117" title="MS_Cert_Trainer_logo_dark_grey_footer" srcset="//store.excel-university.com/template/excel-university/images/MS_Cert_Trainer_logo_dark_grey_footer.png 1117w, //store.excel-university.com/template/excel-university/images/MS_Cert_Trainer_logo_dark_grey_footer-300x197.png 300w, //store.excel-university.com/template/excel-university/images/MS_Cert_Trainer_logo_dark_grey_footer-1024x672.png 1024w, //store.excel-university.com/template/excel-university/images/MS_Cert_Trainer_logo_dark_grey_footer-768x504.png 768w" sizes="(max-width: 1117px) 100vw, 1117px" /> 907 </div> 908 </div> 909 </div> 910</div> 911 </div> 912</div> 913 <div class="fl-col fl-node-5ed53fbc690f1 fl-col-small" data-node="5ed53fbc690f1"> 914 <div class="fl-col-content fl-node-content"> 915 <div class="fl-module fl-module-photo fl-node-5ed53fc3161ec" data-node="5ed53fc3161ec"> 916 <div class="fl-module-content fl-node-content"> 917 <div class="fl-photo fl-photo-align-center" itemscope itemtype="https://schema.org/ImageObject"> 918 <div class="fl-photo-content fl-photo-img-png"> 919 <img class="fl-photo-img wp-image-13441 size-full" src="//store.excel-university.com/template/excel-university/images/NASBA_CPERegistry_logo_dark_grey_transparent_footer.png" alt="NASBA_CPERegistry_logo_dark_grey_transparent_footer" itemprop="image" height="465" width="524" title="NASBA_CPERegistry_logo_dark_grey_transparent_footer" srcset="//store.excel-university.com/template/excel-university/images/NASBA_CPERegistry_logo_dark_grey_transparent_footer.png 524w, //store.excel-university.com/template/excel-university/images/NASBA_CPERegistry_logo_dark_grey_transparent_footer-300x266.png 300w" sizes="(max-width: 524px) 100vw, 524px" /> 920 </div> 921 </div> 922 </div> 923</div> 924 </div> 925</div> 926 <div class="fl-col fl-node-5ed542a3c043e" data-node="5ed542a3c043e"> 927 <div class="fl-col-content fl-node-content"> 928 <div class="fl-module fl-module-rich-text fl-node-5eb18b4f2a5c4" data-node="5eb18b4f2a5c4"> 929 <div class="fl-module-content fl-node-content"> 930 <div class="fl-rich-text"> 931 <p><a href="https://excel-university.com/">Excel University</a> | Copyright © 2012-2020 | All rights reserved</p> 932</div> 933 </div> 934</div> 935 </div> 936</div> 937 </div> 938 </div> 939</div> 940 </div> 941 </div> 942 </div> 943</div> 944</footer><div class="uabb-js-breakpoint" style="display: none;"></div> </div><!-- .fl-page -->
945<script src='//store.excel-university.com/template/excel-university/js/jquery.ba-throttle-debounce.min.js'></script>
vendor: 1 bytes, line 945
945
946<script src='//store.excel-university.com/template/excel-university/js/6e10e558fdca07e41191e0849f4c0709-layout-bundle.js'></script>
vendor: 1 bytes, line 946
946
947<script src='//store.excel-university.com/template/excel-university/js/jquery.magnificpopup.min.js'></script>
vendor: 1 bytes, line 947
947
948<script src='//store.excel-university.com/template/excel-university/js/bootstrap.min.js'></script>
vendor: 1 bytes, line 948
948
949<script> 950var themeopts = {"medium_breakpoint":"992","mobile_breakpoint":"768"}; 951</script>
vendor: 1 bytes, line 951
951
952<script src='//store.excel-university.com/template/excel-university/js/theme.min.js'></script>
vendor: 1 bytes, line 952
952
953<script src='//store.excel-university.com/template/excel-university/js/wp-embed.min.js'></script>
953 954</body> 955</html> 956
Line numbers count LF bytes from the start of the resource, as the search results do. Vendor segments are library code the classifier recognised; they are stored but not indexed. Bytes are shown as Latin1 characters, one per byte.