Best products from r/excel

Okay, I came across this trick while reading Excel Gurus Gone Wild. Great book written by Mr Excel,with some neat solutions to some challenging Excel issues. Anyway, a question I routinely see asked (and apparently is routinely asked in the Mr. Excel Forums) is how do I get the interior color or can I conditionally format based on color. This trick uses an arcane function, that can't be used in a regular cell based formula, but can be used in the Named Formula (or Name Manager) box. /u/takin_it_sleazy threw up some great code the other day for pulling it in via a custom UDF in VBA, found here. Anyway, I know many people try to avoid VBA for a variety of reasons, and so I thought this was a cool approach. Don't know how well it works, it may be buggy, but I did get it to work when I tried, as shown in the GIF. The named formula = GET.CELL(63,INDIRECT("RC",0)) The 63 references the interior cell fill color. The Indirect("RC",0) basically tells Excel to look in the same cell the formula is in, which works well for conditional formatting. For some reason, I think this may mute the color codes to either one of the older version of Excel's limitations, so 65 or 256, if memory serves. I just added the 4 cells with the GetFillColor formula to see what the codes the function picked up were. Note that the GET.CELL function returns a whole ton of info about the cell, hard to find on the web, but I did find it here. Obviously, it would be great to be able to use these without creating the named formulas, but it doesn't seem to be possible. Anyway, I thought it was cool enough that you might be interested to see it, give it a try. Good luck.

I've been using Excel since the day it came out. I've always used it casually as a classic spreadsheet tool. I didn't use it much for a few years but recently came back to it for job-related reasons. It has evolved into quite something. The current version (2019/O365) is a numerical wonder. Its data analysis tools/features are fantastic. As others have commented, the query, data modeling, data transformation and visualisation features are eye-popping. It's data science for the masses. I assume this is no accident. Microsoft realises the data revolution that is coming and not everyone wants to (or can) learn R or Python.

It goes without saying that you need to learn the basics before progressing to the more advanced stuff. But the more advanced stuff isn't very hard to learn and, as Nyct0phile pointed out, you can appear to be a data magician using some of the relatively easy to use tools. Youtube is good for the basics and an introduction to analytics.

