***Official*** Excel Help Corner (3 Viewers)

[Herc]

Ned I took your advice and blew up the entire workbook and redesigned it. To get around my previous problem I made drop down menus linked to drop down menus and it works great, the first menu has all the companies listed the second menu only lists the employees with each company.

I am having some problems with calculating something and my formula just isn't working like I would expect it to. Basically what I am trying to do is check 6 cells for a Yes each yes= 8 hours but there is a cap of 40 hours for the week then I am subtracting the working hours from that number(we are tracking idle time). I nested a bunch of if statements and I cant seem to figure out what I am doing wrong. It always returns a Not on Site value, is it a simple syntax error or should I approach this differently maybe with a count if? I even tried reversing my arguments from highest to lowest to the opposite and its the same thing

Here's what I have currently:

=IF(IF(IF(IF(IF(IF(IF(IF(B122="Y",8,0)+IF(C122="Y",8,0)+IF(D122="Y",8,0)+IF(E122="Y",8,0)+IF(F122="Y",8,0)+IF(G122="Y",8,0)>=40,40-F15,IF(B122="Y",8,0)+IF(C122="Y",8,0)+IF(D122="Y",8,0)+IF(E122="Y",8,0)+IF(F122="Y",8,0)+IF(G122="Y",8,0)=32),32-F15,IF(B122="Y",8,0)+IF(C122="Y",8,0)+IF(D122="Y",8,0)+IF(E122="Y",8,0)+IF(F122="Y",8,0)+IF(G122="Y",8,0)=24),24-F15,IF(B122="Y",8,0)+IF(C122="Y",8,0)+IF(D122="Y",8,0)+IF(E122="Y",8,0)+IF(F122="Y",8,0)+IF(G122="Y",8,0)=16),16-F15,IF(B122="Y",8,0)+IF(C122="Y",8,0)+IF(D122="Y",8,0)+IF(E122="Y",8,0)+IF(F122="Y",8,0)+IF(G122="Y",8,0)=8),8-F15),IF(B122="Y",8,0)+IF(C122="Y",8,0)+IF(D122="Y",8,0)+IF(E122="Y",8,0)+IF(F122="Y",8,0)+IF(G122="Y",8,0)=0),0,"Not on Site")

 

[Ned]

That's a crazy deep formula that looks over-engineered to me. It's hard to see with that many nested IFs, so I'd suggest using the Evaluate Formula tool (under the Formulas ribbon) to step through each condition. I'm guessing you're always falling into the final FALSE because you're always summing more than 0.

I'm not sure what the final FALSE is doing ("Not On Site"), but you can simplify your calc with this:

=(COUNTIF(B12:G12,"Y")*8)-F15

That'll give you the hours for each Y and subtracting out whatever you have in cell F15.

 

[Herc]

=IF(COUNTIF(B124:G124,"Y")*8>=40,40-F15,COUNTIF(B124:G124,"Y")*8-F15) this should work. Thanks Ned!

Also sent you a PM

 

[TheIronSheik]

Ned - Here's a question I've come across several times and have never been able to figure out on my own. Is it possible to put an image inside a cell? I can copy an image and put it on the sheet, but I can't confine it to a cell. If I'm in WORD, I can create a chart and put images inside certain cells and that looks great. But in WORD, I can't then create formulas to go with the images. So I'm kind of stuck between the 2. I'd like to have some images confined to certain cells and also be able to have the ability to create functions around them (not using them).

Any ideas on how to accomplish putting images in certain cells?

 

[hagmania]

Why do you need it confined to the cell, in particular?

 

[El Floppo]

Ned - Here's a question I've come across several times and have never been able to figure out on my own. Is it possible to put an image inside a cell? I can copy an image and put it on the sheet, but I can't confine it to a cell. If I'm in WORD, I can create a chart and put images inside certain cells and that looks great. But in WORD, I can't then create formulas to go with the images. So I'm kind of stuck between the 2. I'd like to have some images confined to certain cells and also be able to have the ability to create functions around them (not using them).

