The best Spreadsheet Books: Reddit reviews

From 3.5 billion Reddit comments

Excel 2003 Power Programming with VBA. Currently $4.94 on Amazon, though you might consider the latest edition. Got this when I was an undergrad, taught myself VBA and became an Excel "power user". Knowing VBA has been infinitely useful in my career and having it on my resume (along with projects and tools I've created with it) landed me my current job even though I wasn't even in the market at the time!

The Definitive Guide to DAX

Edit: just noticed that Marco finally published the second edition 3 days ago!!!!!!!!

Edit2: u/itsnotaboutthecell warns that we should be careful trusting Amazon’s info.

Couple good resources out there. Workout Wednesdays have good Power BI challenges. The first 4 build on each other which was pretty neat but I dropped off since.

The Definitive Guide to DAX is like the new analysts Bible. Lots of great material to better understand filter and row contexts. This will help write DAX like a programmer.

SQLBI (same guys that wrote Definitive Guide to DAX) have a bunch of free tutorials on data modeling scenarios. I haven't taken the DA-100 but if you want to go beyond the basic star schema and learn a few real world examples of what to do when you have two fact tables and when is the right time to make a second date dimension, I highly recommend their free courses.

I have a few other resources I like for Power BI but no others that offer challenges like you asked for. Good luck! Let me know how it goes!

If you're making a career in finance, as opposed to in Tech, then Python and Excel are both great ways to go. Per SteamPunkMoonz's excellent comment, Python actually could be better.

If you do go the VBA route, this book is great: https://www.amazon.com/Excel-2013-Power-Programming-VBA/dp/1118490398

If you go the Python route... then I highly recommend that you: 1) Download Anaconda (https://www.anaconda.com/) 2) Learn how to use Jupyter Notebook (comes with Anaconda) 3) Use Jupyter to learn how to use Pandas (also comes with Anaconda)

If you need more details, just ask... happy to help!

Highly recommend Power Pivot and Power BI by Rob Collie and Avi Singh. It helped me a lot with understanding both data models and DAX.

They use AdventureWorks DB so you can practice as you go.

Read the Bible and realize you know nothing.

https://smile.amazon.com/Definitive-Guide-DAX-intelligence-Microsoft/dp/1509306978/ref=sr_1_2?dchild=1&keywords=Dax&qid=1608323759&sr=8-2

Your question is a little vague, but if you’re asking how to create magic in Power BI without being taught how to use a spreadsheet, this is it. It’s not easy, you may realize you know less than you think.

In case anyone else is also interested, I found this book to be a fantastic resource. It teaches Excel, but more it actually teaches how to create professional workbooks: https://www.amazon.co.uk/Professional-Excel-Development-Applications-Addison-Wesley-dp-0321508793/dp/0321508793/ref=dp_ob_title_bk

I don't get to use Excel much, these days, but this book plus some prior Excel experience was extremely valuable for solving general business problems.

Sure ;)

The Definitive Guide to DAX: Business intelligence for Microsoft Power BI, SQL Server Analysis Services, and Excel Second Edition (Business Skills) https://www.amazon.co.uk/dp/1509306978/ref=cm_sw_r_apan_i_24XCBS2R51MTGVET2RKP

Power Query and Power Pivot will make you a stronger analyst than 90% out there (that primarily use excel at least)

Learning how to model data in Power Pivot and basic DAX functionality will make your pivot table and chart game on another level. It would also transfer to Power BI.

No more of that formula outside of an Intermediate pivot table shit.

Highly recommend this book as it changed the way I use Excel for the betters day forever and while I’ve always been the go-to excel guy (on to more powerful tools now), it blows people’s minds all the time.

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

I should add specific to your original question — a great resource and guidance for data analysis using sql and excel is the book by the same title. https://www.amazon.com/Data-Analysis-Using-SQL-Excel/dp/111902143X

