Top products from r/excel

First, thank you for the taking the time to respond to my question - I really appreciate it. Second, congratulations on receiving the MCSA: BI Reporting badge!

The reason I am struggling to prepare for this exam is because there is no real comprehensive prep course like there is for the CPA exam. There's no "Becker for MCSA: BI Reporting". Can you share how you went about preparing for these exams? I feel like I have learned a lot from edX but not enough to pass the exam and I cannot find any additional practice questions/tests to study - anywhere. Do you have any insight on this?

Yes, the edX Excel course was very good and has significantly improved my Excel skills. However I am still unsure what to expect as far as test questions are concerned. Are they similar to the ones on the edX course?

As far as DAX and M, I assume DAX will be more heavily tested on the Power BI exam then the Excel one. Would you say that's correct? At the moment, my DAX skills are limited because I haven't been able to sit down and really run through it yet - but I will do so. I am waiting for my DAX book to come in the mail. M doesn't seem that difficult but nonetheless I have to run through it.

Again, thank you for your insight - I really appreciate it!

Let's look at it from another angle. What are you going to be doing with Excel?

My experience is that it's all about the Data Process. You have to clean it up. Check the minimum values, maximum values, date ranges, see that different fields are what you want. Half your SSNs are text and half are numbers? How do you deal with that?

Do you have all the information needed for the statistical analysis you want to do?

Your client claims this list of people have been paid out this much. Here's a list of actual benefit payments from their bank trust. Compare them and explain the differences.

For added fun, the bank trust gave you benefit payment information as pdf files. Turn them into excel and find some way to connect them to each participant.

May be worthwhile to simply research the data process and build from there. Much of the actual learning comes from working with others and paying attention to the tricks and hotkeys they're using. Every single candidate puts 'proficient in Excel' on their resume. Telling a story about something done with it is usually better.

Hell. Research Beersheets for fantasy football, rip it apart and see how it ticks. Apply lessons learned to another sport.

Burning through this there are certain things you run into often.

VLOOKUPS. COUNTIF. Filters. DATE. TEXT. MATCH. Grouping. VALUE. General practices such as color-code inputs. Center Across Area rather than Merge Cells.

alt+e+s in sequence to bring up special paste options. (alt+e+s+v for paste values and alt+e+s+t for paste formats are super common. paste transpose exists.)

There's got to be some online site to offer services for pay. I'd be shocked if there's not someplace to offer data cleanup.

Really want to go to the next level? Dive into VBA. The go to is Excel 2010 Power Programming with VBA. Read through a section, do all the examples, come back then try to do them all again without guidance. This gives insight into a lot of what goes on behind the scenes in Excel, teaches many hotkeys you wouldn't otherwise pick up on. The moment I discovered how to access the Immediate window (ctrl+G) then learned to throw a Print command or two into my coding to test values in the VBA editor was the key moment I connected my programming in VBA to what I had done in R, Matlab, C, or Python.

In college is an excellent time, because you have time. It doesn't seem like it now, but time is hard to find afterward as well.

I hear ya...starting out can be a bit intimidating at first and all the math can feel like you're reading Greek!

