[Dalaran1991]

As a data analyst I do ok with Excel, i.e most calculations, charts, and crosstables, basic macro.

But there are some advanced tools that I simply don't know, or dont even know exist. Most of this has to do with Virtual Basic, Advanced Sorting, in order to automate some office tasks.

Most of the time when I dont know something I just google the solution, but Im starting to see the need for a more structured way to learn.

Anyone knows of some good websites where you can master excel and makes the most use out of it? Some sites that are easy to follow and have well-structured courses?

Thanks guys,

[Meliorare93]

Not the best site but you could start here:
https://msdn.microsoft.com/en-us/library...e.14).aspx

If you can spare the time I would advise getting VBA lessons though...

Sometimes vba falls short for automating, you could also have a look here, this is a very very powerfull tool imo:
https://autohotkey.com/

[Huey]

https://excelexposure.com/lesson-guide/

This is the best guide I know of to learn Excel. It covers almost everything you need to know.

[polar]

Meliorare, what do you use AHK for? It looks interesting, but I'm not sure what I'd use it for. Opening programs doesn't take long enough for me to need hot keys.

Dalaran, I picked up VBA from playing with a variety of sources, I can't think of a good single source. A tip to keep in mind for later is that no VBA user in Excel writes all their code from scratch. Instead, you record SHORT macros (one thing at a time / no more than 20 seconds of clicking around), then modify the hard coded values with variables.

Keep in mind that there is no UNDO for VBA, you must never experiment with live data until you know your code works well. Also, no autosave, so hit save often.

Learn range, address, and cells functions, how to navigate references / cell addresses in a few different ways.
Learn how to define, set and change cell and variable values.
Learn how to set value of a cell equal to something (string or variable)
Learn a couple of ways to find last row / column (important!) and set last row equal to a variable
Learn to record macros, identify hard coded values, replace with variables.
Learn for, while loops (ie cycle through the rows of your data and perform an operation)
Learn functions and how to call them, pass variables

---> Learn to use Google to find people's solutions to your problems. I've saved hours and hours by lifting someone else's solution. But you should understand how it's implemented.

That should get you started.

[Stealth]

It's a book, and it can cost a bit, but I found it's quite comprehensive.

https://www.amazon.com/Excel-2013-Power-...1118490398

[ElSanto]

I'm planning to study Excel and the route i'm gonna take after a bit of research is this:
1.- https://www.udemy.com/microsoft-excel-20...nd-beyond/
2.- https://openeducation.blackboard.com/moo..._id=_219_1

and complement with this:
https://www.youtube.com/user/ExcelIsFun/playlists
http://www.automateexcel.com/learn-vba-tutorial/

Of course take my comment with a grain of salt

[Meliorare93]

(10-24-2017 10:05 AM)polar Wrote:  

Meliorare, what do you use AHK for? It looks interesting, but I'm not sure what I'd use it for. Opening programs doesn't take long enough for me to need hot keys.

Lot's of things...

If I have a recurring computer task then I record an AHK macro for it or write a script and let the computer do that mindless task. There are a some nifty macro recorders out there.

I also use it for autotext. Recurring pieces of text I have to type, if you code this can save you a lot of time! I've also have some autocorrect scripts, so I have the same autocorrect on all my software, etc

Very handy tool imo, saves me a lot of time. Some stuff what you can do with it on lifehacker:
https://lifehacker.com/tag/autohotkey

[Cane Toad]

As an analyst you are better off focusing on the reasons why you are doing the analysis in the first place eg; the business problem you are solving, the reason you are looking for a trend etc.

The risk is that if you focus too much on the "tools" (Excel, VB) you end up being labelled a data monkey.

Choosing a technical path is fine, but Excel and VB are really beginner steps.

You might be better off studying statistical methods and working with tools such as SPSS. This would get you above the data monkey level and start you on the path to real analytical work. Even studying accounting or engineering is a good option, at least you would know the reasons why the data is being generated in the first place.

[Dalaran1991]

(10-25-2017 02:32 AM)Cane Toad Wrote:  

As an analyst you are better off focusing on the reasons why you are doing the analysis in the first place eg; the business problem you are solving, the reason you are looking for a trend etc.

The risk is that if you focus too much on the "tools" (Excel, VB) you end up being labelled a data monkey.

Choosing a technical path is fine, but Excel and VB are really beginner steps.

You might be better off studying statistical methods and working with tools such as SPSS. This would get you above the data monkey level and start you on the path to real analytical work. Even studying accounting or engineering is a good option, at least you would know the reasons why the data is being generated in the first place.

That's an excellent point Toad, truthfully I'm trying to get out of this job as soon as my naturalization process is finished.

I had a blue pill past and I studied Sociology, so getting this job for me in Paris was already lucky. But I work as a research analyst in the media industry and it couldnt be more cucked.

I do know basic SPSS from college training, but we dont use it here in my office so I get no chance to practice.

I was just thinking short term, though longer term I do want to get into data science but I've no background in this. Is it possible such a career move?

[polar]

Peregrine has a few good posts on this around here. Search around (I'm on mobile)

If you work with more than 10-20k rows of data regularly, especially in the same format every time, you really should pick up SQL. The reality is that Excel is good to edit a few cells at a time. If you're doing calculations and lookups with entire columns, SQL is often faster. You'll often start out in SQL, get the data into a good format, then paste into a template in Excel and get it into a finished format. Excel, VBA, SQL are in many ways a foundational toolkit of data analysis in the corporation world.

At the same time, my impression is that to be a "data scientist" you really need a master's in a math field. Or demonstrate fluent knowledge that is comparable.

A last thought is just because your job title is one thing, doesn't mean you can't call yourself a data analyst or whatever...assuming your resume job description fits. Hint, hint. But know the underlying stuff.

And don't forget about the relationships part of the job. Just because you work with numbers doesn't mean you shouldn't be on great terms with your coworkers and management.

[Pacesetter20]

I'm using Kubicle at the moment through my employer. Seems pretty good, not sure if it is at the level that OP is looking for though.

[kosko]

SPSS licence costs are rape, very few firms aside from mega institutions that can afford to light money on fire will use it; big reason why folks never touch it much after University.

Excel is practical and very powerful if you learn it on a advanced level. The truth is though as mentioned you can learn SQL or some other sort of language that will crush big data for you more quickly.

[cibo]

If you have no money.

Learn SQL.

If you want to do advanced stats on a budget.

Learn Python/R.

Honestly, Excel and SQL are more than sufficient for most data jobs. It's only where you get to the advanced stuff that you start using sas, python and the like.

[polar]

(10-25-2017 10:45 PM)kosko Wrote:  

SPSS licence costs are rape, very few firms aside from mega institutions that can afford to light money on fire will use it; big reason why folks never touch it much after University.

Excel is practical and very powerful if you learn it on a advanced level. The truth is though as mentioned you can learn SQL or some other sort of language that will crush big data for you more quickly.

As someone who knows Excel well, I wouldn't trust it with more than 20k rows of data when you're dealing with dozens of columns and lots of formulas. You'll have long load times and formula issues you don't realize you're having with your lookups (such as lookup formulas skipping cells). Luckily, lookups are easy as pie with SQL...but you need to make sure your data formats are kosher (in part, as Excel likes to convert anything that looks like numbers into integers automatically, even if other values in a column are alphanumeric or have leading zeros).

Pivot tables and charts are basically SQL lite, and are great for quickly manipulating and presenting data.

Part of learning this stuff is knowing what tool is right for which job. If you're dealing with entire columns, that's a job for SQL. If you're summarizing data or manipulating small numbers of data points or cells with formulas, that's Excel. So you start with manipulating the raw data in SQL, then move to Excel to model and analyze, and finally make your analysis readable and pretty for your boss.