Marco Russo’s book brought me from average PBI dev to team lead … if you thought DAX was rough before, wait until you find out about all the implicit bullshit under the hood!

https://www.amazon.ca/Definitive-Guide-DAX-intelligence-Microsoft/dp/073569835X

Deal: The Definitive Guide to DAX: Business intelligence for Microsoft Power BI, SQL Server Analysis Services, and Excel Second Edition (Business Skills) https://www.amazon.co.uk/dp/1509306978/ref=cm_sw_r_apan_glt_fabc_dl_QVNW51QKHSFFT1N9KWPV

^ to master DAX. Not just be proficient at it, but to master it. It's the single best resource available.

You'll also want to read up on Power Query as there's many times where you want to transform your data before loading it. You don't need to master Power Query to the same extent as DAX though. A lot of what you need is baked into the Query Editor GUI.

I’m in finance looking to better understand Power BI and after searching I’m starting with these two books:

The Definitive Guide to DAX: Business Intelligence for Microsoft Power BI, SQL Server Analysis Services, and Excel Second Edition (Business Skills) https://www.amazon.com/dp/1509306978/ref=cm_sw_r_cp_api_glt_fabc_HRM563JVHWKMVG8GZCVN

M Is for (Data) Monkey: A Guide to the M Language in Excel Power Query https://www.amazon.com/dp/1615470344/ref=cm_sw_r_cp_api_glt_fabc_QKSF3EWG49AKZ3Z6ZRM0

I’m starting with the DAX books first as we have some datasets that I could start working with already, so it would give me the more immediate benefit. Then I think I’ll move onto M. I’d also like to learn SQL. Maybe Python eventually.

Thanks. I assume you're referring to this one?

https://www.amazon.com/Definitive-Guide-DAX-intelligence-Microsoft/dp/1509306978/ref=sr_1_1?crid=3SV4SWWDVG3TV&dchild=1&keywords=the+definitive+guide+to+dax&qid=1631818620&sprefix=the+definitive+%2Caps%2C194&sr=8-1

Switch true is just a fancy way to write nested if statements. If you just want "What is the difference in date between these values?" then you can just use datediff.

Dax and m are both based on functions. Functions have zero or more parameters and return a value. Braces [ ] usually represent an array of data or in Dax, they represent a column of data. Ex [1,2,3,4] Brackets { } usually represent key:value objects ex: {“value”: 1, “something else”: 2}.

M is a very different from Dax. It’s important to note that after the power query stage, data is stored in memory in a columnar format. So column ‘a’ is a vector of data in the format of that column (integer, float, w/e). This is a very efficient storage method to run fast real time calculations and Dax operates on the data in this columnar way. This requires you to think about it differently than querying a sql database or writing formulas in excel. Measures will also be effected by contractual filters. If you throw data into a table, the engine is basically filtering the data by each category in your table and then applying the measure logic.

M on the other hand, does not work like this. It is a row by row way of applying changes and functions to the data. It’s much slower, but more similar to sql.

This book has a lot of simple concepts and patterns: https://www.amazon.com/Power-Pivot-BI-Excel-2010-2016/dp/1615470395/ref=mp_s_a_1_1?dchild=1&keywords=power+pivot+rob+collie&qid=1615398200&sprefix=power+pivot+rob+coll&sr=8-1

More info: https://docs.microsoft.com/en-us/dax/dax-syntax-reference

I took a live power bi class with the author of this book. He's amazing. I refer to his book from time to time with specific questions when I build dashboards. One of these days, I should go through all the pages of this book - I'm sure I'll pick up more tips along the way.

Realize that what you'll mainly study is the engine of PowerBI dashboards, which is driven by DAX/PowerQuery.

Here's some content in the book, which I learned in 2 days (16 hrs):