Any ideas on how to accomplish putting images in certain cells?

I used to do this all the time... at least that's what my brain is telling me. the only physical examples I can find just have the image floating over the top, and not "in" the cell.

 

[hagmania]

According to http://stackoverflow.com/questions/9776611/insert-picture-into-excel-cell, it was at one point possible if you involved Google Docs, but the .xlsx now just downloads the "formula" instead of the picture.

 

[TheIronSheik]

Why do you need it confined to the cell, in particular?

If I want to do a nice visual report that say, talks about sales for each state, it would be nice to have the picture of the state in a cell of the row. Or a region. Or a country. Or if I'm running a football pool, it would be nice to put a helmet in the cell to identify the team instead of typing out the team name. I mean, there are a lot of reasons.

 

[Ned]

Why do you need it confined to the cell, in particular?

If I want to do a nice visual report that say, talks about sales for each state, it would be nice to have the picture of the state in a cell of the row. Or a region. Or a country. Or if I'm running a football pool, it would be nice to put a helmet in the cell to identify the team instead of typing out the team name. I mean, there are a lot of reasons.

I do this all the time since I do a lot of prettied up external reporting for work.... The best I think you can do is to change the properties to 'Move but don't size with cells'. When you move rows/columns around it'll stay in the cell location you put it. The only downside is if you resize the row or column, it's not going to move with it. So you'd need to re-center if you re-sized.

 

[TheIronSheik]

Why do you need it confined to the cell, in particular?

If I want to do a nice visual report that say, talks about sales for each state, it would be nice to have the picture of the state in a cell of the row. Or a region. Or a country. Or if I'm running a football pool, it would be nice to put a helmet in the cell to identify the team instead of typing out the team name. I mean, there are a lot of reasons.

I do this all the time since I do a lot of prettied up external reporting for work.... The best I think you can do is to change the properties to 'Move but don't size with cells'. When you move rows/columns around it'll stay in the cell location you put it. The only downside is if you resize the row or column, it's not going to move with it. So you'd need to re-center if you re-sized.

Where is this, exactly?

 

[TheIronSheik]

Why do you need it confined to the cell, in particular?

If I want to do a nice visual report that say, talks about sales for each state, it would be nice to have the picture of the state in a cell of the row. Or a region. Or a country. Or if I'm running a football pool, it would be nice to put a helmet in the cell to identify the team instead of typing out the team name. I mean, there are a lot of reasons.

I do this all the time since I do a lot of prettied up external reporting for work.... The best I think you can do is to change the properties to 'Move but don't size with cells'. When you move rows/columns around it'll stay in the cell location you put it. The only downside is if you resize the row or column, it's not going to move with it. So you'd need to re-center if you re-sized.

Where is this, exactly?

Nevermind. I see it. Yeah. This is the best I've had in the past, but I was hoping for something better. Damn.

 

[hagmania]

I would most likely start deleting parts of the workbook. Excel can only track so many dependencies at one time. Once you delete enough, the button should work for you.

 

[hagmania]

Why do you need it confined to the cell, in particular?

If I want to do a nice visual report that say, talks about sales for each state, it would be nice to have the picture of the state in a cell of the row. Or a region. Or a country. Or if I'm running a football pool, it would be nice to put a helmet in the cell to identify the team instead of typing out the team name. I mean, there are a lot of reasons.

I do this all the time since I do a lot of prettied up external reporting for work.... The best I think you can do is to change the properties to 'Move but don't size with cells'. When you move rows/columns around it'll stay in the cell location you put it. The only downside is if you resize the row or column, it's not going to move with it. So you'd need to re-center if you re-sized.

Where is this, exactly?

Nevermind. I see it. Yeah. This is the best I've had in the past, but I was hoping for something better. Damn.

I'm confused as to why you don't want it to just lay on top.

 

[TheIronSheik]

