***Official*** Excel Help Corner

[Ned]

I'm an Excel nerd to the hilt and enjoy helping others when it comes to Excel problems, so I figured I'd start a thread where y'all can ask questions on how to do certain things in Excel. I'm game for anything from formulas to Excel VBA.

I'll also try to bump this at times with various tips/tricks I've learned over the years. Here are a few random thoughts:

Repeating formulas

When you're using a formula against a data population that resides in multiple rows, you can make Excel do the work for you. First, enter the formula you want to use next to your data. While still selected on that cell with the formula, take your mouse and double click the tiny box that's in the lower right hand corner of the cell. Excel will autofill that formula all the way down until it finds a gap in your data.

Autofill Days, Months

Excel will autofill Days or Months for you. Enter "January" and then click/drag the tiny box in the lower right hand corner (while still selected on January) and drag it as far as you need it. Excel will fill the rest of the months. The same can be done for days of the week as well as the 3 letters for each (Jan, Feb, Mon, Tue). You can also add your own custom lists for Excel to autofill by going to File - Options - Advanced - Edit Custom Lists (this is the 2013 path).

Pivot tables are powerful

Learn how to use this tool - it will take care of a lot of things people are looking to do with formulas when they want to summarize/analyze it. Here's a decent video - https://www.youtube.com/watch?v=8ffdXfriLPQ

[Jayrok]

Maybe we can move or copy our exchange from the other thread to here.

[Harry Manback]

Glad you opened this.

An excel file my company uses for years (the file is almost 4 years old) has multiple dropdown fields (combobox activeX control) that have stopped working today.

I suspect it is due to some update on excels end, as this is effecting outside vendors as well as internal folks (though one internal folk reports this working at his home, unsure of which version of excel he is using, he thinks 2010)

The file was created with excel 2003 most likely. We are now using Office 365 and Excel 2010.

Any thoughts?

[Ned]

Glad you opened this.

An excel file my company uses for years (the file is almost 4 years old) has multiple dropdown fields (combobox activeX control) that have stopped working today.

I suspect it is due to some update on excels end, as this is effecting outside vendors as well as internal folks (though one internal folk reports this working at his home, unsure of which version of excel he is using, he thinks 2010)

The file was created with excel 2003 most likely. We are now using Office 365 and Excel 2010.

Any thoughts?

When you say stopped working what's it doing (or not doing)?

Any error messages?

Did you upgrade the file to 2010 (i.e. doing a save-as and saving it as a .xslx)?

[Ned]

Excel help request: I have a column with multiple text that I'd like to remove except for one character string.

Example:

G1 cell = OSVGA #5544,MSBV #MS223-023, CARD #2014-A-0034

G2 cell = OSVGA #6354,STOP #MS123-654, RAIN #2011-B-1076, OSDGB #546-098

and so forth in all cells of column G

I'd like to remove all text/characters except for the highlighted above. Maybe the TRIM function, but I don't need to keep any characters except for the 2014-A-0034 (and so on for each cell/row). Sometimes the XXXX-X-XXXX is in the middle of the text string, as in example G2 above, and sometimes it is at the end as in example G1.

The character string will always be "XXXX-X-XXXX" and it will be the only string in this format in any given cell, but this number is different for each row in the spreadsheet/workbook.

Is there a wildcard I can use with TRIM that removes all characters (before and after the target string) except for this particular string of characters in the cell (i.e. XXXX-X-XXXX)?

=MID(G1,SEARCH("-?-",G1)-4,11)

That's assuming your data will never have a single char in between two dashes other than the your XXXX-X-XXXX string.

This works for the row G1, but what about all the rows in G? Could I highlight the column then insert the function?

What I need is to run a function on the column and have the results (from the example above) be:

G1 -- 2014-A-0034

G2 -- 2011-B-1076

and so on for each row.

Type the formula in G1. Copy G1 and paste all the way down as far as you need it. Excel is smart enough to update the references.

Sorry, I assumed you knew that excel would do this part for you. Another way is to double click the tiny little box that shows up in the lower right hand corner of cell G1 (when selected). Excel will autofill that formula until it finds a break in the data (consecutive rows).

Ned,

