All articles in 'Master Class' Category
Hiya folks… just a quick update on whats keeping me busy this year, with a bonus Excel tip at the end. Take a sip of your favorite brew and read on.Continue »
Do you work with accounting or finance data? You are going to love, no scratch that, adore Power Query. It can save you precious time, make you look like a hero in-front of clients and keep you sane.
In this masterclass, learn all about Power Query for accountants (and other kinds of finance people) & 5 tips.
Why Power Query?
Power Query is a data processing software. Using Power Query, you can create process driven programs (queries) to collect data, clean or reshape it, calculate things and publish final output as a table. Once you create a query in PQ, when the underlying data changes, you just refresh the query and brand new data will be fetched, cleaned and published for you.
For example, you can use Power Query to combine ledger files from various accounts to one master file with Power Query. No more VBA or manual copy pasting. Just one query and you are done.Continue »
I recently finished a long consulting gig with one of the government ministries in New Zealand. Guess what I was doing? HR Analytics and Reporting. In this post, I want to share my top 5 Excel tips for HR people, based on what I learned in the last 18 months.
Specifically, we will cover:
- Gathering and structuring Employee data in Excel
- How to use Power Query to collect data
- Polish / clean data in Power Query
- Bring cleaner data to Excel as refreshable table
- Answering questions about employees
- Using Excel formulas such as COUNTIFS, SUMIFS, AVERAGEIFS
- Pivot tables for data analysis
- Understanding the results quickly with conditional formatting
- Understanding pay gap
- Calculating gender pay gap
- Visualize pay gap
- Creating salary distribution charts
- Working with histogram charts in Excel 2016 / Office 365
- Making interactive charts
- Generating letters thru mail merge
- Calculating employee bonus based on bonus mapping logic
- Creating 100s of letters with a single click using Mail Merge + Word
Sounds interesting? Read on for details.Continue »
This is third episode of our Monthly Master Class.
In this one, you will learn how to get maps in to your Excel workbooks. Understand 5 key techniques for making maps based visualizations in Excel – from regular charts to cell grids to VBA to Power Maps, everything is covered in this intense Master Class.Continue »
This is second episode of our Monthly Master Class.
In this one, you will learn all about SUMPRODUCT. Know all about basics to advanced usage of this powerful & versatile formula in this 98 minute master class.Continue »
As announced earlier, here is the first one of our Master Classes. In this one, you will learn how to create advanced interactive charts in Excel by blending pivot tables, slicers, timelines, VBA, conditional formatting and charts. The final outcome is BEAUTIFUL!!! Monthly Master Class – Episode 1 – Trailer Check out a quick trailer of the […]Continue »