Advanced Excel Topics

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

Subtopics

Last 10 posts

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 9 months 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 11 months 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 1 year 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 1 year 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 1 year 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 2 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 2 years ago
External File Links
I am working with a model that references an external file to pull in specific data points for multiple business units. The referenced file has all of the BUs in separate tabs. However, my model has all BUs in a single sheet. If I include in my file the exact BU tab names from the external file, can... Read More
Go to post added 2 years ago
waterfall chart with multiple series
Hi. I have encounter a problem when trying to do a waterfall chart with multiple series in excel. I have income data for a sector and don't know how to put them into one waterfall chart with time series. What I want to accomplish is something like the chart below. Could you advice a method to do... Read More
Go to post added 2 years ago
I have
I am using a SUMPRODUCT that is contingent upon 2 criteria: region and product division. Regions are NAM, LATAM, ASPAC, and EMEA. Product divisions are CS, TT, and FL. The criteria are located in a drop down list to limit responses. My model works fine if I select both a region and a division, such... Read More
Go to post added 2 years ago