A few months ago I started a thread about learning Python which I found very helpful. A position has come up at my company which sounds appealing but one of the prerequisites is advanced mastery (their words) of Microsoft Excel. Unfortunately, I know next to nothing about Excel. The details of what constitutes advanced mastery are somewhat vague, but I presume it includes all of the following:
Learning how to write macros in VBA.
Learning how to write formulas/functions which can handle more than one thing at a time.
Learning how to display data in a wide variety of formats.
Learning how to format data in a spreadsheet and make it look nice.
Manipulating data, adding it, subtracting it, summing it…all that stuff.
If I were to study for 1-2 hours a day, roughly how long would it take for me to learn all the above and be confident with it? The interview is in 3 weeks so need answer fast…ish
In case it’s relevant, I would rate my own Python skills as ‘beginner, but nearly intermediate’. I can do functions and a little OOP and I’m confident with else/if statements, loops, list comprehensions and other beginner stuff, but not much beyond that.
Search for Excel Boot camps. They are designed to immerse a newbie in Excel and get them ready for a job that requires it.
The OP is describing a job that requires actual experience in Excel. There’s no way of faking that. You have to write spreadsheets for awhile and get a feel for designing them.
Most people don’t learn macros until after they’ve been writing spreadsheets for awhile.
If you know Python at the level you describe you could learn VBA pretty quickly. If you commit 30 hours (that’s 2 hours per day, 5 days a week), I would think you be confident enough to answer any questions or be able to pass a straightforward practical test. VBA is not that hard to learn. The trickiest bit in VBA is learning how it make it interact with Excel. This is just pure memorization of the worksheet (and other related objects) methods and properties. If you practice using them it should come pretty fast.
So find some online tutorials that have problems/assignments and the set about learning how to do it.
Sure, there are some middling to advanced features like pivot tables, VLOOKUP, building VBA functions, Ctrl+Shift+Enter {array} manipulation and Data Analysis functions you can learn. But over the course of my career, I’ve seen too many instances where companies have created these elaborate Excel based applications to perform complex business operations that ultimately had to be replaced. I feel like you’d be better off learning the tools used to replace them. SQL Server for database, Python or Java for programming and scripting, R for stats, BI tools like QlikView or Tableau.
My guess would be they have existing processes, spreadsheets etc. that need to be maintained and used at least until they’re replaced, which it isn’t clear they intend to do.
From a purely techincal point I agree with the above. Unfortunately, many organizations are heavily wedded to using Excel (or some other obsolete and overextended tool) to manage data or perform some task and are unwilling to switch over to other, better tools for fear of breaking some functionality or the labor hours to stand up and learn new tools even if their existing tools are performing inadequately or are inefficient.
As an example, I have to deal with several analysts using Excel to try to manage large sets of time history data (several gigabytes of data) and trying to perform complex transforms and statistical analysis. They all have access to both Matlab and and Scilab, and could use Python/NumPy/SciPy, all of which are specifically designed for performing such operations, but they stick with Excel because it is what they know even though it is not only slow as a hard shit but also has notable bugs in handilng high precision calculations, has limited statistical functionality, and limits the number of individual data samples they can handle at once, which means they can’t actually do the calculations in one flow and instead have to do multiple iterations and cut & paste of summary statistics into intermediate spreadsheets. It results in a process that takes a couple of weeks to do what could be done in Matlab or Python in a few hours, and often ends up with calculation errors that are nearly impossible to debug because they’re spread across formulae in so many cells rather than organized as a coherent script.
And realize that any macro functionality built into Excel will probably break every time you switch to a new major release, for no goddamned reason whatsoever. A few years ago I had an automagical report generator that took statistics generated from a collection of Matlab scripts, dumped them into Excel, and then autopopulated a Word document with pregenerated figures and tables so all I had to do was add some expository text. It turned writing a post-flight report from being the work of a couple of weeks for a half dozen people into something that could be done literally overnight and then proofed by the responsible engineers in each discipline. And then we had to switch to a new version of Microsoft Office and so many functions were broken or deprecated it didn’t even make sense to try to fix it. I eventually wrote some scripts to dump the figures and tables into a LaTex document which also tracked references and generated lists of figures and tables without having to fuck around with a bunch of “styles”, but the organization pointedly did not want to use LaTex even though the result was identical and the end product was a PDF file, so they’re back to spending 500 person-hours to manually import and locate figures and tables in a Word document and then have someone go through and fix all of the formatting problems that are inevitable in a large Word document.
Anyway, to become proficient in Excel is probably the work of about 40 to 60 hours, including Pivot Tables, context-sensitive formatting, and writing simple VBA scripts. You should figure out what kind of functionality is required for this role and focus on that. Once you get the job, however, start figuring out how to use better tools and slyly integrate them in (there are several Python modules specifically designed to interface with Excel or to import and export data into .XLSX format). Excel is fine for what it is intended for–being a spreadsheet for performing simple chained calculations, managing data for trade studies and evaluations, and doing quick and easy 2D data visualization–but it sucks for any heavy lifting in finance, large data handling, or statistics.
I am sorry but going from “next to nothing” to “advanced mastery” isn’t going to happen in 1-2 hours a day in three weeks. The features you list are just scratching the surface. You may also need:
[ul]
[li]Pivot tables and pivot charts[/li][li]Finding solutions using Goal Seek, Solver, and Data Analysis[/li][li]Charting, including multiple axes, regression lines, and combination charts[/li][li]Advanced Filter[/li][li]Data Validation[/li][li]Conditional Formatting[/li][li]Tables[/li][/ul]
And learning enough to pass a test is a much different thing than learning enough to actually do anything useful. You can learn what the textbooks say about all these features but you need real experience to understand how to solve various types of problems and what is the best approach (there is often more than one way to do things in Excel). In particular, VBA is honest-to-god programming; if you are not already a programmer, you can learn some basic VBA but to write real applications in VBA with good code (good performance, low maintenance) takes years. I have been using Excel regularly for business applications for many years, and VBA for 13 years (with a background as a software developer), and I am still learning new things all the time.
CookinWithGas scooped me on what I was going to say so I’ll just throw out this caveat. I’ve seen too many projects where someone was using Excel when they should have been using Access. OTOH, macros in Excel just about line up with coding in Access.
A little tangential but a good point, similar to Stranger’s point. It’s the hammer/nail scenario. I’ve seen people use Excel when they should be using a database, a desktop publisher, a math program.
I agree, I am pretty good with Excel and Access and I find people have given me things in Excel that are much easier to do in Access, because that’s what it is destined for.