It includes lessons on the difference between calculated columns and measures; how formulas can be reused across reports of completely different shapes; how to merge disjointed sets of data into unified reports; how to make certain columns in a pivot behave as if the pivot were filtered while other columns do not; and how to create time-intelligent calculations in pivot tables such as “Year over Year” and “Moving Averages” whether they use a standard, fiscal, or a complete custom calendar.

​

If your work would sponsor, take the P3 live training class. You'll learn much faster with an instructor!

It looks like there is a second edition of the book on Amazon but it is 1-3 months from delivery? Is the 1st edition enough?

The Definitive Guide to DAX: Business intelligence with Microsoft Excel, SQL Server Analysis Services, and Power BI (Business Skills) https://www.amazon.com/dp/073569835X/ref=cm_sw_r_cp_api_i_pIrECbK2S62TG

The Definitive Guide to DAX: Business intelligence with Microsoft Excel, SQL Server Analysis Services, and Power BI (2nd Edition) (Business Skills) https://www.amazon.com/dp/1509306978/ref=cm_sw_r_cp_api_i_nGrECb8VXVB3R

I'd go straight to a cloud/big data database for analytics if I were you (i'm a senior engineer at a national bank, using AWS)

Consider PrestoDB, Spark, Hive or Redshift

This book has some interesting analytics / data mining applications in pure SQL: https://www.amazon.ca/Data-Analysis-Using-SQL-Excel-dp-111902143X/dp/111902143X/ref=dp_ob_image_bk

I used that book in a course I taught

Might be a bit advanced for you, however

Any suggestions for Excel learning resources? I recently purchased this book, which looks great. I'd love some suggestions of resources that complement this book.

My background, for context: I'm a Python developer -- I do most of my data wrangling & ML work on top of pandas (& numpy).

You can do without learning in most cases but in time you have to learn. Also you'll learn the logic behind your clicks so it's a good thing to learn. I recommend two books

Gil Raviv's

and

Ken Puls's

In addition to M is for Data Monkey, there's Collect, Combine, and Transform Data using Power Query. I have it and it's really good.

Both of these books will teach you how to use Power Query through the GUI, with some focus on the language. If you want to really learn the ins and outs of the language, the best resources are still probably all online, including the official Microsoft language specification. I guess you could print the official language docs off and have them tape or spiral bound for pretty cheap as well.

Yes, start with VBA for dummies :-)

https://www.amazon.com/Excel-Programming-Dummies-John-Walkenbach/dp/1118490371

Look at the related books on amazon to this book, then you have other books also.

Anything by John Walkenbach if you're looking for a textbook. 2013 power programming is an excellent resource if you're willing to pour through it. Only $40 new on Amazon.

https://www.amazon.com/dp/1118490398/ref=cm_sw_r_apa_i_8kuEEb43XQB3B

And their latest book: The Definitive Guide to DAX

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!

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.

Excel VBA Programming for Dummies is where I started, and it covered everything I ended up needing. It's only $10 bucks, so there's minimal risk if you think it sucks ;)

http://www.amazon.com/Excel-VBA-Programming-For-Dummies/dp/1118490371

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

MS doesn't fuck around with their educational books.

If you're trying to learn VB itself, you don't need to fuck with VBA for Excel - just learn VB. But really don't learn VB because there's no reason to. You should start with C++ and graduate to C#. Fuck Java.

Have you explored converting the VBA to a COM add in?

Edit:

• Consider posting this over at /r/excel.

• Check out the book Professional Excel Development: http://www.amazon.com/Professional-Excel-Development-Definitive-Applications/dp/0321508793

I used the 2007 edition of Power Programming VBA

It has the normal "Hello World" type of introuduction, but has a great glossary for looking up specific terms/case scenarios. It also came with a disc that included workbooks with macro code, so you can see it in action. But it looks like the 2013 edition has gone with a web based host for the documents.

I used it to get up to speed with VBA, then used it as reference, and after about 1-2 years it's now collecting dust. However, it was definitely worth it to get off the ground and I can't recommend it enough.