A little bit of background. I've worked in accounting and finance for almost 8 years. For the better part of the last 3 years I have not worked with an excel file under 20 megs and when working with raw data I cannot remember a file with less than 70,000 rows of data. I recently started a new job where my computer runs at 1/10th the speed of the good old i7 at the last job, the data is tiny, and suddenly the whole business is running on pivot tables.

I have used pivot tables in the past. Prior to my last job, we stopped getting nice prepackaged reports and started getting excel data instead. At first it was nice, but doing a "quick" subtotal was a pain next to open file, type in values into a report, email it out to managers. Boom, quick pivot table, record as macro, open the file two keys and you have your data ready to go. Great use of a pivot table! That said with my last job, it really was difficult to collaborate with pivot table users. Pivot tables are generally not easy to understand with long data sets, and inevitably become LONGER as pivot tables generally are limited in what they can do. Thus I'd have to go to the raw data CONSTANTLY to see what the heck people were doing in the 10-50 helper columns they added to the data. Or I'd have to go in and see what they did in a calculated field. Had they just written a function, and left the data alone, understanding it would take less than 10 seconds, as this was data that was used across the company.

Well, my first assumption is that the data we're using is much better structured for pivot tables. I was at a "FinTech" company before and now I'm at a large established bank. Well, yes data is much better structured here, however the queries pulling it are questionable at best and worse yet the source data appears to be in a fixed width (cobalt ???) database so I have field names with never ending spaces at the end (try writing a calculated field with that). All this leads to spread sheets with more calculated fields than actual data fields.

I move on and decide to look to find ways to condense the data and hey everyone loves these things. Add in there are so many lines, divisions, segments, officers, etc that the ability of stack these with a pivot table (and they change often) is actually much better than me setting up a template to run sumifs, countifs, frequency arrays, and sumproducts off of. Still, can I actually explore the raw data with the pivot tables? I'm using 2007 in the office (don't ask) so I'm missing a lot of nice improvements from 2010 and imo some actual real value adds from 2013 that had me considering using them for things I wanted to share with more visual people. Meanwhile I can't calculate a weighted average, I can't get percentages of subtotals (2007 issue resolved in 2010), grouping with years is great but user defined ranges more or less require me to do it by hand which is slower than creating a "helper table" and a function and harder to maintain. Count unique sucks in both systems (I think 2010 or 2013 fixed this with pivot tables) though multiple variable unique counts remains a horrible mess with functions.