One other thing. It really is helpful if you actually understand what Excel is doing (and you're not simply invoking commands). This book is a really great introduction to statistics, data analytics and machine learning. https://www.amazon.co.uk/gp/product/0241398630/ref=ppx_yo_dt_b_asin_title_o01_s00?ie=UTF8&psc=1

If you learn to use Excel's data analysis tools and read this book you too will be able to say: "It's sometimes better to apply a simple linear regression model for predictive analytics than to over-fit a complex Machine Learning algorithm".

This doesn’t answer your exact questions but I couldn’t recommend this book more: Storytelling with Data - it’s an excellent primer on what makes a good (or bad) data visualization. This book provides tons of dos and don’ts to help you think about how and when to use different types of charts (be forewarned she hates pie charts though) as well as providing before and after examples as inspiration on how to make visualizations better.

Storytelling with Data: A Data Visualization Guide for Business Professionals https://www.amazon.com/dp/1119002257/ref=cm_sw_r_cp_api_i_qMFQCbDCYBFGC

Another book to consider that’s more specific to Excel (still gives data viz tips but it’s more how to do this in excel technical) is Data Visualization & Presentation with Microsoft Office - this book was a little basic for my needs but still a good resource.

Data Visualization & Presentation With Microsoft Office https://www.amazon.com/dp/1483365158/ref=cm_sw_r_cp_api_i_vPFQCbQZQ5876

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 got the book M is for Data Monkey, but honestly I just picked easy projects and googled and looked in this subreddit as I went. Excel is Fun on youtube is always a good source. This dude is also awesome and so entertaining.

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.

Totally agree with this. /r/MechanicalKeyboards has a lot of great resources and will help you pick the perfect keyboard.

I personally have the Corsair Vengeance K95 with Cherry Reds and it's phenomenal. They advertise it towards gamers, but it's great for typing.

The one thing about mechanical keyboards is that they can be loud, so be sure to take that into consideration which choosing which types of switches you want if you'll be in an office setting.

For a mouse, I use a nice Gigabyte gaming mouse that has a ton of customization features. It feels right, has adjustable weights, multiple different surfaces for the pads, forward and back buttons on each side, and a switch to easily change the sensitivity on the fly.

I did notice that you asked for wireless mice/keyboard recommendations, and while neither of these are, I preferred wired over wireless. Won't have to worry about batteries, no connectivity issues, and they're often cheaper than their wireless equivalents. Some keyboards also include a USB port allowing you to connect your mouse to your keyboard and then just have one cable running to your computer.

This is what I use:

https://www.amazon.com/gp/product/B01LVZU9B0/ref=oh_aui_detailpage_o05_s00?ie=UTF8&psc=1

It's not the best keyboard in the world, but it is very quiet, has elevated keys which I love, and feels wonderful (and is very cheap). When I feel like splurging I plan on getting the Razor Blackwidow Silent (yellow) edition, because I love the feel of mechanical. I have the non-silent variant at home and I type SOOO much faster and more accurately.

Personal opinion, I absolutely positively undeniably HATE that keyboard you posted. I can't deal with the thin keys and hate the way they press down too. I even like laptop keyboards more. But to each his own!

I would recommend that you do it the old fashioned way. Pick up a good book on the subject. In my opinion, a good book will be generally be far more thorough going than a video tutorial and have good examples that you can really study. The one by Bill Jelen (Mr. Excel) is excellent: Microsoft Excel 2019 VBA and Macros

I started learning SQL by reading Itzik Ben-Gan's T-SQL Fundamentals. It's a fantastic text that I read cover to cover. One of the chapters on ACID was extremely boring (that's mostly DBA stuff) but other than that I thought it was very interesting. Microsoft also has an EdX course that's similar to the book if you'd prefer to use that.

As far as applying it goes, I set up multiple databases. So I had SQL Server, postgres, Access, and SQLite. I didn't get to apply it at my last job, but our DBA was comfortable enough with my knowledge to create a schema for me so that I could use postgres instead of having to use MS Access. I also personally found that employers were fairly impressed by it.

Do you know of any good books (or websites) for learning practical applications and techniques for financial analysis?

I've been looking at this one but the reviews are mixed.

I am already an expert in Excel. I am looking to get a Financial Analyst job, and want to improve my ability to do the analysis.

As somebody who recently did exactly what you are aspiring to do, there are a few resources I would recommend.

• Macabacus - learn everything on this site. Download the models, use them, learn how they work.
  
• Breaking Into Wallstreet - same with the above.
  
  Learn everything you can about the three financial statements, how they flow into each other, and how to forecast each major line item. Any solid investment banking book will help with a lot of this, I recommend this one.
  
  VBA is nice, but not necessary. I'd much more recommend knowing how to answer:"If I have 100 million in EBITDA and I subtract 10 million from Depreciation and Amortization, how does that impact Net Income, what changes in your balance sheet, and how does that flow through to the cash flow statement?"
  
  But even more importantly in banking is your personality and attitude. You're being interviewed mostly to see if your coworkers can tolerate you for 60+ hours a week, as they're more likely to spend more time with you at work than they are at home with their families.

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

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

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've used a G610 Brown for about 2 years now, and it's awesome.

​

I like that it's not RGB. It's a joy to type on, and goes well with my MX Master

​

https://www.amazon.com/Logitech-Backlit-Mechanical-Gaming-Keyboard/dp/B01CDYB8F6

​

​

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.

Been 3 months in preparing, thought it would be much less since i had extended (in my not so humble view) knowledge of Excel.

​

90% finished, only powerpivot, custom number formats and cube functions left.

​

Ive been reading the official literature, https://www.amazon.com/Study-Guide-Microsoft-Excel-Expert-ebook/dp/B01MXG0UYU/ref=sr_1_1?keywords=excel+728&qid=1562091613&s=gateway&sr=8-1

​

And Udemy guide, https://www.udemy.com/microsoft-excel-certification-mos-excel-expert-exam-77-728/

http://www.amazon.co.uk/Excel-Power-Programming-Spreadsheets-Bookshelf/dp/1118490398/ref=sr_1_1?s=books&ie=UTF8&qid=1412752286&sr=1-1&keywords=john+walkenbach

Excel 2013 Power Programming with VBA

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.