Why do you need it confined to the cell, in particular?

If I want to do a nice visual report that say, talks about sales for each state, it would be nice to have the picture of the state in a cell of the row. Or a region. Or a country. Or if I'm running a football pool, it would be nice to put a helmet in the cell to identify the team instead of typing out the team name. I mean, there are a lot of reasons.

I do this all the time since I do a lot of prettied up external reporting for work.... The best I think you can do is to change the properties to 'Move but don't size with cells'. When you move rows/columns around it'll stay in the cell location you put it. The only downside is if you resize the row or column, it's not going to move with it. So you'd need to re-center if you re-sized.

Where is this, exactly?

Nevermind. I see it. Yeah. This is the best I've had in the past, but I was hoping for something better. Damn.

I'm confused as to why you don't want it to just lay on top.

For resizing and moving columns or rows.

 

[Ned]

Every time I open a particular spreadsheet, I get a dialog box telling me Excel cannot calculate because of a circular reference. It never tells me where this reference is. The "Circular:" at the bottom of the screen lists a cell but there's no error in that cell to trace.

I think I'll just throw the computer out the window instead.

Go to the Formulas ribbon and under the Error Checking button there's an option to show the circular references.

 

[Ned]

Every time I open a particular spreadsheet, I get a dialog box telling me Excel cannot calculate because of a circular reference. It never tells me where this reference is. The "Circular:" at the bottom of the screen lists a cell but there's no error in that cell to trace.

I think I'll just throw the computer out the window instead.

Go to the Formulas ribbon and under the Error Checking button there's an option to show the circular references.

I don't see this. I have a "Check for Errors" dropdown, and the two options are 'Check for Errors..." and "Trace error". I clicked "Check for Errors...." and the dialog that popped up read: "The Error Check is complete for the entire sheet." There were no errors found.

Excel Mac 2011

The bold is your problem.

 

[ragincajun]

I feel so lonely.

 

[Ned]

I feel so lonely.

=IF(A1="Lonely","PM Wet Dream","What's your problem?")

 

[ragincajun]

Let's talk Fuzzy Lookup.

I have several warehouses with say 100k items. The warehouses may have the same item but when it was originally uploaded into SAP the description might have been different.

Example:

Whse 1: pen, red, bic

Whse 2: bic pen

Whse 3: pen model #abcd

The goal is to identify all of the similar items across the different warehouses. Very watered down but a fuzzy lookup will lookup similar items based on percentage similarity. Let's say I have 100k items amongst 10 warehouses. I can perform a fuzzy lookup with a 90% accuracy and then with conditional formatting highlight the same words. At this point it would become more of a manual process to review and find the like items.

thoughts?

Here you go

 

[Ned]

Let's talk Fuzzy Lookup.

I have several warehouses with say 100k items. The warehouses may have the same item but when it was originally uploaded into SAP the description might have been different.

Example:

Whse 1: pen, red, bic

Whse 2: bic pen

Whse 3: pen model #abcd

The goal is to identify all of the similar items across the different warehouses. Very watered down but a fuzzy lookup will lookup similar items based on percentage similarity. Let's say I have 100k items amongst 10 warehouses. I can perform a fuzzy lookup with a 90% accuracy and then with conditional formatting highlight the same words. At this point it would become more of a manual process to review and find the like items.

thoughts?

Here you go

I'll take a look when I'm back in the office. I haven't used fuzzy lookup so I'm curious.

 

[Nemesis]

Let's talk Fuzzy Lookup.

I have several warehouses with say 100k items. The warehouses may have the same item but when it was originally uploaded into SAP the description might have been different.

Example:

Whse 1: pen, red, bic

Whse 2: bic pen

Whse 3: pen model #abcd

The goal is to identify all of the similar items across the different warehouses. Very watered down but a fuzzy lookup will lookup similar items based on percentage similarity. Let's say I have 100k items amongst 10 warehouses. I can perform a fuzzy lookup with a 90% accuracy and then with conditional formatting highlight the same words. At this point it would become more of a manual process to review and find the like items.

