Excel MVP Blog Roundup

By
Published 10-09-2020 10:00 AM 2,258 Views
Microsoft

This week's blog roundup brought to you by Excel MVPs Leila Gharani, Bill "Mr. Excel" Jelen, and Cristiano Galvão.

 

Leila GharaniLeila GharaniBill "Mr. Excel" JelenBill "Mr. Excel" JelenCristiano GalvãoCristiano Galvão

 

Excel Filter Trick Using Non-Adjacent Columns, Leila Gharani

Leila Gharani shows an interesting application of the FILTER function. With this method you can use the FILTER function to return match results from non-adjacent columns. The same trick can be applied to the UNIQUE function to get a unique list of values from non-adjacent columns.

 

Using SEQUENCE inside of other functions such as IPMT, Bill "Mr. Excel" Jelen

When you first see the dynamic array functions of SORT, FILTER, UNIQUE, SORTBY, RANDARRAY, the SEQUENCE function might seem like the most trivial. But the real power of SEQUENCE is when it is used as an argument in other functions to coerce them to return an array. In this article, Bill "Mr. Excel" Jelen shows how to calculate the total interest to book for an entire year.

 

Dynamic Folders in Excel Power Query (in Portuguese), by Cristiano Galvão

Excel MVP Cristiano Galvão describes step by step how to get data with Power Query from a lot of files inside folders that can be switched according to the user's needs and keeping the same query, in a dynamic way.

###

O MVP de Excel Cristiano Galvão descreve passo a passo como obter dados com o Power Query a partir de um monte de arquivos dentro de pastas que podem ser trocadas de acordo com as necessidades dos usuários e mantendo a mesma query, de um jeito dinâmico.

 

 

 

Click the Like button and/or leave a comment below

 

 

 

1 Comment

Thanks @cuong for including me on this week's blog roundup! It's a great pleasure to be featured among Leila Gharani and Bill Jelen! 