I can't open that link, but I think I see where I misunderstood you in the beginning. I can paste the formula into a separate cell on the sheet and then drag the formula down the page, as you mentioned above, and have it correspond with the cells in the original column. And this works great.

G1-G56 (for instance) has the original data in each cell. Pasting your formula into H1 extracts the text I need from G1 and puts it into H1. When I drag the formula down column H it grabs all the text that I need from each row in column G. I can then go back and delete column G and use column H for my new data.

This works great. Thanks a million!

Hopefully you just omitted a step in your post.

yes, of course. I can't delete column G or the formula is hosed. I can hide it instead.

Edited January 12, 2015 by Ned

[Harry Manback]

Glad you opened this.

An excel file my company uses for years (the file is almost 4 years old) has multiple dropdown fields (combobox activeX control) that have stopped working today.

I suspect it is due to some update on excels end, as this is effecting outside vendors as well as internal folks (though one internal folk reports this working at his home, unsure of which version of excel he is using, he thinks 2010)

The file was created with excel 2003 most likely. We are now using Office 365 and Excel 2010.

Any thoughts?

When you say stopped working what's it doing (or not doing)?

Any error messages?

Did you upgrade the file to 2010 (i.e. doing a save-as and saving it as a .xslx)?

Yes, all of those things I tried.

No errors, the excel file almost appears to be treating the drop down as a picture, not a dynamic drop down box.

[Harry Manback]

Actually, when checking for compatibility I get the following:

Uninitialized ActiveX controls cannot be transferred to the selected file format. The controls will be lost if you continue.

[TheWalkmen]

Hey Dorks, is there a way to trace precedents in a cell with a vlookup or hlookup that will return the exact cell that is being found in the lookup instead of returning the entire lookup group?

[Notorious T.R.E.]

Glad you opened this.

An excel file my company uses for years (the file is almost 4 years old) has multiple dropdown fields (combobox activeX control) that have stopped working today.

I suspect it is due to some update on excels end, as this is effecting outside vendors as well as internal folks (though one internal folk reports this working at his home, unsure of which version of excel he is using, he thinks 2010)

The file was created with excel 2003 most likely. We are now using Office 365 and Excel 2010.

Any thoughts?

Harry - we had a few macros go kaput recently and had to do this fix for activex:

1) Shut down all office applications you're running - locally or via Citrix

2) Paste this this link into your browser: http://support.microsoft.com/kb/3025036/EN-US

3) Scroll down a bit and click the microsoft "fix it" button and follow the prompt

Note there are two "Fix it" button on that page - click the first button

If you are prompted, click "Save" and then go into your Downloads folder and open the file from there and follow the prompts

[Dinsy Ejotuz]

Jayrock, you could also just use Data --> Text to Columns and tell it to start a new column where it finds the "#" sign.

Then delete the columns you don't need and do it again on the "," if you have too much data (as in the 2nd record you posted).

[tri-man 47]

A couple random thoughts of mine:

While probably basic for most, the options under View / Window are helpful ...freezing frames to keep titles in place, and different ways to align workbooks when working with two or more ..OR to open a new window of the same workbook to view different areas of the workbook. Under Formulas, the Watch Window is a neat tool to track a specific cell value(s) as other changes are being made.

For a long time, I was unaware of the separate Developer tab that needs to be enabled under File / Options / Customize Ribbon. You can then insert some great Control tools for user selections.

--

A set of excellent support books, available for less than $20 each on Amazon, are the Excel 2013 Essential Skills (and Expert Skills) with the Smart Method. Every lesson is two pages with detailed steps and clear screen shots. The supporting files for training are available from the related web site for free.

ned, I look forward to reading more about how you and other advanced users utilize Excel.

[TheWalkmen]

I keep shortcuts in my toolbar for paste values, paste formulas, paste format, freeze panes, filter, group, ungroup, and couple others like Paste and Transpose that really make life easy. I also use ctrl + 1 a lot for formatting and stuff.

[tri-man 47]

I keep shortcuts in my toolbar for paste values, paste formulas, paste format, freeze panes, filter, group, ungroup, and couple others like Paste and Transpose that really make life easy. I also use ctrl + 1 a lot for formatting and stuff.

Good point. A well-organized Quick Access Toolbar with some Separators is very useful.