thoughts?

Here you go

I'll take a look when I'm back in the office. I haven't used fuzzy lookup so I'm curious.

I've read about the Fuzzy Lookup Add-in and never did install it, but I think this could help with problems using Vlookups, etc...

 

[Nemesis]

Why do you need it confined to the cell, in particular?

If I want to do a nice visual report that say, talks about sales for each state, it would be nice to have the picture of the state in a cell of the row. Or a region. Or a country. Or if I'm running a football pool, it would be nice to put a helmet in the cell to identify the team instead of typing out the team name. I mean, there are a lot of reasons.

I do this all the time since I do a lot of prettied up external reporting for work.... The best I think you can do is to change the properties to 'Move but don't size with cells'. When you move rows/columns around it'll stay in the cell location you put it. The only downside is if you resize the row or column, it's not going to move with it. So you'd need to re-center if you re-sized.

Where is this, exactly?

Nevermind. I see it. Yeah. This is the best I've had in the past, but I was hoping for something better. Damn.

I'm confused as to why you don't want it to just lay on top.

For resizing and moving columns or rows.

I have experience in this area as I have projects in the US, Canada, and Latin America and I display the country's flag in a cell and it stays put in the cell and conforms to the cell size ......I think you need to hold down the ALT button and then stretch the image. By holding down the ALT button it will automatically align to the cell wall (or multiple cells, if you merge cells) that you want to apply it to.

BTW: If you want to improve your Excel Visual skills, I highly recommend a course called "Excel Driven Dashboards". It's an 8 hr course that I learned the above technique that walks you thru how to make advanced Excel charts and dashboards. Most of us learned Excel years ago when the chart functions were limited. But now Excel has incorporated a lot of new stuff to help you make better visual presentations. The course is interactive, where it gives you step by step instructions for each technique and example workbooks to create the chart/dashboard with a tab next to it of what it is supposed to look like when u are finished so you can check your work. Some of the techniques you learn are really cool....like in-cell Sparklines, Advanced Conditional Formatting, etc. The course allowed me to make a bunch of new KPI charts that our department was tasked with and allowed me to interact with some other Sr Managers and CEOs that I hadn't had much interaction before. It definitely opened some "doors" for me.

https://www.westerncpe.com/store/detail.aspx?id=2134528ONL

If anyone is interested, pls PM me (be patient though as it may take a couple days to respond)

 

[roadkill1292]

When you're setting up a vlookup, where's the switch that automatically makes the array you are searching absolute instead of relational? Does this mess up a bunch of other basic functions?

 

[2Young2BBald]

I would like to build a sheet with several layers of security along with protecting the entire workbook. Is it possible to password protect a sheet when opening and then, as an additional layer, hide a few columns where they could only be unhid via a different password? Any ideas on how to make a workbook and its data as secure as possible would be appreciated.

 

[acarey50]

When you're setting up a vlookup, where's the switch that automatically makes the array you are searching absolute instead of relational? Does this mess up a bunch of other basic functions?

Just press F4 (with the array selected in the formula bar or formula wizard) to toggle between the various relative vs. absolute references

 

[Ned]

I would like to build a sheet with several layers of security along with protecting the entire workbook. Is it possible to password protect a sheet when opening and then, as an additional layer, hide a few columns where they could only be unhid via a different password? Any ideas on how to make a workbook and its data as secure as possible would be appreciated.

You can protect the book from being opened under the General Options when saving the book.

You can also protect specific cells -> https://support.office.com/en-us/article/Lock-cells-to-protect-them-54fedd95-28dc-4abc-a26c-6566917c1a59

 

[2Young2BBald]

I would like to build a sheet with several layers of security along with protecting the entire workbook. Is it possible to password protect a sheet when opening and then, as an additional layer, hide a few columns where they could only be unhid via a different password? Any ideas on how to make a workbook and its data as secure as possible would be appreciated.

