Excel Functions | officenewb

LIVE QA ON MICROSOFT EXCEL - June 24th, 2020

LIVE QA ON MICROSOFT EXCEL

Microsoft Excel Power Query Cleaning Nested Data with Conditional Columns and Fill Command

{"items":["6023c0d7ee3d2e0017a440bb","6023c0d7ee3d2e0017a440ba","6023c0d7ee3d2e0017a440b7"],"styles":{"galleryType":"Columns","groupSize":1,"showArrows":true,"cubeImages":true,"cubeType":"max","cubeRatio":1.7777777777777777,"isVertical":true,"gallerySize":30,"collageAmount":0,"collageDensity":0,"groupTypes":"1","oneRow":false,"imageMargin":5,"galleryMargin":0,"scatter":0,"rotatingScatter":"","chooseBestGroup":true,"smartCrop":false,"hasThumbnails":false,"enableScroll":true,"isGrid":true,"isSlider":false,"isColumns":false,"isSlideshow":false,"cropOnlyFill":false,"fixedColumns":0,"enableInfiniteScroll":true,"isRTL":false,"minItemSize":50,"rotatingGroupTypes":"","rotatingCropRatios":"","columnWidths":"","gallerySliderImageRatio":1.7777777777777777,"numberOfImagesPerRow":3,"numberOfImagesPerCol":1,"groupsPerStrip":0,"borderRadius":0,"boxShadow":0,"gridStyle":0,"mobilePanorama":false,"placeGroupsLtr":true,"viewMode":"preview","thumbnailSpacings":4,"galleryThumbnailsAlignment":"bottom","isMasonry":false,"isAutoSlideshow":false,"slideshowLoop":false,"autoSlideshowInterval":4,"bottomInfoHeight":0,"titlePlacement":["SHOW_ON_THE_RIGHT","SHOW_BELOW"],"galleryTextAlign":"center","scrollSnap":false,"itemClick":"nothing","fullscreen":true,"videoPlay":"hover","scrollAnimation":"NO_EFFECT","slideAnimation":"SCROLL","scrollDirection":0,"scrollDuration":400,"overlayAnimation":"FADE_IN","arrowsPosition":0,"arrowsSize":23,"watermarkOpacity":40,"watermarkSize":40,"useWatermark":true,"watermarkDock":{"top":"auto","left":"auto","right":0,"bottom":0,"transform":"translate3d(0,0,0)"},"loadMoreAmount":"all","defaultShowInfoExpand":1,"allowLinkExpand":true,"expandInfoPosition":0,"allowFullscreenExpand":true,"fullscreenLoop":false,"galleryAlignExpand":"left","addToCartBorderWidth":1,"addToCartButtonText":"","slideshowInfoSize":200,"playButtonForAutoSlideShow":false,"allowSlideshowCounter":false,"hoveringBehaviour":"NEVER_SHOW","thumbnailSize":120,"magicLayoutSeed":1,"imageHoverAnimation":"NO_EFFECT","imagePlacementAnimation":"NO_EFFECT","calculateTextBoxWidthMode":"PERCENT","textBoxHeight":26,"textBoxWidth":200,"textBoxWidthPercent":65,"textImageSpace":10,"textBoxBorderRadius":0,"textBoxBorderWidth":0,"loadMoreButtonText":"","loadMoreButtonBorderWidth":1,"loadMoreButtonBorderRadius":0,"imageInfoType":"ATTACHED_BACKGROUND","itemBorderWidth":0,"itemBorderRadius":0,"itemEnableShadow":false,"itemShadowBlur":20,"itemShadowDirection":135,"itemShadowSize":10,"imageLoadingMode":"BLUR","expandAnimation":"NO_EFFECT","imageQuality":90,"usmToggle":false,"usm_a":0,"usm_r":0,"usm_t":0,"videoSound":false,"videoSpeed":"1","videoLoop":true,"jsonStyleParams":"","gallerySizeType":"px","gallerySizePx":1000,"allowTitle":true,"allowContextMenu":true,"textsHorizontalPadding":-30,"itemBorderColor":{"themeName":"color_12","value":"rgba(200,200,200,0)"},"showVideoPlayButton":true,"galleryLayout":2,"calculateTextBoxHeightMode":"MANUAL","targetItemSize":1000,"selectedLayout":"2|bottom|1|max|true|0|true","layoutsVersion":2,"selectedLayoutV2":2,"isSlideshowFont":true,"externalInfoHeight":26,"externalInfoWidth":0.65},"container":{"width":300,"galleryWidth":305,"galleryHeight":0,"scrollBase":0,"height":null}}

Excel LOOKUP Functions

I recently recorded a handful of videos for my Complete Microsoft Excel course and thought I would share these videos. I've placed them...

Microsoft Excel TEXTJOIN() Function

TEXTJOIN() FUNCTION DEFINITION =TEXTJOIN(delimiter, ignore_empty, text_1, [text_2], [text_3], …) BENEFITS The TEXTJOIN function combines...

Microsoft Excel Running Total with SUM and SUMIF Functions

(Watch the video for a demonstration) The Microsoft Excel function, SUM, is the most used function in all of Excel. We typically use the...

Microsoft Announces a New XLOOKUP Function for Excel

Microsoft announced a brand new function for Microsoft Excel called the XLOOKUP(). This function provides an enhanced method of looking...

Microsoft Excel VLOOKUP Function with Multiple Criteria

Microsoft Excel's VLOOKUP() Function is a very popular and powerful function, but it has some limitations. In the video below, you will...

Use Microsoft Excels Camera Tool with Formulas

Everyday I take some time to get through the questions I get in my online courses. I enjoy getting questions for a couple of reasons....

WOW!!! New Microsoft Excel Functions Coming Soon

Microsoft recently announced a handful of new Microsoft Excel Functions. And, they are blowing my mind!! "Note: September 24, 2018: The...

Dynamic Charts with Excels Offset Function

Create dynamic, interactive Charts with Microsoft Excel Offset Function. What is the OFFSET() Function Description Returns a reference to...

Microsoft Excel Two Way Lookup with Index() and Match()

As many of you know, I recently released a new course on working with Advanced Excel Formulas using the built in Index() and Match()...

Look Up Data with Microsoft Excels INDEX(), MATCH() and MAX() Functions

The technique of NESTING Microsoft Excel functions within one another allows for more dynamic and powerful formulas. I got an excellent...

{"items":["6023c0d7ee3d2e0017a440b9","6023c0d7ee3d2e0017a440bc","6023c0d76336df00172a91e5","6023c0d76336df00172a91e4","6023c0d76336df00172a91e1","6023c0d76336df00172a91e7","6023c0d76336df00172a91e2","6023c0d7af39f80017eb88ab","6023c0d7af39f80017eb88a7","6023c0d7af39f80017eb88ae"],"styles":{"galleryType":"Columns","groupSize":1,"showArrows":true,"cubeImages":true,"cubeType":"max","cubeRatio":1.7777777777777777,"isVertical":true,"gallerySize":30,"collageAmount":0,"collageDensity":0,"groupTypes":"1","oneRow":false,"imageMargin":32,"galleryMargin":0,"scatter":0,"rotatingScatter":"","chooseBestGroup":true,"smartCrop":false,"hasThumbnails":false,"enableScroll":true,"isGrid":true,"isSlider":false,"isColumns":false,"isSlideshow":false,"cropOnlyFill":false,"fixedColumns":0,"enableInfiniteScroll":true,"isRTL":false,"minItemSize":50,"rotatingGroupTypes":"","rotatingCropRatios":"","columnWidths":"","gallerySliderImageRatio":1.7777777777777777,"numberOfImagesPerRow":3,"numberOfImagesPerCol":1,"groupsPerStrip":0,"borderRadius":0,"boxShadow":0,"gridStyle":0,"mobilePanorama":false,"placeGroupsLtr":true,"viewMode":"preview","thumbnailSpacings":4,"galleryThumbnailsAlignment":"bottom","isMasonry":false,"isAutoSlideshow":false,"slideshowLoop":false,"autoSlideshowInterval":4,"bottomInfoHeight":0,"titlePlacement":"SHOW_BELOW","galleryTextAlign":"center","scrollSnap":false,"itemClick":"nothing","fullscreen":true,"videoPlay":"hover","scrollAnimation":"NO_EFFECT","slideAnimation":"SCROLL","scrollDirection":0,"scrollDuration":400,"overlayAnimation":"FADE_IN","arrowsPosition":0,"arrowsSize":23,"watermarkOpacity":40,"watermarkSize":40,"useWatermark":true,"watermarkDock":{"top":"auto","left":"auto","right":0,"bottom":0,"transform":"translate3d(0,0,0)"},"loadMoreAmount":"all","defaultShowInfoExpand":1,"allowLinkExpand":true,"expandInfoPosition":0,"allowFullscreenExpand":true,"fullscreenLoop":false,"galleryAlignExpand":"left","addToCartBorderWidth":1,"addToCartButtonText":"","slideshowInfoSize":200,"playButtonForAutoSlideShow":false,"allowSlideshowCounter":false,"hoveringBehaviour":"NEVER_SHOW","thumbnailSize":120,"magicLayoutSeed":1,"imageHoverAnimation":"NO_EFFECT","imagePlacementAnimation":"NO_EFFECT","calculateTextBoxWidthMode":"PERCENT","textBoxHeight":240,"textBoxWidth":200,"textBoxWidthPercent":50,"textImageSpace":10,"textBoxBorderRadius":0,"textBoxBorderWidth":0,"loadMoreButtonText":"","loadMoreButtonBorderWidth":1,"loadMoreButtonBorderRadius":0,"imageInfoType":"ATTACHED_BACKGROUND","itemBorderWidth":0,"itemBorderRadius":0,"itemEnableShadow":false,"itemShadowBlur":20,"itemShadowDirection":135,"itemShadowSize":10,"imageLoadingMode":"BLUR","expandAnimation":"NO_EFFECT","imageQuality":90,"usmToggle":false,"usm_a":0,"usm_r":0,"usm_t":0,"videoSound":false,"videoSpeed":"1","videoLoop":true,"jsonStyleParams":"","gallerySizeType":"px","gallerySizePx":1000,"allowTitle":true,"allowContextMenu":true,"textsHorizontalPadding":-30,"itemBorderColor":{"themeName":"color_15","value":"rgba(36,36,36,1)"},"showVideoPlayButton":true,"galleryLayout":2,"calculateTextBoxHeightMode":"MANUAL","textsVerticalPadding":-15,"targetItemSize":1000,"selectedLayout":"2|bottom|1|max|true|0|true","layoutsVersion":2,"selectedLayoutV2":2,"isSlideshowFont":true,"externalInfoHeight":240,"externalInfoWidth":0},"container":{"width":560,"galleryWidth":592,"galleryHeight":0,"scrollBase":0,"height":null}}