[Harry Manback]

Glad you opened this.

An excel file my company uses for years (the file is almost 4 years old) has multiple dropdown fields (combobox activeX control) that have stopped working today.

I suspect it is due to some update on excels end, as this is effecting outside vendors as well as internal folks (though one internal folk reports this working at his home, unsure of which version of excel he is using, he thinks 2010)

The file was created with excel 2003 most likely. We are now using Office 365 and Excel 2010.

Any thoughts?

Harry - we had a few macros go kaput recently and had to do this fix for activex:

1) Shut down all office applications you're running - locally or via Citrix

2) Paste this this link into your browser: http://support.microsoft.com/kb/3025036/EN-US

3) Scroll down a bit and click the microsoft "fix it" button and follow the prompt

Note there are two "Fix it" button on that page - click the first button

If you are prompted, click "Save" and then go into your Downloads folder and open the file from there and follow the prompts

This did it, unfortunately I don't know if it's a fix I can have our external vendors use.

Thanks!

[shadyridr]

There was a recent Microsoft Office update that screwed up Active X Controls for all existing spreadsheets. i create a lot of models using VBA at work and Im the one who discovered the solution at work even though Im not in IT. You have to delete the MSForms.exd file

[Kanil]

Oh man... I'm going to use you like the prettiest man in prison. I'll probably be checking in here weekly.

[sports_fan]

How can I go about learning how to write macros? I consider myself pretty good with Excel and enjoy using it. I can handle most any function. But I know very little about macros other than that they can be extremely useful. I can record a macro, look at the code and get a general idea of what's going on but that's about it.

[Ned]

Hey Dorks, is there a way to trace precedents in a cell with a vlookup or hlookup that will return the exact cell that is being found in the lookup instead of returning the entire lookup group?

Try using MATCH instead. It will return the row it was found.

I'm a big fan of using nested INDEX/MATCH instead of VLOOKUP.

Some light reading on the subject - http://www.mrexcel.com/articles/excel-vlookup-index-match.php

[shadyridr]

How can I go about learning how to write macros? I consider myself pretty good with Excel and enjoy using it. I can handle most any function. But I know very little about macros other than that they can be extremely useful. I can record a macro, look at the code and get a general idea of what's going on but that's about it.

get yourself a VBA book. Its easy and people look at you like a God.

[shadyridr]

Hey Dorks, is there a way to trace precedents in a cell with a vlookup or hlookup that will return the exact cell that is being found in the lookup instead of returning the entire lookup group?

Try using MATCH instead. It will return the row it was found.

I'm a big fan of using nested INDEX/MATCH instead of VLOOKUP.

Some light reading on the subject - http://www.mrexcel.com/articles/excel-vlookup-index-match.php

much quicker processing time.

[bushdocda]

How can I go about learning how to write macros? I consider myself pretty good with Excel and enjoy using it. I can handle most any function. But I know very little about macros other than that they can be extremely useful. I can record a macro, look at the code and get a general idea of what's going on but that's about it.

I was able to make better sense of the vba book stuff after recording some simple macros and then messing with the code. Made it easy to connect what I did with the code. I still suck but I use a few mainly for formatting or automating a redundant task

[Ned]

How can I go about learning how to write macros? I consider myself pretty good with Excel and enjoy using it. I can handle most any function. But I know very little about macros other than that they can be extremely useful. I can record a macro, look at the code and get a general idea of what's going on but that's about it.

I'm self taught and the bolded is how I learned along with a lot of online reading. There are more efficient ways of doing things vs. How the macro is recorded. A few random tips:

Indent your code on things like If/Then/Else. It makes your code much easier to read. Use four spaces or a single tab.

If Range("B1") = "Hi Shuke" Then    Range("C1") = "69"Else    Range("C1") = "0"End If

There's rarely ever a need to select a cell. Instead of this:

Range("A1").SelectSelection.CopyRange("B1").SelectActive sheet.Paste

Do this:

Range("A1").Copy Destination:= Range("B1")

Use the With statement whenever you can. Instead of this:

Range("B1").Value = "Woz sucks"Range("B1").Bold = True

Do this:

With Range("B1")    .Value = "Woz Sucks"    .Bold = TrueEnd With