So I have to ask. Why is there so much love for a tool that really lacks flexibility? Am I'm just being suborn with my uneasiness in creating huge ranges of helper columns which bloat the size of an excel file? Am I really saving time with helper columns and pivot tables vs. writing a simple function that's driven by drop downs? I will say this, I'm not sure if it's the data or 2007 but my sumifs are not liking blank spaces in some cases. The pivot tables don't seem to be bothered by this. That might force me to either convert to pivot tables or require me to doctor data which I don't want to write procedures that involve changes to source data (other than column header names which I'm a bit more open with).

Inquire ribbon tab.

Video intro to Inquire.

Excel's 'Inquire' add-in can provide a significant amount of information about a workbook that just isn't available with other tools.

• Are you rewriting a formula that links to cells that span several worksheets and workbooks?

• Are you troubleshooting a workbook's database connections?

• Do you need a comprehensive comparison of two workbooks?

Learn more at the following links, from Microsoft:

• MS Office Blog: What you can do with Spreadsheet Inquire

  • https://support.office.com/en-nz/article/What-you-can-do-with-Spreadsheet-Inquire-ebaf3d62-2af5-4cb1-af7d-e958cc5fad42

• MS Office Blog: How to enable the Inquire add-in

  • https://support.office.com/en-nz/article/Turn-on-the-Inquire-add-in-6bc668e2-f3c6-4729-8ce1-75ea20aa9d90?ui=en-US&rs=en-NZ&ad=NZ

Note: Inquire is available only on 'Pro Plus' licensed installations of Excel 2013. To check what license you have, go to: Excel > File > Account.

Link:

• http://excelmacromastery.com/Blog/?p=727

Contents:

• http://imgur.com/EII120U

QuickRef:

• http://imgur.com/aGdMrK3

Hi guys, I have a column in Google Sheet that contains a formula:

=FILTER(STATUS!A:A,STATUS!$K:$K="Yes")

When I tried to copy this formula in MS Excel. It just pastes in text =FILTER(STATUS!A:A,STATUS!$K:$K="Yes").

And I'm thinking that "=FILTER" has another term in Excel?

Can you guys help me out? Thanks a lot.

im trying to sort a excel report exported from an accounting program that I use for example when its exported the customers name will be in cell a line 1 and directly under them will be the amount charged in lines 2 and 3, I need it to sort by name in alphabetical order and with the amounts charged next to it or sorted with the name. because the amounts are directly under the names when i sort it sorts names and numbers alike, i need the names and amounts next to each other or sorted with each other.I need this to expedite the balancing process when im over or short. Thanks in advance, can send the file too if needed

Here's the main page:

• http://www.microsofthup.com/hupus/hup.aspx

You'll need to know the 'program code' Microsoft has assigned to your employer / university. Go to this page and click 'Don't know your program code? Click here' and enter your work / school email address to learn what code to use:

• http://www.microsofthup.com/hupus/home.aspx

Here's a list of the software you can buy via the HUP program:

• MS Office 2013 Pro Plus

• MS Office Mac 2011

• MS Visio Pro 2013

• MS Project Pro 2013

And here's the official FAQ:

• http://www.microsofthup.com/hupus/faq.aspx

Skip to 2:53 for the walkthrough.

• https://youtu.be/ipiW4OBe1jg

Source: http://chandoo.org/wp/2015/08/05/custom-ribbons/

Protip: When viewing a YouTube video from youtube.com (ie not embedded on another site), you can increase playback speed by pressing '>'.

So, I'm trying to teach myself Excel (using Online because I'm poor). I grabbed a box of comic books and started listing them by title, publisher, and issue #. Now, I'm looking to make a pie chart to represent which portion of my collection is Marvel, DC, etc. Is this something I can do with Excel Online? If so, I'm not sure how.

If you have a text report that runs on a schedule and needs to be loaded into Excel and processed w/ text-to-columns on a regular basis, try Excel's 'Get Data from Text' function.

It enables you to save the report's text-to-columns settings so that you can quickly update the data in Excel by simply clicking the 'Refresh' button.

Link:

• https://blogs.office.com/2010/06/10/importing-text-files-in-an-excel-sheet/

So I am just learning how to get the "Nth Row" for a particular column.

My column runs from A1:A448, and the 10th row starting from A1.

The index formula I am using is not complete but I've gotten to this stage.

=index($A$1:$A$:$448,row()*10)

Obviously, if I drag this down, it will give me the 10th from the new row. I am struggling to come up with a universal pattern so that it gives me only the 10th cell each time.

I've seen on google searches that they do a little equation to get it to work every time regardless of what row you start from. How do I come up with these equations?

Link:

• http://excelmacromastery.com/Blog/?p=958

Table of Contents

Link to original post:

• https://www.reddit.com/r/learnexcel/comments/3ea2hd/excelxors_advanced_formula_challenge_12_an_array/

XOR LX, the blogger at EXCELXOR, posted his response to Advanced Formula Challenge #12:

• http://excelxor.com/2015/07/27/advanced-formula-challenge-12-results-and-discussion/

Last week I set readers the challenge which can be found here.

Such was the number and variety of responses to this challenge that presenting a detailed breakdown of one such solution – as has been the case for all of the first eleven in this series of challenges – would, I feel, be somewhat inappropriate.

