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
% Change
I have daily data that I need monthly and yearly price change, how do I do that?
Jay
I have daily data that I need monthly and yearly price change, how do I do that?
Jay
Data Manipualtion
Hi, how can I turn daily pricing data, into monthly price data, and yearly price data.
Thank you,
Hi, how can I turn daily pricing data, into monthly price data, and yearly price data.
Thank you,
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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... 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 for row and column
x y z
a 7 8 9
b 6 6 5
c 2 9 4 Read More