Use vbNullString when you need a blank instead of "".

[Ned]

. Its easy and people look at you like a God.

Ain't that the truth.

[Ned]

How can I go about learning how to write macros? I consider myself pretty good with Excel and enjoy using it. I can handle most any function. But I know very little about macros other than that they can be extremely useful. I can record a macro, look at the code and get a general idea of what's going on but that's about it.

I was able to make better sense of the vba book stuff after recording some simple macros and then messing with the code. Made it easy to connect what I did with the code. I still suck but I use a few mainly for formatting or automating a redundant task

There are a ton of books out there that will give you everything you'll ever need, but I'm not a book guy. I learn quicker by just diving in and feeling my way through it.

Edited January 13, 2015 by Ned

[bryhamm]

[Long Ball Larry]

Autofill Days, Months

Excel will autofill Days or Months for you. Enter "January" and then click/drag the tiny box in the lower right hand corner (while still selected on January) and drag it as far as you need it. Excel will fill the rest of the months. The same can be done for days of the week as well as the 3 letters for each (Jan, Feb, Mon, Tue). You can also add your own custom lists for Excel to autofill by going to File - Options - Advanced - Edit Custom Lists (this is the 2013 path).

I seem to have lost the box when we switched to 2010. Any idea how to re-enable it? Have never been able to find an option anywhere.

[Long Ball Larry]

Macro question:

I have spreadsheets with about 15 columns that I have to format each month with borders and put in subtotals for the last 4-5 columns of the sheet (and do a custom border for the subtotals).

The sheets vary in the number of rows, so I do not know how to tell it which row is the final row where it should be subtotaling and custom bordering. Thoughts?

[Ned]

Autofill Days, Months

Excel will autofill Days or Months for you. Enter "January" and then click/drag the tiny box in the lower right hand corner (while still selected on January) and drag it as far as you need it. Excel will fill the rest of the months. The same can be done for days of the week as well as the 3 letters for each (Jan, Feb, Mon, Tue). You can also add your own custom lists for Excel to autofill by going to File - Options - Advanced - Edit Custom Lists (this is the 2013 path).

I seem to have lost the box when we switched to 2010. Any idea how to re-enable it? Have never been able to find an option anywhere.

I'm on my iPad so I can't check for sure, but it's under excel options - editing options. Called drag and drop or something along those lines.

[Ned]

Macro question:

I have spreadsheets with about 15 columns that I have to format each month with borders and put in subtotals for the last 4-5 columns of the sheet (and do a custom border for the subtotals).

The sheets vary in the number of rows, so I do not know how to tell it which row is the final row where it should be subtotaling and custom bordering. Thoughts?

LastRow = Range("a1048576").end(xlup).row

[Random]

dot

[gmbacm]

Pivot tables are powerful

Learn how to use this tool - it will take care of a lot of things people are looking to do with formulas when they want to summarize/analyze it. Here's a decent video - https://www.youtube.com/watch?v=8ffdXfriLPQ

Keep meaning to look these...