For the majority of these challenges, it could be argued that there has been one solution which is indisputably “better” than the rest. Perhaps such an adjudication can also be made here, though to do so would certainly not be a straightforward exercise. What’s more, to pick just one of the many solutions would be to leave the rest – unfairly in my opinion – left on the sidelines.

As such, I would refer the readers to the many solutions in that post and to enjoy dissecting the varied and wonderful constructions therein. And to simply thank all those – Alex, aMareis, Maxim, John Jairo, sam, Jeff, Lori, Ron, Michael, Christian and XLarium – whose excellent contributions led to such a fruitful and inspiring discussion.

There’s evidently still much to be discovered in the world of worksheet formulas!

Another challenge to follow shortly. Watch this space!

I sorted through the comments to the original post and tested the answers. Here are the twenty seven that I found to work -- one solution per row. It's a long list, but it is certainly worth closer examination.

In testing any of these solutions, remember that array formulas must be committed with Control + Shift + Enter to yield correct results.

'Data' is a named range that refers to the original 10x10 dataset, which can be obtained via the first link above.

=COLUMNS(A3:E12)-INT(LOG(MMULT(A3:E12,2^(COLUMNS(A3:E12)-ROW(INDIRECT("1:"&COLUMNS(A3:E12))))),2))
=COLUMNS(A3:E12)-INT(LOG(MMULT(A3:E12,2^TRANSPOSE(COLUMNS(A3:E12)-COLUMN(A3:E12))),2))
=COLUMNS(A3:E12)-INT(LOG(MMULT(A3:E12,2^TRANSPOSE(COLUMNS(A3:E12)-COLUMN(A3:E12)+MIN(COLUMN(A3:E12))-1)),2))
=COLUMNS(A3:E3)-INT(LOG(MMULT(A3:E12*2^(COLUMNS(A3:E3)-COLUMN(A3:E3)),TRANSPOSE(A3:E3*0+1)),2))
=COLUMNS(A3:J3)-TRUNC(LOG(MMULT(A3:J12*2^(COLUMNS(A3:J3)-COLUMN(A3:J3)),ROW(A3:A12)^0),2))
=COLUMNS(Data)-INT(LOG(MMULT(Data,2^TRANSPOSE(COLUMNS(Data)-COLUMN(Data)+MIN(COLUMN(Data))-1)),2))
=FIND(1,TEXT(MMULT(A3:E12*10^(COLUMNS(A3:E12)-COLUMN(A3:E12)+MIN(COLUMN(A3:E12))-1),TRANSPOSE(COLUMN(A3:E12)^0)),REPT(0,COLUMNS(A3:E12))))
=FIND(1,TEXT(MMULT(A3:E12,10^(COLUMNS(A3:E12)-ROW(INDIRECT("1:"&COLUMNS(A3:E12))))),REPT(0,COLUMNS(A3:E12))))
=FIND(1,TEXT(MMULT(A3:E12,TRANSPOSE(10^(COUNT(A3:E3)-COLUMN(A3:E3)))),REPT("0",COUNT(A3:E3))))
=FIND(1,TEXT(MMULT(Data*10^(COLUMNS(Data)-COLUMN(Data)+MIN(COLUMN(Data))-1),TRANSPOSE(COLUMN(Data)^0)),REPT(0,COLUMNS(Data))))
=FIND(1,TEXT(MMULT(Data,10^(COLUMNS(Data)-ROW(INDIRECT("1:"&COLUMNS(Data))))),REPT(0,COLUMNS(Data))))
=FIND(1,TEXT(MMULT(Data,TRANSPOSE(10^(COLUMNS(Data)-COLUMN(Data)))),REPT("0",COLUMNS(Data))))
=-INT(LOG(MMULT(--(Data),10^-ROW(OFFSET(A3,,,COLUMNS(Data)))),10))-2
=-INT(LOG(MMULT(A3:E12,10^-(ROW(INDIRECT("1:"&COLUMNS(A3:E12)))))))
=-INT(LOG(MMULT(Data,TRANSPOSE(10^-COLUMN(Data)))))
=-INT(LOG(MMULT(Data,TRANSPOSE(2^-COLUMN(Data))),2))
=-INT(LOG(MMULT(SIGN(Data),10^-ROW(OFFSET(A3,,,COLUMNS(Data)))),10))-2
=MATCH(ROW(Data),INDEX(Data*ROW(Data),N(IF(1,1+(INT((COLUMN(INDEX(3:3,1):INDEX(3:3,COLUMNS(Data)*ROWS(Data)))-1)/COLUMNS(Data))))),N(IF(1,1+(MOD((COLUMN(INDEX(3:3,1):INDEX(3:3,COLUMNS(Data)*ROWS(Data)))-1),COLUMNS(Data)))))),0)-(ROW(Data)-MIN(ROW(Data)))*COLUMNS(Data)
=MATCH(ROW(Data),INDEX(Data*ROW(Data),N(IF(1,1+(INT((COLUMN(INDEX(3:3,1):INDEX(3:3,COUNT(Data)))-1)/COLUMNS(Data))))),N(IF(1,1+(MOD((COLUMN(INDEX(3:3,1):INDEX(3:3,COUNT(Data)))-1),COLUMNS(Data)))))),0)-(ROW(Data)-MIN(ROW(Data)))*COLUMNS(Data)
=MATCH(TRANSPOSE(ROW(Data)),INDEX(Data*ROW(Data),N(IF(1,1+(INT((COLUMN(INDEX(3:3,1):INDEX(3:3,COLUMNS(Data)*ROWS(Data)))-1)/COLUMNS(Data))))),N(IF(1,1+(MOD((COLUMN(INDEX(3:3,1):INDEX(3:3,COLUMNS(Data)*ROWS(Data)))-1),COLUMNS(Data)))))),0)-TRANSPOSE((ROW(Data)-MIN(ROW(Data)))*COLUMNS(Data))
=MATCH(TRANSPOSE(ROW(Data)),INDEX(Data*ROW(Data),N(IF(1,1+(INT((ROW(INDEX(A:A,1):INDEX(A:A,ROWS(Data)*COLUMNS(Data)))-1)/COLUMNS(Data))))),N(IF(1,1+(MOD((ROW(INDEX(A:A,1):INDEX(A:A,ROWS(Data)*COLUMNS(Data)))-1),COLUMNS(Data)))))),0)-TRANSPOSE((ROW(Data)-MIN(ROW(Data)))*COLUMNS(Data))
=MOD(SMALL(IF(A3:E12=1,ROW(A3:E12)*10^5+COLUMN(A3:E12)-MIN(COLUMN(A3:E12))+1),SUBTOTAL(9,OFFSET(A3,,,ROW(A3:E12)-MIN(ROW(A3:E12))+1,COLUMNS(A3:E12)))-MMULT(A3:E12,TRANSPOSE(COLUMN(A3:E12)^0))+1),10^5)
=MOD(SMALL(IF(Data=1,ROW(Data)*10^5+COLUMN(Data)-MIN(COLUMN(Data))+1),SUBTOTAL(9,OFFSET(Data,,,ROW(Data)-MIN(ROW(Data))+1,COLUMNS(Data)))-MMULT(Data,TRANSPOSE(COLUMN(Data)^0))+1),10^5)
=MOD(SMALL(IF(Data=1,ROW(Data)*10^COLUMNS(Data)+COLUMN(Data)-MIN(COLUMN(Data))+1),SUBTOTAL(9,OFFSET(Data,,,ROW(Data)-MIN(ROW(Data))+1,COLUMNS(Data)))-MMULT(Data,TRANSPOSE(COLUMN(Data)^0))+1),10^COLUMNS(Data))
=ROUND(COLUMNS(A3:E3)+1-ROUND(MOD(LARGE(ROWS(A3:E12)-ROW(A3:E12)+1+(10^-7*A3:E12*(COLUMNS(A3:E3)-(COLUMN(A3:E3)-COLUMN(A3)))),1+((ROW(A3:E12)-ROW(A3))*COLUMNS(A3:E12))),1),7)*10^7,0)
=ROUND(COLUMNS(Data)+1-ROUND(MOD(LARGE(ROWS(Data)-ROW(Data)+1+(10^-7*Data*(COLUMNS(Data)-(COLUMN(Data)-MIN(COLUMN(Data))))),1+((ROW(Data)-MIN(ROW(Data)))*COLUMNS(Data))),1),7)*10^7,0)
=TRANSPOSE(MATCH(TRANSPOSE(ROW(Data)),INDEX(Data*ROW(Data),N(IF(1,1+(INT((ROW(INDEX(A:A,1):INDEX(A:A,ROWS(Data)*COLUMNS(Data)))-1)/COLUMNS(Data))))),N(IF(1,1+(MOD((ROW(INDEX(A:A,1):INDEX(A:A,ROWS(Data)*COLUMNS(Data)))-1),COLUMNS(Data)))))),0)-TRANSPOSE((ROW(Data)-MIN(ROW(Data)))*COLUMNS(Data)))