You can protect the book from being opened under the General Options when saving the book.

You can also protect specific cells -> https://support.office.com/en-us/article/Lock-cells-to-protect-them-54fedd95-28dc-4abc-a26c-6566917c1a59

Thanks as always!!

 

[Village Idiot]

I do macros occasionally to manipulate data.

In one of them, I'm doing a text to columns.

Is there any way to make that annoying do you really want to do this text to columns message that pops up, not pop up?

Yes I really want to do it, that's why I built this macro like this.

 

[Ned]

I do macros occasionally to manipulate data.

In one of them, I'm doing a text to columns.

Is there any way to make that annoying do you really want to do this text to columns message that pops up, not pop up?

Yes I really want to do it, that's why I built this macro like this.

I hate those too. You can disable them before that step, and then re-enable afterwards.

Application.DisplayAlerts = False

your code here

Application.DisplayAlerts = True

 

[TheIronSheik]

I know I'm probably missing something easy here, but I've noticed that sometimes the more advanced I get at Excel, I tend to not remember or know some easy things.

I have a simple sheet where I just keep track rof orders. It's by far the simplest sheet I use. In one column, I copy order numbers from a web site and paste them into it. Thing is, some of these order numbers start with a zero. Now, I know that I can add a ' in front of the number and then put a zero and it will show. But I would much rather just paste them in and have the zero show up. Is there a simple way to do this? I feel like I've done it before using column properties. Does this make sense?

 

[Ned]

I know I'm probably missing something easy here, but I've noticed that sometimes the more advanced I get at Excel, I tend to not remember or know some easy things.

I have a simple sheet where I just keep track rof orders. It's by far the simplest sheet I use. In one column, I copy order numbers from a web site and paste them into it. Thing is, some of these order numbers start with a zero. Now, I know that I can add a ' in front of the number and then put a zero and it will show. But I would much rather just paste them in and have the zero show up. Is there a simple way to do this? I feel like I've done it before using column properties. Does this make sense?

Format the column as text. When you're pasting in the orders, paste special - values.

Did you know you can download data from the web into Excel automatically? I use it all the time for some reporting I do.

https://support.office.com/en-us/article/Get-external-data-from-a-Web-page-708f2249-9569-4ff9-a8a4-7ee5f1b1cfba

 

[TheIronSheik]

I know I'm probably missing something easy here, but I've noticed that sometimes the more advanced I get at Excel, I tend to not remember or know some easy things.

I have a simple sheet where I just keep track rof orders. It's by far the simplest sheet I use. In one column, I copy order numbers from a web site and paste them into it. Thing is, some of these order numbers start with a zero. Now, I know that I can add a ' in front of the number and then put a zero and it will show. But I would much rather just paste them in and have the zero show up. Is there a simple way to do this? I feel like I've done it before using column properties. Does this make sense?

Format the column as text. When you're pasting in the orders, paste special - values.

Did you know you can download data from the web into Excel automatically? I use it all the time for some reporting I do.

https://support.office.com/en-us/article/Get-external-data-from-a-Web-page-708f2249-9569-4ff9-a8a4-7ee5f1b1cfba

Thank you! This is what I was looking for. I knew it was in there somewhere, I just couldn't remember it. You da man, as always.

 

[roadkill1292]

When you're setting up a vlookup, where's the switch that automatically makes the array you are searching absolute instead of relational? Does this mess up a bunch of other basic functions?

Just press F4 (with the array selected in the formula bar or formula wizard) to toggle between the various relative vs. absolute references

Does this make the array default to absolute values the next time I set up a lookup?

 

[Village Idiot]

I do macros occasionally to manipulate data.

In one of them, I'm doing a text to columns.

Is there any way to make that annoying do you really want to do this text to columns message that pops up, not pop up?

Yes I really want to do it, that's why I built this macro like this.

I hate those too. You can disable them before that step, and then re-enable afterwards.