[cap'n grunge]

[TheWalkmen]

Hey Dorks, is there a way to trace precedents in a cell with a vlookup or hlookup that will return the exact cell that is being found in the lookup instead of returning the entire lookup group?

Try using MATCH instead. It will return the row it was found.

I'm a big fan of using nested INDEX/MATCH instead of VLOOKUP.

Some light reading on the subject - http://www.mrexcel.com/articles/excel-vlookup-index-match.php

much quicker processing time.

With an index/match the index group, the entire match row, and the entire match column are highlighted, not the actual cell with the returned value. I'm not sure it can be done.

Edited January 13, 2015 by TheWalkmen

[coyote5]

[Ned]

Hey Dorks, is there a way to trace precedents in a cell with a vlookup or hlookup that will return the exact cell that is being found in the lookup instead of returning the entire lookup group?

Try using MATCH instead. It will return the row it was found.

I'm a big fan of using nested INDEX/MATCH instead of VLOOKUP.

Some light reading on the subject - http://www.mrexcel.com/articles/excel-vlookup-index-match.php

much quicker processing time.

With an index/match the index group, the entire match row, and the entire match column are highlighted, not the actual cell with the returned value. I'm not sure it can be done.

I misread that last night and didn't realize you were using the actual Trace Precedents function. Yes, I don't think there's a way to highlight the actual resulting matched cell. I'm not sure exactly what you're doing without seeing your sheet, but you can get the address of a matched cell pretty easily via formula...

Here's a google sheet of what I think you're asking for (TheWalkmen tab) - https://docs.google.com/spreadsheets/d/1CX39meSEa2uwFAmh_rgn4x5ltS-7zdzYncfSjpT1tL8/edit?usp=sharing

Column A is the data to search through

Column D is the search criteria

Column E is the matched address

[Kanil]

I feel like this should be really easy but I just cant figure it out.

I have a group of cells in a row that may or may not contain text in them. Each row has one (and only one) cell that has text in it but it could be anywhere in the row (sheet goes to column IU). What I need to do is have a formula in column A that looks at the rest of the columns in the row and places that text into column A.

For example:

===|===A===|===B===|===C===|===D===|

=1=|BLA====|=======|BLA====|=======|

=2=|IAII=====|IAlI=====|=======|=======|

=3=|12A====|=======|=======|12A====|

Edited March 16, 2015 by Kanil

[shadyridr]

Think youre gonna have to write some code for that using instr() function

[Long Ball Larry]

ASAP Utilities

[gmbacm]

Would merging across the rows not work?

[Dinsy Ejotuz]

Not 100% sure how it handles character values, but you could try the "max" function for the entire range of cells. If there's only one value there I think it would be returned.

[hagmania]

For text values (non-numeric):

If you start in A1, use the below formula:

=INDEX(B1:IU1,0,MATCH(REPT("z",255),B1:IU1))

This uses INDEX-MATCH to scan for the last text value in a given range. You can alter B1:IU1 to suit your range.

Edited March 16, 2015 by hagmania

[hagmania]

Not 100% sure how it handles character values, but you could try the "max" function for the entire range of cells. If there's only one value there I think it would be returned.

If you are finding numerical values this is the correct answer imo, but it will return 0 if there are only text values in the columns.

Starting in A1:

=MAX(B1:IU1)

Edited March 16, 2015 by hagmania

[Kanil]

For text values (non-numeric):

If you start in A1, use the below formula:

=INDEX(B1:IU1,0,MATCH(REPT("z",255),B1:IU1))

This uses INDEX-MATCH to scan for the last text value in a given range. You can alter B1:IU1 to suit your range.

Thanks man... you're awesome.

[Spin]

Is this where excel nerds come to hang out?

[hagmania]

Is this where excel nerds come to hang out?

Gooble gobble one of us

[cap'n grunge]

In cell a1 type...

=hlookup("*",b1:iu1,1,0)

Copy down in column a.

Lookups return the first value they find and in this case it is looking for any values with the wildcard character.

Edited March 16, 2015 by NetnautX

[DropKick]

For text values (non-numeric):

If you start in A1, use the below formula:

=INDEX(B1:IU1,0,MATCH(REPT("z",255),B1:IU1))

This uses INDEX-MATCH to scan for the last text value in a given range. You can alter B1:IU1 to suit your range.

I used offset-match, which is very similar. Also, rather than specify a "high value" with rept("z",255), used the -1 option on the match, which is "greater than or equal" and it takes a low value

=OFFSET(A14,0,MATCH("0",B14:D14,-1))

[Maurile Tremblay]

Macro question:

I have spreadsheets with about 15 columns that I have to format each month with borders and put in subtotals for the last 4-5 columns of the sheet (and do a custom border for the subtotals).

The sheets vary in the number of rows, so I do not know how to tell it which row is the final row where it should be subtotaling and custom bordering. Thoughts?

LastRow = Range("a1048576").end(xlup).row

A large number of my macros have a

LastRow = .Cells(Rows.count, 1).End(xlUp).Row

somewhere in them.

[Mr. Ected]

:sehorn:

[Herc]

Looking for a little assistance please.

=INDEX('Daily Report Fri'!$B23:$B33,MATCH($A$13,'Daily Report Fri'!$C$23:$C$33,0))

this line works great if there is info to find if not it returns an #NA

For some reason I can not use if(isna I get a too many arguments. I would like a blank if the result is NA what am I doing wrong?