I'm possibly going to be tested in Excel for a job interview in the near future. The job description lists intermediate to advanced Excel skills needed. Do you have any idea on what they'll test me on most likely? Here is the job description:

"- Assist Senior level Telecommunications Analyst

- Support Full Life Cycle Management of Telecom Audit and Analysis projects

- Assist in Developing and maintaining inventory of clients' telecommunications services

- Analysis and processing of Client's telecom invoices focusing on identifying and eliminating invoicing errors

- Analysis and management of client telecom contracts

- Providing Customized Reports for Clients (Associating services by Cost Centers, G/​L codes, etc)

- Provisioning and Management of Clients’ Telecommunications services (Moves, Adds, Changes, Disconnects)

- Support for Daily interaction with clients and carriers in regards to invoicing issues, dispute resolution, recommendations, etc.​

- Filing claims with carriers on behalf of clients

- Obtaining and negotiating pricing on behalf of clients

- Developing recommendations for the optimization of existing services

- Perform all other duties as assigned"

Hmm, tbh I'm not sure but with that in mind:

The only interviews i've had where they 'tested' me in excel, I had to send code examples up front. Specific questions I can think of that I have been asked in an interview "why would you use array formulas?" and "whats the point of option explicit?" both have been covered ITT. I'm not sure they how they would test your ability to do any of the things listed unless you are on a computer and even then it's the kind of thing that can vary widely.

From the description it looks like they are more interested in you than your excel ability, the specific things you will need to do in excel would be secondary to that. IME most job descriptions look a lot more daunting than they turn out to be.

Either way it looks like an interesting job that will have you covering a lot of things, good luck in the interview let us know how it goes. Sorry I couldn't be more helpful.

I use YYYY-MM-DD or YYYY-MM-DD HH:MM (depending on if I want to display hours). Then I wrote a very easy little macro to update all highlighted dates. I keep often used macros in another workbook that I open when the app starts.

Sub DateFormat()
Selection.NumberFormat = "yyyy-mm-dd hh:mm"
End Sub

Sub DateFormatNoTime()
Selection.NumberFormat = "yyyy-mm-dd"
End Sub

I use Excel hours a day and never really use pivot tables. I'm all about array functions. I've never found anything I can't really do.

Couldn't you just enter Now() in the cell and then copy/paste special > values to retain the time?

This isn't true now that VB generally refers to VB.net. VB6 was horrible.

Thanks for the help, zomg! This was what I was looking for. I slightly modified it, but your tip got me to where I needed to be.

Oh i wasn't aware of that, but from some googling you are definitely correct. I always classified them as three 'different' subsets of VB (VBA, VB and .NET).

It seems like there is a belief that microsoft is trying to phase out VBA in favour of vb.net, are you a .net programmer? any comments on advantages? switching process? I think i remember reading that it is OO, I have some history with java so i imagine the switch, at least on a conceptual level wouldn't be too difficult.

Even if they do phase out VBA in office 2012+ (conservative guess, according to a quick search it looks like they have removed it from autoCAD 2010) at the current rate of adoption I can't really seeing it being a huge issue for the next 5 years.

How does it run? Are they standalone applications? Can i put a button in excel that will run a .NET script? I assume you need an IDE (visual studio?) I'll probably download it in the next couple of weeks when I have some time.

This worked. Thank you so much. I have never tried any of the insert functions before. Any way you can give a quick rundown on what the most useful ones do?

I like to think i'm pretty good with Excel so I'll try to follow this.

Why did you lie to your boss about your wife being in an emergency? Why not just explain to him that you had an interview for a better company?

bahaha ^^^^

<3

is there a way to make a graph with the x and y axes in a log scale?

I have no 2007 at hand, but in Office XP:
Add a graph, right click on Axis, Format Axis, go to 2nd tab (Scale), click Logaritmic scale at the bottom.

This only works for the Y-axis on 'normal' graphs.
If you need it on the X-axis too, switch Graph type to X-Y Scatter.

this may be simple:
We have an excel document that tracks when people clock in it contain 10's of thousands of entries over time.
What we are trying to do is sort the sheet by the number card swipes so that the names with the fewest entries are at the top and order the rest of the sheet ascending order with the people with the most entries are at the bottom while still keeping the clock in of each individual grouped together.

Thanks. That was easy enough.

say you have a degree in econ or finance, would passing the certification exam for excel be useful?

they have a cert for excel?

Given the, uh, local-to-world coordinate transformation matrices for two distinct objects in 3d space (tacos), how can you tell if the objects are facing each other?
This is probably pretty basic stuff but yeah... I just got done doing 300 pushups or whatever.

I use Excel for grading. I'd like to prepare progress reports that I could print out.

I have grade categories and assignments in columns and names/IDs in rows along with points for each. Something like this:

So we make a report for Bob Barker on a new worksheet, and it looks like this:

Any way to do this without much VBA? I can muck VBA and figure out how to edit existing scripts, but haven't really learned it.

If you have headers in A1, B1 etc,
select every column, go to Data -> Sort, if he doesn't see you're using headers, tick the box, select which column/header to sort by...done!

Ive experimented a bit but it seems easier to order you first sheet differently, so you can use VLOOKUP on your second sheet.
Its gonna be very hard now with HLOOKUP, INDEX, MATCH, some string concatenation and transposing.

Like this:

Just saw this, thanks. It's not as easy as it was in Lotus 1-2-3 but it's still quite handy if they are long columns.

Re pivot tables - can someone tell me how easy it is to see what they are actually doing? I've never really set them up but when sheets have them I've not found it easy to follow exactly what they are doing. It's the same with data tables - very powerful but moreso when you set them up rather then adjust etc.

Just saw this, thanks. It's not as easy as it was in Lotus 1-2-3 but it's still quite handy if they are long columns.

Re pivot tables - can someone tell me how easy it is to see what they are actually doing? I've never really set them up but when sheets have them I've not found it easy to follow exactly what they are doing. It's the same with data tables - very powerful but moreso when you set them up rather then adjust etc.

this won't work. you need to aggregate the data first...perfect opportunity for a pivot table.


Select the data range and create a pivot table. Lets assume its simply 2 columns: employee and swipe-timestamp. Add [employee] to the row labels group and the [swipe-timestamp] to the values group. Now just sort the employee row label by [swipe-timestamp] ascending.