Application.DisplayAlerts = False

your code here

Application.DisplayAlerts = True

Thanks but that gives me a syntax error in my macro.

I tried inserting those lines before my first line and after my last line in the macro.

And I really do not know Virtual Basic.

I was using the Record Macro button in the Developer Tab to set up the macro.

EDIT - And then I closed it and reopened the macro and entered the commands again, did a run from the editor, saved it, tried it again, and it ran perfectly.

F'n computers!

Thanks Ned!

 

[Ned]

Post your code. That DisplayAlerts code shouldn't be giving you any errors.

 

[Village Idiot]

Post your code. That DisplayAlerts code shouldn't be giving you any errors.

I edited my post above yours.

It works now.

I probably accidentally deleted something the 1st go around trying to enter those commands.

I'm good at breaking stuff but I'm all set now.

Thanks!

 

[Jayrok]

Is there a hotkey for grabbing all data in a worksheet, that is similar to Ctrl + arrow keys to go to the end of the last line of data? Not Ctrl+A

Example: You have 7,000 rows of data spanning 8 columns. You want to delete all rows in the worksheet except for a few rows that you've filtered.

I want to leave rows 1 through 10, but select and delete the rest of the 6,990 rows. I can select row 11 and drag all the way down the sheet, but that takes awhile. Is there a quick way to do this?

TIA

 

[Ignoramus]

Is there a hotkey for grabbing all data in a worksheet, that is similar to Ctrl + arrow keys to go to the end of the last line of data? Not Ctrl+A

Example: You have 7,000 rows of data spanning 8 columns. You want to delete all rows in the worksheet except for a few rows that you've filtered.

I want to leave rows 1 through 10, but select and delete the rest of the 6,990 rows. I can select row 11 and drag all the way down the sheet, but that takes awhile. Is there a quick way to do this?

TIA

CTRL + Shift + Arrows This will select to the last cell that contains something.

 

[Jayrok]

Is there a hotkey for grabbing all data in a worksheet, that is similar to Ctrl + arrow keys to go to the end of the last line of data? Not Ctrl+A

Example: You have 7,000 rows of data spanning 8 columns. You want to delete all rows in the worksheet except for a few rows that you've filtered.

I want to leave rows 1 through 10, but select and delete the rest of the 6,990 rows. I can select row 11 and drag all the way down the sheet, but that takes awhile. Is there a quick way to do this?

TIA

CTRL + Shift + ArrowsThis will select to the last cell that contains something.

Thanks!

 

[Ignoramus]

Is there a hotkey for grabbing all data in a worksheet, that is similar to Ctrl + arrow keys to go to the end of the last line of data? Not Ctrl+A

Example: You have 7,000 rows of data spanning 8 columns. You want to delete all rows in the worksheet except for a few rows that you've filtered.

I want to leave rows 1 through 10, but select and delete the rest of the 6,990 rows. I can select row 11 and drag all the way down the sheet, but that takes awhile. Is there a quick way to do this?

TIA

CTRL + Shift + ArrowsThis will select to the last cell that contains something.

Thanks!

Welcome.CTRL + Arrows will move you around without selecting. Learn to use those. Soooooooooooooooo much faster than mousing around.

 

[hagmania]

This is a such a frustrating problem:

Using a data connection and trying to update its definitions. Click OK and nothing happens. Stone nothing. Go back a screen and try to add a new connection, click "Add..." and nothing. Wtf.

 

[Mr. Ected]

=IF(COUNTIF(B124:G124,"Y")*8>=40,40-F15,COUNTIF(B124:G124,"Y")*8-F15) this should work. Thanks Ned!

Also sent you a PM

I know it's been a while, but are you still having issues? Would a simple DB be more efficient than a spreadsheet?

 

[Ned]

This is a such a frustrating problem:

Using a data connection and trying to update its definitions. Click OK and nothing happens. Stone nothing. Go back a screen and try to add a new connection, click "Add..." and nothing. Wtf.