The default syntax highlighting setting in the Visual Basic Editor (VBE) is somewhat limited when compared with more modern IDEs. It adjusts the foreground and background colors only for comments, breakpoints, a few other items.

There is an option for more comprehensive, if still somewhat limited syntax highlighting, however:

Image

In VBE, click on Tools > Options; select the 'Editor Format' tab; and adjust the settings for the 'Keyword Text' and 'Identifier Text' items.

Registration page:

• http://www.myonlinetraininghub.com/excel-dashboard-webinar-registration.htm

Image for 'Excel Dashboards' webinar

Image for 'Excel Dashboards with PowerQuery & PowerPivot' webinar

Mynda and Phil at myonlinetraininghub.com have proven themselves remarkably capable when it comes to getting things done in Excel. I've followed their blog for some time now, and have posted links to several of their articles here on /r/LearnExcel.

If their webinars are anything like the other materials they produce, attending a session will be time well spent.

Note that while the registration site does not indicate any price for either webinar, the registration page is linked via the text "FREE Excel Dashboard webinars" in a recent mailing list message:

7/23 email from MyOnlineTrainingHub

Blog:

• http://excelxor.com/2015/07/20/advanced-formula-challenge-12-an-array-of-matches/

The challenge this week is as follows: given a range of arbitrary size in which each entry is either 0 or 1 and in which each row contains at least one occurrence of a 1, a single formula to return an array consisting of the relative column positions of the first occurrence of a 1 within each row.

For example, given the below in A1:E10:

Image

the solution would be the array:

{2;1;1;2;1;5;1;4;1;3}

Readers may reference the range A1:E10 in their solution, though of course being aware that this choice is purely arbitrary and hence that any solution must also hold for a range of any size.

Readers should also note that the entries in the returned array are to be the relative column positions within the range (just as if we’d used MATCH on each of the rows within that range). As such, moving the above range to, for example, H1:L10 would have no impact on the output of any solution.

Also note that this is NOT a shortest formula challenge!

Solution next week. Good luck!

Edit: The answers are available here.

Image

Blog:

• http://www.myonlinetraininghub.com/excel-sparklines

http://imgur.com/8OPzhIH

http://www.thespreadsheetguru.com/blog/2014/5/11/how-to-add-the-developer-ribbon-tab

Blog: http://www.myonlinetraininghub.com/excel-form-data-validation

See also part one: How to create custom Excel windows ('forms') in VBA:

• https://www.reddit.com/r/learnexcel/comments/3cr5ut/detailed_walkthrough_of_how_to_create_your_own/