%3CLINGO-SUB%20id%3D%22lingo-sub-1759742%22%20slang%3D%22en-US%22%3EExcel%20MVP%20Blog%20Roundup%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1759742%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%3EThis%20week's%20blog%20roundup%20brought%20to%20you%20by%20Excel%20MVPs%20%3CA%20href%3D%22https%3A%2F%2Fmvp.microsoft.com%2Fen-us%2FPublicProfile%2F5002885%3FfullName%3DLeila%2520Gharani%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3ELeila%20Gharani%3C%2FA%3E%2C%20%3CA%20href%3D%22https%3A%2F%2Fmvp.microsoft.com%2Fen-us%2FPublicProfile%2F21505%3FfullName%3DBill%2520Jelen%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3EBill%20%22Mr.%20Excel%22%20Jelen%3C%2FA%3E%2C%20and%20%3CA%20href%3D%22https%3A%2F%2Fmvp.microsoft.com%2Fen-us%2FPublicProfile%2F5003914%3FfullName%3DCristiano%2520Galv%25C3%25A3o%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3ECristiano%20Galv%C3%A3o%3C%2FA%3E.%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22leila.jpg%22%20style%3D%22width%3A%20200px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F225154iC165271003B29EDE%2Fimage-size%2Fsmall%3Fv%3D1.0%26amp%3Bpx%3D200%22%20role%3D%22button%22%20title%3D%22leila.jpg%22%20alt%3D%22Leila%20Gharani%22%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-caption%22%20onclick%3D%22event.preventDefault()%3B%22%3ELeila%20Gharani%3C%2FSPAN%3E%3C%2FSPAN%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22bill.jpg%22%20style%3D%22width%3A%20200px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F225155i89C62737A316E9CF%2Fimage-size%2Fsmall%3Fv%3D1.0%26amp%3Bpx%3D200%22%20role%3D%22button%22%20title%3D%22bill.jpg%22%20alt%3D%22Bill%20%26quot%3BMr.%20Excel%26quot%3B%20Jelen%22%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-caption%22%20onclick%3D%22event.preventDefault()%3B%22%3EBill%20%22Mr.%20Excel%22%20Jelen%3C%2FSPAN%3E%3C%2FSPAN%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22cristiano.jpg%22%20style%3D%22width%3A%20200px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F225156i34023D0ED1681D2F%2Fimage-size%2Fsmall%3Fv%3D1.0%26amp%3Bpx%3D200%22%20role%3D%22button%22%20title%3D%22cristiano.jpg%22%20alt%3D%22Cristiano%20Galv%C3%A3o%22%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-caption%22%20onclick%3D%22event.preventDefault()%3B%22%3ECristiano%20Galv%C3%A3o%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%3E%3CSTRONG%3E%3CA%20href%3D%22https%3A%2F%2Fwww.xelplus.com%2Fexcel-filter-trick-non-adjacent-columns%2F%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noreferrer%22%3EExcel%20Filter%20Trick%20Using%20Non-Adjacent%20Columns%3C%2FA%3E%3C%2FSTRONG%3E%3C%2FSPAN%3E%3CSPAN%3E%2C%20Leila%20Gharani%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%3CSPAN%3ELeila%20Gharani%20shows%20an%20interesting%20application%20of%20the%20FILTER%20function.%20With%20this%20method%20you%20can%20use%20the%20FILTER%20function%20to%20return%20match%20results%20from%20non-adjacent%20columns.%20The%20same%20trick%20can%20be%20applied%20to%20the%20UNIQUE%20function%20to%20get%20a%20unique%20list%20of%20values%20from%20non-adjacent%20columns.%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%3E%3CSTRONG%3E%3CA%20href%3D%22https%3A%2F%2Fwww.mrexcel.com%2Fexcel-tips%2Fexcel-2020-sequence-inside-of-other-functions-such-as-ipmt%2F%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noreferrer%22%3EUsing%20SEQUENCE%20inside%20of%20other%20functions%20such%20as%20IPMT%3C%2FA%3E%3C%2FSTRONG%3E%2C%20Bill%20%22Mr.%20Excel%22%20Jelen%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%3CSPAN%3EWhen%20you%20first%20see%20the%20dynamic%20array%20functions%20of%20SORT%2C%20FILTER%2C%20UNIQUE%2C%20SORTBY%2C%20RANDARRAY%2C%20the%20SEQUENCE%20function%20might%20seem%20like%20the%20most%20trivial.%20But%20the%20real%20power%20of%20SEQUENCE%20is%20when%20it%20is%20used%20as%20an%20argument%20in%20other%20functions%20to%20coerce%20them%20to%20return%20an%20array.%20In%20this%20article%2C%20Bill%20%22Mr.%20Excel%22%20Jelen%20shows%20how%20to%20calculate%20the%20total%20interest%20to%20book%20for%20an%20entire%20year.%3CBR%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%3E%3CSTRONG%3E%3CA%20href%3D%22https%3A%2F%2Fexcelturbo.com.br%2Fdynamic-folders-excel-power-query%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noreferrer%22%3EDynamic%20Folders%20in%20Excel%20Power%20Query%3C%2FA%3E%3C%2FSTRONG%3E%26nbsp%3B(%3CA%20href%3D%22https%3A%2F%2Fexcelturbo.com.br%2Fpastas-dinamicas-power-query-excel%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noreferrer%22%3Ein%20Portuguese%3C%2FA%3E)%2C%20by%20Cristiano%20Galv%C3%A3o%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EExcel%20MVP%20Cristiano%20Galv%C3%A3o%20describes%20step%20by%20step%20how%20to%20get%20data%20with%20Power%20Query%20from%20a%20lot%20of%20files%20inside%20folders%20that%20can%20be%20switched%20according%20to%20the%20user's%20needs%20and%20keeping%20the%20same%20query%2C%20in%20a%20dynamic%20way.%3C%2FP%3E%0A%3CP%3E%23%23%23%3C%2FP%3E%0A%3CP%3EO%20MVP%20de%20Excel%20Cristiano%20Galv%C3%A3o%20descreve%20passo%20a%20passo%20como%20obter%20dados%20com%20o%20Power%20Query%20a%20partir%20de%20um%20monte%20de%20arquivos%20dentro%20de%20pastas%20que%20podem%20ser%20trocadas%20de%20acordo%20com%20as%20necessidades%20dos%20usu%C3%A1rios%20e%20mantendo%20a%20mesma%20query%2C%20de%20um%20jeito%20din%C3%A2mico.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CEM%3EClick%20the%20Like%20button%20and%2For%20leave%20a%20comment%20below%3C%2FEM%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-1759742%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22MSC17_collaboration_009_noBG.png%22%20style%3D%22width%3A%20821px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F215690iF4223357BE7EBB69%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22MSC17_collaboration_009_noBG.png%22%20alt%3D%22MSC17_collaboration_009_noBG.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ECheck%20out%20the%20latest%20from%20Excel%20MVPs%20Leila%20Gharani%2C%20Bill%20Jelen%2C%20and%20Cristiano%20Galv%C3%A3o%3C%2FP%3E%3C%2FLINGO-TEASER%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1759742%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%20MVP%20Blog%20Roundup%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1766497%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20MVP%20Blog%20Roundup%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1766497%22%20slang%3D%22en-US%22%3E%3CP%3EThanks%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F320924%22%20target%3D%22_blank%22%3E%40cuong%3C%2FA%3E%26nbsp%3Bfor%20including%20me%20on%20this%20week's%20blog%20roundup!%20It's%20a%20great%20pleasure%20to%20be%20featured%20among%20Leila%20Gharani%20and%20Bill%20Jelen!%26nbsp%3B%3CIMG%20class%3D%22lia-deferred-image%20lia-image-emoji%22%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Fhtml%2Femoticons%2F1f64f_1f3fd.png%22%20alt%3D%22%3Afolded_hands%3A%22%20title%3D%22%3Afolded_hands%3A%22%20%2F%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Version history
Last update:
‎Oct 08 2020 02:04 PM
Updated by: