Advanced Excel Topics

General advanced Excel topics and questions to make your life easier and to increase your productivity and efficiency countlessly


Last 10 posts

Return Column and Row Vales for the minimum value in the table
Hi - I am trying to find a formula that will return the correct column and row for the minimum value in a table.. For example - in the below table I would want to type in a formula that gives me the column (x) and the row (c) for the minimum value. I am okay with these being separate functions fo... Read More
Go to post added 3 years ago
% Change
I have daily data that I need monthly and yearly price change, how do I do that?
Go to post added 6 years ago
Data Manipualtion
Hi, how can I turn daily pricing data, into monthly price data, and yearly price data.
Thank you,
Go to post added 6 years ago
WLSRegression using Excel VBA
Hello, I'm using the following code to create a diagonal matrix and then to produce the Betas for a regression analysis. For some reason my output ends as 0, or I receive an #Value error stating one of my values is of the wrong data type. My analysis involves a y-intercept and 2 independent vari... Read More
Go to post added 7 years ago
Adding hyperlink to header in Excel
I am having difficulty adding a hyperlink to a header of an excel sheet and I think it requires some VBA. However, when I click on the header (from the page layout view), I can't even access VBA. Any ideas on how to access it and what syntax is needed to add a hyperlink? Thanks a bunch, Elliot
Go to post added 7 years ago
New Collection and Looping
I want to build a new collection from a desired range, but I want to skip the blank cells. I tried to begin the loop with Do While Not (ISempty(activecell)), but this is still pulling blank cells. The collection involves 2 thresholds - a minimum and maximum budget variance - corresponding to a list... Read More
Go to post added 7 years ago
An alternative needed to a multi-select drop-down list
I am in search of a way to convert multiple selections in a drop-down list into a single field with the selections separated by a semicolon. Background: I need to get contact data into a specific format that is importable into a particular on-line software app. I was contemplating having a user-f... Read More
Go to post added 7 years ago
Shortcut Keys for Macros Not Working
For some reason, macros work when I click on them in the Developer/Macros dialogue box. But when I hit the shortcut keys, the macro fails to run. I created the macro by recording it, so it prompted me for what shortcut key I wanted. I've created macros this way many times in the past so I don't th... Read More
Go to post added 7 years ago
Advanced Excel for Data Analysis Course Questions
1) I am working off a version of Windows Excel 2016. Do you know what the home key shortcut is? "Fn" does not and work neither does "alt +h." 2) For Excel 2016 In the Pivot Tables Section, when I get to step 3 out of 3 to set up the table, excel doesn't give me the option of clicking layout (it'... Read More
Go to post added 7 years ago
Saving function across workbooks in Excel 2010
Hi, I have a Personal.XLSB filed saved the location discussed in the link "Saving macros across workbooks in 2007". I see my macros in other workbooks and that works. But a function I created/copied in VBA does not appear in other workbooks. The new function works perfectly in the Personal.xl... Read More
Go to post added 8 years ago