What data source?

 

[hagmania]

This is a such a frustrating problem:

Using a data connection and trying to update its definitions. Click OK and nothing happens. Stone nothing. Go back a screen and try to add a new connection, click "Add..." and nothing. Wtf.

What data source?

This is more a quality of life issue, really, since I can just force it to change with VBA, but I've encountered this error in both the Connection Definition and the Command Definition, and again both OLEDB and ODBC, once even with just a straight SQL Server connection.

 

[Spin]

Taking this is another direction. I accepted a new position and am about to start interviewing replacement candidates for my old position, which is pretty excel heavy with some pretty advanced excel stuff. Plenty of array formulas, complicated index/match look ups, about as "advanced" as excel formula can get really.

Anyways, what are some good excel interview questions, that'll show if someone really understands some of the more complex excel stuff?

This is also my first time interviewing someone else for a position, so I'm not trying to trip them up or "stump them" with annoying syntax related questions such as "What is the difference between search and find" etc. Because that stuff can easily be googled. I'm really looking for stuff that shows who really understands some of the more complex things excel can do.

Like do I just show them a formula like this:

{=IFERROR(INDEX(Data!$C$1:$CD$9661,SMALL(IF(IFERROR(SEARCH($B$1,Data!$AC$1:$AC$9661),"False"),ROW(Data!$BW$1:$BW$9661)),ROW(1:1)),1),"")}

And say, what is this formula doing when copied down a column?

Some questions I initially thought of:

1) We generally can't use vlookups within our data set, even though the information is set up within columns, why do you think that is? - Do they understand the limitations of using vlookups

2) What do brackets enclosing a formula indicate? -Have they ever used an array formula before

3) What are some instances where Index/Match would be necessary instead of using a vlookup? -Have they used index/match before

4) How could you do a look up against multiple criteria? - IE, return the value in col A, if Col B is x, col C is y, Col D is Z. etc

Any other good ones?

 

[BroadwayG]

What do you say when the interviewee asks 'Why don't you just use VBA?'

 

[Ned]

#5-What's a pivot table?

 

[ragincajun]

Pivot tables organize and consolidate data.

Simple example. If you have a list of 5,000 cars with colors listed you can create a pivot table and sort by car colors in a seperate table telling you how many colors of each car you have.

 

[ragincajun]

Taking this is another direction. I accepted a new position and am about to start interviewing replacement candidates for my old position, which is pretty excel heavy with some pretty advanced excel stuff. Plenty of array formulas, complicated index/match look ups, about as "advanced" as excel formula can get really.

Anyways, what are some good excel interview questions, that'll show if someone really understands some of the more complex excel stuff?

This is also my first time interviewing someone else for a position, so I'm not trying to trip them up or "stump them" with annoying syntax related questions such as "What is the difference between search and find" etc. Because that stuff can easily be googled. I'm really looking for stuff that shows who really understands some of the more complex things excel can do.

Like do I just show them a formula like this:

{=IFERROR(INDEX(Data!$C$1:$CD$9661,SMALL(IF(IFERROR(SEARCH($B$1,Data!$AC$1:$AC$9661),"False"),ROW(Data!$BW$1:$BW$9661)),ROW(1:1)),1),"")}

And say, what is this formula doing when copied down a column?

Some questions I initially thought of:

1) We generally can't use vlookups within our data set, even though the information is set up within columns, why do you think that is? - Do they understand the limitations of using vlookups

2) What do brackets enclosing a formula indicate? -Have they ever used an array formula before

3) What are some instances where Index/Match would be necessary instead of using a vlookup? -Have they used index/match before

4) How could you do a look up against multiple criteria? - IE, return the value in col A, if Col B is x, col C is y, Col D is Z. etc

Any other good ones?

Ask if they know what a Fuzzy lookup is. Serious.

 

[ragincajun]

Not sure if it will rule anyone out but if they can explain it then you have an Excel Yoda on your hands.