In terms of my posts, I would recommend this one (which I haven't posted to Reddit) which explains how to build a neural net and it explains how the "learning" behind "machine learning" works. Specifically, it explains the concepts of gradient descent and backpropagation which are both used to tweak "parameters/weights" when training an algorithm to generate better predictions. These are the heart of most "deep learning/neural net" algorithms. I admit though that this is still math heavy and my posts may not be the right approach for you depending on your comfort level.

Another approach to consider is to check out the book 'Data Smart' by Jon Foreman. If you don't have a coding background, this is a great place to get started (this is how I got started). He introduces a number of friendly easy-to-follow machine learning examples (all in spreadsheets) and the algorithms he covers are easier for beginners.

If you have a coding background, Andrew Ng's Coursera course on machine learning is far and away the most popular course out there.

Ultimately, it depends on what your learning goals are and what you're interested in...

Hope this helps.

As long as you understand that VBA would be less efficient, and you'd be doing it for the experience.

I like to get fully immersed in topics so I'd recommend this book. It helped me get a solid introduction of most topics without getting too complex. You can get by with just googling functions but long term you'll want a more thorough introduction either with this book or an online resource. Also the contextuals (spelling?) and mrexcel sites are good iirc. And definitely stackoverflow.

With that being said, this is a pretty straight forward script, so it shouldn't give you that much trouble. If you have trouble feel free to post your sample script and I can make some suggestions. The best method to learning VBA is to just go at it and see what work. Google and stackoverflow are your friend.

Here is an image of something I threw together that could work for presenting data across two distinct groups, as you are describing. It isn't a pie chart, although you could use the same philosophy of creating two colors for your series and using labels to differentiate each of the boys and girls. You could also do an exploded pie, but I'm not a big fan of them (to each his own, though). I based this build off of a blog post on BaconBits, an Excel blog by the author of Excel Dashboards (among other books). The process is a bit cumbersome, but it can create some cool looking chart effects for dashboards. The post explaining the process can is found here. Here is the spreadsheet if you want to explore it. Good luck.

I don't have the direct link on hand. But edx.org has a free one that I found useful.

Edit: I purchased this book and it was incredibly helpful. Worth the purchase:

https://www.amazon.com/Power-Pivot-BI-Excel-2010-2016/dp/1615470395

yeah that's a bit more advanced than just reading up on some functions like /u/aristite said.

I am guessing that's not the kind of stuff they'll want you to do in the interview session, 60 minute is a short amount of time once you start working with bigger/more advanced datasets.

I would go with the stuff already mentioned in this thread, + array formulas, and for the more advanced statistical/analytical methods (monte carlo simulations etc.) and how to do them in excel, have a look at this book:

http://www.amazon.com/Data-Smart-Science-Transform-Information/dp/111866146X

I'm not familiar with Business.com, however I am using Lynda.com, Pluralsight.com and MOS 2013 Study Guide for Microsoft Excel to study for my exam. The websites offer training for a plethora of subjects, as to not contradict my previous point of overspending on training. There are a lot of YouTube tutorials that can tutor you through from beginner to advanced Excel, however I prefer to learn from accredited and verified instructors who keep their videos short and to the point.

I don't know how to hyperlink so here is the link to the study guide ($13): http://www.amazon.com/2013-Study-Guide-Microsoft-Excel/dp/0735669201

There won't be any mandatory books for the course, but I will put on the syllabus that a good reference is John Walkenbach's "Excel 2013 Power Programming with VBA". I think this book is better for people who already have some experience in VBA since it is very thick an intimidating to the beginning.

http://www.amazon.com/Excel-2013-Power-Programming-VBA/dp/1118490398

If you want to learn a lot about PowerPivot and (don't mind reading) I'd recommend anything written by Alberto Ferrari and Marco Russo. They write on PowerPivot / SSAS / Power BI for the Microsoft Press. One of their books was recommended by Michael Alexander who's a Microsoft MVP (I think for Access, but he also knows Excel very, very well.) Take a look at a few of the books below:

https://www.amazon.com/Definitive-Guide-DAX-intelligence-Microsoft/dp/073569835X/ref=asap_bc?ie=UTF8

https://www.amazon.com/Microsoft-Building-Models-PowerPivot-Business/dp/0735676348/ref=asap_bc?ie=UTF8

I only read a bit of their 2013 book, but it's very comprehensive and of high quality.

I also want to discuss a few other things mentioned here in the comments: PowerPivotPro by Rob Collie and SQLBi. Rob Collie is a former Microsoft engineer on Excel, is an expert on it, and still talks to many engineers on the Excel team. And SQL Bi is run by Marco Russo, who I mentioned above.

It's designed so you could hand it to someone who's never used excel and to take them up to having a grasp on VBA and some advanced formulas like array formulas. I haven't picked it up in about a year it doesn't sound like it would be a massive learning jump for yourself.

When it comes to charts/reports etc there are actually 2 books that I'm still deciding if I should buy or not. Both have been recomended to myself but I'm waiting until payday before buying them myself so can't testify to how good they actually are.

http://www.amazon.co.uk/dp/1118519655/ref=wl_it_dp_o_pC_S_ttl?_encoding=UTF8&colid=26FRQZR9FPSJT&coliid=I2TLB44KQWBWAS

http://www.amazon.co.uk/dp/1118490428/ref=wl_it_dp_o_pC_nS_ttl?_encoding=UTF8&colid=26FRQZR9FPSJT&coliid=I3LYA71RKLXI19

I highly recommend Excel 2013 Power Programming with VBA. Newer versions have a different author, who I'm not familiar with. Walkenbach was pretty damn good with what he covered and how he covered it.

I really liked Excel Dashboards and Reports by Michael Alexander and John Walkenbach. I have a little Excel library at my desk and every time I reach for that one I have to track it down because it keeps getting borrowed.

I'm considering these two. Leaning more towards the second one based on reviews and me also needing to understand the PowerPoint parts of VBA.

But if anyone has any experience with either and can vouch for the quality of one or both I'd love to hear it.

VB & VBA in a Nutshell: The Language (In a Nutshell (O'Reilly)) https://www.amazon.com/dp/1565923588/ref=cm_sw_r_awd_8PhJwbEJ15WWZ

Mastering VBA for Office 2010 https://www.amazon.com/dp/0470634006/ref=cm_sw_r_awd_qShJwbKDE6RS6

Take a look at this link, gives you a guide on how to use VBA to create a pivot table. I can walk you through any steps you are not sure about if you wish.

This is the one I'm using and it's pretty decent.

This book is really helpful and always handy to keep on your desk.

Professional Excel Development by Bovey provides step-by-step instructions to build a time keeping program. The CD that comes with it has the final product.

I would highly recommend power programming with vba. Exceptional reference. http://www.amazon.com/gp/aw/d/0470475358/ref=s9_simh_gw_d0_g14_i1?pf_rd_m=ATVPDKIKX0DER&pf_rd_s=mobile-1&pf_rd_r=09S70XW4XDWY2BC2JT9K&pf_rd_t=36701&pf_rd_p=2068141862&pf_rd_i=mobile.

personally, i've really enjoyed Power Programming with VBA by John Walkenbach. It is very elementary, with lots of examples and a disc included. If you are a total noob (like me) and want to speed up repetitive BS at work, this isn't a bad one to pick up.

To learn SQL, start with this book:
http://www.amazon.com/Sams-Teach-Yourself-Minutes-Edition/dp/0672336073/ref=sr_1_1?ie=UTF8&qid=1407993921&sr=8-1&keywords=sql+in+10+minutes

Then once you need more ideas with SQL, go to this book:
http://www.amazon.com/gp/product/0596009763/ref=oh_aui_detailpage_o05_s00?ie=UTF8&psc=1

For Python, I would start with this book:
http://www.amazon.com/Python-Programming-Introduction-Computer-Science/dp/1590282418/ref=sr_1_1?s=books&ie=UTF8&qid=1407994104&sr=1-1&keywords=python+programming

SQL may come almost naturally to you. For me at least, the basics of SQL came rather easily. With Python, expect to be a little lost, not with the programming concepts but with setting up your computer and getting Python packages installed, etc... Once you get all that done though, you will be golden.

Just change to the Amazon Australia domain; try this: https://www.amazon.com.au/dp/B07JV9ZJGM

https://www.amazon.co.uk/Information-Dashboard-Design-Effective-Communication/dp/0596100167

This book.

For anyone in the UK;

https://www.amazon.co.uk/Excel-VBA-Programming-Step-Step-ebook/dp/B07JV9ZJGM/

https://www.amazon.com/HP-D9Y32AA-UltraSlim-Docking-Station/dp/B07JVBPJDT/ref=asc_df_B07JVBPJDT/?tag=hyprod-20&linkCode=df0&hvadid=309707619534&hvpos=1o2&hvnetw=g&hvrand=11226807286134430589&hvpone=&hvptwo=&hvqmt=&hvdev=c&hvdvcmdl=&hvlocint=&hvlocphy=9052282&hvtargid=pla-571235472711&psc=1

I cannot recommend this book enough for someone starting out with Pivot Tables.

Rob Collie and Bill Jelen's books basically cover the entire thing and are very approachable (they're basically written for Excel power users):

PowerPivot and PowerBI

PowerPivot Alchemy