What are your top-of-mind questions to understand if a person has Basic Excel skill?

[Darren Bartrup]

Dates are a real pain.

I receive a dump of data each quarter and every quarter I ask him to put it in English date format and not US and every time I have to go through and change 6/7/2014 to 7/6/2014 - got a formula for it somewhere now.

Then I regularly get dates such as 7.6.14 or 7/6//20014 - to be honest, that last one is people just not caring about their data quality "'cos the Excel expert will sort it out." so they feel they can just dump on a sheet and expect us to give it a polish for them.

As my old boss said - if they put s**t in you can't expect anything but s**t out.

And Excel as a 'shared' data entry system to be built in a couple of weeks and just work? Ok, gotta stop now before I really start ranting....

Edit....

And can they have a workbook that does this, this, this and that to 100,000 rows of data allowing for the data to move, change and transform at any point and draw lots of charts and rank it and sort it and everything with formula as they don't know where the sort button is and ..... oh.... bit slow isn't it?

 

[steve case]

Thanks for the support (-:

I wish there was a button that I could turn the date function off, but I am told there isn't one.

 

[Jon von der Heyden]

I receive a dump of data each quarter and every quarter I ask him to put it in English date format and not US and every time I have to go through and change 6/7/2014 to 7/6/2014 - got a formula for it somewhere now.

I feel your pain.

By the way:

If your dates are entered e.g. mm/dd/yyyy but you want dd/mm/yyyy, do the following:

1. Highlight the column of offending dates
2. Invoke Text to Columns
3. In step 1 choose delimited
4. In step 2 uncheck all delimiters
5. In step 3 choose Date > MDY
6. Hit Finish

Voila!

 

[Jon von der Heyden]

Array formulas to me mean users may not know the best approaches. Array formulas are bad for the large datasets I work with & would indicate to me that users don't know how to work smart with large datasets.

I love you to bits Fazza and I learned many things from you over the years, but I'm afraid I strongly disagree with you here. I have spent many years working with DB's and interacting with DB's using Excel. And I believe I have learned the very many good lessons you have shared on this forum. But I have also learned from our formula big-hitters. I definitely think array formulas provide solutions to problems that are simply too impractical so solve using a DB approach. In fact I have even opted for array formulae to calc results where otherwise I would have to resort to T-SQL.

I do however think you have a valid point to a degree. I do see countless instances where array formulae are used unnecessarily and where better solutions would exist had the data been structured better (properly!).

 

[Fazza]

Thanks for the kind words, Jon. I've too learnt much from others on the forum. There are uses for array formulas, for sure. Good data structure is an under-addressed important basis for spreadsheets. best regards, Fazza

 

[mfexcel]

Can't agree more. A well structured data base would absolutely make everyone's life easier.

I do however think you have a valid point to a degree. I do see countless instances where array formulae are used unnecessarily and where better solutions would exist had the data been structured better (properly!).

 

[Darren Bartrup]

Thanks for the pointer Jon - again, I learn something new with Excel / Access / VBA every day.