2017 in review and a look forward to 2018

Happy New Year to you all!

spacex launch

Max Q‘ is a term used in rocketry to denote the moment during a rocket flight when aerodynamic stress on the rocket’s airframe is at it’s maximum. It’s one of the early milestones of any flight, and one of the most dangerous to boot. Astronauts and engineers both breathe a sigh of relief when the rocket passes this point. Engines, which have been throttled back to 60 – 70% of capacity during this phase, are once again opened up and the great fire-breathing, bone-rattling tin can accelerates rapidly upwards again, on its way to space.

Well, the summer of 2017 felt like ‘max Q’ for our family. We had a second baby boy in late May (he’s brilliant!), relocated from DC to Florida in early July, sold a house and bought a house, prepared for our first hurricane in August (that was stressful!) and had a to-do list longer than our arm, in fact, all our arms combined.

Through this, I did what I could, when I could, to keep the ball rolling with my business. I launched my second course, Data Cleaning and Pivot Tables in Google Sheets, in June, although almost all of the work was completed prior to my son’s arrival in May.

Since October, the throttle has been fully open again on the business and things are moving along nicely. I launched my third course, Advanced Formulas 30 Day Challenge, in mid December, and had over 1k students sign up in the first week.

2017 highlights

Continue reading 2017 in review and a look forward to 2018

Data Studio Connector fields get an update

The engineers at Google continue to roll out updates to Data Studio at a relentless and impressive pace.

Back in September they released community connectors, which allow you to connect any web service with an API to Data Studio for reporting.

Yesterday, they released an update which gives developers much more control over the connector fields in Data Studio, by allowing developers to define Data Types and Semantic Types with more granularity, in your Apps Script code.

Additionally, developers can now embed calculated fields into the connector’s schema too, so it’s not left up to the user to figure this out.

This is a huge improvement as it obviates the need for the end user to select the correct field settings (for example, which aggregation to use) and should therefore make it easier for users to build accurate reports.

Data Type and Semantic Type features in the connector fields

Continue reading Data Studio Connector fields get an update

Introducing Advanced Formulas 30 Day Challenge – My new, free Google Sheets Course

I’ve just launched Advanced Formulas 30 Day Challenge, my latest course.

Advanced Formulas 30 Day Challenge

It’s 30 bite-size tutorials, each focusing on an advanced formula or technique.

If you feel like your spreadsheet knowledge has plateaued after the VLOOKUP, then this is the course for you. You’ll learn everything from classics like the INDEX-MATCH through to more exotic advanced formulas like SUMPRODUCT or MMULT.

Best of all, it’s free! That’s right, this one’s on the house. ๐Ÿ˜€

Start today, and up your spreadsheet game over the next 30 days.

Check out Advanced Formulas 30 Day Challenge here.

Running Total Array Formulas (using the MMULT function)

In this post we’ll look at how to calculate a running total, using a standard method and an array formula method. We’ll cover the topic of matrix multiplication (take a deep breath, it’s going to be ok!) using the MMULT formula, one of the more exotic, and challenging formulas in Google Sheets.

If you like video tutorials, here’s the one on MMULT:

This is a lesson from my latest, Google Sheets course on Advanced Formulas 30 Day Challenge (it’s free!).

Continue reading Running Total Array Formulas (using the MMULT function)

10 techniques to use when building budget templates in Google Sheets

It probably won’t surprise you to hear that I use Google Sheets to build financial/budget templates and track my incomings and outgoing, both at home and for my business.

The dashboards available through online banking sites are pretty rudimentary and don’t give me much insight into what’s happening with my finances, particularly over longer time frames.

I like using Google Sheets, as opposed to another third party service like Mint, because it’s fully customizable, it’s easy to use and I can share any spending or budget templates easily with my wife.

Credit Card budget template with Tiller

I’m not a financial expert, so I won’t be dispensing any financial advice here. I won’t opine on what you should or shouldn’t show in your spending and budget templates in this post, nor will I talk about what your financial goals should be or how to get there.

What I will do in this post however, is show you some useful techniques in Google Sheets that you can use for building your own budget templates. Techniques to make them more insightful and more helpful for reaching your goals. They are:

  1. Why are you doing this?
  2. Invest time little and often
  3. Get basic formulas dialed
  4. Leverage power of more advanced formulas
  5. Don’t reinvent the wheel! Google has specific financial formulas
  6. Use comments to record specific details
  7. Get all your financial data into your Google Sheet with Tiller
  8. Set budgets and highlight spend over the budgeted amount
  9. Build drop-down menus to show different categories in your reports
  10. Use words!

Continue reading 10 techniques to use when building budget templates in Google Sheets